Convert Pivot Like Data into JSON using Python or Pandas

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

enter image description here

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

enter image description here

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

>Solution :

Use the following approach:


[{'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',

Leave a Reply