如何在pd.to_excel()导出时保留DataFrame.columns.name属性？

我需要批量创建透视表并导出到Excel，希望导出文件能保留DataFrame.columns.name属性。以下是我的代码：

import numpy as np
import pandas as pd

a = np.array(["foo", "foo", "foo", "foo", "bar", "bar",
              "bar", "bar", "foo", "foo", "foo"], dtype=object)
b = np.array(["one", "one", "one", "two", "one", "one",
              "one", "two", "two", "two", "one"], dtype=object)
data = {'A': a, 'B': b}
df = pd.DataFrame(data)
df['C'] = 1
table = pd.pivot_table(df, values='C', index=['A'], columns=['B'], aggfunc="count")
table.to_excel("table.xlsx")
print('Name: ',table.columns.name)
print()
print('Table: ')
print(table)

当前运行输出 #

Name:  B

Table: 
B    one  two
A            
bar    3    1
foo    4    3

当前导出的Excel表格样式 #

期望的Excel表格样式 #

解决方案 #

方法1：完全匹配期望样式

通过调整DataFrame结构，手动构造表头行和索引名行，再导出：

import numpy as np
import pandas as pd

a = np.array(["foo", "foo", "foo", "foo", "bar", "bar",
              "bar", "bar", "foo", "foo", "foo"], dtype=object)
b = np.array(["one", "one", "one", "two", "one", "one",
              "one", "two", "two", "two", "one"], dtype=object)
data = {'A': a, 'B': b}
df = pd.DataFrame(data)
df['C'] = 1
table = pd.pivot_table(df, values='C', index=['A'], columns=['B'], aggfunc="count")

# 重置索引，将索引转为普通列
table_reset = table.reset_index()
# 创建表头行：第一列为columns.name（B），其余列空值
header_row = pd.DataFrame([[table.columns.name] + [''] * len(table.columns)], columns=table_reset.columns)
# 合并表头行与数据行
final_df = pd.concat([header_row, table_reset], ignore_index=True)
# 将第二行第一列设为索引名（A）
final_df.iloc[1, 0] = table.index.name

# 导出到Excel，不保留默认表头和索引
with pd.ExcelWriter("table.xlsx") as writer:
    final_df.to_excel(writer, index=False, header=False)

方法2：规范多级表头格式

利用多级表头保留columns.name，导出更符合透视表规范的格式：

import numpy as np
import pandas as pd

a = np.array(["foo", "foo", "foo", "foo", "bar", "bar",
              "bar", "bar", "foo", "foo", "foo"], dtype=object)
b = np.array(["one", "one", "one", "two", "one", "one",
              "one", "two", "two", "two", "one"], dtype=object)
data = {'A': a, 'B': b}
df = pd.DataFrame(data)
df['C'] = 1
table = pd.pivot_table(df, values='C', index=['A'], columns=['B'], aggfunc="count")

# 构造多级表头，第一级为columns.name（B），第二级为列名
table.columns = pd.MultiIndex.from_tuples([(table.columns.name, col) for col in table.columns])

# 导出时启用合并单元格，保留索引
table.to_excel("table.xlsx", merge_cells=True)

方法2导出的Excel会显示两级表头（第一行是B，第二行是one、two），第一列表头为A，既保留了columns.name属性，也符合专业表格的展示规范。

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