Python - Json to Excel

Python
1

merhaba
Jsondan excele veri aktarmak istiyorum, Json verisini okutup aktarımını yapabiliyorum ama mantığını yapamıyorum.

Python kodu:

import json
import pandas as pd
import openpyxl
with open(r'C:\Users\Asus\test\TEST\test.json', encoding='utf-8') as file:
data = json.loads(file.read())
invoices = data ['INVOICES']
df = pd.json_normalize(invoices)
print(invoices)
df.to_excel('output.xlsx')

buna göre çıktı Details ve Paymenti aynı hücrede otoplu olarak oluşturuyor.
hesap sonuna geldiği zamanda A2.A3.A4 olarak satır satır yazdığından kayma yapıyor
ama istediğim oluşum dizilim (a1.b1.c1… title a2.b2.c2 data), bittikten sonra aynı stunda sağa doğru ilk önce Details sonra Payment bilgilerinin yazmak. örneği aşağıda

2
21018×139 5.37 KB

Json bilgisi

{"INVOICES":[
{"CODE":"20240113_16EB01_286","ISEDOS":1,"LEGALNUMBER":"286","STATUS":"I","SALESTYPE":"VADELISATIS","ISSUEDATE":"2024-01-13T08:47:00","DUEDATE":"2024-01-20T00:00:00","CREDITDAYS":7,"NETAMOUNT":56.6800,"OUTSTANDINGAMOUNT":56.6800,"TAXAMOUNT":15.0000,"TOTAL_DISCOUNT":0.27,
"CUSTOMER":{"CODE":"368","REGISTEREDNAME":"NRKMEZ","TAXNUMBER":"201","LICENSENUMBER":"1598"},"POSITION":{"CODE":"D16DR01","DESCRIPTION":"01"},
"PAYMENTS":[{"CODE":"1692677759","ISSUEDATE":"2024-01-13T09:47:00","AMOUNT":77.4400,"PAYMENTFORM":{"CODE":"HAVALE","DESCRIPTION":"Havale"}},{"CODE":"169267","ISSUEDATE":"2024-01-13T09:47:00","AMOUNT":27.4400,"PAYMENTFORM":{"CODE":"NAKİT","DESCRIPTION":"NAKİT"}}],
"DETAILS":[
{"PRODUCT":{"SEQUENCE":63,"CODE":"URUN1","DESCRIPTION":"URUN1"},"QUANTITY":0.0,"NETAMOUNT":0.0000,"GROSSAMOUNT":0.0000,"PRICE":51.5700,"AVAILABILITY":0},
{"PRODUCT":{"SEQUENCE":64,"CODE":"URUN2","DESCRIPTION":"URUN2"},"QUANTITY":0.0,"NETAMOUNT":0.0000,"GROSSAMOUNT":0.0000,"PRICE":51.5700,"AVAILABILITY":0},
{"PRODUCT":{"SEQUENCE":57,"CODE":"URUN3","DESCRIPTION":"URUN3"},"QUANTITY":0.0,"NETAMOUNT":0.0000,"GROSSAMOUNT":0.0000,"PRICE":51.5700,"AVAILABILITY":0}],
"RETURNGOODS":null},
{"CODE":"20240113_16EB01_0290","ISEDOS":1,"LEGALNUMBER":"0290","STATUS":"I","SALESTYPE":"HHSATIS","ISSUEDATE":"2024-01-13T09:47:00","DUEDATE":"2024-01-13T00:00:00","CREDITDAYS":0,"NETAMOUNT":7.4400,"OUTSTANDINGAMOUNT":0.0000,"TAXAMOUNT":7.5100,"TOTAL_DISCOUNT":1.34,
"CUSTOMER":{"CODE":"6636","REGISTEREDNAME":"ARAS","TAXNUMBER":"3768","LICENSENUMBER":"1464"},"POSITION":{"CODE":"D16DR01","DESCRIPTION":"01"},
"PAYMENTS":[{"CODE":"1692677759","ISSUEDATE":"2024-01-13T09:47:00","AMOUNT":7.4400,"PAYMENTFORM":{"CODE":"HAVALE","DESCRIPTION":"Havale"}}],
"DETAILS":[
{"PRODUCT":{"SEQUENCE":63,"CODE":"URUN1","DESCRIPTION":"URUN1"},"QUANTITY":0.0,"NETAMOUNT":0.0000,"GROSSAMOUNT":0.0000,"PRICE":51.5700,"AVAILABILITY":0},
{"PRODUCT":{"SEQUENCE":64,"CODE":"URUN2","DESCRIPTION":"URUN2"},"QUANTITY":0.0,"NETAMOUNT":0.0000,"GROSSAMOUNT":0.0000,"PRICE":51.5700,"AVAILABILITY":0},
{"PRODUCT":{"SEQUENCE":57,"CODE":"URUN3","DESCRIPTION":"URUN3"},"QUANTITY":0.0,"NETAMOUNT":0.0000,"GROSSAMOUNT":0.0000,"PRICE":51.5700,"AVAILABILITY":0}],
"RETURNGOODS":null}]}
3

Merhaba, hoş geldiniz.

Anladığım kadarıyla, sözlük içindeki bütün anahtarların excel’de birer sütun ismi olarak oluşmasını ve verilerin de ilgili sütunlara yerleştirilmesini istiyorsunuz.

Mesela DETAILS için şöyle:

