英文:
Convert Pivot Like Data into JSON using Python or Pandas
问题
以下是要翻译的内容：
我是Python的新手，我在将一些CSV数据转换为JSON数据时遇到了问题。
所以这是CSV中的数据
原始数据
编辑1： 示例Excel文件链接
数据透视表
这些数据的数据透视表如下所示。基本上它对配置文件进行分组，并列出与之关联的所有ID
我尝试使用Python和pandas将上述数据转换为以下格式：
[
{
"profile": "SG-1234-BOM-A",
"ids": ["8695e561-....639", "a65744ae-....9cb54"]
},
{
"profile": "SG-4567-PUN02",
"ids": ["6ee0f559-....96b08"]
},
{
"profile": "SG-7527-DEL02",
"ids": ["6f2c.....62ba", "78f2.....fc939", "8885....0dc"]
}
]
我也尝试使用以下代码：
gp = df.groupby(['profile']).apply(lambda x: x.to_json(orient='records')).to_dict()
无论我尝试什么都没有成功。我有10000多行需要以JSON格式进行格式化。在此先感谢任何帮助！
英文:
I am a newbie to python and I have an issue with converting some csv data to json data.
So here is the data in csv
RAW Data
EDIT 1: Link to sample excel file
Pivot Data
Pivot of this data translates like this. Basically it groups the profile and list all ids associated with it
I am trying to use python and pandas to convert the above data into following format:
[
{
"profile": "SG-1234-BOM-A",
"ids": ["8695e561-....639", "a65744ae-....9cb54"]
},
{
"profile": "SG-4567-PUN02",
"ids": ["6ee0f559-....96b08"]
},
{
"profile": "SG-7527-DEL02",
"ids": ["6f2c.....62ba", "78f2.....fc939", "8885....0dc"]
}
]
I have tried to use following codes as well:
gp = df.groupby(['profile']).apply(lambda x: x.to_json(orient='records')).to_dict()
No luck with anything I tried. I have 10000+ rows that needs to be formatted in json. Any help would be appreciated! thanks in advance
答案1
得分: 1
使用以下方法：
df.groupby('profile')['id'].apply(list)\
.reset_index(name='ids').to_dict(orient='records')
[{'profile': 'SG-1234-BOM-A',
'ids': ['8298-hdfbs-37483-s', 'dhduf-7435-4897h-a']},
{'profile': 'SG-4567-PUN02', 'ids': ['game-uio-09349-831']},
{'profile': 'SG-7527-DEL02',
'ids': ['2342-5648-9637-abc',
'4587-9721-4547-ytf',
'4654-a123-b789-09d',
'1234-gt3g-56qw-321',
'9897-93729-fghb-df',
'uidr-9087-tyip-012',
'hexa-tata-1234-fd3']}]
英文:
Use the following approach:
df.groupby('profile')['id'].apply(list)\
.reset_index(name='ids').to_dict(orient='records')
[{'profile': 'SG-1234-BOM-A',
'ids': ['8298-hdfbs-37483-s', 'dhduf-7435-4897h-a']},
{'profile': 'SG-4567-PUN02', 'ids': ['game-uio-09349-831']},
{'profile': 'SG-7527-DEL02',
'ids': ['2342-5648-9637-abc',
'4587-9721-4547-ytf',
'4654-a123-b789-09d',
'1234-gt3g-56qw-321',
'9897-93729-fghb-df',
'uidr-9087-tyip-012',
'hexa-tata-1234-fd3']}]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论