Convert A Pandas Dataframe Into A Nested Json

I need to convert pandas Dataframe into nested json. The output of .to_json gives the following {'Annual Expenditure':{'0':250,'1':250},'Annual Frequency':{'0':1,'1':1},'Avg days'

Solution 1:

Use set_index + to_json with parameter orient:

df.set_index('Guest').to_json('file.json', orient='index')

---

{"25642":{"Annual Expenditure":250,"Annual Frequency":1,"Avg days":null,"AvgLTV":13305.7692307692,"First visit":1449100800000,"Frequency":1,"Last visit":1449100800000,"Monetory":250,"RFMClass":"444","Recency":701,"Visit_Ids":[80611]},"55521":{"Annual Expenditure":250,"Annual Frequency":1,"Avg days":null,"AvgLTV":13305.7692307692,"First visit":1490054400000,"Frequency":1,"Last visit":1490054400000,"Monetory":250,"RFMClass":"144","Recency":227,"Visit_Ids":[342104]}}

Input DataFrame:

d = {"Annual Expenditure":{"0":250,"1":250},"Annual Frequency":{"0":1,"1":1},"Avg days":{"0":np.nan,"1":np.nan},"First visit":{"0":1449100800000,"1":1490054400000},"Frequency":{"0":1,"1":1},"Guest":{"0":25642,"1":55521},"Last visit":{"0":1449100800000,"1":1490054400000},"Monetory":{"0":250,"1":250},"Recency":{"0":701,"1":227},"Visit_Ids":{"0":[80611],"1":[342104]},"RFMClass":{"0":"444","1":"144"},"AvgLTV":{"0":13305.7692307692,"1":13305.7692307692}}

---

df = pd.DataFrame(d)
print (df)
   Annual Expenditure  Annual Frequency  Avg days        AvgLTV  \
0                 250                 1       NaN  13305.769231   
1                 250                 1       NaN  13305.769231   

     First visit  Frequency  Guest     Last visit  Monetory RFMClass  Recency  \
0  1449100800000          1  25642  1449100800000       250      444      701   
1  1490054400000          1  55521  1490054400000       250      144      227   

  Visit_Ids  
0   [80611]  
1  [342104] 

EDIT:

j = df.set_index('Guest').to_json(orient='index')
j_final = {45: j}