如何用pd.json_normalize读取嵌套JSON至DataFrame并获取auto_scan_date

针对嵌套JSON无法提取auto_scan_date的问题，核心原因是该字段位于多层嵌套数组（config→scan→auto_scan）中，需要明确指定pd.json_normalize的路径参数。以下是完整实现步骤：

完整代码示例 #

import pandas as pd

# 实际场景中可替换为读取JSON文件：data = pd.read_json("your_file.json")
data = [
    {
        "owner": "admin@123",
        "ipadd": "10.10.10.10",
        "servername": "demoserver1.admin.com",
        "Status": "live",
        "config": [
            {
                "ipadd": "10.10.10.10",
                "scan": {
                    "last_scan_date": "2000-10-10 23:53",
                    "auto_scan": [{"auto_scan_date": "2000-10-11 23:53"}],
                    "scan_status": "Enable",
                    "enable_datetime": "2000-09-20 23:53",
                    "scanned_by": "serveradmin"
                },
                "repo": "main"
            }
        ],
        "repo": "main"
    },
    {
        "owner": "admin@123",
        "ipadd": "10.10.10.11",
        "servername": "demoserver2.admin.com",
        "Status": "live",
        "config": [
            {
                "ipadd": "10.10.10.10",
                "scan": {
                    "last_scan_date": "2000-10-10 23:53",
                    "auto_scan": [{"auto_scan_date": "2000-10-11 23:53"}],
                    "scan_status": "Enable",
                    "enable_datetime": "2000-09-20 23:53",
                    "scanned_by": "serveradmin"
                },
                "repo": "main"
            }
        ],
        "repo": "main"
    }
]

# 多层嵌套归一化
df = pd.json_normalize(
    data,
    record_path=['config', 'scan', 'auto_scan'],  # 指向存储auto_scan_date的内层数组
    meta=[
        'servername',
        ['config', 'ipadd'],
        ['config', 'scan', 'last_scan_date'],
        ['config', 'scan', 'scan_status'],
        ['config', 'scan', 'enable_datetime']
    ]
)

# 调整列名与顺序以匹配期望输出
df.columns = ['auto_scan_date', 'servername', 'ipadd', 'last_scan_date', 'scan_status', 'enable_datetime']
df = df[['servername', 'ipadd', 'last_scan_date', 'auto_scan_date', 'scan_status', 'enable_datetime']]

# 输出为指定格式
print(df.to_csv(sep='|', index=False))

关键参数说明 #

• record_path：必须指定到包含auto_scan_date的最内层数组['config', 'scan', 'auto_scan']，Pandas会展开该数组并提取目标字段。
• meta：用于提取上层结构中的非数组字段，通过嵌套列表指定字段的完整路径（如['config', 'scan', 'last_scan_date']）。
• 列调整：归一化后的列名会带路径前缀，因此需要重命名并调整顺序，匹配期望的输出格式。

运行代码后，输出结果如下：

servername|ipadd|last_scan_date|auto_scan_date|scan_status|enable_datetime
demoserver1.admin.com|10.10.10.10|2000-10-10 23:53|2000-10-11 23:53|Enable|2000-09-20 23:53
demoserver2.admin.com|10.10.10.10|2000-10-10 23:53|2000-10-11 23:53|Enable|2000-09-20 23:53

内容的提问来源于stack exchange，提问作者Bandhala Raja Selvam