如何将嵌套层级的JSON组织数据展平并转换为DataFrame？

需求说明 #

将包含N级嵌套的员工-经理层级JSON数据，转换为结构化DataFrame，每条记录包含员工ID、姓名、入职日期，以及其直属经理的ID（顶级员工的经理ID设为None）。

解决思路 #

由于层级是N级，采用递归遍历的方式处理嵌套结构：

1. 先清理JSON中键名的多余空格（原数据存在" manages "、" employee_id "这类带空格的键，会影响数据解析）；
2. 定义递归函数，遍历每个员工节点：
   • 提取当前员工的基本信息，关联对应的经理ID；
   • 如果该员工有下属（manages字段），递归处理下属节点，将当前员工ID作为下属的经理ID；
3. 将所有收集到的员工数据整理为DataFrame。

代码实现 #

import pandas as pd
import json

def clean_json_keys(obj):
    """清理JSON对象中键名和字符串值的多余空格"""
    if isinstance(obj, dict):
        return {k.strip(): clean_json_keys(v) for k, v in obj.items()}
    elif isinstance(obj, list):
        return [clean_json_keys(item) for item in obj]
    else:
        return obj.strip() if isinstance(obj, str) else obj

def extract_employees(data, manager_id=None):
    """递归提取员工信息，关联直属经理ID"""
    employee_data = []
    # 提取当前员工的基础信息
    emp_info = {
        "employee_id": data["employee_id"],
        "employee_name": data["employee_name"],
        "join_date": data["join_date"],
        "manager_id": manager_id
    }
    employee_data.append(emp_info)
    
    # 递归处理下属员工
    if "manages" in data and data["manages"]:
        for subordinate in data["manages"]:
            employee_data.extend(extract_employees(subordinate, manager_id=data["employee_id"]))
    return employee_data

# 加载并清理原始JSON数据
raw_json = '''
{
    "employee_id": "e1",
    "employee_name": "employee name 1",
    "join_date": "2011-01-01",
    " manages ": [
        {
            " employee_id ": " e11 ",
            " employee_name ": " employee name 11 ",
            " join_date ": " 2011 - 02 - 01 "
        },
        {
            " employee_id ": " e12 ",
            " employee_name ": " employee name 12 ",
            " join_date ": " 2011 - 02 - 02 ",
            " manages ": [
                {
                    " employee_id ": " e121 ",
                    " employee_name ": " employee name 121 ",
                    " join_date ": " 2011 - 02 - 21 "
                }
            ]
        }
    ]
}
'''
cleaned_data = clean_json_keys(json.loads(raw_json))

# 提取员工数据并生成DataFrame
employee_list = extract_employees(cleaned_data)
df = pd.DataFrame(employee_list)
print(df)

输出结果 #

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