如何让堆叠后的MultiIndex DataFrame导出Excel时索引每行填充

要让分层索引的每个层级在导出Excel的每行都显式显示，核心是把MultiIndex转换为普通数据列——之前设置的display.multi_sparse和styler.sparse.index仅影响Jupyter Notebook的可视化，不会改变DataFrame的底层结构，因此导出Excel仍会出现合并单元格。

具体实现步骤 #

1. 对堆叠后的df_stk调用reset_index()，将所有索引层级（Product、Year、Color、Size）转换为普通列
2. 为原数据列命名（比如Sales），避免导出后列名缺失
3. 导出Excel时直接使用转换后的DataFrame即可

完整代码示例 #

import pandas as pd
import numpy as np

# 生成宽格式MultiIndex DataFrame
colhead = ["Small Black", "Small White", "Small Brown", "Medium Black", "Medium White", "Medium Brown", "Large Black", "Large White", "Large Brown"]
rowhead = pd.MultiIndex.from_product([['sofa','table','chair'],[2011, 2012, 2013, 2014, 2015]])
df_mix = pd.DataFrame(np.random.randint(1,10, size=(15,9)), index=rowhead, columns=colhead)

# 修复列层级索引
hierarch1 = ["Small", "Small", "Small", "Medium", "Medium", "Medium", "Large", "Large", "Large"]
hierarch2 = ["Black", "White", "Brown", "Black", "White", "Brown", "Black", "White", "Brown"]
df_mix.columns = [hierarch1, hierarch2]
df_mix.columns.names = ['Size', 'Color']
df_mix.index.names = ['Product', 'Year']

# 堆叠为Tidy格式
stk = df_mix.stack()
df_stk = stk.stack()

# 关键操作：将MultiIndex转为普通列，并重命名数据列
df_final = df_stk.reset_index(name='Sales')

# 导出Excel，无合并单元格
with pd.ExcelWriter('Tidy.xlsx') as writer:
    df_final.to_excel(writer, sheet_name='Sales', index=False)

效果说明 #

导出的Excel文件中，每行都会完整显示Product、Year、Color、Size和Sales的内容，完全消除合并单元格，符合需求格式。

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