将嵌套字典转换为带节点层级的Pandas DataFrame

给定如下嵌套字典：

{
    8582: {
        "Amounts": 35892,
        8586: {
            "Amounts": 8955,
            8590: {"Amounts": 399},
            8674: {"Amounts": 111},
            8589: {"Amounts": 8445},
        },
        8585: {"Amounts": 13232, 8588: {"Amounts": 3884}, 8587: {"Amounts": 9348}},
        8593: {"Amounts": 8559, 8583: {"Amounts": 8559}},
        8584: {"Amounts": 5146, 8597: {"Amounts": 5146}},
    }
}

需求是将其转换为DataFrame：

• 所有层级的ID（非"Amounts"的键）作为独立列，层级数不固定（最多17级）
• 最后一列对应"Amounts"的数值

尝试过的无效方法 #

方法一 #

import pandas as pd

df = pd.DataFrame.from_dict([[k1, k2, v]
              for k1,d in data.items()
              for k2,v in d.items()])

方法二 #

import pandas as pd

def flatten_dict(d, parent_keys = None):
    if parent_keys is None:
        parent_keys = []

    items = []
    for k, v in d.items():
        keys = parent_keys + [k]
        if isinstance(v, int):
            items.append({"Keys": keys, "Amounts": v})
        else:
            items.extend(flatten_dict(v, keys))
    return items

flat_data = flatten_dict(data)
df = pd.DataFrame(flat_data)

正确转换方案 #

核心思路：遍历字典时记录每一层ID，遇到Amounts时生成包含所有层级ID和对应数值的记录，最后统一补全到最大层级数的列数，缺失ID用NaN填充。

import pandas as pd
import numpy as np

def flatten_nested_dict(d, current_ids=None):
    if current_ids is None:
        current_ids = []
    records = []
    
    for key, value in d.items():
        if key == "Amounts":
            # 生成记录：当前所有ID + Amount值
            records.append(current_ids + [value])
        else:
            # 递归遍历子ID
            records.extend(flatten_nested_dict(value, current_ids + [key]))
    
    return records

# 原始字典
data = {
    8582: {
        "Amounts": 35892,
        8586: {
            "Amounts": 8955,
            8590: {"Amounts": 399},
            8674: {"Amounts": 111},
            8589: {"Amounts": 8445},
        },
        8585: {"Amounts": 13232, 8588: {"Amounts": 3884}, 8587: {"Amounts": 9348}},
        8593: {"Amounts": 8559, 8583: {"Amounts": 8559}},
        8584: {"Amounts": 5146, 8597: {"Amounts": 5146}},
    }
}

# 扁平化得到所有记录
flat_records = flatten_nested_dict(data)

# 确定最大ID层级数
max_level = max(len(record)-1 for record in flat_records)

# 补全记录的ID列，不足部分用NaN填充
filled_records = []
for record in flat_records:
    ids = record[:-1]
    amount = record[-1]
    filled_ids = ids + [np.nan]*(max_level - len(ids))
    filled_records.append(filled_ids + [amount])

# 生成DataFrame并设置列名
columns = [f"ID_{i+1}" for i in range(max_level)] + ["Amounts"]
df = pd.DataFrame(filled_records, columns=columns)

print(df)

最终输出 #

ID_1   ID_2   ID_3  Amounts
0  8582    NaN    NaN    35892
1  8582  8586.0    NaN     8955
2  8582  8586.0  8590.0      399
3  8582  8586.0  8674.0      111
4  8582  8586.0  8589.0     8445
5  8582  8585.0    NaN    13232
6  8582  8585.0  8588.0     3884
7  8582  8585.0  8587.0     9348
8  8582  8593.0    NaN     8559
9  8582  8593.0  8583.0     8559
10 8582  8584.0    NaN     5146
11 8582  8584.0  8597.0     5146

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