Hướng dẫn write nested dictionary to excel python - viết từ điển lồng nhau vào excel python

Bạn có thể sử dụng csv.DictWriter và csv.DictReader như thế này:

import csv

d = {'Apple':{'Weight': 12,
              'Colour': 'red'},
     'Banana':{'Weight': 11,
               'Colour': 'yellow',
               'Bunched': 1}}

fieldnames = ["name", "Weight", "Colour", "Bunched"]

with open("fruits.csv", "w") as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    for fruit, fruit_info in d.items():
        row = fruit_info
        row.update({"name": fruit})
        writer.writerow(row)

with open('fruits.csv', 'r') as f:
    reader = csv.DictReader(f, fieldnames=fieldnames)
    ret_d = {}
    for row in reader:
        fruit = {}
        for key, value in row.items():
            if not key == "name" and value:
                fruit[key] = value
        ret_d[row["name"]] = fruit
    print(ret_d)

Với giải pháp này, bạn cần làm phẳng từ điển, bởi vì csv.DictWriter viết từng hàng với một mô hình sau:

{column1: data, column2: data, column3: data, ...}

Và bạn phải đưa ra các tên trường, bởi vì một dict không được đặt hàng.

Xem các tài liệu để biết thêm thông tin.

Từ điển của tôi sẽ tương tự như dưới đây. Tôi muốn nó không có các thuật ngữ màu đỏ và tôi muốn mỗi hàng là một phần tư ngày đầu ra và mỗi tiêu đề là một cột có các giá trị từ điển. Bất kỳ sự giúp đỡ của làm thế nào để biến điều này thành một bảng trong excel? Cảm ơn!!

Ví dụ đầu ra: Tổng số nợ thay đổi ................. 2018-09-30 64000000 ................
Total change liabilities .................
2018-09-30 64000000 ................

{'CashflowStatementHistoryQuarterly': {'apa': [{'2018-09-30': { ': -48000000,' Netincome ': 81000000,' Changeincash ': -379000000,' TotalCashFromOperationActivities ': 1006000000,' 'ChangetoAcCountreCeVables': -12000000, 'KhácCashFlowsFromFinanceCeralies': -135000000, 'Changetonetincome': 243000000, 'CapitalExpenditures': -942000000},'cashflowStatementHistoryQuarterly': {'APA':
[{'2018-09-30': {'changeToLiabilities': 64000000, 'totalCashflowsFromInvestingActivities': -926000000, 'netBorrowings': -228000000, 'totalCashFromFinancingActivities': -459000000, 'changeToOperatingActivities': -48000000, 'netIncome': 81000000, 'changeInCash': -379000000, 'totalCashFromOperatingActivities': 1006000000, 'depreciation': 676000000, 'otherCashflowsFromInvestingActivities': -22000000, 'dividendsPaid': -96000000, 'changeToInventory': 2000000, 'changeToAccountReceivables': -12000000, 'otherCashflowsFromFinancingActivities': -135000000, 'changeToNetincome': 243000000, 'capitalExpenditures': -942000000}},

{'2018-06-30': {'ChangeToliabilities': -20000000, 'TotalCashFlowsFromInvestingActivities': -1024000000, 'NetBorrowing': -228000000 'Changeincash': -105000000, 'TotalCashFromOperationActivities': 1113000000, 'Khấu hao': 656000000, 'KhácCashFlowsFromInvestingActivities': -11000000, ' -98000000, 'changetonetincome': 81000000, 'capitalexpenditures': -1017000000}}, {'2018-03-31': " Hoạt động ': -22000000,' SexidEndSpaid ': -95000000, '2017-12-31': {'ChangeToliaBitability': 61000000, 'TotalCashFlowsFromInvestingActivities': -793000000, ' ': -274000000,' TotalCashFromOperationActivities ': 668000000,' khấu hao ': 632000000 , 'changetonetincome': -328000000, 'capitalexpenditures': -763000000}}]}}}}}}}
{'2018-03-31': {'changeToLiabilities': 66000000, 'totalCashflowsFromInvestingActivities': -890000000, 'netBorrowings': -150000000, 'totalCashFromFinancingActivities': -316000000, 'changeToOperatingActivities': -152000000, 'netIncome': 145000000, 'changeInCash': -591000000, 'totalCashFromOperatingActivities': 615000000, 'depreciation': 596000000, 'otherCashflowsFromInvestingActivities': -22000000, 'dividendsPaid': -95000000, 'changeToInventory': -33000000, 'changeToAccountReceivables': -65000000, 'otherCashflowsFromFinancingActivities': -71000000, 'changeToNetincome': 58000000, 'capitalExpenditures': -877000000}},
{'2017-12-31': {'changeToLiabilities': 61000000, 'totalCashflowsFromInvestingActivities': -793000000, 'netBorrowings': -150000000, 'totalCashFromFinancingActivities': -149000000, 'changeToOperatingActivities': 32000000, 'netIncome': 456000000, 'changeInCash': -274000000, 'totalCashFromOperatingActivities': 668000000, 'depreciation': 632000000, 'otherCashflowsFromInvestingActivities': -45000000, 'dividendsPaid': -95000000, 'changeToInventory': 15000000, 'changeToAccountReceivables': -200000000, 'otherCashflowsFromFinancingActivities': -54000000, 'changeToNetincome': -328000000, 'capitalExpenditures': -763000000}}]}}

