Converting nested list into data frame using pandas-CodePudding

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?

CodePudding user response：

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