从嵌套字典列表创建Pandas DataFrame时字段获取失败求助

测试数据 #

test_dict = {'name': 'Henry VIII', 'wives': [{'WED': '11JUNE1509', 'spouse': 'TBC', 'marriage': {'name': 'Henry VIII', 'identifier': 'henry'}, 'died': 0.0, 'marriage_lasted': {'yrs': 23.0, 'mth': 11.0, 'days': 12.0}, 'timestamp': '2022-07-30T13:48:44+00:00', 'last_at': '2022-07-30T13:48:44+00:00', 'fetch_at': '2022-07-30T13:51:47+00:00', 'url': 'https://https://en.wikipedia.org/wiki/Wives_of_Henry_VIII', 'husband': 'king henry of england', 'wife': 'catherine of aragon'}, {'WED': '28MAY1533', 'spouse': 'TBC', 'marriage': {'name': 'Henry VIII', 'identifier': 'henry'}, 'died' : 1.0, 'marriage_lasted': {'yrs': 2.0, 'mth': 11.0, 'days': 19.0}, 'timestamp': '2022-07-30T13:51:47+00:00', 'last_at': '2022-07-30T13:51:47+00:00', 'fetch_at': '2022-07-30T13:51:47+00:00', 'url': 'https://https://en.wikipedia.org/wiki/Wives_of_Henry_VIII', 'husband': 'king henry of england', 'wife': 'anne boleyn'}]}

期望输出 #

name  identifier         WED spouse  died   yrs   mth   days                husband                 wife
0   Henry VIII       henry  11JUNE1509    TBC  0.00  23.0  11.0   12.0  king henry of england  catherine of aragon
1   Henry VIII       henry   28MAY1533    TBC  1.00   2.0  11.0   19.0  king henry of england          anne boleyn

当前代码及错误输出 #

直接用pd.DataFrame转换时，嵌套在marriage和marriage_lasted中的字段无法被识别，返回NaN：

import pandas as pd
df = pd.DataFrame(test_dict['wives'], columns = ['name', 'identifier', 'WED','spouse', 'died', 'yrs', 'mth', 'days', 'husband', 'wife'])
print(df)

错误输出：

name  identifier         WED spouse  died  yrs  mth  days                husband                 wife
0   NaN         NaN  11JUNE1509    TBC  0.00  NaN  NaN   NaN  king henry of england  catherine of aragon
1   NaN         NaN   28MAY1533    TBC  1.00  NaN  NaN   NaN  king henry of england          anne boleyn

解决方法 #

方法1：手动提取嵌套字段 #

遍历每个子字典，将嵌套层级的字段提取到顶层后再转换为DataFrame：

import pandas as pd

processed_wives = []
for wife in test_dict['wives']:
    processed = {
        'name': wife['marriage']['name'],
        'identifier': wife['marriage']['identifier'],
        'WED': wife['WED'],
        'spouse': wife['spouse'],
        'died': wife['died'],
        'yrs': wife['marriage_lasted']['yrs'],
        'mth': wife['marriage_lasted']['mth'],
        'days': wife['marriage_lasted']['days'],
        'husband': wife['husband'],
        'wife': wife['wife']
    }
    processed_wives.append(processed)

df = pd.DataFrame(processed_wives)
print(df)

方法2：使用pd.json_normalize自动展平嵌套结构 #

利用Pandas内置的json_normalize函数自动展平嵌套字典，再筛选并重命名所需列：

import pandas as pd

# 展平所有嵌套字段
df = pd.json_normalize(test_dict['wives'])

# 重命名嵌套列并筛选目标字段
df = df.rename(columns={
    'marriage.name': 'name',
    'marriage.identifier': 'identifier',
    'marriage_lasted.yrs': 'yrs',
    'marriage_lasted.mth': 'mth',
    'marriage_lasted.days': 'days'
})[['name', 'identifier', 'WED','spouse', 'died', 'yrs', 'mth', 'days', 'husband', 'wife']]

print(df)

两种方法均可得到期望输出，其中json_normalize更适合处理复杂嵌套结构，无需手动遍历提取。

内容的提问来源于stack exchange，提问作者Diop Chopra