How to create Nested json using python pandas dataframe-CodePudding

import pandas as pd

data = [['INDIA', 'UP', 'BANARAS'], ['INDIA', 'UP', 'KANPUR'], ['INDIA', 'TN', 'CHENNAI'],  ['US', 'TEXAS', 'HUSTON']]

cols = ['COUNTRY', 'STATE', 'CITY']

df = pd.DataFrame(data=data, columns=cols)

I want result like this...

[
  {
    "COUNTRY": "INDIA",
    "STATE": "TN",
    "CITIES": [
      {
        "CITY": "CHENNAI"
      }
    ]
  },
  {
    "COUNTRY": "INDIA",
    "STATE": "UP",
    "CITIES": [
      {
        "CITY": "BANARAS"
      },
      {
        "CITY": "KANPUR"
      }
    ]
  },
  {
    "COUNTRY": "US",
    "STATE": "TEXAS",
    "CITITES": [
      {
        "CITY": "HUSTON"
      }
    ]
  }
]

CodePudding user response：

You can try

out = (df.groupby(['COUNTRY', 'STATE'])
       .apply(lambda g: g[['CITY']].to_dict(orient='records'))
       .to_frame('CITIES')
       .reset_index()
       .to_dict(orient='records'))

pprint(out)

[{'CITIES': [{'CITY': 'CHENNAI'}], 'COUNTRY': 'INDIA', 'STATE': 'TN'},
 {'CITIES': [{'CITY': 'BANARAS'}, {'CITY': 'KANPUR'}],
  'COUNTRY': 'INDIA',
  'STATE': 'UP'},
 {'CITIES': [{'CITY': 'HUSTON'}], 'COUNTRY': 'US', 'STATE': 'TEXAS'}]

CodePudding user response：

add this to the above code:

import json

with open("sample.json", "w") as outfile:
    json.dump(out, outfile)

or you could remove to_dict() and add this line after

out.to_json("filename.json")