如何将指定结构的JSON行情数据转换为含37列的DataFrame单行数据？

我明白你现在的困扰：你需要把嵌套的订单簿JSON转换成一行37列的DataFrame，但之前的代码只生成了6列，这是因为你只是将Bids和Asks的DataFrame按列拼接，却没有把每个挂单的字段（size、price、totalorder）按序号展开成独立的列。下面是完整的解决方案：

Step 1: 导入依赖并准备数据 #

首先确保你导入了必要的库，并把JSON数据转换成Python字典（注意转义引号的处理）：

import pandas as pd
from datetime import datetime

# 你的原始JSON数据转换为Python字典
data = {
    "MessageCode":1502, "MessageVersion":4, "ApplicationType":0, "TokenID":0, 
    "ExchangeSegment":1, "ExchangeInstrumentID":22, "ExchangeTimeStamp":1309954231, 
    "Bids":[
        {"Size":21,"Price":1999.15,"TotalOrders":2,"BuyBackMarketMaker":0}, 
        {"Size":20,"Price":1999.1,"TotalOrders":1,"BuyBackMarketMaker":0}, 
        {"Size":40,"Price":1999.05,"TotalOrders":1,"BuyBackMarketMaker":0}, 
        {"Size":185,"Price":1999,"TotalOrders":6,"BuyBackMarketMaker":0}, 
        {"Size":7,"Price":1998.8,"TotalOrders":2,"BuyBackMarketMaker":0}
    ], 
    "Asks":[
        {"Size":1,"Price":1999.8,"TotalOrders":1,"BuyBackMarketMaker":0}, 
        {"Size":3,"Price":1999.85,"TotalOrders":1,"BuyBackMarketMaker":0}, 
        {"Size":34,"Price":1999.9,"TotalOrders":2,"BuyBackMarketMaker":0}, 
        {"Size":199,"Price":2000,"TotalOrders":10,"BuyBackMarketMaker":0}, 
        {"Size":1,"Price":2000.05,"TotalOrders":1,"BuyBackMarketMaker":0}
    ], 
    "Touchline":{
        "BidInfo":{"Size":21,"Price":1999.15,"TotalOrders":2,"BuyBackMarketMaker":0}, 
        "AskInfo":{"Size":1,"Price":1999.8,"TotalOrders":1,"BuyBackMarketMaker":0}, 
        "LastTradedPrice":1999.9, "LastTradedQunatity":12, 
        "TotalBuyQuantity":145954, "TotalSellQuantity":81287, 
        "TotalTradedQuantity":114118, "AverageTradedPrice":1999.65, 
        "LastTradedTime":1309954224, "LastUpdateTime":1309954231, 
        "PercentChange":0.6365580576173091, "Open":1997.65, 
        "High":2006.6, "Low":1989.1, "Close":1987.25, 
        "TotalValueTraded":None, "BuyBackTotalBuy":0, "BuyBackTotalSell":0
    }, 
    "BookType":1, "XMarketType":1, "SequenceNumber":476310325663841
}

Step 2: 展开Bids和Asks为扁平列 #

我们需要把每个挂单的3个字段（Size、Price、TotalOrders）按序号（1-5）转换成独立的列，比如bid_size1、bid_price1等：

处理Bids #

# 转换Bids为DataFrame，并重命名TotalOrders字段
bids_df = pd.DataFrame(data['Bids']).rename(columns={'TotalOrders': 'totalorder'})
# 给每个挂单添加序号（1-5）作为索引
bids_df.index = range(1, len(bids_df)+1)
# 转置并生成目标列名
bids_flat = bids_df.unstack().reset_index()
bids_flat['column_name'] = 'bid_' + bids_flat['level_0'].str.lower() + bids_flat['level_1'].astype(str)
# 转换为单行DataFrame
bids_flat = bids_flat.set_index('column_name')[0].to_frame().T

处理Asks #

用同样的逻辑处理Asks：

asks_df = pd.DataFrame(data['Asks']).rename(columns={'TotalOrders': 'totalorder'})
asks_df.index = range(1, len(asks_df)+1)
asks_flat = asks_df.unstack().reset_index()
asks_flat['column_name'] = 'ask_' + asks_flat['level_0'].str.lower() + asks_flat['level_1'].astype(str)
asks_flat = asks_flat.set_index('column_name')[0].to_frame().T

Step 3: 处理其他要求的列 #

处理日期、时间转换以及Touchline中的指定字段：

# 生成当前日期（如果只需要日的话可以用datetime.now().day）
current_date = datetime.now().strftime('%Y-%m-%d')
# 转换ExchangeTimeStamp为时间格式
exchange_time = datetime.fromtimestamp(data['ExchangeTimeStamp']).strftime('%H:%M:%S')
# 转换Touchline中的时间字段
last_traded_time = datetime.fromtimestamp(data['Touchline']['LastTradedTime']).strftime('%H:%M:%S')
last_update_time = datetime.fromtimestamp(data['Touchline']['LastUpdateTime']).strftime('%H:%M:%S')

# 收集所有单独字段为DataFrame
other_cols = pd.DataFrame({
    'Date': [current_date],
    'Time': [exchange_time],
    'ExchangeInstrumentID': [data['ExchangeInstrumentID']],
    'LastTradedPrice': [data['Touchline']['LastTradedPrice']],
    'LastTradedQunatity': [data['Touchline']['LastTradedQunatity']],
    'LastTradedTime': [last_traded_time],
    'LastUpdateTime': [last_update_time]
})

Step 4: 合并所有部分为最终DataFrame #

把所有处理好的部分按列拼接，得到37列的单行数据：

final_df = pd.concat([other_cols, bids_flat, asks_flat], axis=1)
# 检查列数（应该是37）
print(f"Total columns: {len(final_df.columns)}")

为什么之前的代码不行？ #

你之前的代码pd.concat([pd.DataFrame(d['Bids']).rename(columns=rename).add_prefix('bid'), pd.DataFrame(d['Asks']).rename(columns=rename).add_prefix('ask')], axis=1)只是把Bids的5行和Asks的5行按列拼接，得到的是5行×6列的DataFrame，而不是你需要的1行×30列的挂单字段。我们上面的方法通过转置和重命名，把每个挂单的字段都展开成了单独的列。

内容的提问来源于stack exchange，提问作者Deep Vora