[Python] Pandas deeply nested json object, how to get parent nested with json_normalize

`Im trying to read an nested api. The record nodes is nested in level 3, and I need a subvalue of to the parent.

When reading the parent I get the object, but reading a sub key it returns a Key error:

JSON object:

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}
}

]
}


Json normalize call, which gives error: KeyError: "Key 'TEST' not found. To replace missing values of 'TEST' with np.nan, pass in errors='ignore'"

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

)


With error = "ignore" the following dataframe is returned.

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

Continue reading...