如何从API返回的嵌套JSON数据中提取指定字段生成Excel？

核心问题是嵌套JSON直接转DataFrame时，深层嵌套的字段不会自动展开为顶层列，导致Pandas找不到你指定的字段。下面是两种靠谱的解决方法：

方法1：用Pandas的json_normalize扁平化嵌套结构 #

这是处理嵌套JSON最推荐的方式，专门用来把嵌套的JSON结构转成扁平的DataFrame。

示例代码（适配常见嵌套结构） #

假设你的API返回的JSON结构类似这样：

[
  {
    "account": {"accountId": "ACC-001", "accountName": "Alpha Corp"},
    "site": {"name": "Main Office", "siteType": "ON_PREMISE", "externalId": "EXT-100"},
    "license": {"activeLicenses": 8, "totalLicenses": 10, "sku": "BUSINESS-PRO"},
    "state": "ACTIVE"
  },
  {
    "account": {"accountId": "ACC-002", "accountName": "Beta Inc"},
    "site": {"name": "Remote Hub", "siteType": "CLOUD", "externalId": "EXT-101"},
    "license": {"activeLicenses": 3, "totalLicenses": 5, "sku": "BUSINESS-BASIC"},
    "state": "INACTIVE"
  }
]

对应的处理代码：

import pandas as pd
import json

# 加载API返回的JSON数据（如果是直接从API拿到的响应，用response.json()即可）
with open('api_data.json', 'r') as f:
    api_data = json.load(f)

# 使用json_normalize展开嵌套字段，指定要提取的字段路径
flat_df = pd.json_normalize(
    api_data,
    meta=[
        ['account', 'accountId'],
        ['account', 'accountName'],
        ['site', 'name'],
        ['site', 'siteType'],
        ['site', 'externalId'],
        ['license', 'activeLicenses'],
        ['license', 'totalLicenses'],
        ['license', 'sku'],
        'state'
    ]
)

# 重命名列，去掉嵌套的层级标识，改成你需要的字段名
flat_df.columns = [
    'accountId', 'accountName', 'name', 'siteType', 
    'externalId', 'activeLicenses', 'totalLicenses', 'sku', 'state'
]

# 写入Excel
flat_df.to_excel('account_licenses.xlsx', index=False)

复杂嵌套（含数组）的适配 #

如果你的JSON里有数组型嵌套（比如一个账户对应多个站点），需要指定record_path来展开数组：

# 假设API返回的JSON包含账户下的站点数组
flat_df = pd.json_normalize(
    api_data,
    record_path='sites',  # 展开sites数组中的每个元素
    meta=[['account', 'accountId'], ['account', 'accountName']]  # 保留账户级别的字段
)

# 筛选并重命名需要的列
flat_df = flat_df[[
    'account.accountId', 'account.accountName', 'name', 'siteType', 
    'externalId', 'license.activeLicenses', 'license.totalLicenses', 'license.sku', 'state'
]]
flat_df.columns = [
    'accountId', 'accountName', 'name', 'siteType', 
    'externalId', 'activeLicenses', 'totalLicenses', 'sku', 'state'
]

方法2：手动遍历提取字段（适合结构简单或特殊嵌套） #

如果json_normalize搞不定你的特殊嵌套结构，可以手动遍历每个JSON对象，提取需要的字段：

import pandas as pd
import json

with open('api_data.json', 'r') as f:
    api_data = json.load(f)

# 初始化空列表存储每行数据
extracted_rows = []
for item in api_data:
    # 按嵌套路径提取每个字段，用get方法避免因字段缺失导致的KeyError
    row = {
        'accountId': item['account'].get('accountId'),
        'accountName': item['account'].get('accountName'),
        'name': item['site'].get('name'),
        'siteType': item['site'].get('siteType'),
        'externalId': item['site'].get('externalId'),
        'activeLicenses': item['license'].get('activeLicenses'),
        'totalLicenses': item['license'].get('totalLicenses'),
        'sku': item['license'].get('sku'),
        'state': item.get('state')
    }
    extracted_rows.append(row)

# 转成DataFrame并写入Excel
df = pd.DataFrame(extracted_rows)
df.to_excel('account_licenses.xlsx', index=False)

排查小技巧 #

如果不确定字段的嵌套路径，可以先把JSON转成临时DataFrame，打印列名看看：

temp_df = pd.json_normalize(api_data)
print(temp_df.columns)

输出的列名会显示扁平化后的路径（比如account.accountId），你可以根据这个调整提取逻辑。

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