如何将包含JSON数据的两列CSV数据格式化到字典中？

Question

因此，我正在使用一个导出到CSV文件中的DB进行一些工作。它有两列，'id'和'json'。'id'列包含表示'json'列中数据的任意整数。以下是一些示例CSV数据：

id,json
-2023284724,"{""classType"":2,""displayProperties"":{""name"":""Warlock"",""hasIcon"":false},""genderedClassNames"":{""Male"":""Warlock"",""Female"":""Warlock""},""genderedClassNamesByGenderHash"":{""3111576190"":""Warlock"",""2204441813"":""Warlock""},""hash"":2271682572,""index"":2,""redacted"":false,""blacklisted"":false}"
-639573535,"{""classType"":0,""displayProperties"":{""name"":""Titan"",""hasIcon"":false},""genderedClassNames"":{""Male"":""Titan"",""Female"":""Titan""},""genderedClassNamesByGenderHash"":{""3111576190"":""Titan"",""2204441813"":""Titan""},""hash"":3655393761,""index"":0,""redacted"":false,""blacklisted"":false}"
671679327,"{""classType"":1,""displayProperties"":{""name"":""Hunter"",""hasIcon"":false},""genderedClassNames"":{""Male"":""Hunter"",""Female"":""Hunter""},""genderedClassNamesByGenderHash"":{""3111576190"":""Hunter"",""2204441813"":""Hunter""},""hash"":671679327,""index"":1,""redacted"":false,""blacklisted"":false}"

现在我想把它转换成一个python字典，它使用一个{id:json...}的键值对。我可以通过以下代码实现这一点：

import csv
import json

with open('data.csv', mode='r') as infile: #open CSV data file
    reader = csv.reader(infile) #create CSV reader
    mydict = {rows[0]:rows[1] for rows in reader if rows[0] != "id"} #create the key value pairs in the format shown above. (The if rows[0] != "id" is to prevent the program from putting in the header row)

这基本上奏效了。这是我在运行该程序时得到的数据：

{'-2023284724': '{"classType":2,"displayProperties":{"name":"Warlock","hasIcon":false},"genderedClassNames":{"Male":"Warlock","Female":"Warlock"},"genderedClassNamesByGenderHash":{"3111576190":"Warlock","2204441813":"Warlock"},"hash":2271682572,"index":2,"redacted":false,"blacklisted":false}',
 '-639573535': '{"classType":0,"displayProperties":{"name":"Titan","hasIcon":false},"genderedClassNames":{"Male":"Titan","Female":"Titan"},"genderedClassNamesByGenderHash":{"3111576190":"Titan","2204441813":"Titan"},"hash":3655393761,"index":0,"redacted":false,"blacklisted":false}',
 '671679327': '{"classType":1,"displayProperties":{"name":"Hunter","hasIcon":false},"genderedClassNames":{"Male":"Hunter","Female":"Hunter"},"genderedClassNamesByGenderHash":{"3111576190":"Hunter","2204441813":"Hunter"},"hash":671679327,"index":1,"redacted":false,"blacklisted":false}'}

所以我得到了我想要的。但是，字典中'json'列中的JSON数据只是一个字符串。我的问题是，如何将JSON数据格式化为python字典中正确格式化的一部分

以下是我希望它看起来像什么的示例（仅一行）：

{
    -2023284724: {
        "classType":2,
        "displayProperties":{
            "name":"Warlock",
            "hasIcon":false
        },
        "genderedClassNames":{
            "Male":"Warlock",
            "Female":"Warlock"
        },
        "genderedClassNamesByGenderHash":{
            "3111576190":"Warlock",
            "2204441813":"Warlock"
        },
        "hash":2271682572,
        "index":2,
        "redacted":false,
        "blacklisted":false
    }, 
    ...
}

谢谢

Answer 1

调用第一列上的int()和第二列上的json.loads()，将它们从字符串转换为所需的类型：

import csv
import json
import pprint

with open('data.csv', newline='') as infile: #open CSV data file
    reader = csv.reader(infile) #create CSV reader
    next(reader) # skip header
    mydict = {int(i):json.loads(j) for i,j in reader}

pprint.pprint(mydict)

输出：

{-2023284724: {'blacklisted': False,
               'classType': 2,
               'displayProperties': {'hasIcon': False, 'name': 'Warlock'},
               'genderedClassNames': {'Female': 'Warlock', 'Male': 'Warlock'},
               'genderedClassNamesByGenderHash': {'2204441813': 'Warlock',
                                                  '3111576190': 'Warlock'},
               'hash': 2271682572,
               'index': 2,
               'redacted': False},
 -639573535: {'blacklisted': False,
              'classType': 0,
              'displayProperties': {'hasIcon': False, 'name': 'Titan'},
              'genderedClassNames': {'Female': 'Titan', 'Male': 'Titan'},
              'genderedClassNamesByGenderHash': {'2204441813': 'Titan',
                                                 '3111576190': 'Titan'},
              'hash': 3655393761,
              'index': 0,
              'redacted': False},
 671679327: {'blacklisted': False,
             'classType': 1,
             'displayProperties': {'hasIcon': False, 'name': 'Hunter'},
             'genderedClassNames': {'Female': 'Hunter', 'Male': 'Hunter'},
             'genderedClassNamesByGenderHash': {'2204441813': 'Hunter',
                                                '3111576190': 'Hunter'},
             'hash': 671679327,
             'index': 1,
             'redacted': False}}