Bài viết: 11.42311,423

Chủ đề: 435435

Tham gia: Tháng 9 năm 2016Sep 2016

Danh tiếng: 441 441

Bạn đã tạo từ điển chưa? Nếu không, việc xử lý dễ dàng hơn nhiều nếu các từ điển nhúng không nằm trong ví dụ danh sách:
If not, it's much easier to handle if the embedded dictionaries are not in a list
Example:

import json
import os
import pandas as pd


class DictToExcel:
    os.chdir(os.path.abspath(os.path.dirname(__file__)))
    def __init__(self):
        self.exdict = {
            'cashflowStatementHistoryQuarterly': {
                'APA': {
                    '2018-09-30': {
                        'changeToLiabilities': 64000000, 
                        'totalCashflowsFromInvestingActivities': -926000000, 
                        'netBorrowings': -228000000, 
                        'totalCashFromFinancingActivities': -459000000, 
                        'changeToOperatingActivities': -48000000, 
                        'netIncome': 81000000, 
                        'changeInCash': -379000000, 
                        'totalCashFromOperatingActivities': 1006000000, 
                        'depreciation': 676000000, 
                        'otherCashflowsFromInvestingActivities': -22000000, 
                        'dividendsPaid': -96000000, 
                        'changeToInventory': 2000000, 
                        'changeToAccountReceivables': -12000000, 
                        'otherCashflowsFromFinancingActivities': -135000000, 
                        'changeToNetincome': 243000000, 
                        'capitalExpenditures': -942000000
                    },
                    '2018-06-30': {
                        'changeToLiabilities': -20000000, 
                        'totalCashflowsFromInvestingActivities': -1024000000, 
                        'netBorrowings': -228000000, 
                        'totalCashFromFinancingActivities': -194000000, 
                        'changeToOperatingActivities': 213000000, 
                        'netIncome': 195000000, 
                        'changeInCash': -105000000, 
                        'totalCashFromOperatingActivities': 1113000000, 
                        'depreciation': 656000000, 
                        'otherCashflowsFromInvestingActivities': -11000000, 
                        'dividendsPaid': -96000000, 
                        'changeToInventory': 24000000, 
                        'changeToAccountReceivables': -36000000, 
                        'otherCashflowsFromFinancingActivities': -98000000, 
                        'changeToNetincome': 81000000, 
                        'capitalExpenditures': -1017000000
                    },
                    '2018-03-31': {
                        'changeToLiabilities': 66000000,
                        'totalCashflowsFromInvestingActivities': -890000000,
                        'netBorrowings': -150000000,
                        'totalCashFromFinancingActivities': -316000000,
                        'changeToOperatingActivities': -152000000,
                        'netIncome': 145000000,
                        'changeInCash': -591000000,
                        'totalCashFromOperatingActivities': 615000000,
                        'depreciation': 596000000,
                        'otherCashflowsFromInvestingActivities': -22000000,
                        'dividendsPaid': -95000000,
                        'changeToInventory': -33000000,
                        'changeToAccountReceivables': -65000000,
                        'otherCashflowsFromFinancingActivities': -71000000,
                        'changeToNetincome': 58000000,
                        'capitalExpenditures': -877000000
                    },
                    '2017-12-31': {
                        'changeToLiabilities': 61000000,
                        'totalCashflowsFromInvestingActivities': -793000000,
                        'netBorrowings': -150000000,
                        'totalCashFromFinancingActivities': -149000000,
                        'changeToOperatingActivities': 32000000,
                        'netIncome': 456000000,
                        'changeInCash': -274000000,
                        'totalCashFromOperatingActivities': 668000000,
                        'depreciation': 632000000,
                        'otherCashflowsFromInvestingActivities': -45000000,
                        'dividendsPaid': -95000000,
                        'changeToInventory': 15000000,
                        'changeToAccountReceivables': -200000000,
                        'otherCashflowsFromFinancingActivities': -54000000,
                        'changeToNetincome': -328000000,
                        'capitalExpenditures': -763000000
                    }
                }
            }
        }
        self.display_dict(self.exdict)

    def display_dict(self, thedict):
        for key, value in thedict.items():
            if isinstance(value, dict):
                print(f'\n{key}:')
                self.display_dict(value)
            else:
                print(f'    {key}: {value}')


