將PythonPandasDataframe轉換為嵌套的JSON格式

我的資料來自這樣的資料庫：

我需要使用 python pandas 將上述資料轉換為以下 json 格式。我正在嘗試 df.to_json(orient = 'index') 但它沒有創建如下嵌套的甲酸鹽。有什么建議 ？

{
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
                    }
                ]
            }
    }
]
}

uj5u.com熱心網友回復：

我在下面嘗試過，它奏效了：

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)

uj5u.com熱心網友回復：

這只是有助于獲得您的輸出。可能有替代方法。

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)

標籤：Python json 熊猫 数据框 到 json

上一篇：比較資料框中的2個元組

下一篇：Pyspark：匹配來自兩個不同資料幀的列并添加值