CSV转JSON时如何将PolicyID字段从字符串转为列表？

问题 #

我用Python写了一段CSV转JSON的代码，但需要把其中的PolicyID字段从字符串格式改成包含数字的列表（去掉引号并添加方括号）。现有代码、CSV内容、当前JSON输出及期望的JSON输出如下，请问该如何修改代码实现需求？

现有代码 #

import csv
import json


# Function to convert a CSV to JSON
# Takes the file paths as arguments
def make_json(csvFilePath, jsonFilePath):
    # create a dictionary
    data = {}

    # Open a csv reader called DictReader
    with open(csvFilePath, encoding='utf-8') as csvf:
        csvReader = csv.DictReader(csvf)

        # Convert each row into a dictionary
        # and add it to data
        for rows in csvReader:
            # Assuming a column named 'No' to
            # be the primary key
            key = rows['Username']
            data[key] = rows
            

    # Open a json writer, and use the json.dumps()
    # function to dump data
    with open(jsonFilePath, 'w', encoding='utf-8') as jsonf:
        jsonf.write(json.dumps(data, indent=4))


# Driver Code

# Decide the two file paths according to your
# computer system
csvFilePath = r'template.csv'
jsonFilePath = r'template.json'

# Call the make_json function
make_json(csvFilePath, jsonFilePath)

CSV内容 #

Username   First Name   Last Name  Email Address      PolicyID  Group
jazmin00!  Jazmin       Parker     jazmin@gmail.com   555       A
jon00!     Jon          Dimer      jon@gmail.com      222       B

当前JSON输出 #

{
    "jazmin00!": {
        "Username": "jazmin00!",
        "First Name": "Jazmin",
        "Last Name": "Parker",
        "Email Address": "jazmin@gmail.com",
        "PolicyID": "555",
        "Group": "A"
    },
    "jon00!": {
        "Username": "jon00!",
        "First Name": "Jon",
        "Last Name": "Dimer",
        "Email Address": "jon@gmail.com",
        "PolicyID": "222",
        "Group": "B"
    }
}

期望的JSON输出 #

{
    "jazmin00!": {
        "Username": "jazmin00!",
        "First Name": "Jazmin",
        "Last Name": "Parker",
        "Email Address": "jazmin@gmail.com",
        "PolicyID": [555],
        "Group": "A"
    },
    "jon00!": {
        "Username": "jon00!",
        "First Name": "Jon",
        "Last Name": "Dimer",
        "Email Address": "jon@gmail.com",
        "PolicyID": [222],
        "Group": "B"
    }
}

解决方案 #

只需要在循环处理每一行数据时，对PolicyID字段做格式转换：先把字符串转成整数，再包装成列表。修改后的代码如下：

import csv
import json


def make_json(csvFilePath, jsonFilePath):
    data = {}

    with open(csvFilePath, encoding='utf-8') as csvf:
        csvReader = csv.DictReader(csvf)

        for rows in csvReader:
            key = rows['Username']
            # 对PolicyID进行格式转换：字符串转整数后包装为列表
            rows['PolicyID'] = [int(rows['PolicyID'])]
            data[key] = rows
            

    with open(jsonFilePath, 'w', encoding='utf-8') as jsonf:
        jsonf.write(json.dumps(data, indent=4))


csvFilePath = r'template.csv'
jsonFilePath = r'template.json'
make_json(csvFilePath, jsonFilePath)

说明 #

• CSV文件读取的所有字段默认都是字符串类型，所以需要先用int()把PolicyID转成数字；
• 用方括号[]把转换后的数字包裹起来，就得到了期望的列表格式；
• 修改操作放在将行数据存入字典data之前，确保存入的是处理后的字段值。

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