使用Python将嵌套JSON导出到CSV

2024-09-28 22:34:55 发布

您现在位置：Python中文网/ 问答频道 /正文
7657 3

我有以下从Xero获得的JSON脚本。 这是一个嵌套的JSON脚本，im试图创建一个平面表，然后将其导出到CSV。在

我已经编写了这段python代码，但是我正在努力使嵌套的JSON脚本变平。 最初我从Xero获取数据，然后使用json.dumps文件以便序列化日期时间。这里显示的是Postman导出的JSON软件。当我使用python获取JSON脚本时，日期格式如下所示'UpdatedDateUTC'：日期时间。日期时间（2018年，10、24、12、53、55、93万）。所以我用json.dumps文件以便将其序列化。在

当我生产第一个出口产品时：

df = pd.read_json(b_str)
df.to_csv(path+'invoices.csv')

CSV文件如下所示：

![enter image description here

下一步是展平联系人和CreditNotes列，并使它们成为主表的一部分。因此，代替联系人列将有8个新列：联系人ID、联系人号码、姓名、地址、电话、联系人组、联系人、HasValidationErrors。CreditNotes列的类似流程

我试图在这个link上复制方法论，但没有成功。我得到一个像这样的出口。contacts_with_id dataframe显示在多行而不是多列上。我不知道我做错了什么。在

enter image description here

我也使用了flant_json函数，但也没有什么好运气。在

我真的不需要让这种方法发挥作用。我只想找到一种方法将嵌套的json脚本导出到可读的csv文件。在

Python代码：

^{pr2}$

Json脚本如下：

{
"Id": "568d1686-7c53-4f22-a93f-754589a246a7",
"Status": "OK",
"ProviderName": "Rest API",
"DateTimeUTC": "/Date(1552234854959)/",
"Invoices": [
    {
        "Type": "ACCPAY",
        "InvoiceID": "8289ab9d-2134-4601-8622-e7fdae4b6d89",
        "InvoiceNumber": "10522",
        "Reference": "10522",
        "Payments": [],
        "CreditNotes": [],
        "Prepayments": [],
        "Overpayments": [],
        "AmountDue": 102,
        "AmountPaid": 0,
        "AmountCredited": 0,
        "CurrencyRate": 1,
        "HasErrors": false,
        "IsDiscounted": false,
        "HasAttachments": false,
        "Contact": {
            "ContactID": "d1dba397-0f0b-4819-a6ce-2839b7be5008",
            "ContactNumber": "c03bbcb5-fb0b-4f46-83f0-8687f754488b",
            "Name": "Micro",
            "Addresses": [],
            "Phones": [],
            "ContactGroups": [],
            "ContactPersons": [],
            "HasValidationErrors": false
        },
        "DateString": "2017-02-06T00:00:00",
        "Date": "/Date(1486339200000+0000)/",
        "DueDateString": "2017-03-08T00:00:00",
        "DueDate": "/Date(1488931200000+0000)/",
        "Status": "AUTHORISED",
        "LineAmountTypes": "Exclusive",
        "LineItems": [],
        "SubTotal": 85,
        "TotalTax": 17,
        "Total": 102,
        "UpdatedDateUTC": "/Date(1529940362110+0000)/",
        "CurrencyCode": "GBP"
    },
    {
        "Type": "ACCREC",
        "InvoiceID": "9e37150f-88a5-4213-a085-b30c5e01c2bf",
        "InvoiceNumber": "(13)",
        "Reference": "",
        "Payments": [],
        "CreditNotes": [
            {
                "CreditNoteID": "3c5c7dec-534a-46e0-ad1b-f0f69822cfd5",
                "CreditNoteNumber": "(12)",
                "ID": "3c5c7dec-534a-46e0-ad1b-f0f69822cfd5",
                "AppliedAmount": 1200,
                "DateString": "2011-05-04T00:00:00",
                "Date": "/Date(1304467200000+0000)/",
                "LineItems": [],
                "Total": 7800
            },
            {
                "CreditNoteID": "af38e37f-4ba3-4208-a193-a32b418c2bbc",
                "CreditNoteNumber": "(14)",
                "ID": "af38e37f-4ba3-4208-a193-a32b418c2bbc",
                "AppliedAmount": 2600,
                "DateString": "2011-05-04T00:00:00",
                "Date": "/Date(1304467200000+0000)/",
                "LineItems": [],
                "Total": 2600
            }
        ],
        "Prepayments": [],
        "Overpayments": [],
        "AmountDue": 0,
        "AmountPaid": 0,
        "AmountCredited": 3800,
        "CurrencyRate": 1,
        "HasErrors": false,
        "IsDiscounted": false,
        "HasAttachments": false,
        "Contact": {
            "ContactID": "58164bd6-5225-4f30-ad89-35140db5b624",
            "ContactNumber": "d0b420b8-4a58-40d1-9717-8525edda7658",
            "Name": "FSales (1)",
            "Addresses": [],
            "Phones": [],
            "ContactGroups": [],
            "ContactPersons": [],
            "HasValidationErrors": false
        },
        "DateString": "2011-05-04T00:00:00",
        "Date": "/Date(1304467200000+0000)/",
        "DueDateString": "2011-06-03T00:00:00",
        "DueDate": "/Date(1307059200000+0000)/",
        "Status": "PAID",
        "LineAmountTypes": "Exclusive",
        "LineItems": [],
        "SubTotal": 3166.67,
        "TotalTax": 633.33,
        "Total": 3800,
        "UpdatedDateUTC": "/Date(1529943661150+0000)/",
        "CurrencyCode": "GBP",
        "FullyPaidOnDate": "/Date(1304467200000+0000)/"
    },
    {
        "Type": "ACCPAY",
        "InvoiceID": "1ddea7ec-a0d5-457a-a8fd-cfcdc2099d51",
        "InvoiceNumber": "01596057543",
        "Reference": "",
        "Payments": [
            {
                "PaymentID": "fd639da3-c009-47df-a4bf-98ccd5c68e43",
                "Date": "/Date(1551657600000+0000)/",
                "Amount": 173.86,
                "Reference": "",
                "CurrencyRate": 1,
                "HasAccount": false,
                "HasValidationErrors": false
            }
        ],
        "CreditNotes": [],
        "Prepayments": [],
        "Overpayments": [],
        "AmountDue": 0,
        "AmountPaid": 173.86,
        "AmountCredited": 0,
        "CurrencyRate": 1,
        "HasErrors": false,
        "IsDiscounted": false,
        "HasAttachments": true,
        "Contact": {
            "ContactID": "309afb74-0a3b-4d68-85e8-2259ca5acd13",
            "ContactNumber": "91eef1f0-5fe6-45d7-b739-1ab5352a5523",
            "Name": "Company AAA",
            "Addresses": [],
            "Phones": [],
            "ContactGroups": [],
            "ContactPersons": [],
            "HasValidationErrors": false
        },
        "DateString": "2019-02-23T00:00:00",
        "Date": "/Date(1550880000000+0000)/",
        "DueDateString": "2019-03-21T00:00:00",
        "DueDate": "/Date(1553126400000+0000)/",
        "Status": "PAID",
        "LineAmountTypes": "Exclusive",
        "LineItems": [],
        "SubTotal": 144.88,
        "TotalTax": 28.98,
        "Total": 173.86,
        "UpdatedDateUTC": "/Date(1551777481907+0000)/",
        "CurrencyCode": "GBP",
        "FullyPaidOnDate": "/Date(1551657600000+0000)/"
    },
    {
        "Type": "ACCPAY",
        "InvoiceID": "ba5ff3b1-1058-4645-80da-5475c23da949",
        "InvoiceNumber": "Q0603",
        "Reference": "",
        "Payments": [],
        "CreditNotes": [],
        "Prepayments": [],
        "Overpayments": [],
        "AmountDue": 213.24,
        "AmountPaid": 0,
        "AmountCredited": 0,
        "CurrencyRate": 1,
        "HasErrors": false,
        "IsDiscounted": false,
        "HasAttachments": true,
        "Contact": {
            "ContactID": "f0473b41-da92-4397-9d2c-741812f2475c",
            "ContactNumber": "1f124969-de8d-40b8-8140-d4997511b0dc",
            "Name": "BTelcom",
            "Addresses": [],
            "Phones": [],
            "ContactGroups": [],
            "ContactPersons": [],
            "HasValidationErrors": false
        },
        "DateString": "2019-03-05T00:00:00",
        "Date": "/Date(1551744000000+0000)/",
        "DueDateString": "2019-03-21T00:00:00",
        "DueDate": "/Date(1553126400000+0000)/",
        "Status": "SUBMITTED",
        "LineAmountTypes": "Exclusive",
        "LineItems": [],
        "SubTotal": 177.7,
        "TotalTax": 35.54,
        "Total": 213.24,
        "UpdatedDateUTC": "/Date(1552068778417+0000)/",
        "CurrencyCode": "GBP"
    }
]

}


Tags： 脚本jsonfalsedatestatus联系人totalreference
1条回答
网友
1楼 · 发布于 2024-09-28 22:34:55

我以前做过这样的事：

基本上是将整个嵌套的json放平，然后遍历这些列（它使用一个模式来包含它将被构造到表中的哪一行）来创建新行。在

有4个发票，这将创建4行（针对每个发票）。希望这就是你想要的。在

注意您可能会遇到一些问题：

如果试图展开一个json文件，其中有嵌套列表，并且嵌套列表的长度不同，那么需要考虑的一件事是，只要一行对任何给定的列有一个值，它就必须创建该列，即使所有其他行都为null。在这个Payments键中，有一个列表包含另外7个元素。因此，如果某些ID有8个付款（而不是所有其他ID只有1个付款），则必须创建56个附加列以将所有这些存储在单独的列/平面文件中。在

jsonStr = '''{
"Id": "568d1686-7c53-4f22-a93f-754589a246a7",
"Status": "OK",
"ProviderName": "Rest API",
"DateTimeUTC": "/Date(1552234854959)/",
"Invoices": [
    {
        "Type": "ACCPAY",
        "InvoiceID": "8289ab9d-2134-4601-8622-e7fdae4b6d89",
        "InvoiceNumber": "10522",
        "Reference": "10522",
        "Payments": [],
        "CreditNotes": [],
        "Prepayments": [],
        "Overpayments": [],
        "AmountDue": 102,
        "AmountPaid": 0,
        "AmountCredited": 0,
        "CurrencyRate": 1,
        "HasErrors": false,
        "IsDiscounted": false,
        "HasAttachments": false,
        "Contact": {
            "ContactID": "d1dba397-0f0b-4819-a6ce-2839b7be5008",
            "ContactNumber": "c03bbcb5-fb0b-4f46-83f0-8687f754488b",
            "Name": "Micro",
            "Addresses": [],
            "Phones": [],
            "ContactGroups": [],
            "ContactPersons": [],
            "HasValidationErrors": false
        },
        "DateString": "2017-02-06T00:00:00",
        "Date": "/Date(1486339200000+0000)/",
        "DueDateString": "2017-03-08T00:00:00",
        "DueDate": "/Date(1488931200000+0000)/",
        "Status": "AUTHORISED",
        "LineAmountTypes": "Exclusive",
        "LineItems": [],
        "SubTotal": 85,
        "TotalTax": 17,
        "Total": 102,
        "UpdatedDateUTC": "/Date(1529940362110+0000)/",
        "CurrencyCode": "GBP"
    },
    {
        "Type": "ACCREC",
        "InvoiceID": "9e37150f-88a5-4213-a085-b30c5e01c2bf",
        "InvoiceNumber": "(13)",
        "Reference": "",
        "Payments": [],
        "CreditNotes": [
            {
                "CreditNoteID": "3c5c7dec-534a-46e0-ad1b-f0f69822cfd5",
                "CreditNoteNumber": "(12)",
                "ID": "3c5c7dec-534a-46e0-ad1b-f0f69822cfd5",
                "AppliedAmount": 1200,
                "DateString": "2011-05-04T00:00:00",
                "Date": "/Date(1304467200000+0000)/",
                "LineItems": [],
                "Total": 7800
            },
            {
                "CreditNoteID": "af38e37f-4ba3-4208-a193-a32b418c2bbc",
                "CreditNoteNumber": "(14)",
                "ID": "af38e37f-4ba3-4208-a193-a32b418c2bbc",
                "AppliedAmount": 2600,
                "DateString": "2011-05-04T00:00:00",
                "Date": "/Date(1304467200000+0000)/",
                "LineItems": [],
                "Total": 2600
            }
        ],
        "Prepayments": [],
        "Overpayments": [],
        "AmountDue": 0,
        "AmountPaid": 0,
        "AmountCredited": 3800,
        "CurrencyRate": 1,
        "HasErrors": false,
        "IsDiscounted": false,
        "HasAttachments": false,
        "Contact": {
            "ContactID": "58164bd6-5225-4f30-ad89-35140db5b624",
            "ContactNumber": "d0b420b8-4a58-40d1-9717-8525edda7658",
            "Name": "FSales (1)",
            "Addresses": [],
            "Phones": [],
            "ContactGroups": [],
            "ContactPersons": [],
            "HasValidationErrors": false
        },
        "DateString": "2011-05-04T00:00:00",
        "Date": "/Date(1304467200000+0000)/",
        "DueDateString": "2011-06-03T00:00:00",
        "DueDate": "/Date(1307059200000+0000)/",
        "Status": "PAID",
        "LineAmountTypes": "Exclusive",
        "LineItems": [],
        "SubTotal": 3166.67,
        "TotalTax": 633.33,
        "Total": 3800,
        "UpdatedDateUTC": "/Date(1529943661150+0000)/",
        "CurrencyCode": "GBP",
        "FullyPaidOnDate": "/Date(1304467200000+0000)/"
    },
    {
        "Type": "ACCPAY",
        "InvoiceID": "1ddea7ec-a0d5-457a-a8fd-cfcdc2099d51",
        "InvoiceNumber": "01596057543",
        "Reference": "",
        "Payments": [
            {
                "PaymentID": "fd639da3-c009-47df-a4bf-98ccd5c68e43",
                "Date": "/Date(1551657600000+0000)/",
                "Amount": 173.86,
                "Reference": "",
                "CurrencyRate": 1,
                "HasAccount": false,
                "HasValidationErrors": false
            }
        ],
        "CreditNotes": [],
        "Prepayments": [],
        "Overpayments": [],
        "AmountDue": 0,
        "AmountPaid": 173.86,
        "AmountCredited": 0,
        "CurrencyRate": 1,
        "HasErrors": false,
        "IsDiscounted": false,
        "HasAttachments": true,
        "Contact": {
            "ContactID": "309afb74-0a3b-4d68-85e8-2259ca5acd13",
            "ContactNumber": "91eef1f0-5fe6-45d7-b739-1ab5352a5523",
            "Name": "Company AAA",
            "Addresses": [],
            "Phones": [],
            "ContactGroups": [],
            "ContactPersons": [],
            "HasValidationErrors": false
        },
        "DateString": "2019-02-23T00:00:00",
        "Date": "/Date(1550880000000+0000)/",
        "DueDateString": "2019-03-21T00:00:00",
        "DueDate": "/Date(1553126400000+0000)/",
        "Status": "PAID",
        "LineAmountTypes": "Exclusive",
        "LineItems": [],
        "SubTotal": 144.88,
        "TotalTax": 28.98,
        "Total": 173.86,
        "UpdatedDateUTC": "/Date(1551777481907+0000)/",
        "CurrencyCode": "GBP",
        "FullyPaidOnDate": "/Date(1551657600000+0000)/"
    },
    {
        "Type": "ACCPAY",
        "InvoiceID": "ba5ff3b1-1058-4645-80da-5475c23da949",
        "InvoiceNumber": "Q0603",
        "Reference": "",
        "Payments": [],
        "CreditNotes": [],
        "Prepayments": [],
        "Overpayments": [],
        "AmountDue": 213.24,
        "AmountPaid": 0,
        "AmountCredited": 0,
        "CurrencyRate": 1,
        "HasErrors": false,
        "IsDiscounted": false,
        "HasAttachments": true,
        "Contact": {
            "ContactID": "f0473b41-da92-4397-9d2c-741812f2475c",
            "ContactNumber": "1f124969-de8d-40b8-8140-d4997511b0dc",
            "Name": "BTelcom",
            "Addresses": [],
            "Phones": [],
            "ContactGroups": [],
            "ContactPersons": [],
            "HasValidationErrors": false
        },
        "DateString": "2019-03-05T00:00:00",
        "Date": "/Date(1551744000000+0000)/",
        "DueDateString": "2019-03-21T00:00:00",
        "DueDate": "/Date(1553126400000+0000)/",
        "Status": "SUBMITTED",
        "LineAmountTypes": "Exclusive",
        "LineItems": [],
        "SubTotal": 177.7,
        "TotalTax": 35.54,
        "Total": 213.24,
        "UpdatedDateUTC": "/Date(1552068778417+0000)/",
        "CurrencyCode": "GBP"
    }
]
}'''



import json
import pandas as pd
import re

def flatten_json(y):
    out = {}
    def flatten(x, name=''):
        if type(x) is dict:
            for a in x:
                flatten(x[a], name + a + '_')
        elif type(x) is list:
            i = 0
            for a in x:
                flatten(a, name + str(i) + '_')
                i += 1
        else:
            out[name[:-1]] = x
    flatten(y)
    return out

jsonObj = json.loads(jsonStr)
flat = flatten_json(jsonObj)

results = pd.DataFrame()
special_cols = []

columns_list = list(flat.keys())
for item in columns_list:
    try:
        row_idx = re.findall(r'\_(\d+)\_', item )[0]
    except:
        special_cols.append(item)
        continue
    column = re.findall(r'\_\d+\_(.*)', item )[0]
    column = column.replace('_', '')

    row_idx = int(row_idx)
    value = flat[item]

    results.loc[row_idx, column] = value

for item in special_cols:
    results[item] = flat[item]

输出：

^{pr2}$

相关问题 更多 >

    编程相关推荐