python - 使用 json_normalize 从多个级别获取元值

Question

假设这是我的 JSON:

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

我通过以下方式创建数据框:

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

这给了我:

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

但是，我还想包括 subGroup 列。

我尝试:

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

但这给出了:

KeyError: 'subGroup'

有什么想法吗？

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