将字符串转换为字典到数据框

深沃拉

我正在尝试通过 API 获取数据，该 API 将输出作为字符串。我将其转换为 JSON，但在将其转换为数据帧时遇到了困难。

字符串看起来像：

{"MessageCode":1502,"MessageVersion":4,"ApplicationType":0,"TokenID":0,"ExchangeSegment":1,"ExchangeInstrumentID":22,"ExchangeTimeStamp":1309963220,"Bids":[{"Size":1,"Price":1980.15,"TotalOrders":1,"BuyBackMarketMaker":0},{"Size":30,"Price":1980.1,"TotalOrders":1,"BuyBackMarketMaker":0},{"Size":232,"Price":1980.05,"TotalOrders":5,"BuyBackMarketMaker":0},{"Size":593,"Price":1980,"TotalOrders":4,"BuyBackMarketMaker":0},{"Size":141,"Price":1979.95,"TotalOrders":4,"BuyBackMarketMaker":0}],"Asks":[{"Size":5,"Price":1980.65,"TotalOrders":4,"BuyBackMarketMaker":0},{"Size":564,"Price":1980.7,"TotalOrders":4,"BuyBackMarketMaker":0},{"Size":105,"Price":1980.9,"TotalOrders":2,"BuyBackMarketMaker":0},{"Size":2,"Price":1981,"TotalOrders":1,"BuyBackMarketMaker":0},{"Size":2,"Price":1981.3,"TotalOrders":1,"BuyBackMarketMaker":0}],"Touchline":{"BidInfo":{"Size":1,"Price":1980.15,"TotalOrders":1,"BuyBackMarketMaker":0},"AskInfo":{"Size":5,"Price":1980.65,"TotalOrders":4,"BuyBackMarketMaker":0},"LastTradedPrice":1980.15,"LastTradedQunatity":10,"TotalBuyQuantity":109663,"TotalSellQuantity":101522,"TotalTradedQuantity":287746,"AverageTradedPrice":1990.53,"LastTradedTime":1309963216,"LastUpdateTime":1309963220,"PercentChange":-0.3572776449867865,"Open":1997.65,"High":2006.6,"Low":1975,"Close":1987.25,"TotalValueTraded":null,"BuyBackTotalBuy":0,"BuyBackTotalSell":0},"BookType":1,"XMarketType":1,"SequenceNumber":476310350954633}

我做到了： res = json.loads(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":null,
"BuyBackTotalBuy":0,
"BuyBackTotalSell":0},
"BookType":1,
"XMarketType":1,
"SequenceNumber":476310325663841}

然后我做了

df = pd.DataFrame(res)

这给出了一个错误：

ValueError：将 Dict 与非系列混合可能会导致排序不明确

我进一步尝试了这个：

df = pd.concat([pd.DataFrame(res['Bids']), 
             pd.DataFrame(res['Asks'])], axis=1, keys=('Bids','Asks'))
df.columns = df.columns.map('_'.join)

但这仅适用于两个键。我想将每个键转换为 df。任何优化代码都会有所帮助。

耶斯列

想法通过键单独转换并通过MessageCode以下方式连接在一起：

df1 = pd.json_normalize(d, 'Bids').add_prefix('bid')
df2 = pd.json_normalize(d, 'Asks','MessageCode').add_prefix('ask')
df3 = pd.json_normalize(d).drop(['Bids','Asks'], axis=1)

df = (pd.concat([df1, df2], axis=1)
        .rename(columns={'askMessageCode':'MessageCode'})
        .merge(df3, on='MessageCode', how='left'))
print (df)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。