将CSV文件转换为JSON文件

Question

我正试图将CSV文件转换为基于列值的JSON文件。csv文件看起来像这样。

ID        Name          Age         
CSE001    John           18
CSE002    Marie          20
ECE001    Josh           22
ECE002    Peter          23

目前我正在使用以下代码来获取json文件。

import csv
import json

def csv_to_json(csv_file_path, json_file_path):

data_dict = {}

with open(csv_file_path, encoding = 'utf-8') as csv_file_handler:
csv_reader = csv.DictReader(csv_file_handler)

for rows in csv_reader:

key = rows['ID']
data_dict[key] = rows
with open(json_file_path, 'w', encoding = 'utf-8') as json_file_handler:
json_file_handler.write(json.dumps(data_dict, indent = 4))

输出:

**{  
"CSE001":{ 
"ID":"CSE001",
"Name":"John",
"Age":18
}
"CSE002":{
"ID":"CSE002",
"Name":"Marie",
"Age":20
}
"ECE001":{
"ID":"ECE001",
"Name":"Josh",
"Age":22
}
"ECE002":{
"ID":"ECE002",
"Name":"Peter",
"Age":23
}
}**

我希望我的输出基于ID值为CSE和ECE生成两个单独的json文件。有没有办法达到这个输出?

所需输出:

CSE.json:

{  
"CSE001":{ 
"ID":"CSE001",
"Name":"John",
"Age":18
}
"CSE002":{
"ID":"CSE002",
"Name":"Marie",
"Age":20
}
}

ECE.json:

{
"ECE001":{
"ID":"ECE001",
"Name":"Josh",
"Age":22
}
"ECE002":{
"ID":"ECE002",
"Name":"Peter",
"Age":23
}
}

Answer 1

我建议你使用pandas，那样会更容易。

代码可能看起来像:

import pandas as pd
def csv_to_json(csv_file_path):
df = pd.read_csv(csv_file_path)
df_CSE = df[df['ID'].str.contains('CSE')]
df_ECE = df[df['ID'].str.contains('ECE')]
df_CSE.to_json('CSE.json')
df_ECE.to_json('ESE.json')

Answer 2

您可以创建数据框架，然后执行以下操作

import pandas as pd
df = pd.DataFrame.from_dict({  
"CSE001":{ 
"ID":"CSE001",
"Name":"John",
"Age":18
},
"CSE002":{
"ID":"CSE002",
"Name":"Marie",
"Age":20
},
"ECE001":{
"ID":"ECE001",
"Name":"Josh",
"Age":22
},
"ECE002":{
"ID":"ECE002",
"Name":"Peter",
"Age":23
}
},orient='index')
df["id_"] = df["ID"].str[0:2] # temp column for storing first two chars
grps = df.groupby("id_")[["ID", "Name", "Age"]]
for k, v in grps:
print(v.to_json(orient="index")) # you can create json file as well

Answer 3

您可以将每行存储到两层字典中，最顶层是ID的前3个字符。

这些可以写入单独的文件，密钥是文件名的一部分:

from collections import defaultdict
import csv
import json

def csv_to_json(csv_file_path, json_base_path):
data_dict = defaultdict(dict)

with open(csv_file_path, encoding = 'utf-8') as csv_file_handler:
csv_reader = csv.DictReader(csv_file_handler)

for row in csv_reader:
key = row['ID'][:3]
data_dict[key][row['ID']] = row

for key, values in data_dict.items():
with open(f'{json_base_path}_{key}.json', 'w', encoding='utf-8') as json_file_handler:
json_file_handler.write(json.dumps(values, indent = 4))

csv_to_json('input.csv', 'output')

defaultdict用来避免在使用一个键之前需要先测试它是否已经存在。

这会创建output_CSE.json和output_ECE.json，例如

{
"ECE001": {
"ID": "ECE001",
"Name": "Josh",
"Age": "22"
},
"ECE002": {
"ID": "ECE002",
"Name": "Peter",
"Age": "23"
}
}