Multiple keys defined in record_path not working when using json_normalize function of pandas

user_5594493 0
Viewed 28

I have a json value in where two keys are of List type inside main data point.

[
  {
    "org_id": 1,
    "org_name": "Test",
    "super_admin": [],
    "sub_admin": []
  },
  {
    "org_id": 2,
    "org_name": "QA",
    "super_admin": [
      {
        "id": 1,
        "first_name": "User",
        "last_name": null,
        "email": "qw@qw.com",
        "phone": null
      },
      {
        "id": 2,
        "first_name": "Test",
        "last_name": "User",
        "email": "asd@qw.com",
        "phone": null
      }
    ],
    "sub_admin": [
      {
        "id": 3,
        "first_name": "pd",
        "last_name": null,
        "email": "rt@rt,com",
        "phone": null
      }
    ]
  },
  {
    "org_id": 3,
    "org_name": "My test Org",
    "super_admin": [],
    "sub_admin": []
  },
  {
    "org_id": 4,
    "org_name": "test Org",
    "super_admin": [],
    "sub_admin": []
  }
]

When I am trying to Normalize this using pandas as

df = pd.json_normalize(arr, meta=['org_name', 'org_id',], record_path=['super_admin', 'sub_admin', ],   errors='ignore')

It raises an error KeyError: "Key 'sub_admin' not found. If specifying a record_path, all elements of data should have the path."

But if only one key is defined in record_path like pd.json_normalize(arr, meta=['org_name', 'org_id',], record_path=['super_admin', ], errors='ignore') then everything is working fine.

Initially, I thought It might be due to same keys in both super_admin & sub_admin but even with different keys json_normalize was raising same error.

I can merge both lists super_admin & sub_admin and create one list and use that in the record list but then I won't be able to separate in the final result which one is super admin and which one is sub admin value.

pandas python
1 Answers

Here is one way to do it:

import json

import pandas as pd

with open("file.json") as f:  # Your json copied in a file
    data = json.load(f)


df = pd.concat(
    [
        pd.json_normalize(
            data,
            meta=[
                "org_name",
                "org_id",
            ],
            record_path=[
                value,
            ],
            errors="ignore",
        ).assign(admin_type=value)
        for value in ("super_admin", "sub_admin")
    ]
).reset_index(drop=True)

print(df)
# Output
   id first_name last_name       email phone org_name org_id   admin_type
0   1       User      None   qw@qw.com  None       QA      2  super_admin
1   2       Test      User  asd@qw.com  None       QA      2  super_admin
2   3         pd      None   rt@rt,com  None       QA      2    sub_admin
user_112460560
Related