[FIXED] How to generate a json file from dataframe concatenating columns

Issue

I have this dataframe:

name        age
Paul        12
Andres      13
Caroline    14
Justin      15

I want to create a json file with the structure below:

{"NAME":["Paul","Andres","Caroline","Justin"], "NAME-AGE" :{"Paul":12,"Andres":13,"Caroline":14,"Justin":15},"VERSION":1}

I know I can use df.to_json() but I'm having a hard time to get the exactly output I want, including the version information.

What's the best approach to do it?

Solution

You can use:

d = {'NAME': df['name'].to_list(),
     'NAME-AGE': df.set_index('name')['age'].to_dict(),
     'VERSION': 1,
    }

import json

out = json.dumps(d)

output:

'{"NAME": ["Paul", "Andres", "Caroline", "Justin"], "NAME-AGE": {"Paul": 12, "Andres": 13, "Caroline": 14, "Justin": 15}, "VERSION": 1}'

Answered By - mozway