使用Pandas json_normalize处理深层嵌套JSON的父级字段获取问题

解析嵌套API返回的JSON数据时，目标记录节点位于第三层（counties数组），需要同时提取父级层级的子字段。读取父级对象时正常，但读取Y.TEST字段时触发KeyError；设置errors='ignore'后，该字段返回NaN，无法正确获取有效值。

JSON数据示例 #

data={
    'results': [
        {
            'id':1,
            "x": [
                {
                    "state": "Florida",
                    "shortname": "FL",
                    "info": {"governor": "Rick Scott"},
                    "counties": [
                        {"name": "Dade", "population": 12345},
                        {"name": "Broward", "population": 40000},
                        {"name": "Palm Beach", "population": 60000},
                    ],
                },
                {
                    "state": "Ohio",
                    "shortname": "OH",
                    "info": {"governor": "John Kasich"},
                    "counties": [
                        {"name": "Summit", "population": 1234},
                        {"name": "Cuyahoga", "population": 1337},
                    ],
                }
            ],
            "Y": {'TEST':1}
        },
        {  
            'id':2,
            "x":[
                {
                    "state": "Ohio",
                    "shortname": "OH",
                    "info": {"governor": "John Kasich"},
                    "counties": [
                        {"name": "Summit", "population": 1234},
                        {"name": "Cuyahoga", "population": 1337},
                    ],
                }
            ],
            "Y": {'TEST':1}
        }
    ]
}

错误代码及问题 #

原调用代码存在语法和路径配置错误，触发KeyError: "Key 'TEST' not found..."：

result = pd.json_normalize(
    record_path=data["results"], 
    meta=['x', "counties"][["x", "state"], ["x", "info", "governor"], "Y", ["Y", "TEST"]],
)

设置errors='ignore'后，Y.TEST字段返回NaN，无法正确取值：

name  population  x.state x.info.governor            Y Y.TEST
0        Dade       12345  Florida      Rick Scott  {'TEST': 1}    NaN
1     Broward       40000  Florida      Rick Scott  {'TEST': 1}    NaN
2  Palm Beach       60000  Florida      Rick Scott  {'TEST': 1}    NaN
3      Summit        1234     Ohio     John Kasich  {'TEST': 1}    NaN
4    Cuyahoga        1337     Ohio     John Kasich  {'TEST': 1}    NaN
5      Summit        1234     Ohio     John Kasich  {'TEST': 1}    NaN
6    Cuyahoga        1337     Ohio     John Kasich  {'TEST': 1}    NaN

解决方案 #

正确配置record_path定位到最内层的counties数组，并在meta中正确指定父级字段的路径：

import pandas as pd

result = pd.json_normalize(
    data["results"],
    record_path=['x', 'counties'],  # 直接定位到目标记录所在的counties层级
    meta=[
        ['x', 'state'],               # 提取x下的state字段
        ['x', 'info', 'governor'],    # 提取x.info下的governor字段
        ['Y', 'TEST']                 # 正确提取父级Y下的TEST字段
    ]
)

运行结果 #

name  population x.state x.info.governor Y.TEST
0        Dade       12345  Florida      Rick Scott      1
1     Broward       40000  Florida      Rick Scott      1
2  Palm Beach       60000  Florida      Rick Scott      1
3      Summit        1234     Ohio     John Kasich      1
4    Cuyahoga        1337     Ohio     John Kasich      1
5      Summit        1234     Ohio     John Kasich      1
6    Cuyahoga        1337     Ohio     John Kasich      1

关键修正点 #

1. record_path定位错误：原代码将记录路径设为results，没有深入到counties层，导致层级关联错误。改为['x', 'counties']后，直接定位到目标数据节点。
2. meta参数格式错误：原代码的meta写法存在语法错误，正确格式应为包含字段路径列表的数组，每个路径对应父级的嵌套字段。
3. 字段路径正确指定：['Y', 'TEST']明确指定从父级Y对象中提取TEST字段，确保层级关联正确，避免KeyError和NaN值。

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