pandas中如何读取json文件并将其存储在具有列和多行的数据框中

hiz5n14c 于 2023-04-10 发布在其他

关注(0)|答案(1)|浏览(105)

{ 
    "_id" : "63bbe1580b485627714220a3", 
    "userId" : "63a2a7b83d9cfb08817540d2", 
    "tenantId" : "319", 
    "createDate" : "2023-01-09T09:41:44.772Z", 
    "type" : "cibil", 
    "data" : {
        "scoreInfo" : {
            "creditReportTimeStamp" : "24-12-2022", 
            "creditScore" : "00779", 
            "creditscoreVersion" : "10", 
            "creditScoreName" : "CIBILTUSC3"
        }, 
        "accountInfo" : [
            {
                "paymentEndDate" : "01-06-2022", 
                "ownershipIndicator" : "1", 
                "dateOpened" : "17-06-2022", 
                "highCreditSanctionedAmount" : "50000", 
                "cashLimit" : "10000", 
                "accountType" : "10", 
                "currentBalance" : "0", 
                "dateReported" : "31-10-2022", 
                "paymentHistory1" : "000000000000000", 
                "memberName" : "NOT DISCLOSED", 
                "creditLimit" : "50000", 
                "paymentStartDate" : "01-10-2022"
            }, 
            {
                "highCreditSanctionedAmount" : "50000", 
                "cashLimit" : "5000", 
                "accountType" : "10", 
                "currentBalance" : "0", 
                "memberName" : "NOT DISCLOSED", 
                "dateClosed" : "31-08-2022", 
                "paymentFrequency" : "Monthly", 
                "paymentEndDate" : "01-09-2021", 
                "ownershipIndicator" : "1", 
                "dateOpened" : "29-09-2021", 
                "dateReported" : "15-09-2022", 
                "paymentHistory1" : "000000000000000000000000000000000000", 
                "creditLimit" : "50000", 
                "paymentStartDate" : "01-08-2022"
            }, 
            {
                "paymentEndDate" : "01-09-2020", 
                "ownershipIndicator" : "1", 
                "dateOpened" : "10-09-2020", 
                "highCreditSanctionedAmount" : "44758", 
                "accountType" : "10", 
                "currentBalance" : "12742", 
                "dateReported" : "31-10-2022", 
                "paymentHistory1" : "000000000000000000000000000000000000000000000000000000", 
                "memberName" : "NOT DISCLOSED", 
                "dateOfLastPayment" : "29-10-2022", 
                "paymentStartDate" : "01-10-2022", 
                "paymentHistory2" : "000000000000000000000000"
            }, 
            {
                "highCreditSanctionedAmount" : "1000000", 
                "accountType" : "44", 
                "currentBalance" : "536400", 
                "memberName" : "NOT DISCLOSED", 
                "paymentFrequency" : "Monthly", 
                "paymentEndDate" : "01-11-2019", 
                "ownershipIndicator" : "4", 
                "dateOpened" : "22-03-2019", 
                "emiAmount" : "5067", 
                "dateReported" : "31-10-2022", 
                "paymentHistory1" : "000000000000000000000000000000000000000000000000000000", 
                "repaymentTenure" : "240", 
                "dateOfLastPayment" : "25-10-2022", 
                "paymentStartDate" : "01-10-2022", 
                "paymentHistory2" : "000000000000000000000000000000000000000000000000000000"
            }, 
            {
                "highCreditSanctionedAmount" : "26589", 
                "accountType" : "03", 
                "currentBalance" : "8830", 
                "memberName" : "NOT DISCLOSED", 
                "paymentFrequency" : "Monthly", 
                "paymentEndDate" : "01-11-2019", 
                "ownershipIndicator" : "4", 
                "dateOpened" : "22-03-2019", 
                "emiAmount" : "590", 
                "dateReported" : "31-10-2022", 
                "paymentHistory1" : "000000000000000000000000000000000000000000000000000000", 
                "repaymentTenure" : "60", 
                "dateOfLastPayment" : "25-10-2022", 
                "paymentStartDate" : "01-10-2022", 
                "paymentHistory2" : "000000000000000000000000000000000000000000000000000000"
            }
        ],  
        "enquiryInfo" : [
            {
                "enquiryPurpose" : "10", 
                "memberName" : "AU SFB", 
                "enquiryAmount" : "100", 
                "dateOfEnquiry" : "03-11-2022"
            }, 
            {
                "enquiryPurpose" : "10", 
                "memberName" : "NOT DISCLOSED", 
                "enquiryAmount" : "100", 
                "dateOfEnquiry" : "10-10-2022"
            }, 
            {
                "enquiryPurpose" : "10", 
                "memberName" : "NOT DISCLOSED", 
                "enquiryAmount" : "100000", 
                "dateOfEnquiry" : "30-06-2022"
            }, 
            {
                "enquiryPurpose" : "10", 
                "memberName" : "NOT DISCLOSED", 
                "enquiryAmount" : "10000", 
                "dateOfEnquiry" : "12-06-2022"
            }, 
            {
                "enquiryPurpose" : "10", 
                "memberName" : "NOT DISCLOSED", 
                "enquiryAmount" : "1000", 
                "dateOfEnquiry" : "03-09-2021"
            }, 
            {
                "enquiryPurpose" : "02", 
                "memberName" : "NOT DISCLOSED", 
                "enquiryAmount" : "708000", 
                "dateOfEnquiry" : "10-07-2021"
            }, 
            {
                "enquiryPurpose" : "10", 
                "memberName" : "NOT DISCLOSED", 
                "enquiryAmount" : "1000", 
                "dateOfEnquiry" : "10-09-2020"
            }, 
            {
                "enquiryPurpose" : "02", 
                "memberName" : "NOT DISCLOSED", 
                "enquiryAmount" : "100000", 
                "dateOfEnquiry" : "05-09-2020"
            }, 
            {
                "enquiryPurpose" : "02", 
                "memberName" : "NOT DISCLOSED", 
                "enquiryAmount" : "100000", 
                "dateOfEnquiry" : "04-09-2020"
            }, 
            {
                "enquiryPurpose" : "02", 
                "memberName" : "NOT DISCLOSED", 
                "enquiryAmount" : "1000000", 
                "dateOfEnquiry" : "12-03-2019"
            }
        ], 
        "officeInfo" : [
            {
                "occupationType" : "Self Employed", 
                "userIncome" : "360000000"
            }
        ], 
        "bureauStatus" : "SUCCESS"
    }, 
    "isOutputValid" : true
}

