Python解析NIH资助API多层嵌套JSON并导出CSV的问题

你可以用下面的代码实现嵌套JSON扁平化，直接输出符合要求的CSV文件：

import requests
import json
import csv
from typing import Dict, Any

# 通用嵌套JSON扁平化函数
def flatten_dict(d: Dict[str, Any], parent_key: str = '', sep: str = '_') -> Dict[str, Any]:
    items = []
    for k, v in d.items():
        new_key = parent_key + sep + k if parent_key else k
        if isinstance(v, dict):
            # 嵌套字典递归展开
            items.extend(flatten_dict(v, new_key, sep=sep).items())
        elif isinstance(v, list):
            # 数组类型转成分号分隔的字符串，适配CSV格式
            items.append((new_key, ';'.join(map(str, v))))
        else:
            items.append((new_key, v))
    return dict(items)

params = {
     "criteria":
     {
       "fiscal_years":[2021]
     },
     "include_fields": [
        "ApplId","ContactPiName","FiscalYear",
        "OrgCountry","AllText",
        "FullStudySection","Organization","ProjectEndDate",
        "ProjectNum","ProjectNumSplit","ProjectStartDate","SubprojectId"
     ],
     "offset":0,
     "limit":25,
     "sort_field":"project_start_date",
     "sort_order":"desc"
 }
response = requests.post("https://api.reporter.nih.gov/v2/projects/search", json = params)
resdecode = json.loads(response.text)
results = resdecode["results"]

# 先处理第一条数据拿到所有表头字段
if results:
    first_flat = flatten_dict(results[0])
    headers = list(first_flat.keys())
    
    with open('C:/Users/nkmou/Desktop/Venture/Tech Opportunities/NIH.csv', 'w', newline = '', encoding='utf-8-sig') as f:
        writer = csv.DictWriter(f, fieldnames=headers)
        # 写入表头
        writer.writeheader()
        # 遍历所有结果，扁平化后写入
        for item in results:
            flat_item = flatten_dict(item)
            writer.writerow(flat_item)

• flatten_dict 函数会递归处理所有嵌套字典，自动给嵌套字段加上父级键名作为前缀避免重名，比如full_study_section下的name字段会被重命名为full_study_section_name
• 用csv.DictWriter写入不需要手动计数处理表头，逻辑更简洁
• 输出指定utf-8-sig编码，避免用Excel打开CSV时出现乱码

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