嵌套json到csv转换

eqzww0vc  于 2023-07-31  发布在  其他
关注(0)|答案(1)|浏览(100)

我正在尝试提取嵌套的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}"


任何帮助都是感激的。

2vuwiymt

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

相关问题