输出应为：

pandas

来源：https://stackoverflow.com/questions/75926838/how-to-read-json-file-and-stored-it-in-data-frame-with-columns-and-multiple-rows

1条答案

按热度按时间

ozxc1zmp1#

您可以将json_normalize与.explode一起使用，然后稍微清理一下

import pandas as pd
import json

json_fp = 'x.json' ## put the ACTUAL file path here
with open(json_fp) as f: data = json.load(f)

df = pd.json_normalize(data)
df.columns = [c.split('.')[-1] for c in df.columns]

df = df.explode('accountInfo').reset_index(drop=True)
df = pd.concat([df, pd.json_normalize(df['accountInfo'])], axis=1)
df = df.drop(columns=['accountInfo','enquiryInfo','officeInfo'])

最后，df应该看起来像

╒════╤══════════════════════════╤══════════════════════════╤════════════╤══════════════════════════╤════════╤═════════════════╤═════════════════════════╤═══════════════╤══════════════════════╤═══════════════════╤════════════════╤══════════════════╤══════════════════════╤══════════════╤══════════════════════════════╤═════════════╤═══════════════╤══════════════════╤════════════════╤════════════════════════════════════════════════════════╤═══════════════╤═══════════════╤════════════════════╤══════════════╤════════════════════╤═════════════════════╤═══════════════════╤═════════════╤═══════════════════╕
│    │ _id                      │ userId                   │   tenantId │ createDate               │ type   │ isOutputValid   │ creditReportTimeStamp   │   creditScore │   creditscoreVersion │ creditScoreName   │ bureauStatus   │ paymentEndDate   │   ownershipIndicator │ dateOpened   │   highCreditSanctionedAmount │   cashLimit │   accountType │   currentBalance │ dateReported   │                                        paymentHistory1 │ memberName    │   creditLimit │ paymentStartDate   │ dateClosed   │ paymentFrequency   │ dateOfLastPayment   │   paymentHistory2 │   emiAmount │   repaymentTenure │
╞════╪══════════════════════════╪══════════════════════════╪════════════╪══════════════════════════╪════════╪═════════════════╪═════════════════════════╪═══════════════╪══════════════════════╪═══════════════════╪════════════════╪══════════════════╪══════════════════════╪══════════════╪══════════════════════════════╪═════════════╪═══════════════╪══════════════════╪════════════════╪════════════════════════════════════════════════════════╪═══════════════╪═══════════════╪════════════════════╪══════════════╪════════════════════╪═════════════════════╪═══════════════════╪═════════════╪═══════════════════╡
│  0 │ 63bbe1580b485627714220a3 │ 63a2a7b83d9cfb08817540d2 │        319 │ 2023-01-09T09:41:44.772Z │ cibil  │ True            │ 24-12-2022              │         00779 │                   10 │ CIBILTUSC3        │ SUCCESS        │ 01-06-2022       │                    1 │ 17-06-2022   │                        50000 │       10000 │            10 │                0 │ 31-10-2022     │                                        000000000000000 │ NOT DISCLOSED │         50000 │ 01-10-2022         │ nan          │ nan                │ nan                 │               nan │         nan │               nan │
├────┼──────────────────────────┼──────────────────────────┼────────────┼──────────────────────────┼────────┼─────────────────┼─────────────────────────┼───────────────┼──────────────────────┼───────────────────┼────────────────┼──────────────────┼──────────────────────┼──────────────┼──────────────────────────────┼─────────────┼───────────────┼──────────────────┼────────────────┼────────────────────────────────────────────────────────┼───────────────┼───────────────┼────────────────────┼──────────────┼────────────────────┼─────────────────────┼───────────────────┼─────────────┼───────────────────┤
│  1 │ 63bbe1580b485627714220a3 │ 63a2a7b83d9cfb08817540d2 │        319 │ 2023-01-09T09:41:44.772Z │ cibil  │ True            │ 24-12-2022              │         00779 │                   10 │ CIBILTUSC3        │ SUCCESS        │ 01-09-2021       │                    1 │ 29-09-2021   │                        50000 │        5000 │            10 │                0 │ 15-09-2022     │                   000000000000000000000000000000000000 │ NOT DISCLOSED │         50000 │ 01-08-2022         │ 31-08-2022   │ Monthly            │ nan                 │               nan │         nan │               nan │
├────┼──────────────────────────┼──────────────────────────┼────────────┼──────────────────────────┼────────┼─────────────────┼─────────────────────────┼───────────────┼──────────────────────┼───────────────────┼────────────────┼──────────────────┼──────────────────────┼──────────────┼──────────────────────────────┼─────────────┼───────────────┼──────────────────┼────────────────┼────────────────────────────────────────────────────────┼───────────────┼───────────────┼────────────────────┼──────────────┼────────────────────┼─────────────────────┼───────────────────┼─────────────┼───────────────────┤
│  2 │ 63bbe1580b485627714220a3 │ 63a2a7b83d9cfb08817540d2 │        319 │ 2023-01-09T09:41:44.772Z │ cibil  │ True            │ 24-12-2022              │         00779 │                   10 │ CIBILTUSC3        │ SUCCESS        │ 01-09-2020       │                    1 │ 10-09-2020   │                        44758 │         nan │            10 │            12742 │ 31-10-2022     │ 000000000000000000000000000000000000000000000000000000 │ NOT DISCLOSED │           nan │ 01-10-2022         │ nan          │ nan                │ 29-10-2022          │                 0 │         nan │               nan │
├────┼──────────────────────────┼──────────────────────────┼────────────┼──────────────────────────┼────────┼─────────────────┼─────────────────────────┼───────────────┼──────────────────────┼───────────────────┼────────────────┼──────────────────┼──────────────────────┼──────────────┼──────────────────────────────┼─────────────┼───────────────┼──────────────────┼────────────────┼────────────────────────────────────────────────────────┼───────────────┼───────────────┼────────────────────┼──────────────┼────────────────────┼─────────────────────┼───────────────────┼─────────────┼───────────────────┤
│  3 │ 63bbe1580b485627714220a3 │ 63a2a7b83d9cfb08817540d2 │        319 │ 2023-01-09T09:41:44.772Z │ cibil  │ True            │ 24-12-2022              │         00779 │                   10 │ CIBILTUSC3        │ SUCCESS        │ 01-11-2019       │                    4 │ 22-03-2019   │                      1000000 │         nan │            44 │           536400 │ 31-10-2022     │ 000000000000000000000000000000000000000000000000000000 │ NOT DISCLOSED │           nan │ 01-10-2022         │ nan          │ Monthly            │ 25-10-2022          │                 0 │        5067 │               240 │
├────┼──────────────────────────┼──────────────────────────┼────────────┼──────────────────────────┼────────┼─────────────────┼─────────────────────────┼───────────────┼──────────────────────┼───────────────────┼────────────────┼──────────────────┼──────────────────────┼──────────────┼──────────────────────────────┼─────────────┼───────────────┼──────────────────┼────────────────┼────────────────────────────────────────────────────────┼───────────────┼───────────────┼────────────────────┼──────────────┼────────────────────┼─────────────────────┼───────────────────┼─────────────┼───────────────────┤
│  4 │ 63bbe1580b485627714220a3 │ 63a2a7b83d9cfb08817540d2 │        319 │ 2023-01-09T09:41:44.772Z │ cibil  │ True            │ 24-12-2022              │         00779 │                   10 │ CIBILTUSC3        │ SUCCESS        │ 01-11-2019       │                    4 │ 22-03-2019   │                        26589 │         nan │            03 │             8830 │ 31-10-2022     │ 000000000000000000000000000000000000000000000000000000 │ NOT DISCLOSED │           nan │ 01-10-2022         │ nan          │ Monthly            │ 25-10-2022          │                 0 │         590 │                60 │
╘════╧══════════════════════════╧══════════════════════════╧════════════╧══════════════════════════╧════════╧═════════════════╧═════════════════════════╧═══════════════╧══════════════════════╧═══════════════════╧════════════════╧══════════════════╧══════════════════════╧══════════════╧══════════════════════════════╧═════════════╧═══════════════╧══════════════════╧════════════════╧════════════════════════════════════════════════════════╧═══════════════╧═══════════════╧════════════════════╧══════════════╧════════════════════╧═════════════════════╧═══════════════════╧═════════════╧═══════════════════╛

[打印print(df.to_markdown(tablefmt='fancy_grid')) ]

赞(0）回复(0）举报 2023-04-10

我来回答

pandas中如何读取json文件并将其存储在具有列和多行的数据框中

1条答案

相关问题

热门标签

最新问答