将API响应转换为Pandas DataFrame

Question

使用以下代码进行API调用:

req = urllib.request.Request(url, body, headers)
try:
response = urllib.request.urlopen(req)
string = response.read().decode('utf-8')
json_obj = json.loads(string)

返回以下内容:

{"forecast": [17.588294043898163, 17.412641963452206], 
"index": [
{"SaleDate": 1629417600000, "Type": "Type 1"}, 
{"SaleDate": 1629504000000, "Type": "Type 2"}
]
}

如何将此api响应转换为Panda DataFrame以将pandas DataFrame

中的字典转换为以下格式

Forecast                 SaleDate     Type
17.588294043898163       2021-08-16   Type 1
17.412641963452206       2021-08-17   Type 1

Answer 1

您可以使用以下命令。它使用pandas.Series将字典转换为列，使用pandas.to_datetime从毫秒时间戳映射正确的日期:

d = {"forecast": [17.588294043898163, 17.412641963452206], 
"index": [
{"SaleDate": 1629417600000, "Type": "Type 1"}, 
{"SaleDate": 1629504000000, "Type": "Type 2"}
]
}
df = pd.DataFrame(d)
df = pd.concat([df['forecast'], df['index'].apply(pd.Series)], axis=1)
df['SaleDate'] = pd.to_datetime(df['SaleDate'], unit='ms')

输出:

forecast   SaleDate    Type
0  17.588294 2021-08-20  Type 1
1  17.412642 2021-08-21  Type 2

Answer 2

这里有一个解决方案，你可以尝试一下，使用list comprehension来平化数据。

import pandas as pd
flatten = [
{"forecast": j, **resp['index'][i]} for i, j in enumerate(resp['forecast'])
]
pd.DataFrame(flatten)

---

forecast       SaleDate    Type
0  17.588294  1629417600000  Type 1
1  17.412642  1629504000000  Type 2