DETAILS PRODUCT SEQUENCE CODE DESCRIPTION QUANTITY NETAMOUNT GROSSAMOUNT PRICE AVAILABILITY PRODUCT SEQUENCE CODE DESCRIPTION QUANTITY NETAMOUNT GROSSAMOUNT PRICE AVAILABILITY PRODUCT SEQUENCE CODE DESCRIPTION QUANTITY NETAMOUNT GROSSAMOUNT PRICE AVAILABILITY
63 URUN1 URUN1 0 0 0 51,57 0 64 URUN2 URUN2 0 0 0 51,57 0 57 URUN3 URUN3 0 0 0 51,57 0
63 URUN1 URUN1 0 0 0 51,57 0 64 URUN2 URUN2 0 0 0 51,57 0 57 URUN3 URUN3 0 0 0 51,57 0

DETAILS’den sonra gelecek olan PAYMENTS için de şöyle bir düzen:

PAYMENTS CODE ISSUEDATE AMOUNT PAYMENTFORM CODE DESCRIPTION CODE ISSUEDATE AMOUNT PAYMENTFORM CODE DESCRIPTION RETURNGOODS
1692677759 2024-01-13T09:47:00 77,44 HAVALE Havale 169267 2024-01-13T09:47:00 27,44 NAKİT NAKİT
1692677759 2024-01-13T09:47:00 7,44 HAVALE Havale

Doğru mudur?

İsterseniz aşağıdaki kodları bir çalıştırın, almak istediğiniz çıktıya yakın bir çıktı üretip üretmediğine bir göz atın.

import xlsxwriter
import pandas as pd

header = {"bold": 1, "border": 1, "align": "center", "valign": "vcenter"}
cell = {"align": "center", "valign": "vcenter"}
        
df = pd.read_json("test.json")
invoices = df["INVOICES"]

# PAYMENTS'ı DETAILS'in önüne atalım.
for index in invoices.index:
    d = invoices[index]
    payments = d["PAYMENTS"]
    return_goods = d["RETURNGOODS"]
    d.pop("PAYMENTS")
    d.pop("RETURNGOODS")
    d["PAYMENTS"] = payments
    d["RETURNGOODS"] = return_goods
    invoices[index] = d

with xlsxwriter.Workbook(filename="test.xlsx") as wb:
    ws = wb.add_worksheet()
    for index in range(len(invoices)):
        col = 0
        for key, value in invoices[index].items():
            if not index:
                ws.write(index, col, key, wb.add_format(header))
            col += 1
            if isinstance(value, dict):
                for k, v in value.items():
                    if not index:
                        ws.write(index, col, k, wb.add_format(header))
                    ws.write(index + 1, col, v, wb.add_format(cell))
                    col += 1
            elif isinstance(value, list):
                for item in value:
                    for k, v in item.items():
                        if not index:
                            ws.write(index, col, k, wb.add_format(header))
                        col += 1
                        if isinstance(v, dict):
                            for _k, _v in v.items():
                                if not index:
                                    ws.write(index, col, _k, wb.add_format(header))
                                ws.write(index + 1, col, _v, wb.add_format(cell))
                                col += 1
                        else:
                            ws.write(index + 1, col - 1, v, wb.add_format(cell))
            else:
                ws.write(index + 1, col - 1, value, wb.add_format(cell))
2 Beğeni
4

merhaba çok güzel olmuş emeğinize sağlık bende böyle bir çözüm üretebildim ne olur ne olmaz diye paylaşıyorum belki ihtiyacı olan biri çıkar

import json
import pandas as pd

def flatten_json(json_obj, prefix=''):
    flat_dict = {}
    for key, value in json_obj.items():
        new_key = f"{prefix}_{key}" if prefix else key
        if isinstance(value, dict):
            flat_dict.update(flatten_json(value, new_key))
        elif isinstance(value, list):
            for i, item in enumerate(value, 1):
                flat_dict.update(flatten_json(item, f"{new_key}_{i}"))
        else:
            flat_dict[new_key] = value
    return flat_dict

def flatten_payments(payments_list, prefix=''):
    flat_dict = {}
    for i, payment in enumerate(payments_list, 1):
        flat_payment = flatten_json(payment, f"{prefix}_{i}")
        flat_dict.update(flat_payment)
    return flat_dict

def flatten_details(details_list, prefix=''):
    flat_dict = {}
    for i, detail in enumerate(details_list, 1):
        flat_detail = flatten_json(detail, f"{prefix}_{i}")
        flat_dict.update(flat_detail)
    return flat_dict

def json_to_excel(json_data, excel_file):
      invoices = json_data['INVOICES']

    details_list = []
    payments_list = []
    for invoice in invoices:
        
        details = invoice.get('DETAILS', [])
        flat_details = flatten_details(details, "DETAILS")
        flat_details['INVOICE_CODE'] = invoice.get('CODE')
        details_list.append(flat_details)
        
        
        payments = invoice.get('PAYMENTS', [])
        flat_payments = flatten_payments(payments, "PAYMENTS")
        flat_payments['INVOICE_CODE'] = invoice.get('CODE')
        payments_list.append(flat_payments)

    
    data = pd.json_normalize(invoices)

    
    details_data = pd.DataFrame(details_list)
    payments_data = pd.DataFrame(payments_list)

    
    merged_data = pd.merge(data, details_data, left_on='CODE', right_on='INVOICE_CODE', how='left', suffixes=('', '_DETAILS'))

    
    merged_data = pd.merge(merged_data, payments_data, left_on='CODE', right_on='INVOICE_CODE', how='left', suffixes=('', '_PAYMENTS'))

    
    merged_data.to_excel(excel_file, index=False, engine='openpyxl')


with open(r'test.json', encoding='utf-8') as json_file:
    json_data = json.load(json_file)


excel_file_path = 'output.xlsx'


json_to_excel(json_data, excel_file_path)

print(f' Excel dosyası: {excel_file_path}')
2 Beğeni