嵌套JSON到CSV（多級）

我收到了一個 JSON 檔案以轉換為 CSV 檔案。

[
    {
        "id": "132465",
        "ext_numero_commande": "4500291738L",
        "ext_line_items": [
            {
                "key_0": "10",
                "key_1": "10021405 / 531.415.110",
                "key_4": "4 Pce"
            },
            {
                "key_0": "20",
                "key_1": "10021258 / 531.370.140 /  NPK-Nr. 224412",
                "key_4": "4 Pce"
            },
            {
                "key_0": "30",
                "key_1": "10020895 / 531.219.120 /  NPK-Nr. 222111",
                "key_4": "10 Pce"
            },
            {
                "key_0": "40",
                "key_1": "10028633 / 552.470.110",
                "key_4": "3 Pce"
            }
        ],
        "ext_prix": [
            {
                "key_0": "11.17"
            },
            {
                "key_0": "9.01"
            },
            {
                "key_0": "18.63"
            },
            {
                "key_0": "24.15"
            }
        ],
        "ext_tag": "Date_livraison",
        "ext_jour_livraison": "23-07-2021",
        "ext_jour_livraison_1": null
    }
]

我找到了功能pd.json_normalize。

df=pd.json_normalize(
        json_object[0],
        record_path=['ext_line_items'],
        meta=['id', 'ext_numero_commande', 'ext_tag',
            'ext_jour_livraison', 'ext_jour_livraison_1'])

我幾乎得到了最終結果，我可以["ext_prix"]使用相同的方法和連接函式添加最后一列。

是自動執行的功能嗎？

我使用了這個函式，但它回傳一個錯誤。

df=pd.json_normalize(
        json_object[0],
        record_path=['ext_line_items','ext_prix'],
        meta=['id', 'ext_numero_commande', 'ext_tag',
            'ext_jour_livraison', 'ext_jour_livraison_1'])

uj5u.com熱心網友回復：

如果你的 JSON 資料集長度固定，你可以解決這個問題，我希望這段代碼能正常作業。按照這種方式..（JSON to Dictionary to CSV using pandas）。

# import pandas
import pandas as pd

# Read JSON file
df = pd.read_json('C://Users//jahir//Desktop//json_file.json')

# create dictionary
create_dict = {} 

# Iteration JSON file
for index,values in df.iterrows():
    
    # for loop ext_line_items
    for ext_lines in df['ext_line_items'][index]:
        for item in ext_lines:
            column_name = 'ext_line_items_' item
            if column_name not in create_dict:
                create_dict[column_name] =[]
            
            # four time for to create four product ext_line_items_key_0, ext_line_items_key_1, ext_line_items_key_4
            time_loop = 4
            while time_loop > 0:
                time_loop-=1
                create_dict[column_name] =[ext_lines[item]]
    
    # for loop ext_prix dataset
    # four time for to create four product ext_prix_key_0
    time_loop = 4
    while time_loop > 0:
        time_loop-=1
        for ext_prix in df['ext_prix'][index]:
            for item in ext_prix:
                column_name = 'ext_prix_' item
                if column_name not in create_dict:
                    create_dict[column_name] =[]
                create_dict[column_name] =[ext_prix[item]]
    # add key in dictionary 
    for i in ['id', 'ext_numero_commande', 'ext_tag', 'ext_jour_livraison']:
        create_dict[i]=[]
        
    
    # 16 time for to create 4*4 product 'id', 'ext_numero_commande', 'ext_tag', 'ext_jour_livraison'
    total_time = 16
    while total_time > 0:
        total_time-=1
        for j in ['id', 'ext_numero_commande', 'ext_tag', 'ext_jour_livraison']:
            create_dict[j]  = [df[j][index]]

# Dictionary to DataFrame
pd_dict= pd.DataFrame.from_dict(create_dict) 

# DataFrame to write csv file
write_csv_path = 'C://Users//jahir//Desktop//csv_file.csv'
pd_dict.to_csv(write_csv_path, index = False, header = True)

輸出：

uj5u.com熱心網友回復：

使用 pd.json_normalize 你可以解決這個問題。按照這種方式..（json_normalize合并到csv）

import pandas as pd
data = [
    {
        "id": "132465",
        "ext_numero_commande": "4500291738L",
        "ext_line_items": [
            {
                "key_0": "10",
                "key_1": "10021405 / 531.415.110",
                "key_2": "4 Pce"
            },
            {
                "key_0": "20",
                "key_1": "10021258 / 531.370.140 /  NPK-Nr. 224412",
                "key_2": "4 Pce"
            },
            {
                "key_0": "30",
                "key_1": "10020895 / 531.219.120 /  NPK-Nr. 222111",
                "key_2": "10 Pce"
            },
            {
                "key_0": "40",
                "key_1": "10028633 / 552.470.110",
                "key_2": "3 Pce"
            }
        ],
        "ext_prix": [
            {
                "key_4": "11.17"
            },
            {
                "key_4": "9.01"
            },
            {
                "key_4": "18.63"
            },
            {
                "key_4": "24.15"
            }
        ],
        "ext_tag": "Date_livraison",
        "ext_jour_livraison": "23-07-2021",
        "ext_jour_livraison_1": None
    }
]
# Used json_normalize for record path ext_line_items
normalize_ext_line_items = pd.json_normalize(data, record_path = "ext_line_items", meta = ["id", "ext_numero_commande", 'ext_tag', 'ext_jour_livraison'])

# Used json_normalize for record path ext_prix
normalize_ext_prix = pd.json_normalize(data,record_path = "ext_prix", meta = ["id"])

# merge to
final_output =  normalize_ext_line_items.merge(normalize_ext_prix, on='id', how = 'left')

# DataFrame to write csv file used your file path
write_csv_path = 'C://Users//jahir//Desktop//csv_file.csv'
final_output.to_csv(write_csv_path, index = False, header = True)

輸出：

標籤：json熊猫数据框CSV标准化

上一篇：如何在賽普拉斯測驗中遍歷.CSV檔案中的資料行？

下一篇：預期的str、bytes或os.PathLike物件，而不是reader