将嵌套字典转换为调整索引顺序的Pandas Multi-index DataFrame

可行实现代码 #

首先导入依赖，转换嵌套字典为目标格式DataFrame：

import pandas as pd

budget = {'January':{'income':{'work1':1982,'work2':1983},'expenses':{'rent':1500,'insurance':110}},
        'February':{'income':{'work1':1982,'work2':1983},'expenses':{'rent':1500,'insurance':110}},
        'March':{'income':{'work1':1982,'work2':1983},'expenses':{'rent':1500,'insurance':110,'other':150}},
        'April':{'income':{'work1':1982,'work2':1983},'expenses':{'rent':1500,'insurance':110}},
        'May':{'income':{'work1':1982,'work2':1983},'expenses':{'rent':1500,'insurance':110,'groceries':50}}
        }

# 1. 把嵌套字典展开为长格式数据表
long_data = []
for month, category_dict in budget.items():
    for category, item_dict in category_dict.items():
        for item, value in item_dict.items():
            long_data.append({
                "收支类型": category,
                "收支项": item,
                "月份": month,
                "金额": value
            })
long_df = pd.DataFrame(long_data)

# 2. 转换为行多级索引、列是月份的宽表，符合你要的Excel样式
target_df = long_df.pivot(
    index=["收支类型", "收支项"],
    columns="月份",
    values="金额"
).fillna(0).astype(int)

# 打印查看效果
print(target_df)

效果说明 #

pandas默认渲染DataFrame时，会自动合并上层重复的索引值，你在Notebook类环境中查看时和你期望的Excel格式完全一致。如果需要导出到Excel保留合并单元格效果，使用以下代码即可：

with pd.ExcelWriter("预算表.xlsx", engine="openpyxl") as writer:
    target_df.to_excel(writer, merge_cells=True)

导出的Excel会自动合并相同的收支类型单元格，和你的需求完全匹配。

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