如何使用Python将CSV转换为指定结构的嵌套JSON文件

问题描述 #

我查阅了大量该主题的过往问题，但均无法帮助我得到想要的结果。

所用CSV样例 #

tripId,scooterId,userId,totalDuration,pickUpTime,dropOffTime,userLocationAtBooking.0,userLocationAtBooking.1,userLocationAtDropOff.0,userLocationAtDropOff.1,totalFare
18721,927,38579,45,2021-08-22 03:00:49,2021-08-22 03:45:39,24.76412,46.6493,24.76409833,46.64934,9.58
18722,434,38563,45,2021-08-22 03:01:16,2021-08-22 03:45:39,24.76412,46.64933333,24.76407,46.64933333,13.53
18723,876,38554,33,2021-08-22 03:05:57,2021-08-22 03:38:55,24.71392833,46.660645,24.7097,46.66272,0.67
18724,476,32291,65,2021-08-22 03:14:37,2021-08-22 04:18:56,24.77137833,46.64568667,24.7722,46.64523167,32.35

期望输出结构 #

{
    "38579": {
        "18721": {
        "scooterId": "927",
        "userId": "38579",
        "totalDuration": "45",
        "pickUpTime": "2021-08-22 03:00:49",
        "dropOffTime": "2021-08-22 03:45:39",
        "userLocationAtBooking.0": "24.76412",
        "userLocationAtBooking.1": "46.6493",
        "userLocationAtDropOff.0": "24.76409833",
        "userLocationAtDropOff.1": "46.64934",
        "totalFare": "9.58"
        }
    },
    "38563": {
        "18722" : {
        "scooterId": "434",
        "userId": "38563",
        "totalDuration": "45",
        "pickUpTime": "2021-08-22 03:01:16",
        "dropOffTime": "2021-08-22 03:45:39",
        "userLocationAtBooking.0": "24.76412",
        "userLocationAtBooking.1": "46.64933333",
        "userLocationAtDropOff.0": "24.76407",
        "userLocationAtDropOff.1": "46.64933333"
        }
    }
}

实际输出 #

{
    "38563": {
        "tripId": "18722",
        "scooterId": "434",
        "userId": "38563",
        "totalDuration": "45",
        "pickUpTime": "2021-08-22 03:01:16",
        "dropOffTime": "2021-08-22 03:45:39",
        "userLocationAtBooking.0": "24.76412",
        "userLocationAtBooking.1": "46.64933333",
        "userLocationAtDropOff.0": "24.76407",
        "userLocationAtDropOff.1": "46.64933333",
        "totalFare": "13.53"
    }
}

现有代码 #

import csv, json
csvFilePath = 'tripsData.csv'
jsonFilePath = 'tripsData.json'

data = {}
with open(csvFilePath) as csvFile:
    csvReader = csv.DictReader(csvFile)
    for rows in csvReader:
        id = rows['userId']
        data[id] = rows
    for rows in csvReader:
        tripId = rows[tripId]
        data[tripId] = rows

with open(jsonFilePath, 'w') as jsonFile:
    jsonFile.write(json.dumps(data, indent=4))

修复方案 #

现有代码问题说明 #

• csv.DictReader返回的是迭代器对象，第一次循环遍历完成后指针已经指向文件末尾，第二个循环不会执行
• 没有按照需求构建userId -> tripId -> 行程详情的两层嵌套结构，直接给data[id]赋值会覆盖同一用户的其他行程数据
• tripId = rows[tripId]写法错误，缺少字符串引号，应该为rows['tripId']

修正后代码 #

import csv, json
csvFilePath = 'tripsData.csv'
jsonFilePath = 'tripsData.json'

data = {}
with open(csvFilePath) as csvFile:
    csvReader = csv.DictReader(csvFile)
    for row in csvReader:
        user_id = row['userId']
        trip_id = row['tripId']
        # 若用户ID不存在则先初始化空字典
        if user_id not in data:
            data[user_id] = {}
        # 往对应用户下写入行程ID为key的行程详情
        data[user_id][trip_id] = row

with open(jsonFilePath, 'w') as jsonFile:
    jsonFile.write(json.dumps(data, indent=4))

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