Getting meta values from multiple level with json_normalize

Question

Suppose this is my JSON:

ds = [{
        "name": "groupa",
        "subGroups": [{
            "subGroup": 1,
            "people": [{
                "firstname":"Tony",
            },
            {
                "firstname":"Brian"
            }
            ]
        }]
    },
    {
        "name": "groupb",
        "subGroups": [{
            "subGroup": 1,
            "people": [{
                "firstname":"Tony",
            },
            {
                "firstname":"Brian"
            }
            ]
        }]
    }
]

I create a Dataframe by doing:

df = json_normalize(ds, record_path =['subGroups', 'people'], meta=['name'])

This gives me:

    firstname   name
0   Tony    groupa
1   Brian   groupa
2   Tony    groupb
3   Brian   groupb

However, I'd want to also include the subGroup column.

I try:

df = json_normalize(ds, record_path =['subGroups', 'people'], meta=['name', 'subGroup'])

But that gives:

KeyError: 'subGroup'

Any ideas?

Answer 1

json_normalize(
   ds, 
   record_path=['subGroups', 'people'], 
   meta=[
           'name', 
           ['subGroups', 'subGroup']   # each meta field needs its own path
   ], 
   errors='ignore'
)

  firstname    name  subGroups.subGroup
0      Tony  groupa                   1
1     Brian  groupa                   1
2      Tony  groupb                   1
3     Brian  groupb                   1

Answer 2

Try this.

df = json_normalize(ds, record_path =['subGroups', 'people'],meta['name'['subGroups', 'subGroup']])