Python中的json_normalize

Question

我在这里尝试使用json_normalize以某种方式格式化API的输出，但是我总是得到一个错误的空csv文件。我试图更改df2 = pd.json_normalize(response, record_path=['LIST'])，但一直收到以下错误消息：

TypeError: byte indices must be integers or slices, not str

你能指点我我做错了什么吗？

非常感谢！

import requests
import json
import pandas as pd

url = "https://*hidden*Results/"

payload = json.dumps({
  "id": 12345
})
headers = {
  'Authorization': 'Basic *hidden*',
  'Content-Type': 'application/json'
}

response = requests.request("POST", url, headers=headers, data=payload)


df1 = pd.DataFrame(response).iloc[:,:-2]
df2 = pd.json_normalize(response, record_path=None)

df = pd.concat([df1, df2], axis=1)
df.to_csv("test.csv", index=False)

Answer 1

在调用中传递变量response：

df2 = pd.json_normalize(response, record_path=None)

，这是一个requests.models.Response对象，您需要传递一个dict，所以您需要执行类似于pd.json_normalize(response.json(), record_path=['LIST'])的操作。

我用这个例子试了一下，效果很好：

>>> import pandas as pd
>>> data = [
...     {
...         "state": "Florida",
...         "shortname": "FL",
...         "info": {"governor": "Rick Scott"},
...         "counties": [
...             {"name": "Dade", "population": 12345},
...             {"name": "Broward", "population": 40000},
...             {"name": "Palm Beach", "population": 60000},
...         ],
...     },
...     {
...         "state": "Ohio",
...         "shortname": "OH",
...         "info": {"governor": "John Kasich"},
...         "counties": [
...             {"name": "Summit", "population": 1234},
...             {"name": "Cuyahoga", "population": 1337},
...         ],
...     },
... ]
>>> result = pd.json_normalize(data, ["counties"])
>>> result
         name  population
0        Dade       12345
1     Broward       40000
2  Palm Beach       60000
3      Summit        1234
4    Cuyahoga        1337

编辑我将尝试这样做：

import requests
import json
import pandas as pd

url = "https://*hidden*Results/"

payload = json.dumps({
  "id": 12345
})
headers = {
  'Authorization': 'Basic *hidden*',
  'Content-Type': 'application/json'
}

response = requests.request("POST", url, headers=headers, data=payload)

json_response = response.json()

df1 = pd.DataFrame(json_response).iloc[:,:-2]
df2 = pd.json_normalize(json_response, record_path=['LIST'])

df = pd.concat([df1, df2], axis=1)
df.to_csv("test.csv", index=False)