python—将json请求转换为 Dataframe 中的行

我已将以下字符串转换为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}

我想将数据[“投标”][0][0]（即大小：21）转换为一列，然后将数据[“投标”][0][1]转换为另一列，将数据[“投标”][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

python—将json请求转换为 Dataframe 中的行

1条答案

相关问题

热门标签

最新问答