Python中的JSON到CSV,CSV比JSON有更多的行

7rtdyuoh  于 2023-06-19  发布在  Python
关注(0)|答案(1)|浏览(100)

我试图使用Python将JSON转换为CSV,但我遇到了一个问题,即2行JSON生成6行CSV。以下是我的原始JSON和CSV输出的示例:
JSON:

{ "_id" : "a1" ,"pl" : [ { "age" : 45, "n" : "ar"}, { "age" : 52, "n" : "Pi" }, { "age" : 18, "n" : "al"} ] , "ld" : 13}
{ "_id" : "a2" ,"pl" : [ { "age" : 85, "n" : "ta"}, { "age" : 46, "n" : "lee" }] , "ld" : 14}

CSV:

_id,  ld,  age,  n,  age,  n
a1,  13,  45,  ar,  45,  ar
a1,  13,  52,  Pi,  52,  Pi
a1,  13,  18,  al,  18,  al
a2,  14,  85,  ta,  85,  ta
a2,  14,  46,  lee,  46,  lee

我目前正在处理2行JSON,但意外地收到5行CSV输出。
下面是我目前使用的Python代码:

import pandas as pd
import glob

for i in glob.glob('D:\\1.json'):
    data = []
    df_it = pd.read_json(i, encoding='utf-8', lines=True, chunksize=100, dtype=object)
    for sub in df_it:
     data.append(sub)
    df = pd.concat(data)
    df = df.explode('pl')
    df = pd.concat([
        df.reset_index(drop=True),
        pd.json_normalize(df.pl),
    ], axis=1)
    df = df.drop(['pl', ], axis=1)
    df.to_csv('D:\\1.csv', index=None, encoding='utf-8')

我希望得到一个CSV输出格式如下:

_id, pl.age1,  pl.n1,  pl.age2,  pl.n2, pl.age3,  pl.n3, ld, 
a1,    45,      ar,       52,     Pi,     18,      al,    13
a2,    85,      ta,       46,     lee,      ,        ,    14

我需要对代码或实现进行哪些更改才能实现所需的CSV输出?

ckocjqey

ckocjqey1#

我假设JSON存储在不同的文件中。

实现方式:

import glob
from cherrypicker import CherryPicker
import json
import pandas as pd

df = pd.DataFrame()
for index, file_name in enumerate(glob.glob("json/*.json")):
    with open(file_name, encoding="utf-8") as file:
        data = json.load(file)

    picker = CherryPicker(data)
    flat = picker.flatten().get()
    df = df._append(flat, ignore_index=True)

df.to_csv(f"output.csv", index=False)

输出:

_id,pl_0_age,pl_0_n,pl_1_age,pl_1_n,pl_2_age,pl_2_n,ld
a1,45,ar,52,Pi,18.0,al,13
a2,85,ta,46,lee,,,14

相关问题