不同深度的复杂字典如何转换为带多级列索引的Pandas DataFrame

你得到空DataFrame的核心原因有两个：

• pd.MultiIndex.from_product入参用法错误，该方法要求传入可迭代对象的列表来生成各层级的笛卡尔积组合，你传入的参数不符合格式要求
• 原生pandas.DataFrame初始化不会自动递归解析嵌套字典的多层路径，也无法自动处理key2对应的列表结构中的嵌套字段

具体实现代码 #

1. 依赖导入与通用拍平函数 #

import pandas as pd
from collections.abc import MutableMapping

def flatten_dict(d, parent_key=()):
    """递归拍平嵌套字典，返回路径元组为键的单层字典"""
    items = []
    for k, v in d.items():
        new_key = parent_key + (k,)
        if isinstance(v, MutableMapping):
            items.extend(flatten_dict(v, new_key).items())
        else:
            items.append((new_key, v))
    return dict(items)

2. 数据处理与DataFrame生成 #

# 修正原字典的语法错误（key2数组后漏了逗号）
raw_dict = {
    "key1":"value1",
    "key2":[
        {
            "key2.1a":"value2.1a",
            "key2.2a":"value2.2a",
            "key2.3a":{
                "keya2.3.1a":"value2.3.1a"
            },     
            "key2.4a":"value2.4a",
            "key2.5a":"value2.5a",
            "key2.6a":"value2.6a",
            "key2.7a":"value2.7a",
            "key2.8a":"value2.8a",
            "key2.9a":"value2.9a",
            "key2.10a":{
                "key2.10.1a":"value2.10.1a",
                "key2.10.2a":"value2.10.2a",
                "key2.10.3a":"value2.10.3a",
                "key2.10.4a":{
                    "key2.10.4.1a":"value2.10.4.1a"
                }
            },
            "key2.11a":{
                "key2.11.1a":"value2.11.1a",
                "key2.11.2a":"value2.11.2a"
            },
            "key2.12a":"value2.12a",
            "key2.13a":"value2.13a"
        },
        {
            "key2.1b":"value2.1b",
            "key2.2b":"value2.2b",
            "key2.3b":{
                "keya2.3.1b":"value2.3.1b"
            },     
            "key2.4b":"value2.4b",
            "key2.5b":"value2.5b",
            "key2.6b":"value2.6b",
            "key2.7b":"value2.7b",
            "key2.8b":"value2.8b",
            "key2.9b":"value2.9b",
            "key2.10b":{
                "key2.10.1b":"value2.10.1b",
                "key2.10.2b":"value2.10.2b",
                "key2.10.3b":"value2.10.3b",
                "key2.10.4b":{
                    "key2.10.4.1b":"value2.10.4.1b"
                }
            },
            "key2.11b":{
                "key2.11.1b":"value2.11.1b",
                "key2.11.2b":"value2.11.2b"
            },
            "key2.12b":"value2.12b",
            "key2.13b":"value2.13b"
        }
    ],
    "key3":"value3"
}

# 处理每条记录，拼接全局字段
flat_records = []
global_fields = flatten_dict({k:raw_dict[k] for k in ["key1", "key3"]})
for record in raw_dict["key2"]:
    flat_record = flatten_dict(record)
    # 合并全局字段和当前记录字段
    flat_record.update(global_fields)
    flat_records.append(flat_record)

# 生成DataFrame，自动将路径元组转为多级列
df = pd.DataFrame(flat_records)
# 可选：设置多级列的名称，方便识别层级
df.columns = pd.MultiIndex.from_tuples(df.columns, names=["层级1", "层级2", "层级3", "层级4"])

效果说明 #

生成的DataFrame列就是和嵌套路径对应的多级索引，每一行对应key2中的一条记录，全局的key1、key3会在每一行都有对应值，符合层级索引列的需求。如果需要自定义列的取值路径，只需要调整拍平函数中生成new_key的规则即可。

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