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?

Stephen Rauch · Accepted Answer · 2018-05-05 01:35:09Z

3

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

answered May 5, 2018 at 1:35

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas DataFrame to Nested JSON Without Changing Data Structure

1 Answer 1

Code:

Test Code:

Results:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Code:

Test Code:

Results:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related