Python解析API返回JSON并生成关联display_name与mail的DataFrame

我调用API获取了如下格式的JSON数据：

[{
   "display_name":"IST-XXX1",
   "members":[
      {
         "aad_id":"XXX",
         "user_principal_name":"XXXX@XX.com",
         "user_principal_name_normalized":"XXXX@XX.com",
         "mail":"XXXX@XX.com"
      },
      {
         "aad_id":"XXXX",
         "user_principal_name":"XXXX@XX.com",
         "user_principal_name_normalized":"XXXX@XX.com",
         "mail":"XXXX@XX.com"
      },
      {
         "aad_id":"XXXXX",
         "user_principal_name":"XXXX@XX.com",
         "user_principal_name_normalized":"XXXX@XX.com",
         "mail":"XXXX@XX.com"
      },
      {
         "aad_id":"XXXX",
         "user_principal_name":"XXXX@XX.com",
         "user_principal_name_normalized":"XXXX@XX.com",
         "mail":"XXXX@XX.com"
      },
      {
         "aad_id":"XXXX",
         "user_principal_name":"XXXX@XX.com",
         "user_principal_name_normalized":"XXXX@XX.com",
         "mail":"XXXX@XX.com"
      }
   ],
   "id":"XXX"
}]

需要解析该JSON，提取display_name字段以及members数组中的mail字段，生成DataFrame（display_name随每个mail重复），方便后续插入SQL数据库。我尝试了以下代码，但无法正确关联分组与邮箱：

for i in item_generator(response_data, "display_name"):
    ans = {"display_name": i}
    output.append(ans)
for i in item_generator(response_data, "mail"):
    ans = {"mail": i}
    output.append(ans)
print(output)

请帮忙实现该需求。

解决方案 #

之前的代码问题在于分开遍历display_name和mail，导致两者没有关联，生成的是独立的字典，无法组成对应行。正确的做法是遍历每个分组（JSON数组中的每个元素），然后对每个分组的members数组循环，将display_name和每个mail配对成字典，再收集这些字典生成DataFrame。

方法1：基础循环实现 #

import pandas as pd

# 假设response_data是你获取的JSON数据列表
response_data = [{
    "display_name":"IST-XXX1",
    "members":[
        {"aad_id":"XXX", "user_principal_name":"XXXX@XX.com", "user_principal_name_normalized":"XXXX@XX.com", "mail":"user1@XX.com"},
        {"aad_id":"XXXX", "user_principal_name":"XXXX@XX.com", "user_principal_name_normalized":"XXXX@XX.com", "mail":"user2@XX.com"},
        {"aad_id":"XXXXX", "user_principal_name":"XXXX@XX.com", "user_principal_name_normalized":"XXXX@XX.com", "mail":"user3@XX.com"},
        {"aad_id":"XXXX", "user_principal_name":"XXXX@XX.com", "user_principal_name_normalized":"XXXX@XX.com", "mail":"user4@XX.com"},
        {"aad_id":"XXXX", "user_principal_name":"XXXX@XX.com", "user_principal_name_normalized":"XXXX@XX.com", "mail":"user5@XX.com"}
    ],
    "id":"XXX"
}]

output = []
# 遍历每个分组
for group in response_data:
    group_name = group["display_name"]
    # 遍历当前分组下的所有成员
    for member in group["members"]:
        output.append({
            "display_name": group_name,
            "mail": member["mail"]
        })

# 生成DataFrame
df = pd.DataFrame(output)
print(df)

方法2：列表推导式简化代码 #

如果追求简洁，可以用列表推导式一行完成数据收集：

import pandas as pd

output = [
    {"display_name": group["display_name"], "mail": member["mail"]}
    for group in response_data
    for member in group["members"]
]

df = pd.DataFrame(output)
print(df)

运行后生成的DataFrame结构示例：

生成的DataFrame可直接通过df.to_sql()方法插入SQL数据库。

内容的提问来源于stack exchange，提问作者BokA