嵌套json到csv转换

eqzww0vc 于 2023-07-31 发布在其他

关注(0)|答案(1)|浏览(102)

我正在尝试提取嵌套的json值并生成csv。到目前为止我有这样的代码：

import json
import csv

# Opening JSON file and loading the data
# into the variable data
with open('test.json') as json_file:
    data = json.load(json_file)

employee_data = data['cn']

data1 = []

# now we will open a file for writing
data_file = open('data_file.csv', 'w')

# create the csv writer object
csv_writer = csv.writer(data_file)

header = ['firstName','lastName','jobtitle','company','workPostalCode','workCity','workStreet','mobilePhone','email']
csv_writer.writerow(header)
store = {"firstName":None, "lastName":None, "jobtitle":None, "company":None, "workPostalCode":None, "workCity":None, "workStreet":None, "mobilePhone":None, "email":None}

for emp in employee_data:
    # Writing data of CSV file
    #csv_writer.writerow(emp['_attrs'].values())
    try:
        store['firstName'] = emp["_attrs"]['firstName']
        store['lastName'] = emp["_attrs"]['lastName']
        store['jobtitle'] = emp["_attrs"]['jobtitle']
        store['company'] = emp["_attrs"]['company']
        store['workPostalCode'] = emp["_attrs"]['workPostalCode']
        store['workCity'] = emp["_attrs"]['workCity']
        store['workStreet'] = emp["_attrs"]['workStreet']
        store['mobilePhone'] = emp["_attrs"]['mobilePhone']
        store['email'] = emp["_attrs"]['email']
    except KeyError:
        print('d')
    
    data1.append(store)

    csv_writer.writerow(data1)
    data1 = []

data_file.close()

字符串
但我只需要从列表中的值被写入csv。现在我有这样的csv

firstName,lastName,jobtitle,company,workPostalCode,workCity,workStreet,mobilePhone,email

"{'firstName': 'Adnan', 'lastName': 'xxx', 'jobtitle': None, 'company': None, 'workPostalCode': None, 'workCity': None, 'workStreet': None, 'mobilePhone': None, 'email': None}"

型
任何帮助都是感激的。

csv

来源：https://stackoverflow.com/questions/76685740/nested-json-to-csv-convert

1条答案

按热度按时间

2vuwiymt1#

您似乎混淆了对csv.writer和csv.DictWriter的理解;看起来你想写一个dict到CSV，这意味着你应该使用DictWriter。
我猜你的JSON看起来像这样：

{
    "employees": [
        {
            "_attrs": {
                "firstName": "Alice",
                "lastName": "Cheng",
                "jobtitle": "Guru",
                "company": "Acme",
                "workPostalCode": "12345",
                "workCity": "Anytown",
                "workStreet": "Any Street",
                "mobilePhone": "555-5555",
                "email": "alice@acme.com",
                "extraAttr1":"foo",
                "extraAttr2":"bar",
                "extraAttr3":"baz"
            }
        }
    ]
}

字符串

（注意我添加了额外的属性1 - 3）*

如果JSON对象在CSV中的属性比你想要的多，你可以很容易地控制DictWriter转换成CSV的内容，方法是给它你关心的字段名称，并指示它在将每行（dict）写入CSV时忽略它找到的额外字段名称。
我打开上面分享的JSON，并保留每个employee dict的_attrs dict：

with open("input.json") as f:
    employee_attrs = [emp["_attrs"] for emp in json.load(f)["employees"]]

型
employee_attrs看起来像：

[
    {
        "firstName": "Alice","lastName": "Cheng","jobtitle": "Guru","company": "FooBar",
        "workPostalCode": "12345","workCity": "Anytown","workStreet": "Any Street","mobilePhone": "555-5555","email": "alice@foobar.com",
        "extraAttr1": "foo","extraAttr2": "bar","extraAttr3": "baz",
    }
]

型
然后我用我关心的字段名创建DictWriter，并将其extrasaction设置为“ignore”：

my_fieldnames = [
    "firstName","lastName","jobtitle","company",
    "workPostalCode","workCity","workStreet","mobilePhone","email",
]

with open("output.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=my_fieldnames, extrasaction="ignore")
    writer.writeheader()
    writer.writerows(employee_attrs)

型
我得到以下输出. csv（没有attrs 1 - 3）：

firstName,lastName,jobtitle,company,workPostalCode,workCity,workStreet,mobilePhone,email
Alice,Cheng,Guru,FooBar,12345,Anytown,Any Street,555-5555,alice@foobar.com

型

赞(0）回复(0）举报 2023-07-31

我来回答

嵌套json到csv转换

1条答案

相关问题

热门标签

最新问答