JSON帮助CSV转换,python

bxjv4tth  于 5个月前  发布在  Python
关注(0)|答案(2)|浏览(70)

我有一个JSON文件,我想转换为CSV。我这样做:

import pandas as pd
with open('test.json', encoding='utf-8') as inputfile:
    df = pd.read_json(inputfile)
df.to_csv('test.csv', encoding='utf-8', index=False)

字符串
一切正常,但我的JSON有结构,我想“清理”,使转换更好。我的结构是:

"field A": 117,
"field B": 143,
"field C": 27,
"field D": [
    {
    "id": 782,
    "name": "Some test A",
    "type": "Group"
    }
],
"field E": null,
"field F": "contact",
"field G": [
    {
    "id": 32358,
    "name": "Some test B",
    "type": "Note"
    },
    {
    "id": 37557,
    "name": "Some test C",
    "type": "Note"
    },
    {
    "id": 38416,
    "name": "Some test D",
    "type": "Note"
    }
],
"field H": null,


我想删除所有的“id”和“type”,只留下“name”,如果有多个名字exsists有他们在新的一行。

"field A": 117,
"field B": 143,
"field C": 27,
"field D": "Some test A",
"field E": null,
"field F": "contact",
"field G": "Some test B \n Some test C \n Some test D",
"field H": null,


有什么建议吗?谢谢!

acruukt9

acruukt91#

你需要通过键/值来重新定义,从list创建新值,然后用新值更新dict:

json_data = {
    "field A": 117,
    "field B": 143,
    "field C": 27,
    "field D": [
        {"id": 782, "name": "Some test A", "type": "Group"}
    ],
    "field E": None,
    "field F": "contact",
    "field G": [
        {"id": 32358, "name": "Some test B", "type": "Note"},
        {"id": 37557, "name": "Some test C", "type": "Note"},
        {"id": 38416, "name": "Some test D", "type": "Note"}
    ],
    "field H": None,
}

items=[]
for key, values in json_data.items():
    if isinstance(values, list):
      new_value = ' \n '.join([value['name'] for value in values])
      items.append((key, new_value))

json_data.update(items)

字符串
输出量:

{'field A': 117,
 'field B': 143,
 'field C': 27,
 'field D': 'Some test A',
 'field E': None,
 'field F': 'contact',
 'field G': 'Some test B \n Some test C \n Some test D',
 'field H': None}

yzckvree

yzckvree2#

一个非常特定于结构的代码可以是:

new_dico = {}
for k, v in dico.items():
    if isinstance(v, list):
        # if the value in key value pair, is a list, 
        # it may contain dict with names attributes, let's check :
        names = []
        for lv in v :
            if isinstance(lv, dict) and "name" in lv.keys():
                names.append(lv["name"])
            else :
                break
        if len(names) == len(v):
            # in cases all values in the list are dict and have a name attribute
            # we replace the original list by the list of names. 
            # meaning we are dropping all other keys, including id and type.
            new_dico[k] = names
            continue

    # in all other cases, we just keep the key value pair as is
    new_dico[k] = v

字符串

相关问题