API数据转换

Question

输入：

'{\n  "serverTime": "16/03/21 09:30:08",\n  "msgId": "4933a299-1ba6-4d76-8c8c-68854d34a079",\n  "status": "Success",\n  "statusMessage": "Historical candle data retrieved successfully",\n  "historicalCandleData": [\n    {\n      "date": "2021-03-05",\n      "open": "214.7",\n      "high": "218.75",\n      "low": "214.4",\n      "close": "218.1",\n      "ltp": "214.9",\n      "volume": "9972"\n    },\n    {\n      "date": "2021-03-08",\n      "open": "218.3",\n      "high": "219.55",\n      "low": "215.9",\n      "close": "219.35",\n      "ltp": "218.1",\n      "volume": "9220"\n    },\n    {\n      "date": "2021-03-09",\n      "open": "218.0",\n      "high": "219.4",\n      "low": "214.5",\n      "close": "215.65",\n      "ltp": "219.35",\n      "volume": "10548"\n    },\n    {\n      "date": "2021-03-10",\n      "open": "214.95",\n      "high": "215.6",\n      "low": "212.05",\n      "close": "212.25",\n      "ltp": "215.65",\n      "volume": "8812"\n    },\n    {\n      "date": "2021-03-12",\n      "open": "218.35",\n      "high": "218.35",\n      "low": "213.7",\n      "close": "215.1",\n      "ltp": "217.8",\n      "volume": "8261"\n    },\n    {\n      "date": "2021-03-15",\n      "open": "216.15",\n      "high": "220.95",\n      "low": "215.85",\n      "close": "220.55",\n      "ltp": "215.1",\n      "volume": "9019"\n    }\n  ]\n}'

所需输出：

Date  Open high low close ltp volume

xxx    xxx  xx   xxx xxx  xxx  xxxxx

xxx    xxx  xxx  xxx  xx  xxx  xxxxx

xxx   xxx  xxx  xxx  xxxx xxx  xxxx

Answer 1

在json.loads中使用json_normalize

s = '{\n "serverTime": "16/03/21 09:30:08",\n "msgId": "4933a299-1ba6-4d76-8c8c-68854d34a079",\n "status": "Success",\n "statusMessage": "Historical candle data retrieved successfully",\n "historicalCandleData": [\n {\n "date": "2021-03-05",\n "open": "214.7",\n "high": "218.75",\n "low": "214.4",\n "close": "218.1",\n "ltp": "214.9",\n "volume": "9972"\n },\n {\n "date": "2021-03-08",\n "open": "218.3",\n "high": "219.55",\n "low": "215.9",\n "close": "219.35",\n "ltp": "218.1",\n "volume": "9220"\n },\n {\n "date": "2021-03-09",\n "open": "218.0",\n "high": "219.4",\n "low": "214.5",\n "close": "215.65",\n "ltp": "219.35",\n "volume": "10548"\n },\n {\n "date": "2021-03-10",\n "open": "214.95",\n "high": "215.6",\n "low": "212.05",\n "close": "212.25",\n "ltp": "215.65",\n "volume": "8812"\n },\n {\n "date": "2021-03-12",\n "open": "218.35",\n "high": "218.35",\n "low": "213.7",\n "close": "215.1",\n "ltp": "217.8",\n "volume": "8261"\n },\n {\n "date": "2021-03-15",\n "open": "216.15",\n "high": "220.95",\n "low": "215.85",\n "close": "220.55",\n "ltp": "215.1",\n "volume": "9019"\n }\n ]\n}'

import json
df = pd.json_normalize(json.loads(s), 'historicalCandleData')
print (df)
         date    open    high     low   close     ltp volume
0  2021-03-05   214.7  218.75   214.4   218.1   214.9   9972
1  2021-03-08   218.3  219.55   215.9  219.35   218.1   9220
2  2021-03-09   218.0   219.4   214.5  215.65  219.35  10548
3  2021-03-10  214.95   215.6  212.05  212.25  215.65   8812
4  2021-03-12  218.35  218.35   213.7   215.1   217.8   8261
5  2021-03-15  216.15  220.95  215.85  220.55   215.1   9019

或者：

import json
df = pd.DataFrame(json.loads(s)['historicalCandleData'])
print (df)
         date    open    high     low   close     ltp volume
0  2021-03-05   214.7  218.75   214.4   218.1   214.9   9972
1  2021-03-08   218.3  219.55   215.9  219.35   218.1   9220
2  2021-03-09   218.0   219.4   214.5  215.65  219.35  10548
3  2021-03-10  214.95   215.6  212.05  212.25  215.65   8812
4  2021-03-12  218.35  218.35   213.7   215.1   217.8   8261
5  2021-03-15  216.15  220.95  215.85  220.55   215.1   9019

Answer 2

import json
import pandas as pd

data = json.loads('{\n "serverTime": "16/03/21 09:30:08",\n "msgId": "4933a299-1ba6-4d76-8c8c-68854d34a079",\n "status": "Success",\n "statusMessage": "Historical candle data retrieved successfully",\n "historicalCandleData": [\n {\n "date": "2021-03-05",\n "open": "214.7",\n "high": "218.75",\n "low": "214.4",\n "close": "218.1",\n "ltp": "214.9",\n "volume": "9972"\n },\n {\n "date": "2021-03-08",\n "open": "218.3",\n "high": "219.55",\n "low": "215.9",\n "close": "219.35",\n "ltp": "218.1",\n "volume": "9220"\n },\n {\n "date": "2021-03-09",\n "open": "218.0",\n "high": "219.4",\n "low": "214.5",\n "close": "215.65",\n "ltp": "219.35",\n "volume": "10548"\n },\n {\n "date": "2021-03-10",\n "open": "214.95",\n "high": "215.6",\n "low": "212.05",\n "close": "212.25",\n "ltp": "215.65",\n "volume": "8812"\n },\n {\n "date": "2021-03-12",\n "open": "218.35",\n "high": "218.35",\n "low": "213.7",\n "close": "215.1",\n "ltp": "217.8",\n "volume": "8261"\n },\n {\n "date": "2021-03-15",\n "open": "216.15",\n "high": "220.95",\n "low": "215.85",\n "close": "220.55",\n "ltp": "215.1",\n "volume": "9019"\n }\n ]\n}')

您需要将API数据加载到此json对象

df = pd.DataFrame(columns=['date','open','high','low','close','ltp','volume'])

for i in j['historicalCandleData']:
    df = df.append({'date': i['date'], 'open':i['open'], 'high':i['high'], 'low':i['low'], 'close':i['close'], 'ltp':i['ltp'], 'volume':i['volume']}, ignore_index=True)

并且我们将所有的json对象逐个添加到pandas dataframe中。

因此你得到了这个数据框

    date    open    high    low close   ltp volume
0   2021-03-05  214.7   218.75  214.4   218.1   214.9   9972
1   2021-03-08  218.3   219.55  215.9   219.35  218.1   9220
2   2021-03-09  218.0   219.4   214.5   215.65  219.35  10548
3   2021-03-10  214.95  215.6   212.05  212.25  215.65  8812
4   2021-03-12  218.35  218.35  213.7   215.1   217.8   8261
5   2021-03-15  216.15  220.95  215.85  220.55  215.1   9019