Python CSV转JSON：如何修正数据结构生成有效JSON文件

当前代码生成的是多个独立JSON对象拼接的无效文件，需修改为包含persons数组和data数组的单一有效JSON结构。

原代码 #

import os
import csv
import json

csvFilePath = "./mycsv.txt"
jsonFilePath = "./mydata.jsonc"

if os.path.exists(jsonFilePath):
    os.remove(jsonFilePath);

# Dictionary 1
myData1 = {
    "persons" : [
      {"Id": int,
      "groupId": str
      }
    ]
}

# Dictionary 2
myData2 = { 
    "data": [
      { "Id": int,
      "x": float,
      "y": float,
      "z": float
      }
    ]
}

# Init
myVarformyData1 = {}
myVarformyData2 = {}
myVarformyData1["persons"]={}
myVarformyData2["data"]={}

# Process first the sensor data (the first part persons)
with open(csvFilePath, 'r', encoding = 'utf-8') as csvFile:  
    line = csv.reader(csvFile)
    next(line)
    for line in csvFile:
        col = 0
        for value in line.split(';'):
            col = col + 1
            if (col == 3):
                myVarformyData1["persons"]["sId"] = value              
            elif (col == 8):
                myVarformyData1["persons"]["groupId"] = value
        
        with open(jsonFilePath, 'a', encoding = 'utf-8') as jsonFile1:
            jsonFile1.write(json.dumps(myVarformyData1, indent=4, sort_keys=True))
            jsonFile1.close();

# Closing the csv file
csvFile.close();

# Process other data (the second part data)
# Continues reading from the same .csv file 
with open(csvFilePath, 'r', encoding = 'utf-8') as csvFile:  
    line = csv.reader(csvFile)
    next(line)
    for line in csvFile:
        col = 0
        for value in line.split(';'):
            col = col + 1
            if (col == 3):
                myVarformyData2["data"]["sId"] = value
            elif (col == 10):
                myVarformyData2["data"]["x"] = value
            elif (col == 11):
                myVarformyData2["data"]["y"] = value
            elif (col == 12):
                myVarformyData2["data"]["z"] = value

        with open(jsonFilePath, 'a', encoding = 'utf-8') as jsonFile1:
            jsonFile1.write(json.dumps(myVarformyData2, indent=4,sort_keys=True))
            jsonFile1.close();

# Closing the .csv file
csvFile.close();

当前无效JSON输出 #

{
    "persons": {
        "groupId": "1",
        "sId": "123"
    }
}{
    "persons": {
        "groupId": "2",
        "sId": "456"
    }
}{
    "persons": {
        "groupId": "3",
        "sId": "789"
    }
}{
    "data": {
        "sId": "123",
        "x": "6.11",
        "y": "2.188",
        "z": "1.6"
    }
}{
    "data": {
        "sId": "456",
        "x": "7",
        "y": "2.384",
        "z": "1.6"
    }
}{
    "data": {
        "sId": "789",
        "x": "8.74",
        "y": "1.159",
        "z": "1.6"
    }
}

期望有效JSON输出 #

{
    "persons": [
        {
            "groupId": "1",
            "sId": "123"
        },{
            "groupId": "2",
            "sId": "456"
        },{
            "groupId": "3",
            "sId": "789"
        }
    ], 
    "data": [ 
        {
            "sId": "123",
            "x": "6.11",
            "y": "2.188",
            "z": "1.3"
        },{
            "sId": "456",
            "x": "7",
            "y": "2.384",
            "z": "1.4"
        },{
            "sId": "789",
            "x": "8.74",
            "y": "1.159",
            "z": "1.5"
        }
    ]
}

修改后的代码 #

import os
import csv
import json

csvFilePath = "./mycsv.txt"
jsonFilePath = "./mydata.jsonc"

# 初始化最终数据结构：包含两个空数组
final_data = {
    "persons": [],
    "data": []
}

# 一次性读取CSV，同时收集persons和data数据
with open(csvFilePath, 'r', encoding='utf-8') as csvFile:
    # 使用csv.reader指定分隔符为;，更规范处理CSV
    reader = csv.reader(csvFile, delimiter=';')
    next(reader)  # 跳过表头
    
    for row in reader:
        # 处理persons条目
        person = {
            "sId": row[2],  # 第3列（索引从0开始）
            "groupId": row[7]  # 第8列
        }
        final_data["persons"].append(person)
        
        # 处理data条目
        data_entry = {
            "sId": row[2],  # 第3列
            "x": row[9],    # 第10列
            "y": row[10],   # 第11列
            "z": row[11]    # 第12列
        }
        final_data["data"].append(data_entry)

# 删除旧文件（如果存在）
if os.path.exists(jsonFilePath):
    os.remove(jsonFilePath)

# 一次性将整个有效JSON写入文件
with open(jsonFilePath, 'w', encoding='utf-8') as jsonFile:
    json.dump(final_data, jsonFile, indent=4, ensure_ascii=False)

关键修改说明 #

• 数据结构初始化：直接创建包含persons和data空数组的主字典，而非两个独立字典，确保最终是单一JSON对象。
• 单次遍历CSV：只读取一次CSV文件，同时收集两类数据，提升效率，避免重复IO操作。
• 规范CSV读取：使用csv.reader并指定delimiter=';'，替代手动split(';')，处理边缘情况更可靠。
• 一次性写入：所有数据收集完成后，再将整个主字典写入JSON文件，避免多次追加导致的无效JSON结构。

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