csv到嵌套的JSON? [英] csv to nested JSON?
本文介绍了csv到嵌套的JSON?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将平面结构的CSV转换为嵌套的JSON结构. CSV是从SQL生成的,SQL为每个主要ID创建多个行. CSV的结构如下:
I’m trying to convert a flat structured CSV into a nested JSON structure. The CSV is generated from SQL which creates multiple rows for each primary id. The CSV is structured as follows:
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输出为:
The desired JSON output is:
{
"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"
]
}
]
}
这篇文章和
Both this post and this one have helped but I'm yet to create the correct structure.
推荐答案
将转换为有效csv的数据保存在data.csv
中:
Your data, converted to valid csv is saved in data.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
使用熊猫做繁重的工作,并假设这个有效的csv文件,这是您想要做的一种方式:
Using pandas to do the heavy lifting, and assuming this valid csv file, this is one way of doing what you want:
import json
import pandas as pd
df = pd.read_csv('data.csv')
def get_nested_rec(key, grp):
rec = {}
rec['PrimaryId'] = key[0]
rec['FirstName'] = key[1]
rec['LastName'] = key[2]
rec['City'] = key[3]
for field in ['CarName','DogName']:
rec[field] = list(grp[field].unique())
return rec
records = []
for key, grp in df.groupby(['PrimaryId','FirstName','LastName','City']):
rec = get_nested_rec(key, grp)
records.append(rec)
records = dict(data = records)
print(json.dumps(records, indent=4))
结果:
{
"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到嵌套的JSON?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文