if __name__ == '__main__':
    DictToExcel()

Phương thức Display_DICT cho thấy mức độ dễ dàng của việc đi qua từ điển nếu ở định dạng này và tạo ra các kết quả sau:
and produces the following results:

Output:
cashflowStatementHistoryQuarterly:

APA:

2018-09-30:
    changeToLiabilities: 64000000
    totalCashflowsFromInvestingActivities: -926000000
    netBorrowings: -228000000
    totalCashFromFinancingActivities: -459000000
    changeToOperatingActivities: -48000000
    netIncome: 81000000
    changeInCash: -379000000
    totalCashFromOperatingActivities: 1006000000
    depreciation: 676000000
    otherCashflowsFromInvestingActivities: -22000000
    dividendsPaid: -96000000
    changeToInventory: 2000000
    changeToAccountReceivables: -12000000
    otherCashflowsFromFinancingActivities: -135000000
    changeToNetincome: 243000000
    capitalExpenditures: -942000000

2018-06-30:
    changeToLiabilities: -20000000
    totalCashflowsFromInvestingActivities: -1024000000
    netBorrowings: -228000000
    totalCashFromFinancingActivities: -194000000
    changeToOperatingActivities: 213000000
    netIncome: 195000000
    changeInCash: -105000000
    totalCashFromOperatingActivities: 1113000000
    depreciation: 656000000
    otherCashflowsFromInvestingActivities: -11000000
    dividendsPaid: -96000000
    changeToInventory: 24000000
    changeToAccountReceivables: -36000000
    otherCashflowsFromFinancingActivities: -98000000
    changeToNetincome: 81000000
    capitalExpenditures: -1017000000

2018-03-31:
    changeToLiabilities: 66000000
    totalCashflowsFromInvestingActivities: -890000000
    netBorrowings: -150000000
    totalCashFromFinancingActivities: -316000000
    changeToOperatingActivities: -152000000
    netIncome: 145000000
    changeInCash: -591000000
    totalCashFromOperatingActivities: 615000000
    depreciation: 596000000
    otherCashflowsFromInvestingActivities: -22000000
    dividendsPaid: -95000000
    changeToInventory: -33000000
    changeToAccountReceivables: -65000000
    otherCashflowsFromFinancingActivities: -71000000
    changeToNetincome: 58000000
    capitalExpenditures: -877000000

2017-12-31:
    changeToLiabilities: 61000000
    totalCashflowsFromInvestingActivities: -793000000
    netBorrowings: -150000000
    totalCashFromFinancingActivities: -149000000
    changeToOperatingActivities: 32000000
    netIncome: 456000000
    changeInCash: -274000000
    totalCashFromOperatingActivities: 668000000
    depreciation: 632000000
    otherCashflowsFromInvestingActivities: -45000000
    dividendsPaid: -95000000
    changeToInventory: 15000000
    changeToAccountReceivables: -200000000
    otherCashflowsFromFinancingActivities: -54000000
    changeToNetincome: -328000000
    capitalExpenditures: -763000000