用JSON对象中的头和数据构建CSV

Question

Python有以下问题。你知道吗

给定以下JSON对象-我想

• 把这个json当作dict来读
• 取几个键作为CSV文件的标题，如下所示：

CSV标题

firstName,lastName,managersEmail,contractStartsDate

• 将这些键的相应值作为行放入CSV中，如下所示：

CSV内容

firstName,lastName,managersEmail,contractStartsDate
nameOfPerson,lastNameofPerson,someManager,2000-01-01
nameOfPerson2,lastNameofPerson2,someManager2,2000-02-02

• 不要复制CSV中的任何键
• 但是将每个键的每个值放在CSV中JSON的相应头值下

我的targetJSON.json文件你知道吗

data = '{"details":[
{"firstName":"nameOfPerson,"lastName":"lastNameofPerson","managersEmail":"someEmail","managersName":"someManager",
    "departmentName":"someDepartment",
    "position":"somePosition",
    "contractStartsDate":"2000-01-01",
    "contractEndDate":"2000-01-01",
    "company":"someCompany",
    "division":"someDivision",
    "preferredName":"Unknown"},
{"firstName":"nameOfPerson2","lastName":"lastNameofPerson2","managersEmail":"someEmail2","managersName":"someManager2",
    "departmentName":"someDepartment2",
    "position":"somePosition2",
    "contractStartsDate":"2000-02-02",
    "contractEndDate":"2000-02-02",
    "company":"someCompany",
    "division":"someDivision2",
    "preferredName":"Unknown"}
]}'

我的代码是这样的

with open('targetJSON.json', 'r') as f:
    distros_dict = json.load(f)

for distro in distros_dict:
    print(distro['managersEmail'])


data_file = open("targetJSON.json", "r")
values = json.load(data_file)
data_file.close()

with open("usersData.csv", "wb") as f:
    wr = csv.writer(f)
    for data in values:
        value = data["managersEmail"]
        value = data["firstName"]
        for key, value in data.iteritems():
            #wr.writerow([key, value])
            wr.writerow([key.encode("utf-8"), value.encode("utf-8")])

但结果完全是胡说八道， CSV包含所有混淆的内容：-（

Answer 1

当在python3.x中使用csv.writer()时，需要使用newline=""，wb用于python2.x版本。你知道吗

使用您给出的示例JSON，您只需要遍历头字段并从details中的每个条目创建一行。例如：

import json
import csv

data = """{"details":[{"firstName":"nameOfPerson","lastName":"lastNameofPerson","managersEmail":"someEmail",
    "managersName":"someManager", "departmentName":"someDepartment", "position":"somePosition", "contractStartsDate":"2000-01-01",
    "contractEndDate":"2000-01-01", "company":"someCompany", "division":"someDivision", "preferredName":"Unknown"},
{"firstName":"nameOfPerson2","lastName":"lastNameofPerson2","managersEmail":"someEmail2","managersName":"someManager2",
    "departmentName":"someDepartment2", "position":"somePosition2", "contractStartsDate":"2000-02-02",
    "contractEndDate":"2000-02-02", "company":"someCompany", "division":"someDivision2", "preferredName":"Unknown"}
]}"""

json_data = json.loads(data)
header = ["firstName", "lastName", "managersEmail", "contractStartsDate"]

with open("usersData.csv", "w", newline="", encoding="utf-8") as f_output:
    csv_output = csv.writer(f_output)
    csv_output.writerow(header)

    for entry in json_data["details"]:
        csv_output.writerow([entry[key] for key in header])

给你：

firstName,lastName,managersEmail,contractStartsDate
nameOfPerson,lastNameofPerson,someEmail,2000-01-01
nameOfPerson2,lastNameofPerson2,someEmail2,2000-02-02

如果JSON数据包含重复的条目，那么在开始写入行之前，必须首先加载所有数据并删除重复项。你知道吗

---

或者，您可以使用csv.DictWriter，如下所示：

import json
import csv

data = """{"details":[{"firstName":"nameOfPerson","lastName":"lastNameofPerson","managersEmail":"someEmail",
    "managersName":"someManager", "departmentName":"someDepartment", "position":"somePosition", "contractStartsDate":"2000-01-01",
    "contractEndDate":"2000-01-01", "company":"someCompany", "division":"someDivision", "preferredName":"Unknown"},
{"firstName":"nameOfPerson2","lastName":"lastNameofPerson2","managersEmail":"someEmail2","managersName":"someManager2",
    "departmentName":"someDepartment2", "position":"somePosition2", "contractStartsDate":"2000-02-02",
    "contractEndDate":"2000-02-02", "company":"someCompany", "division":"someDivision2", "preferredName":"Unknown"}
]}"""

json_data = json.loads(data)
fieldnames = ["firstName", "lastName", "managersEmail", "contractStartsDate"]

with open("usersData.csv", "w", newline="", encoding="utf-8") as f_output:
    csv_output = csv.DictWriter(f_output, fieldnames=fieldnames, extrasaction="ignore")
    csv_output.writeheader()
    csv_output.writerows(json_data["details"])

---

要从输入JSON文件读取数据，可以执行以下操作：

import json
import csv

with open("sourceJSON.json", encoding="utf-8") as f_input:
    json_data = json.load(f_input)

fieldnames = ["firstName", "lastName", "managersEmail", "contractStartsDate"]

with open("usersData.csv", "w", newline="", encoding="utf-8") as f_output:
    csv_output = csv.DictWriter(f_output, fieldnames=fieldnames, extrasaction="ignore")
    csv_output.writeheader()
    csv_output.writerows(json_data["details"])

如果需要删除相同的行，请将最后一行替换为：

csv_output.writerows(dict(t) for t in {tuple(entry.items()) : '' for entry in json_data["details"]})