如何用json_normalize一步将含列表的嵌套JSON转为DataFrame？

给定如下嵌套JSON数据（包含内部列表）：

[
    {
        "id": 467,
        "status": 2,
        "leavePeriod": {
            "owner": {
                "employeeNumber": "2620",
                "firstName": "fn_467",
                "lastName": "ln_467"
            },
            "ownerId": 46,
            "leaves": [
                {
                    "leaveAccount": {
                        "id": 1121,
                        "name": "Vacation days 2021/2022",
                        "url": "https://some_link/1121"
                    },
                    "date": "2023-04-06T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1121,
                        "name": "Vacation days 2021/2022",
                        "url": "https://some_link/1121"
                    },
                    "date": "2023-04-06T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1121,
                        "name": "Vacation days 2021/2022",
                        "url": "https://some_link/1121"
                    },
                    "date": "2023-04-07T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1121,
                        "name": "Vacation days 2021/2022",
                        "url": "https://some_link/1121"
                    },
                    "date": "2023-04-07T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1121,
                        "name": "Vacation days 2021/2022",
                        "url": "https://some_link/1121"
                    },
                    "date": "2023-04-11T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1121,
                        "name": "Vacation days 2021/2022",
                        "url": "https://some_link/1121"
                    },
                    "date": "2023-04-11T00:00:00"
                }
            ]
        }
    },
    {
        "id": 477,
        "status": 2,
        "leavePeriod": {
            "owner": {
                "employeeNumber": "2522",
                "firstName": "fn_477",
                "lastName": "lm_477"
            },
            "ownerId": 41,
            "leaves": [
                {
                    "leaveAccount": {
                        "id": 1121,
                        "name": "Vacation days 2021/2022",
                        "url": "https://some_link/1121"
                    },
                    "date": "2023-03-13T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1121,
                        "name": "Vacation days 2021/2022",
                        "url": "https://some_link/1121"
                    },
                    "date": "2023-03-13T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1323,
                        "name": "RTT 2023",
                        "url": "https://some_link/1323"
                    },
                    "date": "2023-03-14T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1323,
                        "name": "RTT 2023",
                        "url": "https://some_link/1323"
                    },
                    "date": "2023-03-14T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1323,
                        "name": "RTT 2023",
                        "url": "https://some_link/1323"
                    },
                    "date": "2023-03-15T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1323,
                        "name": "RTT 2023",
                        "url": "https://some_link/1323"
                    },
                    "date": "2023-03-15T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1323,
                        "name": "RTT 2023",
                        "url": "https://some_link/1323"
                    },
                    "date": "2023-03-16T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1323,
                        "name": "RTT 2023",
                        "url": "https://some_link/1323"
                    },
                    "date": "2023-03-16T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1323,
                        "name": "RTT 2023",
                        "url": "https://some_link/1323"
                    },
                    "date": "2023-03-17T00:00:00"
                },
                {
                    "leaveAccount": {
                        "id": 1323,
                        "name": "RTT 2023",
                        "url": "https://some_link/1323"
                    },
                    "date": "2023-03-17T00:00:00"
                }
            ]
        }
    }
]

需要使用from pandas.io.json import json_normalize，一步操作将其展平为DataFrame，避免先两次展平再合并的方式。

解决方案 #

直接使用json_normalize的record_path参数指定要展开的列表路径，同时用meta参数保留所有需要的上层嵌套字段，代码如下：

import pandas as pd
from pandas.io.json import json_normalize

# 假设你的JSON数据存储在变量data中
df = json_normalize(
    data,
    record_path=['leavePeriod', 'leaves'],  # 指定要展开的内部列表路径
    meta=[
        'id',
        'status',
        ['leavePeriod', 'ownerId'],
        ['leavePeriod', 'owner', 'employeeNumber'],
        ['leavePeriod', 'owner', 'firstName'],
        ['leavePeriod', 'owner', 'lastName']
    ],
    sep='_'  # 指定列名的分隔符，默认是点，这里用下划线更直观
)

# 重命名列（可选，让列名更简洁符合习惯）
df.rename(columns={
    'leaveAccount_id': 'leave_account_id',
    'leaveAccount_name': 'leave_account_name',
    'leaveAccount_url': 'leave_account_url',
    'leavePeriod_ownerId': 'owner_id',
    'leavePeriod_owner_employeeNumber': 'employee_number',
    'leavePeriod_owner_firstName': 'first_name',
    'leavePeriod_owner_lastName': 'last_name'
}, inplace=True)

print(df.head())

代码说明 #

• record_path=['leavePeriod', 'leaves']：指定要展开的列表是leavePeriod下的leaves，每条列表项对应DataFrame的一行。
• meta参数：列出所有需要从上层JSON结构中保留的字段，嵌套字段用列表形式表示层级路径（比如['leavePeriod', 'owner', 'employeeNumber']对应leavePeriod.owner.employeeNumber）。
• sep='_'：将嵌套路径的分隔符从默认的.改为_，生成的列名更符合Python命名习惯。

最终DataFrame结构示例 #

生成的DataFrame包含以下列（顺序可能略有不同）：

• leave_account_id
• leave_account_name
• leave_account_url
• date
• id
• status
• owner_id
• employee_number
• first_name
• last_name

每条leaves列表中的条目都会和对应的上层员工、请假单信息一一对应，实现了一步展平的效果。

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