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.