Myujth

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.