如何将CSV转换为深度嵌套JSON？代码问题排查与修正

当前将CSV转换为嵌套JSON的Python代码存在以下问题：

1. 相同key的CSV行未合并为单个嵌套对象，生成多个重复对象
2. 未在fieldnames中定义的CSV字段仍出现在JSON输出中
3. CSV中的空字段保留在JSON里
4. JSON未包裹为数组，且对象结尾存在多余逗号

提供的原始代码、示例CSV及期望JSON格式如下：

原始Python代码 #

import csv
import json

class SetEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, set):
            return list(obj)
        return json.JSONEncoder.default(self, obj)

def str_to_bool(s):
    if s == "TRUE":
        return True
    elif s == "FALSE":
        return False
    else:
        return None
file = "sample"
csvfile = open(f'csv/{file}.csv', encoding='utf-8-sig')
next(csvfile, None) 
jsonfile = open(f'output/{file}.json', 'w')

fieldnames = ("key", "name", "loc_type",
"loc_id", "cities_name", "shippingMethods_name",
"isExcluded", "cutoffWindows_startTime", "cutoffWindows_endTime",
"cutoffWindows_capacity", "cutoffWindows_slots", "category",
"furniture", "removeFallbacks", "cutoffWindows")
reader = csv.DictReader(csvfile, fieldnames)

for row in reader:
    row['furniture'] = str_to_bool(row.pop('furniture'))
    cities_name = row.pop('cities_name')
    row['cities'] = [{'name': cities_name}]
    for smethod in row['cities']:
        shippingMethods_name = row.pop('shippingMethods_name')
        isExcluded = row.pop('isExcluded')
        removeFallbacks = row.pop('removeFallbacks')
        smethod['shippingMethods'] = [{'name': shippingMethods_name, 'isExcluded': str_to_bool(isExcluded), 'removeFallbacks': str_to_bool(removeFallbacks)}]
        for cwindows in smethod['shippingMethods']:
            cutoffWindows = row.pop('cutoffWindows')
            startTime = row.pop('cutoffWindows_startTime')
            endTime = row.pop('cutoffWindows_endTime')
            capacity = row.pop('cutoffWindows_capacity')
            cwindows['cutoffWindows'] = [{'startTime': startTime, 'endTime': endTime, 'capacity': capacity}]
            for s in cwindows['cutoffWindows']:
                slots = row.pop('cutoffWindows_slots')
                s['slots'] = [{slots}]

    json.dump(row, jsonfile, indent=4, cls=SetEncoder)
    jsonfile.write(',')

示例CSV内容 #

key,name,loc_type,loc_id,cities_name,shippingMethods_name,isExcluded,cutoffWindows_startTime,cutoffWindows_endTime,cutoffWindows_capacity,cutoffWindows_slots,category,furniture,removeFallbacks,Mode,Country,sm_omscode,slots_omscode
store-fashion,UAE - Store (Fashion),S,8502,dubai,next-day-delivery,FALSE,0:01,23:59,20,9pm12am,fashion,,,Normal,BloomingDales AE,NEXTDAY,SLOT21-24
store-fashion,UAE - Store (Fashion),S,8502,dubai,1-3-days-delivery,FALSE,0:01,23:59,,,fashion,,TRUE,Normal,BloomingDales AE,1TO3DAYS,
store-fashion,UAE - Store (Fashion),S,8502,non-dubai,next-day-delivery,FALSE,0:01,21:00,40,12am12am,fashion,,,Normal,BloomingDales AE,NEXTDAY,NEXTDAY
store-fashion,UAE - Store (Fashion),S,8502,non-dubai,1-3-days-delivery,TRUE,0:01,23:59,,,fashion,TRUE,TRUE,Normal,BloomingDales AE,1TO3DAYS,

期望JSON格式 #

[{
    "key": "store-fashion",
    "name": "UAE - Store (Fashion)",
    "loc_type": "S",
    "loc_id": "8502",
    "cities": [{
            "name": "dubai",
            "shippingMethods": [{
                    "name": "same-day-delivery",
                    "isExcluded": false,
                    "cutoffWindows": [{
                            "startTime": "00:01",
                            "endTime": "10:00",
                            "capacity": 20,
                            "slots": [
                                "1pm6pm"
                            ]
                        },
                        {
                            "startTime": "00:01",
                            "endTime": "16:00",
                            "capacity": 20,
                            "slots": [
                                "6pm9pm"
                            ]
                        },
                        {
                            "startTime": "00:01",
                            "endTime": "16:00",
                            "capacity": 15,
                            "slots": [
                                "9pm12am"
                            ]
                        }
                    ]
                },
                {
                    "name": "next-day-delivery",
                    "isExcluded": false,
                    "cutoffWindows": [{
                            "startTime": "00:01",
                            "endTime": "21:00",
                            "capacity": 20,
                            "slots": [
                                "9am1pm"
                            ]
                        },
                        {
                            "startTime": "00:01",
                            "endTime": "23:59",
                            "capacity": 20,
                            "slots": [
                                "1pm5pm"
                            ]
                        },
                        {
                            "startTime": "00:01",
                            "endTime": "23:59",
                            "capacity": 20,
                            "slots": [
                                "6pm9pm"
                            ]
                        },
                        {
                            "startTime": "00:01",
                            "endTime": "23:59",
                            "capacity": 20,
                            "slots": [
                                "9pm12am"
                            ]
                        }
                    ]
                },
                {
                    "name": "1-3-days-delivery",
                    "isExcluded": false,
                    "removeFallbacks": true
                }
            ]
        },
        {
            "name": "non-dubai",
            "shippingMethods": [{
                    "name": "next-day-delivery",
                    "isExcluded": false,
                    "cutoffWindows": [{
                        "startTime": "00:01",
                        "endTime": "21:00",
                        "capacity": 40,
                        "slots": [
                            "12am12am"
                        ]
                    }]
                },
                {
                    "name": "1-3-days-delivery",
                    "isExcluded": false,
                    "removeFallbacks": true
                }
            ]
        }
    ]
}]

