CODEWITHSUNDEEP
pythonjsonpandasdataframenested
Mike Clark
Mike Clark

Reputation: 33

Convert Pandas Dataframe into multi level nested JSON

I have a dataframe that I need to convert into a nested json format. I can get one level of grouping done, but I don't know how to do a second grouping as well as a nesting beneath that.

I have looked a lot of different examples, but nothing really gets me the example I posted below.

import pandas as pd

data= {'Name': ['TEST01','TEST02'],
       'Type': ['Tent','Tent'],
       'Address':['123 Happy','456 Happy'],
       'City':['Happytown','Happytown'],
       'State': ['WA','NY'],
       'PostalCode': ['89985','85542'],
       'Spot' : ['A','A'],
       'SpotAssigment' : ['123','456'],
       'Cost': [900,500]
        }

df = pd.DataFrame(data)

j = (df.groupby(['Name','Type'])
             .apply(lambda x: x[['Address','City', 'State', 'PostalCode']].to_dict('r'))
              .reset_index(name='addresses')
             .to_json(orient='records'))


print(json.dumps(json.loads(j), indent=2, sort_keys=True))

I want it to look like the below.

[
  {
    "Name": "TEST01",
    "Type": "Tent",
    "addresses": [
      {
        "Address": "123 Happy",
        "City": "Happytown",
        "PostalCode": "89985",
        "State": "WA"
      }
    ],
     "spots":[
              {"Spot":'A',
               "SpotAssignments":[
                      "SpotAssignment":"123",
                      "Cost":900
                          ]
              }
              ]
  },
  {
    "Name": "TEST02",
    "Type": "Tent",
    "addresses": [
      {
        "Address": "456 Happy",
        "City": "Happytown",
        "PostalCode": "85542",
        "State": "NY"
      }
     ],
     "spots":[
              {"Spot":'A',
               "SpotAssignments":[
                      "SpotAssignment":"456",
                      "Cost":500
                          ]
              }
              ]
     }
]

Upvotes: 0

Views: 1058

Answers (1)

ESDAIRIM
ESDAIRIM

Reputation: 651

try this:

j = (df.groupby(['Name','Type'])
         .apply(lambda x: x[['Address','City', 'State', 'PostalCode']].to_dict('r'))
          .reset_index(name='addresses'))

k = (df.groupby(['Name','Type', 'Spot'])
         .apply(lambda x: x[['SpotAssigment', 'Cost']].to_dict('r'))
 .reset_index(name='SpotAssignments'))


h = (k.groupby(['Name','Type'])
         .apply(lambda x: x[['Spot','SpotAssignments']].to_dict('r'))
 .reset_index(name='spots'))
         


m = j.merge(h, how='inner', on=['Name', 'Type'])
result = m.to_dict(orient='records')

from pprint import pprint as pp
pp(result)

this result is a python list of dicts in the same format that you want, you should be able to dump it as JSON directly.

Upvotes: 2

Related Questions