I have the data
[{"state": "Florida",
"shortname": "FL",
"info": {"governor": "Rick Scott"},
"counties": [{"name": "Dade",
"population": 12345,
"Attributes": [
{
"capture_date": "2020-01-29",
"Spirit_code": "TRLQR",
"value": 1
},
{
"capture_date": "2020-01-29",
"Spirit_code": "HAVPN",
"value": 57000
}
]},
{"name": "Broward",
"population": 40000,
"Attributes": [
{
"capture_date": "2020-01-29",
"Spirit_code": "GMSTP",
"value": 14
},
{
"capture_date": "2020-01-29",
"Spirit_code": "GWTPN",
"value": 11212
}
]
},
{"name": "Palm Beach",
"population": 60000,
"Attributes": [{
"capture_date": "2020-01-29",
"Spirit_code": "YGHMN",
"value": 154.01
},
{
"capture_date": "2020-01-29",
"Spirit_code": "CXZASD",
"value": 154.01
}]
}
]},
{"state": "Ohio",
"shortname": "OH",
"info": {"governor": "John Kasich"},
"counties": [{"name": "Summit", "population": 1234,
"Attributes": [{
"capture_date": "2020-01-29",
"Spirit_code": "QWERTY",
"value": 154.01
},
{
"capture_date": "2020-01-29",
"Spirit_code": "JKLGH",
"value": 154.01
}]
},
{"name": "Cuyahoga", "population": 1337,
"Attributes": [{
"capture_date": "2020-01-29",
"Spirit_code": "ASDF",
"value": 154.01
},
{
"capture_date": "2020-01-29",
"Spirit_code": "POIUY",
"value": 154.01
}]
}],
}
]
And I am looking for the result:
state, shortname, name, population, attirbute.capture_date, attirbute.spirit_code, attirbute.value
florida, FL ,Dade, 12345 , 2020-0-29 , TRLQR , 1
florida, FL ,Dade, 12345 , 2020-0-29 , HAVPN , 57000
florida, FL ,Broward, 40000 , 2020-0-29 , GMSTP , 14
florida, FL ,Broward, 40000 , 2020-0-29 , GWTPN , 11212
florida, FL ,Palm Beach, 60000 , 2020-0-29 , YGHMN , 154.01
florida, FL ,Palm Beach, 60000 , 2020-0-29 , YGHMN , 154.01
florida, FL ,Palm Beach, 60000 , 2020-0-29 , CXZASD , 154.01
Basically normalizing for the key attributes in the nested json. Key: "Attributes".
json_normalize(data["data"], ["counties", "Attributes"], ["state", "shortname", ["counties", "name"], ["counties", "population"]])
I am getting error:
TypeError: {'name': 'Dade', 'population': 12345, 'Attributes': [{'capture_date': '2020-01-29', 'Spirit_code': 'TRLQR', 'value': 1}, {'capture_date': '2020-01-29', 'Spirit_code': 'HAVPN', 'value': 57000}]} has non iterable value 12345 for path ['population']. Must be iterable or null.
but if I run :
plots_in = json_normalize(data["data"], ["counties", "Attributes"],
["state", "shortname", ["counties", "name"]])
I get the result:
capture_date Spirit_code value state shortname counties.name
0 2020-01-29 TRLQR 1.00 Florida FL Dade
1 2020-01-29 HAVPN 57000.00 Florida FL Dade
2 2020-01-29 GMSTP 14.00 Florida FL Broward
3 2020-01-29 GWTPN 11212.00 Florida FL Broward
4 2020-01-29 YGHMN 154.01 Florida FL Palm Beach
5 2020-01-29 CXZASD 154.01 Florida FL Palm Beach
6 2020-01-29 QWERTY 154.01 Ohio OH Summit
7 2020-01-29 JKLGH 154.01 Ohio OH Summit
8 2020-01-29 ASDF 154.01 Ohio OH Cuyahoga
9 2020-01-29 POIUY 154.01 Ohio OH Cuyahoga
is there something to do with the integer value in the population key? because if I run the following then still I am getting the same error:
plots_in = json_normalize(data["data"], ["counties", "population"])
Please explain, if someone knows what is going on underneath?
Check your version of pandas. If it pandas 1.0.0 then it is most likely related to: json_normalize in 1.0.0 with meta path specified - expects iterable #31507
I had exactly the same issue as I reinstalled my dev environment in linux including installation of all latest packages with pandas 1.0.0. After some search I found the above link and then removed the pandas 1.0.0 and installed pandas 0.25.3 by first uninstalling:
pip3 uninstall pandas # or pip uninstall pandas
and then:
pip3 install pandas==0.25.3 # or pip install pandas==0.25.3
and after that everything worked fine as it did before installing latest pandas.