如何使用 Python 将 API JSON 数据导出到 CSV 文件

Question

我遇到了一个问题。我通过 API 获得了一些 JSON 格式的数据。

它看起来像这样：

示例：

{"ID":"938","ENTITY_ID":"CRM_DEAL","FIELD_NAME":"UF_CRM_111111111111","USER_TYPE_ID":"enumeration","LIST":\[{"ID":"700","SORT":"10","VALUE":"text1","DEF":"N"},{"ID":"948","SORT":"20","VALUE":"text2","DEF":"N"}\]}

我需要以 CSV 格式输出数据，如下所示：

ID, ENTITY_ID, FIELD_NAME

938, CRM_DEAL, UF_CRM_111111111111, 700, text1
938, CRM_DEAL, UF_CRM_111111111111, 948, text2

请帮助我，我该如何解决这个问题？

我尝试运行此代码：

import requests 
import pandas as pd 
r = requests.get("https://myrestAPImethod")

df = pd.DataFrame(r)

print(r.text) with open("test.csv", 'w+', encoding='UTF8', newline='') as f: 
f.write(r.text)

但我得到了错误的 CSV 文件。

我需要这个：

ID, ENTITY_ID, FIELD_NAME

938, CRM_DEAL, UF_CRM_111111111111, 700, text1
938, CRM_DEAL, UF_CRM_111111111111, 948, text2

但是我

{"ID":"938","ENTITY_ID":"CRM_DEAL","FIELD_NAME":"UF_CRM_111111111111","USER_TYPE_ID":"enumeration","LIST":\[{"ID":"700","SORT":"10","VALUE":"text1","DEF":"N"},{"ID":"948","SORT":"20","VALUE":"text2","DEF":"N"}\]}

在第一个 A1 单元格中有文本（当我用 Excel 打开 CSV 时）。

Answer 1

您使用 pandas 将其转换为数据帧。所以现在只需使用 pandas 写入文件：

r = {"ID":"938","ENTITY_ID":"CRM_DEAL","FIELD_NAME":"UF_CRM_111111111111","USER_TYPE_ID":"enumeration","LIST":[{"ID":"700","SORT":"10","VALUE":"text1","DEF":"N"},{"ID":"948","SORT":"20","VALUE":"text2","DEF":"N"}]}


df = pd.DataFrame(r)
df.to_csv("test.csv", index=False)

将其扁平化一点：

import pandas as pd

r = {"ID":"938","ENTITY_ID":"CRM_DEAL","FIELD_NAME":"UF_CRM_111111111111","USER_TYPE_ID":"enumeration","LIST":[{"ID":"700","SORT":"10","VALUE":"text1","DEF":"N"},{"ID":"948","SORT":"20","VALUE":"text2","DEF":"N"}]}

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

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

输出：

print(df)
    ID ENTITY_ID           FIELD_NAME   ID SORT  VALUE DEF
0  938  CRM_DEAL  UF_CRM_111111111111  700   10  text1   N
1  938  CRM_DEAL  UF_CRM_111111111111  948   20  text2   N