我正在尝试将平面结构的CSV转换为嵌套的JSON结构。CSV由SQL生成,SQL为每个主id创建多行。CSV的结构如下:
PrimaryId,FirstName,LastName,City,CarName,DogName
100,John,Smith,NewYork,Toyota,Spike
100,John,Smith,NewYork,BMW,Spike
100,John,Smith,NewYork,Toyota,Rusty
100,John,Smith,NewYork,BMW,Rusty
101,Ben,Swan,Sydney,Volkswagen,Buddy
101,Ben,Swan,Sydney,Ford,Buddy
101,Ben,Swan,Sydney,Audi,Buddy
101,Ben,Swan,Sydney,Volkswagen,Max
101,Ben,Swan,Sydney,Ford,Max
101,Ben,Swan,Sydney,Audi,Max
102,Julia,Brown,London,Mini,Lucy
所需的JSON输出是:
{
"data": [
{
"City": "NewYork",
"FirstName": "John",
"PrimaryId": 100,
"LastName": "Smith",
"CarName": [
"Toyota",
"BMW"
],
"DogName": [
"Spike",
"Rusty"
]
},
{
"City": "Sydney",
"FirstName": "Ben",
"PrimaryId": 101,
"LastName": "Swan",
"CarName": [
"Volkswagen",
"Ford",
"Audi"
],
"DogName": [
"Buddy",
"Max"
]
},
{
"City": "London",
"FirstName": "Julia",
"PrimaryId": 102,
"LastName": "Brown",
"CarName": [
"Mini"
],
"DogName": [
"Lucy"
]
}
]
}
转换为有效csv的数据保存在
data.csv
:使用pandas来完成繁重的工作,并假设这个csv文件是有效的,这是实现您所需的一种方法:
结果是:
下面是使用
csv.DictReader
执行此操作的一般方法。从加载数据开始:
现在,您可以使用
itertools.groupby
对每个组进行分组和处理。例如将按主要id和姓氏分组,并列出汽车列表。
一旦你有了这样的东西,你就可以使用^{} 。
相关问题 更多 >
编程相关推荐