Ad

How To Convert A Nested List To A DataFrame In Pandas

I have a json of Air Quality data from an API. The output looks as follow:

data[0]

{'94103': [{'AQI': 34,
   'Category': {'Name': 'Good', 'Number': 1},
   'DateObserved': '2017-10-09 ',
   'HourObserved': 0,
   'Latitude': 37.75,
   'LocalTimeZone': 'PST',
   'Longitude': -122.43,
   'ParameterName': 'OZONE',
   'ReportingArea': 'San Francisco',
   'StateCode': 'CA'},
  {'AQI': 100,
   'Category': {'Name': 'Moderate', 'Number': 2},
   'DateObserved': '2017-10-09 ',
   'HourObserved': 0,
   'Latitude': 37.75,
   'LocalTimeZone': 'PST',
   'Longitude': -122.43,
   'ParameterName': 'PM2.5',
   'ReportingArea': 'San Francisco',
   'StateCode': 'CA'}]}

The list is nested by zipcodes, and I requested over 400 records. I want to be able to generate a dataframe indexed by zipcodes with my attributes ('AQI','Category' etc.) as columns.

Do I need to create a dictionary before converting to a dataframe?

Ad

Answer

I believe need:

df = pd.concat([pd.concat({k: pd.DataFrame(v) for k, v in x.items()}) for x in data])
Ad
source: stackoverflow.com
Ad