Convert Pivot Like Data into JSON using Python or Pandas

byMR

March 3, 2023

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

>Solution :

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']}]