Python实现CSV转JSON：提取指定列并拆分多值单元格生成新条目

问题描述 #

作为Python新手，需要将给定CSV文件转换为指定格式的JSON，核心需求如下：

• CSV中部分单元格（如CIN2）含逗号分隔的多值，需为每个值生成独立的JSON条目
• 仅保留指定列：Cin、Date、Ex Employer、Batche Source

CSV示例 #

date      customer Ex employer    emailID        CIN     CIN2            BatcheSource
9-Jul-24   ABC1    EmployerAnme1  abc1@abc1.com  123456  9087690,345678  payment
9-Oct-24   BCD1    EMP2           bcd1@bcd1.com  234566                  adasd

目标JSON示例 #

[
   {
      "Cin":"123456",
      "Date":"9-Jul-24",
      "Ex Employer":"Employer Name 1",
      "Batche Source":"Payment"
   },
   {
      "Cin":"9087690",
      "Date":"9-Jul-24",
      "Ex Employer":"Employer Name 1",
      "Batche Source":"Payment"
   },
   {
      "Cin":"345678",
      "Date":"9-Jul-24",
      "Ex Employer":"Employer Name 1",
      "Batche Source":"Payment"
   },
   {
      "Cin":"234566",
      "Date":"9-Oct-24",
      "Ex Employer":"EMP2",
      "Batche Source":"adasd"
   }
]

当前尝试代码 #

import csv
import json

with open('test.csv') as infile:
    reader = csv.DictReader(infile)
    out = [{"CIN": row['CIN'],"Date": row["Date"], "Ex Employer": row["Ex Employer"],"CIN2": row["CIN2"],"Batche Source": row["Batche Source"]} for row in reader]

with open('test1.json', 'w') as outfile:
    json.dump(out, outfile) 

解决方案 #

当前代码未处理CIN2的多值拆分，且保留了不需要的CIN2列。以下是修正后的代码，完全匹配需求：

import csv
import json

def csv_to_json(csv_file_path, json_file_path):
    result = []
    
    with open(csv_file_path, mode='r', encoding='utf-8') as csv_file:
        csv_reader = csv.DictReader(csv_file)
        
        for row in csv_reader:
            # 提取当前行固定字段，避免重复编写
            base_info = {
                "Date": row["date"],
                "Ex Employer": row["Ex employer"],
                "Batche Source": row["BatcheSource"].capitalize()
            }
            
            # 为主CIN生成条目
            main_cin_entry = base_info.copy()
            main_cin_entry["Cin"] = row["CIN"]
            result.append(main_cin_entry)
            
            # 处理CIN2中的多值
            cin2_content = row["CIN2"].strip()
            if cin2_content:
                for cin_value in cin2_content.split(','):
                    cleaned_cin = cin_value.strip()
                    if cleaned_cin:
                        cin2_entry = base_info.copy()
                        cin2_entry["Cin"] = cleaned_cin
                        result.append(cin2_entry)
    
    # 格式化写入JSON，提升可读性
    with open(json_file_path, mode='w', encoding='utf-8') as json_file:
        json.dump(result, json_file, indent=3)

# 替换为你的实际文件路径
csv_to_json('test.csv', 'test1.json')

关键说明 #

1. 基础信息复用：提取每行固定字段，减少冗余代码
2. 多值拆分逻辑：检查CIN2内容，按逗号拆分后逐个生成新条目
3. 格式对齐：对BatcheSource做首字母大写处理，匹配目标示例格式
4. 空值过滤：去除拆分后值的前后空格，避免生成无效空条目

内容的提问来源于stack exchange，提问作者Preeti