时间戳

首页 > 解决方案 > Pandas Json Normalize - 没有得到想要的输出

python - Pandas Json Normalize - 没有得到想要的输出

问题描述

运行此代码以规范化 json：

import pandas as pd
import http.client
import json
import requests

# get order book for a specific ticker

conn = http.client.HTTPSConnection("api.buyucoin.com")
payload = ''
headers = {
 'Content-Type': 'application/json'
}

conn.request("GET", "/ticker/v1.0/liveOrderBook?symbol=USDT-INR", payload, headers)
res = conn.getresponse()
OrderBook = res.read()

print(OrderBook.decode("utf-8"))

OrderBook = pd.json_normalize(json.loads(OrderBook),'data',['status','sub_status'])

但没有得到以下格式的所需输出：

Status   Sub-status   Sell.price   Sell.qty   Sell.total  Buy.price  Buy.qty  Buy.total
Success  Null         89           10         890         87         100      8700

Json数据如下：

{"status":"success","sub_status":null,"data":{"SELL": 
[{"price":"1000000","qty":"0.0009","total":"900"}, 
{"price":"255","qty":"0.45","total":"114.75"}, 
{"price":"78.9","qty":"508.0704","total":"40086.75456"}],"BUY": 
[{"price":"76","qty":"6","total":"456"},{"price":"40","qty":"2.323","total":"92.92"}, 
{"price":"9","qty":"43.2222","total":"388.9998"}]}}

提前致谢

标签： pythonjson

解决方案

这不是json_normalize直接消耗的（在我尝试之后）。由于BUY和的数量SELL不同，并且这些记录不一定应该相互匹配（位于同一行上），因此建议将其拆分为两个数据帧然后连接。

df1 = pd.json_normalize(json.loads(OrderBook), ["data", "BUY"], meta=["status", "sub_status"])
df2 = pd.json_normalize(json.loads(OrderBook), ["data", "SELL"], meta=["status", "sub_status"])
df1['type'] = "BUY"
df2['type'] = "SELL"
df = pd.concat([df1, df2]).reset_index(drop=True)

这使：

>>> df
      price         qty           total   status sub_status  type
0     75.01  50336.2579  3775722.705079  success       None   BUY
1      74.5  96684.9451   7203028.40995  success       None   BUY
2        74       10000          740000  success       None   BUY
3      73.1   6671.1098    487658.12638  success       None   BUY
4        73       8.028         586.044  success       None   BUY
5      72.5       10000          725000  success       None   BUY
6        70   7671.8155      537027.085  success       None   BUY
7        69       10000          690000  success       None   BUY
8        68       11000          748000  success       None   BUY
9        67       10000          670000  success       None   BUY
10       66       10000          660000  success       None   BUY
11       65       10000          650000  success       None   BUY
12       55           1              55  success       None   BUY
13  42.1839           1         42.1839  success       None   BUY
14       41           1              41  success       None   BUY
15       40       2.323           92.92  success       None   BUY
16        9     43.2222        388.9998  success       None   BUY
17  1000000      0.0009             900  success       None  SELL
18      255        0.45          114.75  success       None  SELL
19      200           1             200  success       None  SELL
20      199           1             199  success       None  SELL
21      151         1.2           181.2  success       None  SELL
22      150      1.1895         178.425  success       None  SELL
23    127.5    211.6747     26988.52425  success       None  SELL
24      124       12.97         1608.28  success       None  SELL
25      120       15.45            1854  success       None  SELL
26      110     1938.54        213239.4  success       None  SELL
27      105       9.928         1042.44  success       None  SELL
28      103           1             103  success       None  SELL
29      102     11.0443       1126.5186  success       None  SELL
30      101      243.16        24559.16  success       None  SELL
31      100     57.6676         5766.76  success       None  SELL
32    99.92         5.1         509.592  success       None  SELL
33       99     61.2644       6065.1756  success       None  SELL
34       98          12            1176  success       None  SELL
35       91       50000         4550000  success       None  SELL
36       90    120.1241       10811.169  success       None  SELL
37       88     21.5719       1898.3272  success       None  SELL
38       87      4.1253        358.9011  success       None  SELL
39       85   1688.7511     143543.8435  success       None  SELL
40     84.5     48.3097      4082.16965  success       None  SELL
41       84  40040.2054    3363377.2536  success       None  SELL
42     83.5       10000          835000  success       None  SELL
43     83.2         2.5             208  success       None  SELL
44       83   4932.7792     409420.6736  success       None  SELL
45       82       9.976         818.032  success       None  SELL
46       80  12863.4578     1029076.624  success       None  SELL
47     79.5       28.46         2262.57  success       None  SELL
48       79    417.3402      32969.8758  success       None  SELL
49    78.92     482.681     38093.18452  success       None  SELL
50     78.9    221.2638     17457.71382  success       None  SELL

推荐阅读