How To Create A Dataframe To Generate Json In The Given Format

I need to generate a json from my dataframe but I have tried many formats of df but still I am not able get the required json format. My required json format is, [ { 'K

Solution 1:

I think you need:

• set_index for columns not in nested dictionaries
• create dicts by apply with to_dict
• reset_index for column from index
• create json by to_json

---

print (df)

  Keyword     TC  value
0     Red  Color51  Orange  Color52  Violet  Color5

j = (df.set_index('Keyword')
        .apply(lambda x: [x.to_dict()], axis=1)
        .reset_index(name='values')
        .to_json(orient='records'))
print (j)

[{"Keyword":"Red","values":[{"TC":"Color","value":5}]},
 {"Keyword":"Orange","values":[{"TC":"Color","value":5}]},
 {"Keyword":"Violet","values":[{"TC":"Color","value":5}]}]

For write to file:

(df.set_index('Keyword')
   .apply(lambda x: [x.to_dict()], axis=1)
   .reset_index(name='values')
   .to_json('myfile.json', orient='records'))