Pandas DataFrame to Nested JSON Without Changing Data Structure

Question

I have pandas.DataFrame:

import pandas as pd
import json
df = pd.DataFrame([['2016-04-30T20:02:25.693Z', 'vmPowerOn', 'vmName'],['2016-04-07T22:35:41.145Z','vmPowerOff','hostName']], 
                  columns=['date', 'event', 'object'])

    date                        event       object
0   2016-04-30T20:02:25.693Z    vmPowerOn   vmName
1   2016-04-07T22:35:41.145Z    vmPowerOff  hostName

I want to convert that dataframe into the following format:

  {
    "name":"Alarm/Error",
    "data":[
      {"date": "2016-04-30T20:02:25.693Z", "details": {"event": "vmPowerOn", "object": "vmName"}},
      {"date": "2016-04-07T22:35:41.145Z", "details": {"event": "vmPowerOff", "object": "hostName"}}
    ]
  }

So far, I've tried this:

df = df.to_dict(orient='records')
j = {"name":"Alarm/Error", "data":df}
json.dumps(j)

'{"name": "Alarm/Error", 
  "data": [{"date": "2016-04-30T20:02:25.693Z", "event": "vmPowerOn", "object": "vmName"}, 
           {"date": "2016-04-07T22:35:41.145Z", "event": "vmPowerOff", "object": "hostName"}
          ]
 }'

However, this obviously does not put the detail columns in their own dictionary.

How would I efficiently split the df date column and all other columns into separate parts of the JSON?

Answer 1

With a list and dict comprehension, you can do that like:

Code:

[{'date': x['date'], 'details': {k: v for k, v in x.items() if k != 'date'}}
 for x in df.to_dict('records')]

Test Code:

df = pd.DataFrame([['2016-04-30T20:02:25.693Z', 'vmPowerOn', 'vmName'],
                   ['2016-04-07T22:35:41.145Z', 'vmPowerOff', 'hostName']],
                  columns=['date', 'event', 'object'])

print([{'date': x['date'],
        'details': {k: v for k, v in x.items() if k != 'date'}}
       for x in df.to_dict('records')])

Results:

[{'date': '2016-04-30T20:02:25.693Z', 'details': {'event': 'vmPowerOn', 'object': 'vmName'}}, 
 {'date': '2016-04-07T22:35:41.145Z', 'details': {'event': 'vmPowerOff', 'object': 'hostName'}}
]