python - 将 JSON 请求转换为数据框中的一行
问题描述
我将以下字符串转换为 JSON：
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}
我想将 data["Bids"][0][0] (即 Size:21) 转换为一列，然后将 data["Bids"][0][1] 转换为另一列和 data["Bids"][0] [2] 进入第三......同样我想要 37 个这样的列，即：
1: Date (now.day)
2. Time (convert ExchangeTimeStamp to current time)
3. ExchangeInstrumentID
4-33. [bid_size1, bid_price1, bid_totalorder1, bid_size2, bid_price2, bid_totalorder2, ....bid_size5, bid_price5, bid_totalorder5, ask_size1, ask_price1, ask_totalorder1, ...ask_size5, ask_price5, ask_totalorder5]
34. LastTradedPrice
35. LastTradedQunatity
36. LastTradedTime (converted to present timestamp)
37. LastUpdateTime (converted to present timestamp)
这是我尝试过的：
rename = {'TotalOrders':'_totalorder'}
df = pd.concat([pd.DataFrame(d['Bids']).rename(columns=rename).add_prefix('bid'),
pd.DataFrame(d['Asks']).rename(columns=rename).add_prefix('ask')], axis=1)
这给了我 6 列而不是 6*5 = 30 列。请帮忙
解决方案
尝试：
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
}"""
import json
df = pd.json_normalize(json.loads(data))
df["ExchangeTimeStamp"] = pd.to_datetime(df["ExchangeTimeStamp"], unit="s")
df["Touchline.LastTradedTime"] = pd.to_datetime(
df["Touchline.LastTradedTime"], unit="s"
)
df["Touchline.LastUpdateTime"] = pd.to_datetime(
df["Touchline.LastUpdateTime"], unit="s"
)
asks = df["Asks"].explode()
df = df.explode("Bids")
df["Asks"] = asks
a = df.pop("Asks").apply(pd.Series).add_prefix("Asks_")
b = df.pop("Bids").apply(pd.Series).add_prefix("Bids_")
df = pd.concat([df, a, b], axis=1)
print(df)
印刷：
MessageCode MessageVersion ApplicationType TokenID ExchangeSegment ExchangeInstrumentID ExchangeTimeStamp BookType XMarketType SequenceNumber Touchline.BidInfo.Size Touchline.BidInfo.Price Touchline.BidInfo.TotalOrders Touchline.BidInfo.BuyBackMarketMaker Touchline.AskInfo.Size Touchline.AskInfo.Price Touchline.AskInfo.TotalOrders Touchline.AskInfo.BuyBackMarketMaker Touchline.LastTradedPrice Touchline.LastTradedQunatity Touchline.TotalBuyQuantity Touchline.TotalSellQuantity Touchline.TotalTradedQuantity Touchline.AverageTradedPrice Touchline.LastTradedTime Touchline.LastUpdateTime Touchline.PercentChange Touchline.Open Touchline.High Touchline.Low Touchline.Close Touchline.TotalValueTraded Touchline.BuyBackTotalBuy Touchline.BuyBackTotalSell Asks_Size Asks_Price Asks_TotalOrders Asks_BuyBackMarketMaker Bids_Size Bids_Price Bids_TotalOrders Bids_BuyBackMarketMaker
0 1502 4 0 0 1 22 2011-07-06 12:10:31 1 1 476310325663841 21 1999.15 2 0 1 1999.8 1 0 1999.9 12 145954 81287 114118 1999.65 2011-07-06 12:10:24 2011-07-06 12:10:31 0.636558 1997.65 2006.6 1989.1 1987.25 None 0 0 1.0 1999.80 1.0 0.0 21.0 1999.15 2.0 0.0
0 1502 4 0 0 1 22 2011-07-06 12:10:31 1 1 476310325663841 21 1999.15 2 0 1 1999.8 1 0 1999.9 12 145954 81287 114118 1999.65 2011-07-06 12:10:24 2011-07-06 12:10:31 0.636558 1997.65 2006.6 1989.1 1987.25 None 0 0 3.0 1999.85 1.0 0.0 20.0 1999.10 1.0 0.0
0 1502 4 0 0 1 22 2011-07-06 12:10:31 1 1 476310325663841 21 1999.15 2 0 1 1999.8 1 0 1999.9 12 145954 81287 114118 1999.65 2011-07-06 12:10:24 2011-07-06 12:10:31 0.636558 1997.65 2006.6 1989.1 1987.25 None 0 0 34.0 1999.90 2.0 0.0 40.0 1999.05 1.0 0.0
0 1502 4 0 0 1 22 2011-07-06 12:10:31 1 1 476310325663841 21 1999.15 2 0 1 1999.8 1 0 1999.9 12 145954 81287 114118 1999.65 2011-07-06 12:10:24 2011-07-06 12:10:31 0.636558 1997.65 2006.6 1989.1 1987.25 None 0 0 199.0 2000.00 10.0 0.0 185.0 1999.00 6.0 0.0
0 1502 4 0 0 1 22 2011-07-06 12:10:31 1 1 476310325663841 21 1999.15 2 0 1 1999.8 1 0 1999.9 12 145954 81287 114118 1999.65 2011-07-06 12:10:24 2011-07-06 12:10:31 0.636558 1997.65 2006.6 1989.1 1987.25 None 0 0 1.0 2000.05 1.0 0.0 7.0 1998.80 2.0 0.0
推荐阅读
- flutter - 照片没有显示 image_picker- Flutter
- ruby-on-rails - 在 Ruby-on-rails 中创建和查询问题/答案
- vba - 在 Word VBA 中动态地将框架添加到用户窗体页面
- react-native - 将视频 URI 从 React-Native Picker 上传到 AWS S3 服务器
- firebase - 当我的颤振应用关闭时，如何更新我的 Firebase 数据？
- hive - Hive 中的 SELECT 查询以从表中获取数据作为结构列
- django - Django sqlite 到 postgres 数据库迁移
- mercurial - 如何在没有提交的情况下创建评论
- node.js - 如何将字段值更新为 mongoDB 中的默认值？
- python - 从 CSS span 元素中获取值不断变化的值