Convert Python Pandas Dataframe to nested JSON formate-CodePudding

i have my data coming from database like this :

Account	Name	Address1	State	Zip	Loantype	expiry
100	Sam	Street 5	NY	NY001	E	2019
100	Sam	Street 5	NY	NY001	T	2020
100	Sam	Street 10	NJ	NJ001	E	2019
100	Sam	Street 10	NJ	NJ001	T	2020
101	John	Street 1	CA	CA001	E	2019
101	Joh	Street 1	CA	CA001	T	2020

I Would need to convert above data into below json format using python pandas. I am trying df.to_json(orient = 'index') but it is not creating nested formate as below. Any suggestions ?

{
results: [
    {
        account:100,
        Name: Sam,
        LoanDetails : [
            {
                Address1: Street 5,
                State : NY,
                ZIP: NY0001,
                LoanList : [
                    {
                        Loantype: E,
                        expiry: 2012
                    }
                    {
                        Loantype: T,
                        expiry: 2020
                    }
                ]
            }
            {
                Address1: Street 10,
                State: NJ,
                ZIP: Nj0001,
                LoanList: [
                    {
                        Loantype: E,
                        expiry: 2019
                    }
                    {
                        Loantype: T,
                        expiry: 2020
                    }
                ]
            }
    }
    {
        account:100,
        Name: John,
        LoanDetails : 
            {
                Address1: Street 1,
                State : CA,
                ZIP: CA0001,
                LoanList : [
                    {
                        Loantype: E,
                        expiry: 2012
                    }
                    {
                        Loantype: T,
                        expiry: 2020
                    }
                ]
            }
    }
]
}

CodePudding user response：

I tried below and it worked:

import pandas as pd
import json
df = pd.DataFrame({'account':['100','100','100','100','101'],
                    'name':['sam','sam','sam','sam','john'],
                    'address1':['street 5','street 5','street 10','street 10','street 1'],
                    'state':['ny','ny','nj','nj','ca'],
                    'zip':['ny0001','ny0001','nj0001','nj0001','CA001'],
                    'loantype':['e','t','e','t','e'],
                   'expiry':[2019,2020,2019,2020,2019]
                   })


k = df.groupby(['account','name','address1','state']).apply(lambda x:x[['loantype','expiry']].to_dict('records')).reset_index().rename(columns={0:'Loanlist'})#.to_json(orient = 'records')

j = k.groupby(['account','name',]).apply(lambda x:x[['address1','state','Loanlist']].to_dict('records')).reset_index().rename(columns={0:'Loandetails'}).to_json(orient = 'records')

print(j)

CodePudding user response：

this just helps to get your output. may be alternate approch is there.

a = {}
for i in df.index:
    a[i]={}
    a[i]["LoanDetails"] = {}
    a[i]["LoanList"] = {}
    for col in df:
        if col in (["Account","Name"]):
            a[i][col] = df[col].iloc[i]
        if col in (["Address1","State","Zip"]):
            a[i]["LoanDetails"][col] = df[col].iloc[i]
        if col in (["Loantype","expiry"]):
            a[i]["LoanList"][col] = df[col].iloc[i]
b ={}
b["Result"] =[]
for i,v in a.items():
    b["Result"].append(v)