[FIXED] Throwing KeyError, while performing Nested JSON Normalize using Pandas

Issue

I'm trying to normalize the JSON getting from GraphQL API and convert it to dataframe using json_normalize JSON

        [{
          "node": {
            "organization": {
              "company": "System"
            },
            "id": "15",
            "ip": "10.6.11.110",
            "name": "devce_name",
            "deviceClass": {
              "logicalName": "class OEM",
              "class": "class",
              "description": "OEM",
              "deviceCategory": {
                "name": "Unknown"
              }
            },
            "asset": {
              "location": "",
              "make": "make"
            },
            "events": {
              "edges": [
                {
                  "node": {
                    "message": "Device message",
                    "severity": "3
                  }
                },
                {
                  "node": {
                    "message": "message",
                    "severity": "2",
                  }
                }
              ]
            }
          }
        },
        ...
     ]

This is the json_normalize using pandas I'm trying

nd = pd.json_normalize(
    res,
    record_path=["node", "events", "edges"],
    meta= [["node", "organization", "company"], ["node", "name"], ["node", "ip"], ["node", "id"], ["node", "deviceClass"]]
)

If the meta inside size was 2 then is no error,

eg:- meta= [["node", "organization"], ["node", "name"], ["node", "ip"], ["node", "id"], ["node", "deviceClass"]]

but when I tried for more than 2 in the list then I getting below error.

eg: meta= [["node", "organization", "company"], ["node", "name"],["node", "id"], ["node", "deviceClass"]]

KeyError: 'company' The above exception was the direct cause of the following exception: .... KeyError: "Try running with errors='ignore' as key 'company' is not always present"

How to add more than 3 fields inside the meta?

Thanks

Solution

I stuck with the same issue a couple of days ago, You can try something different like this

nd1 = pd.json_normalize(js, record_path=["node", "events","edges"], /
                         meta = [["node", "id"]])

nd2 = pd.merge(nd1, pd.json_normalize(js), on='node.id',how='left')

Answered By - Pawan Jain