Converting nested list into data frame using pandas

Question

I have below list which stored in data

{'id': 255719,
 'display_name': 'Off Broadway Apartments',
 'access_right': {'status': 'OWNED', 'api_enabled': True},
 'creation_time': '2021-04-26T15:53:29+00:00',
 'status': {'value': 'OFFLINE', 'last_change': '2021-07-10T17:26:50+00:00'},
 'licence': {'id': 74213,
  'code': '23AF-15A8-0514-2E4B-04DE-5C19-A574-B20B',
  'bound_mac_address': '00:11:32:C2:FE:6A',
  'activation_time': '2021-04-26T15:53:29+00:00',
  'type': 'SUBSCRIPTION'},
 'timezone': 'America/Chicago',
 'version': {'agent': '3.7.0-b001', 'package': '2.5.1-0022'},
 'location': {'latitude': '41.4126', 'longitude': '-99.6345'}}

I would like to convert into data frame.can anyone advise?

I tried below code

df = pd.DataFrame(data)

but it's not coming properly as many nested lists. can anyone advise?

Does this answer your question? [Construct pandas DataFrame from items in nested dictionary](https://stackoverflow.com/questions/13575090/construct-pandas-dataframe-from-items-in-nested-dictionary) — vtasca, Oct 05 '21 at 10:06
`pd.json_normalize()` already can flatten nested json/dict. Seems like your data can be supported merely by using `pd.json_normalize()`. So why need separate function to flatten it first ? — SeaBean, Oct 05 '21 at 11:25

score 0 · Accepted Answer · answered Oct 05 '21 at 10:11

from pandas.io.json import json_normalize

 # load json
json = {'id': 255719,
 'display_name': 'Off Broadway Apartments',
 'access_right': {'status': 'OWNED', 'api_enabled': True},
 'creation_time': '2021-04-26T15:53:29+00:00',
 'status': {'value': 'OFFLINE', 'last_change': '2021-07-10T17:26:50+00:00'},
 'licence': {'id': 74213,
  'code': '23AF-15A8-0514-2E4B-04DE-5C19-A574-B20B',
  'bound_mac_address': '00:11:32:C2:FE:6A',
  'activation_time': '2021-04-26T15:53:29+00:00',
  'type': 'SUBSCRIPTION'},
 'timezone': 'America/Chicago',
 'version': {'agent': '3.7.0-b001', 'package': '2.5.1-0022'},
 'location': {'latitude': '41.4126', 'longitude': '-99.6345'}}

Create a fuction to flat nested jsons:

def flatten_json(y):
    out = {}

    def flatten(x, name=''):
        if type(x) is dict:
            for a in x:
                flatten(x[a], name + a + '_')
        elif type(x) is list:
            i = 0
            for a in x:
                flatten(a, name + str(i) + '_')
                i += 1
        else:
            out[name[:-1]] = x

    flatten(y)
    return out

You can now use that function on your original json file:

flat = flatten_json(json)
  
df = json_normalize(flat)

Results:

       id             display_name  ... location_latitude  location_longitude
0  255719  Off Broadway Apartments  ...           41.4126            -99.6345

Converting nested list into data frame using pandas

1 Answers1

Create a fuction to flat nested jsons:

Results: