Pandas：如何向DataFrame追加多行及Binance API数据处理问题

Hey Brian, let's work through this issue step by step— I’ve dealt with similar Binance API data formatting headaches before, so here’s how to fix those 0 values, line break quirks, and set up your datetime index properly:

Binance’s Kline API returns data in a fixed array order, so mismatched column names are a common source of weird values like 0s. Make sure you’re mapping the raw response to the correct columns:

from binance.client import Client
import pandas as pd

# Initialize client (replace with your keys)
client = Client(api_key='YOUR_API_KEY', api_secret='YOUR_API_SECRET')

# Fetch historical klines
klines = client.get_klines(
    symbol='BTCUSDT',
    interval=Client.KLINE_INTERVAL_1HOUR,
    limit=100
)

# Map raw data to correct column names (critical step!)
df = pd.DataFrame(
    klines,
    columns=[
        'timestamp', 'open', 'high', 'low', 'close', 'volume',
        'close_time', 'quote_asset_volume', 'number_of_trades',
        'taker_buy_base', 'taker_buy_quote', 'ignore'
    ]
)

Most 0 values happen because numeric columns are stored as strings. Convert them to floats first, then filter out any legitimate (or erroneous) 0 entries:

# Convert all price/volume columns to float type
numeric_cols = ['open', 'high', 'low', 'close', 'volume', 'quote_asset_volume']
df[numeric_cols] = df[numeric_cols].astype(float)

# Filter out rows where close/volume are 0 (adjust if you expect valid 0s for low-liquidity pairs)
df = df[(df['close'] != 0) & (df['volume'] != 0)]

Line breaks usually come from hidden characters in raw data or pandas’ default display settings. Clean and adjust your output:

# Remove any hidden newline characters from the dataset
df = df.replace(r'\n|\r', '', regex=True)

# Disable auto-wrapping for pandas outputs
pd.set_option('display.expand_frame_repr', False)
pd.set_option('display.max_colwidth', None)

Convert the millisecond timestamp to a datetime object and set it as your index:

# Convert Binance's millisecond timestamp to datetime
df['timestamp'] = pd.to_datetime(df['timestamp'], unit='ms')

# Set timestamp as the index (rename to 'date' if preferred)
df.set_index('timestamp', inplace=True)
df.index.name = 'date'

Putting it all together:

from binance.client import Client
import pandas as pd

client = Client(api_key='YOUR_API_KEY', api_secret='YOUR_API_SECRET')

klines = client.get_klines(
    symbol='BTCUSDT',
    interval=Client.KLINE_INTERVAL_1HOUR,
    limit=100
)

df = pd.DataFrame(
    klines,
    columns=[
        'timestamp', 'open', 'high', 'low', 'close', 'volume',
        'close_time', 'quote_asset_volume', 'number_of_trades',
        'taker_buy_base', 'taker_buy_quote', 'ignore'
    ]
)

# Clean and format data
numeric_cols = ['open', 'high', 'low', 'close', 'volume', 'quote_asset_volume']
df[numeric_cols] = df[numeric_cols].astype(float)
df = df[(df['close'] != 0) & (df['volume'] != 0)]
df = df.replace(r'\n|\r', '', regex=True)

# Set datetime index
df['timestamp'] = pd.to_datetime(df['timestamp'], unit='ms')
df.set_index('timestamp', inplace=True)
df.index.name = 'date'

# Check the final output
print(df.head())

内容的提问来源于stack exchange，提问作者Brian F