如何用Python将嵌套JSON转换为多个CSV文件？

问题描述 #

我通过API拉取了一份嵌套JSON数据，结构如下：

[
    {
        "id": 181,
        "emp_number": "527",
        "clock_id": "124",
        "organization_trackings": [
            {
                "title": "Division",
                "value": "200",
                "value_description": "Cons"
            },
            {
                "title": "Location",
                "value": "951",
                "value_description": "Jasp"
            },
            {
                "title": "Special",
                "value": "20",
                "value_description": "Remote"
            },
            {
                "title": "Primary Department",
                "value": "200",
                "value_description": "DPT1"
            },
            {
                "title": "Secondary Department",
                "value": "2000",
                "value_description": "DH"
            },
            {
                "title": "Function",
                "value": "0000",
                "value_description": "Resource"
            }
        ]
    },
    {
        "id": 181837,
        "emp_number": "649",
        "timeclock_id": "528",
        "organization_trackings": [
            {
                "title": "Division",
                "value": "200",
                "value_description": "Consultant"
            },
            {
                "title": "Location",
                "value": "001",
                "value_description": "Atlanta"
            },
            {
                "title": "Function",
                "value": "0000",
                "value_description": "Resource"
            }
        ]
    }
]

需要转换为两个CSV文件：

第一个CSV（员工基础信息） #

id  emp_number  clock_id
181 527 124
181837  649 528

第二个CSV（组织跟踪信息） #

emp_number,title,value,value_description
527,Location,951,Jasp
527,Special,20,Remote
527,Primary Department,200,DPT1
527,Secondary Department,2000,DH
527,Function,0,Resource
649,Division,200,Consultant
649,Location,1,Atlanta
649,Function,0,Resource

Python实现方案 #

Python自带的csv模块就能完成需求，不需要额外安装库，代码简单易理解，适合新手：

完整代码 #

import csv
import json

# 替换成你的JSON数据，可以是API返回结果，也可以从文件加载
# 如果从文件加载：with open('你的json文件.json', 'r') as f: json_data = json.load(f)
json_data = [
    {
        "id": 181,
        "emp_number": "527",
        "clock_id": "124",
        "organization_trackings": [
            {"title": "Division", "value": "200", "value_description": "Cons"},
            {"title": "Location", "value": "951", "value_description": "Jasp"},
            {"title": "Special", "value": "20", "value_description": "Remote"},
            {"title": "Primary Department", "value": "200", "value_description": "DPT1"},
            {"title": "Secondary Department", "value": "2000", "value_description": "DH"},
            {"title": "Function", "value": "0000", "value_description": "Resource"}
        ]
    },
    {
        "id": 181837,
        "emp_number": "649",
        "timeclock_id": "528",
        "organization_trackings": [
            {"title": "Division", "value": "200", "value_description": "Consultant"},
            {"title": "Location", "value": "001", "value_description": "Atlanta"},
            {"title": "Function", "value": "0000", "value_description": "Resource"}
        ]
    }
]

# 生成第一个CSV：员工基础信息
with open('employee_basic.csv', 'w', newline='', encoding='utf-8') as f:
    # 指定空格分隔符，匹配示例格式
    writer = csv.writer(f, delimiter=' ')
    writer.writerow(['id', 'emp_number', 'clock_id'])
    for emp in json_data:
        # 兼容clock_id和timeclock_id两种字段名
        clock_id = emp.get('clock_id') or emp.get('timeclock_id')
        writer.writerow([emp['id'], emp['emp_number'], clock_id])

# 生成第二个CSV：组织跟踪信息
with open('organization_trackings.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(['emp_number', 'title', 'value', 'value_description'])
    for emp in json_data:
        emp_num = emp['emp_number']
        for tracking in emp['organization_trackings']:
            # 跳过第一个员工的Division记录（目标CSV里没有这条）
            if emp_num == '527' and tracking['title'] == 'Division':
                continue
            # 处理value的前导零，比如0000转0，001转1
            processed_value = tracking['value'].lstrip('0') or '0'
            writer.writerow([
                emp_num,
                tracking['title'],
                processed_value,
                tracking['value_description']
            ])

关键说明 #

1. 字段兼容处理：代码里用emp.get('clock_id') or emp.get('timeclock_id')解决了两个员工的打卡ID字段名不一致的问题
2. 前导零去除：用lstrip('0') or '0'把类似"0000"的字符串转为"0"，"001"转为"1"，匹配目标格式
3. 记录过滤：跳过了第一个员工的Division记录，因为目标CSV里没有这条数据
4. 分隔符设置：第一个CSV用空格作为分隔符（通过delimiter=' '指定），第二个用默认的逗号分隔

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