Convert a Pandas Dataframe into a nested json

Question

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":{"0":null,"1":null},"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}}

But I need in a nested json with key as the guest_id and there corresponding values. Something like this:

{55521: {'Monetory': 250, 'First visit': datetime.date(2017, 3, 21), 'Annual Expenditure': 250, 'Frequency': 1, 'Last visit': datetime.date(2017, 3, 21), 'Avg days': NaT, 'Annual Frequency': 1, 'Recency': 227, 'Visit_Ids': [342104]}, 25642: {'Monetory': 250, 'First visit': datetime.date(2015, 12, 3), 'Annual Expenditure': 250, 'Frequency': 1, 'Last visit': datetime.date(2015, 12, 3), 'Avg days': NaT, 'Annual Frequency': 1, 'Recency': 701, 'Visit_Ids': [80611]}}

The .to_json() function doesn't work the way I want it and I have pretty much exhausted all my options on this. Any Idea how to proceed ?

EDIT:

Thanks for the answer! I'm getting json outputs as part of a loop, and each output I get is a unique JSON. So is there a way to create the following ?:

{45: {"50492":{"Annual Expenditure":1000,"Annual Frequency":1,"Avg days":null,"First visit":1486339200000,"Frequency":1,"Last visit":1486339200000,"Merchants":45,"Monetory":1000,"Recency":270,"Visit_Ids":[300758],"RFMClass":"144","AvgLTV":113800.0},"1041":{"Annual Expenditure":1000,"Annual Frequency":1,"Avg days":null,"First visit":1478649600000,"Frequency":1,"Last visit":1478649600000,"Merchants":45,"Monetory":1000,"Recency":359,"Visit_Ids":[257022],"RFMClass":"244","AvgLTV":113800.0},"783":{"Annual Expenditure":1000,"Annual Frequency":1,"Avg days":null,"First visit":1457049600000,"Frequency":1,"Last visit":1457049600000,"Merchants":45,"Monetory":1000,"Recency":609,"Visit_Ids":[123464],"RFMClass":"444","AvgLTV":113800.0}}}

Here the 45 is a unique identifier that I'll pass in at the end of the loop.

jezrael · Accepted Answer · 2017-11-03 12:18:03Z

2

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}

edited Nov 3, 2017 at 12:18

answered Nov 3, 2017 at 10:00

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rags Gupta Over a year ago

Great answer! Just needed a bit of help if you can on the next step!

Rags Gupta Over a year ago

This is making the inner json a string. Wouldn't that be wrong ?

jezrael Over a year ago

I have problem I have only data in your question, so hard answer. The best is create new question with input data, desired output and what you try. Is it possible? Thanks.

Rags Gupta Over a year ago

created a new question: stackoverflow.com/questions/47096008/…

Collectives™ on Stack Overflow

Convert a Pandas Dataframe into a nested json

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related