import csv
import json

def str_to_bool(s):
    if s.strip().upper() == "TRUE":
        return True
    elif s.strip().upper() == "FALSE":
        return False
    return None

def clean_empty_fields(data):
    # 递归移除空字符串、None的字段
    if isinstance(data, dict):
        return {k: v for k, v in ((k, clean_empty_fields(v)) for k, v in data.items()) if v not in (None, "", {})}
    elif isinstance(data, list):
        return [clean_empty_fields(item) for item in data if item not in (None, "", {})]
    return data

file = "sample"
# 只保留需要的字段，过滤CSV中多余字段
required_fields = {
    "key", "name", "loc_type", "loc_id", "cities_name", 
    "shippingMethods_name", "isExcluded", "cutoffWindows_startTime", 
    "cutoffWindows_endTime", "cutoffWindows_capacity", "cutoffWindows_slots", 
    "category", "furniture", "removeFallbacks"
}

# 用字典存储按key分组的数据，实现合并
result = {}

with open(f'csv/{file}.csv', encoding='utf-8-sig') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        # 过滤掉不需要的字段，同时去除字符串首尾空格
        filtered_row = {k: v.strip() if isinstance(v, str) else v for k, v in row.items() if k in required_fields}
        
        item_key = filtered_row["key"]
        # 如果key不存在，初始化基础结构
        if item_key not in result:
            result[item_key] = {
                "key": item_key,
                "name": filtered_row["name"],
                "loc_type": filtered_row["loc_type"],
                "loc_id": filtered_row["loc_id"],
                "category": filtered_row["category"],
                "furniture": str_to_bool(filtered_row["furniture"]),
                "cities": []
            }
        
        current_item = result[item_key]
        city_name = filtered_row["cities_name"]
        # 查找是否已存在该城市
        city = next((c for c in current_item["cities"] if c["name"] == city_name), None)
        if not city:
            city = {"name": city_name, "shippingMethods": []}
            current_item["cities"].append(city)
        
        shipping_method_name = filtered_row["shippingMethods_name"]
        # 查找是否已存在该配送方式
        shipping_method = next((sm for sm in city["shippingMethods"] if sm["name"] == shipping_method_name), None)
        if not shipping_method:
            shipping_method = {
                "name": shipping_method_name,
                "isExcluded": str_to_bool(filtered_row["isExcluded"]),
                "removeFallbacks": str_to_bool(filtered_row["removeFallbacks"])
            }
            city["shippingMethods"].append(shipping_method)
        
        # 处理 cutoffWindows，只有当有有效数据时才添加
        start_time = filtered_row["cutoffWindows_startTime"]
        end_time = filtered_row["cutoffWindows_endTime"]
        capacity = filtered_row["cutoffWindows_capacity"]
        slots = filtered_row["cutoffWindows_slots"]
        
        if start_time or end_time or capacity or slots:
            cutoff_window = {
                "startTime": start_time,
                "endTime": end_time,
                "capacity": int(capacity) if capacity else None,
                "slots": [slots] if slots else None
            }
            # 初始化 cutoffWindows 列表
            if "cutoffWindows" not in shipping_method:
                shipping_method["cutoffWindows"] = []
            shipping_method["cutoffWindows"].append(cutoff_window)

# 清理所有空字段，转换为列表格式
cleaned_result = clean_empty_fields(list(result.values()))

# 写入JSON，自动处理数组格式，避免语法错误
with open(f'output/{file}.json', 'w') as jsonfile:
    json.dump(cleaned_result, jsonfile, indent=4)

1. 合并相同key的对象：

   • 使用字典result按key分组存储数据，遍历CSV行时先检查key是否已存在，不存在则初始化基础结构；存在则逐层查找对应城市、配送方式，避免重复创建对象。

2. 过滤多余字段：

   • 定义required_fields集合，遍历CSV行时直接过滤掉不在集合中的字段，确保只有需要的数据进入后续处理。

3. 移除空字段：

   • 实现递归函数clean_empty_fields，遍历字典和列表，移除值为None、空字符串或空字典的字段，精简JSON输出。

4. 正确生成JSON数组：

   • 将最终分组结果转换为列表，直接用json.dump写入文件，自动处理数组格式，避免手动添加逗号导致的语法错误。

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