Pandas Crosstable导出Excel时列描述缺失、数据错位如何解决？

我编写了一段用于生成面板与所用电机交叉表的代码：

import pandas as pd

Pnl = ['Panel A','Panel A','Panel A','Panel A','Panel A','Panel B','Panel B','Panel B','Panel B','Panel C','Panel C','Panel C']
Pwr = [0.55,1,0.55,4,2.2,2.2,1,0.55,4,5,1,2.2]

df = pd.DataFrame({"Panel": Pnl, "Power" : Pwr})
df_cross = pd.crosstab(df['Panel'],df['Power'])
print(df_cross)
df_cross.to_excel('crosstable.xlsx')

控制台输出的交叉表显示正常，但导出至Excel后，Power值出现错位，且列描述消失。此前我导出透视表时未出现此类问题，请问我哪里操作有误？

原因 #

pd.crosstab生成的交叉表中，Power是作为列索引存在的，并非普通列名。直接导出时，Excel会自动合并列索引的层级单元格，导致视觉错位；而"Power"作为索引名称，默认不会被当作表头行导出，所以看起来列描述消失了。

解决方法 #

给你两种简单的修复方案：

方案1：把列索引转成普通列，手动添加表头

这种方式会让表格结构和Excel常规格式完全一致：

import pandas as pd

Pnl = ['Panel A','Panel A','Panel A','Panel A','Panel A','Panel B','Panel B','Panel B','Panel B','Panel C','Panel C','Panel C']
Pwr = [0.55,1,0.55,4,2.2,2.2,1,0.55,4,5,1,2.2]

df = pd.DataFrame({"Panel": Pnl, "Power" : Pwr})
df_cross = pd.crosstab(df['Panel'],df['Power'])

# 重置索引，把Panel转成普通列，Power值转为列名
df_cross = df_cross.reset_index()
# 构建表头行：第一列是Panel，后面所有列都是Power
header = pd.DataFrame([['Panel'] + ['Power']*len(df_cross.columns[1:])], columns=df_cross.columns)
# 合并表头和数据
final_df = pd.concat([header, df_cross], ignore_index=True)

# 导出时不要带索引
final_df.to_excel('crosstable.xlsx', index=False)

方案2：禁用Excel单元格合并，保留索引结构

如果想保留交叉表的索引结构，只需要在导出时关闭合并单元格：

import pandas as pd

Pnl = ['Panel A','Panel A','Panel A','Panel A','Panel A','Panel B','Panel B','Panel B','Panel B','Panel C','Panel C','Panel C']
Pwr = [0.55,1,0.55,4,2.2,2.2,1,0.55,4,5,1,2.2]

df = pd.DataFrame({"Panel": Pnl, "Power" : Pwr})
df_cross = pd.crosstab(df['Panel'],df['Power'])

# 用ExcelWriter导出，禁用合并单元格
with pd.ExcelWriter('crosstable.xlsx') as writer:
    df_cross.to_excel(writer, merge_cells=False)

这样导出后，"Power"会显示在Excel表头的左上角，各个Power值作为列名正常排列，不会出现错位。

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