Pandas multiindex to json

Viewed 616

I have a dataframe as below

    name address.office.street address.office.city address.office.country  address.office.postcode location.tracker.id
0  Name1               Street1               City1               Country1                      100         9,99,02,129
1  Name2               Street2               City2               Country2                      200         1,91,95,129
2  Name3               Street3               City3               Country3                      300           99,90,259

I split the column and created MultiIndex as below

idx = df.columns.str.split('.', expand=True)
df.columns = idx
df[('location', 'tracker', 'id')] = df[('location', 'tracker', 'id')].str.split(',')
print(df)


    name  address                                    location
     NaN   office                                     tracker
     NaN   street   city   country postcode                id
0  Name1  Street1  City1  Country1      100  [9, 99, 02, 129]
1  Name2  Street2  City2  Country2      200  [1, 91, 95, 129]
2  Name3  Street3  City3  Country3      300     [99, 90, 259]

I want to convert this to nested json. May I know the pandas way to convert this into below json.

[ { "name": "Name1", "address": { "office": { "street": "Street1", "city": "City1", "country": "Country1", "postcode": 100 } }, "location": { "tracker": { "id": [ "9", "99", "02", "129" ] } } }, { "name": "Name2", "address": { "office": { "street": "Street2", "city": "City2", "country": "Country2", "postcode": 200 } }, "location": { "tracker": { "id": [ "1", "91", "95", "129" ] } } }, { "name": "Name3", "address": { "office": { "street": "Street3", "city": "City3", "country": "Country3", "postcode": 300 } }, "location": { "tracker": { "id": [ "99", "90", "259" ] } } } ]

While I can get the above result with below code, it becomes slow when the number of records(df.shape[0]) are high.

nested_dict = lambda: defaultdict(nested_dict)
result = nested_dict()

result_list = []
for cntr in range(df.shape[0]):
    for i, j in df.iteritems():
        value = j[cntr]
        if not pd.isnull(i[2]):
            result[i[0]][i[1]][i[2]] = value
        elif not pd.isnull(i[1]):
            result[i[0]][i[1]] = value
        elif not pd.isnull(i[0]):
            result[i[0]] = value

    result_list.append(deepcopy(result))

print(json.dumps(result_list, indent=4))

I look to simplify this similar to

(df.groupby(level=['level0']).apply(lambda df: df.xs(df.name))).to_json()

But, could not get the results as expected.

0 Answers
Related