将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

我建议你用熊猫，那样会更容易。

代码看起来可能如下：

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

您可以创建dataframe，然后执行以下操作

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"
    }
}