How to read json nested data with pandas

Question

I have Json data which have a combination of list and Dictionaries, particularly list within Dictionary and vice versa.

I am trying to parse this with Pandas and Json but not getting the right way to get it.

Json Data:

{
  "statusMessage": "OK",
  "statusCode": 200,
  "response": {
    "id": "15076",
    "name": "NetApp525",
    "startTime": 1626710400443,
    "mounted": false,
    "vmwareSnapshot": "Yes",
    "status": "Completed",
    "policy": "BckupPolicy",
    "entities": [
      {
        "entityName": "dbv6017_esx01",
        "quiesced": false,
        "uuid": "netfs://dbv6017.example.com///virt/dbv6017_esx01",
        "locations": [
          "192.168.1.2:/vol/dbv6017_esx01"
        ]
      },
      {
        "entityName": "backs6001.example.com",
        "quiesced": true,
        "uuid": "5558878d-f26a-5018-3cef-cgfh7802a550",
        "locations": [
          "[dbv6017_esx01] backs6001.example.com/backs6001.example.com.vmx"
        ]
      },
      {
        "entityName": "cpt6001",
        "quiesced": true,
        "uuid": "500a1bcc-6757-b5b8-6888-e66a26df713c",
        "locations": [
          "[dbv6017_esx01] dnvt6001/dnvt6001.vmx"
        ]
      },
      {
        "entityName": "cpt0254",
        "quiesced": true,
        "uuid": "300a028f-68e8-0db8-8919-2trpc01a7dfb3",
        "locations": [
          "[dbv6017_esx01] cpt0254/cpt0254.vmx"
        ]
      },
      {
        "entityName": "cpt6018",
        "quiesced": true,
        "uuid": "500aa1bf-38b0-2e05-613b-c232ac0a36c3",
        "locations": [
          "[dbv6017_esx01] cpt6018/cpt6018.vmx"
        ]
      }
    ]
  }
}

What I'm trying:

import json
import pandas as pd
df = pd.read_json("my_json_data")

print(df)


>>> df
               statusMessage  statusCode  \
entities                  OK         200
id                        OK         200
mounted                   OK         200
name                      OK         200
policy                    OK         200
startTime                 OK         200
status                    OK         200
vmwareSnapshot            OK         200

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    response
entities        [{'entityName': 'dbv6017_esx01', 'quiesced': False, 'uuid': 'netfs://dbv6017.example.com///virt/dbv6017_esx01', 'locations': ['192.168.1.2:/vol/dbv6017_esx01']}, {'entityName': 'backs6001.example.com', 'quiesced': True, 'uuid': '5558878d-f26a-5018-3cef-cgfh7802a550', 'locations': ['[dbv6017_esx01] backs6001.example.com/backs6001.example.com.vmx']}, {'entityName': 'cpt6001', 'quiesced': True, 'uuid': '500a1bcc-6757-b5b8-6888-e66a26df713c', 'locations': ['[dbv6017_esx01] dnvt6001/dnvt6001.vmx']}, {'entityName': 'cpt0254', 'quiesced': True, 'uuid': '300a028f-68e8-0db8-8919-2trpc01a7dfb3', 'locations': ['[dbv6017_esx01] cpt0254/cpt0254.vmx']}, {'entityName': 'cpt6018', 'quiesced': True, 'uuid': '500aa1bf-38b0-2e05-613b-c232ac0a36c3', 'locations': ['[dbv6017_esx01] cpt6018/cpt6018.vmx']}]
id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     15076
mounted                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                False
name                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               NetApp525
policy                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           BckupPolicy
startTime                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      1626710400443
status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             Completed
vmwareSnapshot                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           Yes

Desired:

I want the Keys of column response to be converted as column and values remain with column then.

entityName                      quiesced   uuid                                     locations
backs6001.example.com           TRue    5558878d-f26a-5018-3cef-cgfh7802a550        dbv6017_esx01

Corralien · Accepted Answer · 2021-07-19 18:26:19Z

1

Use pd.json_normalize:

import json

data = json.loads("my_json_data")

df = pd.json_normalize(data['response']['entities'])
df['locations'] = df['locations'].explode()

              entityName  quiesced                                              uuid                                          locations
0          dbv6017_esx01     False  netfs://dbv6017.example.com///virt/dbv6017_esx01                     192.168.1.2:/vol/dbv6017_esx01
1  backs6001.example.com      True              5558878d-f26a-5018-3cef-cgfh7802a550  [dbv6017_esx01] backs6001.example.com/backs600...
2                cpt6001      True              500a1bcc-6757-b5b8-6888-e66a26df713c              [dbv6017_esx01] dnvt6001/dnvt6001.vmx
3                cpt0254      True             300a028f-68e8-0db8-8919-2trpc01a7dfb3                [dbv6017_esx01] cpt0254/cpt0254.vmx
4                cpt6018      True              500aa1bf-38b0-2e05-613b-c232ac0a36c3                [dbv6017_esx01] cpt6018/cpt6018.vmx

You need to do some adjustments to get your expected output but the idea is there.

answered Jul 19, 2021 at 18:26

Corralien

121k8 gold badges43 silver badges68 bronze badges

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

1 Comment

user2023 Over a year ago

Thanks @Corralien, +1, i'll check this, what explode is doing.

Collectives™ on Stack Overflow

How to read json nested data with pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related