如何将特定嵌套字典转换为指定格式的Pandas DataFrame

需求说明 #

需要将形如{key1:[{key:value},{key:value}, ...],key2:[{key:value},{key:value},...]}的嵌套字典转换为Pandas DataFrame：

• 顶级字典的key作为DataFrame的索引
• 每个顶级key对应的列表中，所有单键字典的key作为DataFrame的列名，对应value作为该行的记录值
• 不同顶级key对应的列表内字段数量可能不同，缺失字段自动填充NaN

示例输入数据 #

some_dict = {
    '0000297386FB11E2A2730050568F1BAB': [
        {'FILE_ID': '0000297386FB11E2A2730050568F1BAB'},
        {'FileTime': '1362642335'},
        {'Size': '1016439'},
        {'DocType_Code': 'AF3BD580734A77068DD083389AD7FDAF'},
        {'Filenr': 'F682B798EC9481FF031C4C12865AEB9A'},
        {'DateRegistered': 'FAC4F7F9C3217645C518D5AE473DCB1E'},
        {'TITLE': '2096158F036B0F8ACF6F766A9B61A58B'}
    ],
    '000031EA51DA11E397D30050568F1BAB': [
        {'FILE_ID': '000031EA51DA11E397D30050568F1BAB'},
        {'FileTime': '1384948248'},
        {'Size': '873514'},
        {'DatePosted': '7C6BCB90AC45DA1ED6D1C376FC300E7B'},
        {'DocType_Code': '28F404E9F3C394518AF2FD6A043D3A81'},
        {'Filenr': '13A6A062672A88DE75C4D35917F3C415'},
        {'DateRegistered': '8DD4262899F20DE45F09F22B3107B026'},
        {'Comment': 'AE207D73C9DDB76E1EEAA9241VJGN02'},
        {'TITLE': 'DF96336A6FE08E34C5A94F6A828B4B62'}
    ]
}

期望输出格式 #

Index | FILE_ID | FileTime | Size | DocType_Code | Filenr | DateRegistered | Title | DatePosted | Comment
--- | --- | --- | --- | --- | --- | --- | --- | --- | ---
0000297386FB11E2A2730050568F1BAB | 0000297386FB11E2A2730050568F1BAB | 1362642335 | 1016439 | AF3BD580734A77068DD083389AD7FDAF | F682B798EC9481FF031C4C12865AEB9A | FAC4F7F9C3217645C518D5AE473DCB1E | 2096158F036B0F8ACF6F766A9B61A58B | NaN | NaN
000031EA51DA11E397D30050568F1BAB | 000031EA51DA11E397D30050568F1BAB | 1384948248 | 873514 | 28F404E9F3C394518AF2FD6A043D3A81 | 13A6A062672A88DE75C4D35917F3C415 | 8DD4262899F20DE45F09F22B3107B026 | DF96336A6FE08E34C5A94F6A828B4B62 | 7C6BCB90AC45DA1ED6D1C376FC300E7B | AE207D73C9DDB76E1EEAA9241VJGN02

解决方法 #

核心思路是先将每个顶级key对应的列表中的多个单键字典合并为一个完整字典，再将所有顶级key与对应完整字典组合成新结构，最后转换为DataFrame并设置索引。

代码实现 #

import pandas as pd

# 1. 遍历原字典，合并每个列表中的单键字典为完整字典
processed_data = {}
for idx_key, dict_list in some_dict.items():
    merged_dict = {}
    for single_dict in dict_list:
        merged_dict.update(single_dict)
    processed_data[idx_key] = merged_dict

# 2. 转换为DataFrame，指定顶级key为索引
df = pd.DataFrame.from_dict(processed_data, orient='index')

# 可选：调整列名大小写（如将TITLE改为Title）
df.rename(columns={'TITLE': 'Title'}, inplace=True)

# 查看结果
print(df)

代码说明 #

• merged_dict.update(single_dict)：利用字典update方法逐个合并单键字典，得到每个索引对应的完整字段集合
• pd.DataFrame.from_dict(processed_data, orient='index')：通过orient='index'参数直接将字典key设为DataFrame索引，value中的键设为列名
• 缺失字段自动填充NaN，满足不同条目字段数量不一致的需求

内容的提问来源于Stack Exchange，提问作者Wokkel