1

I have a pandas dataframe that looks like this:

id x1 x2 x3 action
23432 example1 1 name Create
678786 example2 1 name Create

How can I convert that pandas dataframe to this json format?

[
  {
    "action": "Create",
    "payload": {
      "id": "23432",
      "x1": "example1",
      "x2": "1",
      "x3": "name"    
    }
  },
  {
    "action": "Create",
    "payload": {
      "id": "678786",
      "x1": "example2",
      "x2": "1",
      "x3": "name" 
    }
 }
]

I have played around with pd.json_normalize but can't get it to work. I also referenced this question but it's a bit different due to the need for groupby.

Nate
  • 466
  • 5
  • 23

2 Answers2

4

If you don't mind typing out the columns one solution can be:

df['payload'] = df[['id','x1','x2','x3']].to_dict(orient='records')
df_json = df[['action','payload']].to_json(orient='records')
print(df_json)
fcsr
  • 921
  • 10
  • 17
1
import pandas as pd
import json

# intialise data of lists.
data = {'id':['23432', '678786'],
        'x1':['example1', 'example2'],
        'x2':['1', '1'],
        'x3':['name', 'name'],
        'action':['Create', 'Create']}

# Create DataFrame
df = pd.DataFrame(data)

print(json.dumps(df.apply(func=lambda x:{'action':x.action,'payload':x[['id','x1','x2','x3']].to_dict()},axis=1).to_list()))
Suneesh Jacob
  • 806
  • 1
  • 7
  • 15