如何在Python中将嵌套JSON规范化为行列表格形式？

我处理了一个大型嵌套JSON文件，但部分列仍保留JSON格式数据，示例数据如下：

data = [
    {
        "task": "jump",
        "taskid": "task-001",
        "task-req": [
            {"taskid": "task-001", "requestID": "task-001-r1"},
            {"taskid": "task-001", "requestID": "task-001-r2"},
            {"taskid": "task-001", "requestID": "task-001-r3"}
        ],
        "task-desc": {
            "task-001-r1": [{"taskid": "task-001", "task": "jump", "details": "long jump"}],
            "task-001-r2": {"taskid": "task-001", "task": "jump", "details": "short jump"},
            "task-001-r3": {"taskid": "task-001", "task": "jump", "details": "high jump"}
        }
    }
]

我期望得到如下扁平化表格格式的输出：

task    taskid    requestID     details
jump    task-001  task-001-r1   long jump
jump    task-001  task-001-r2   short jump
jump    task-001  task-001-r3   high jump

我尝试使用json.normalize()方法，但只能生成顶层列，嵌套的JSON数据仍以值形式存在对应行中，得到的输出如下：

task    taskid    task-req                                                  task-desc
jump    task-001  [{"taskid": "task-001","requestID": "task-001-r1"}, ...]  {"task-001-r1": [{"taskid": "task-001",...}, ...]}

请问有没有方法实现我想要的格式？

可以通过手动遍历嵌套结构，提取所需字段后组装成扁平化的数据集，再转成DataFrame。步骤如下：

1. 修正JSON语法问题 #

原示例中task-desc里的task-001-r1是数组，其余是对象，以下代码兼容这种混合结构；如果实际数据结构更规整，可简化逻辑。

2. 编写Python代码处理 #

import pandas as pd

# 示例数据（已修正语法错误）
data = [
    {
        "task": "jump",
        "taskid": "task-001",
        "task-req": [
            {"taskid": "task-001", "requestID": "task-001-r1"},
            {"taskid": "task-001", "requestID": "task-001-r2"},
            {"taskid": "task-001", "requestID": "task-001-r3"}
        ],
        "task-desc": {
            "task-001-r1": [{"taskid": "task-001", "task": "jump", "details": "long jump"}],
            "task-001-r2": {"taskid": "task-001", "task": "jump", "details": "short jump"},
            "task-001-r3": {"taskid": "task-001", "task": "jump", "details": "high jump"}
        }
    }
]

# 初始化空列表存储扁平化数据
flat_data = []

# 遍历顶层任务
for task_item in data:
    base_task = task_item["task"]
    base_taskid = task_item["taskid"]
    task_req_list = task_item["task-req"]
    task_desc_dict = task_item["task-desc"]
    
    # 遍历每个请求
    for req in task_req_list:
        request_id = req["requestID"]
        # 获取对应的详情，兼容数组和对象格式
        desc = task_desc_dict[request_id]
        details = desc[0]["details"] if isinstance(desc, list) else desc["details"]
        
        # 组装成字典
        flat_data.append({
            "task": base_task,
            "taskid": base_taskid,
            "requestID": request_id,
            "details": details
        })

# 转成DataFrame并输出
df = pd.DataFrame(flat_data)
print(df.to_string(index=False))

输出结果 #

task   taskid    requestID    details
jump task-001 task-001-r1  long jump
jump task-001 task-001-r2 short jump
jump task-001 task-001-r3  high jump

说明 #

手动遍历嵌套结构可以精准控制提取的字段，解决json.normalize()无法直接处理非规则嵌套（如混合数组/对象）的问题；如果实际JSON结构统一，可进一步简化details的提取逻辑。

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