如何将JSON文件转换为CSV文件

Question

我有以下JSON文件:

[
{
"Names": {
"0": "Nat",
"1": "Harry",
"2": "Joe"
},
"Marks": {
"0": 78.22,
"1": 32.54,
"2": 87.23
}
}
]

我编写了以下代码进行转换:

import csv, json
def conversion(Jsonfile,Csvfile):
readfile=open(Jsonfile,"r")
print(readfile)
jsondata=json.load(readfile)
print(jsondata)
readfile.close()

data_file=open(Csvfile,'w')
csv_writer=csv.writer(data_file)

count=0
for data in jsondata:
if count==0:
header=data.keys()
print(header)
csv_writer.writerow(header)
count=count+1

csv_writer.writerow(data.values())
print(data.values())
data_file.close()
Jsonfile="Series.json"
Csvfile="convertedfile.csv"
conversion(Jsonfile,Csvfile)

我在CSV

中得到以下输出

Names,Marks
"{'0': 'Nat', '1': 'Harry', '2': 'Joe'}","{'0': 78.22, '1': 32.54, '2': 87.23}"

我的问题是如何纠正代码以获得以下输出(即每个名称在不同的行中带有标记):

Names,Marks
0,Nat,78.22
1,Harry,32.54
2,Joe,87.23

Answer 1

pandas具有读取json和写入csv的实用程序。

import pandas as pd

j = '[{"Names":{"0":"Nat","1":"Harry","2":"Joe"},"Marks":{"0":78.22,"1":32.54,"2":87.23}}]'
df = pd.read_json(j[1:-1], orient='records')  # 1:-1 because we need to remove the square brackets
df.to_csv("output.csv")

输出:

,Names,Marks
0,Nat,78.22
1,Harry,32.54
2,Joe,87.23

Answer 2

这是一种方法。

首先，我们打开json文件并解析它:

import json
json_file = "foo.json"
csv_file = "foo.csv"
with open(json_file, "r") as f:
json_data = json.load(f)

然后从json列表中提取字典:

my_dict = json_data[0]  # we get the dict inside the list
my_list = []  # we create a new list to store data in the format we need

我们将数据格式化成一种可以很容易地打印到文件中的方式:

for key in my_dict["Names"].keys():
my_list.append(
[
key,
my_dict["Names"][key],
str(my_dict["Marks"][key])  # we cast the mark to string so it's easier to use later on
]
)

现在，数据以一种易于打印的方式格式化，所以我们只需将其保存在csv文件中!

# Our new list is now created, we can now save it to our file
with open(csv_file, "w") as f:
f.write("Names,Marksn")
for line in my_list:
f.write(",".join(line) + "n")

输出:

Names,Marks
0,Nat,78.22
1,Harry,32.54
2,Joe,87.23

Answer 3

您可以从JSON数据生成有效的CSV文件，而不需要重量级模块，如pandas或CSV。您只需要json模块从文件中加载json数据并(隐式地)验证它。

import json
Jsonfile = 'Series.json'
Csvfile = 'convertedfile.csv'
with open(Jsonfile) as jfile:
d = json.load(jfile)[0]
with open(Csvfile, 'w') as cfile:
print('ID,Name,Marks', file=cfile)
if names := d.get('Names'):
for id_, name in names.items():
mark = d.get('Marks', {}).get(id_, '')
print(f'{id_},{name},{mark}', file=cfile)

输出文件看起来像这样:

ID,Name,Marks
0,Nat,78.22
1,Harry,32.54
2,Joe,87.23

Answer 4

import pandas as pd
df = pd.read_json(r'Path where the JSON file is savedFile Name.json')
df.to_csv(r'Path where the new CSV file will be storedNew File Name.csv', index = None)