如何使用Pandas实现透视表中索引列的空单元格合并

问题场景 #

我用Pandas创建了透视表，并计算了行小计：

创建透视表代码 #

import pandas as pd
import numpy as np

table = pd.pivot_table(
   df,
   values="D",
   index=["A", "B"],
   columns=["C"],
   aggfunc=np.sum,
   margins=True,
   margins_name="total",
)

计算行小计代码 #

# 移除total列并排序索引避免性能警告
table = table.sort_index()

# 计算行总计并添加为新列
rows_total = table.reset_index().groupby(["A"]).agg({"C": sum})
for idx in table.index.get_level_values(0).unique():  # bar, foo, total
    table.loc[(idx,), "total"] = rows_total.loc[idx].values[0]

# 重新排序索引并格式化数值
table = table.reindex(index=["bar", "foo", "total"], level=0).astype(int)

# 清除重复的总计值
for idx_0 in table.index.get_level_values(0).unique():  # bar, foo, total
    if idx_0 == "total":
        continue
    for idx_1 in table.loc[(idx_0,), :].index[1:]:
        table.loc[(idx_0, idx_1), "total"] = ""

导出Excel后，当前效果是：索引第一列（A列）的相同值会在每一行重复显示（比如"bar"在多行都出现）；我想要实现的期望效果是：索引列相同值合并显示（相同值仅显示一次，跨对应所有行）。

解决方案 #

Pandas的DataFrame本身无法存储单元格合并的格式信息，需要借助Excel写入引擎（如xlsxwriter或openpyxl）在导出时设置格式：

方案1：使用xlsxwriter引擎 #

# 导出表格并设置合并单元格
writer = pd.ExcelWriter('透视表结果.xlsx', engine='xlsxwriter')
table.to_excel(writer, sheet_name='Sheet1')

workbook = writer.book
worksheet = writer.sheets['Sheet1']

# 处理索引第一列（A列）的相同值合并
prev_value = None
merge_start_row = 1  # 表头行是0，数据从第1行开始（对应Excel的第2行）

for row_idx in range(1, len(table) + 1):
    current_value = table.index.get_level_values(0)[row_idx - 1]
    if current_value != prev_value:
        if prev_value is not None:
            # 合并从merge_start_row到当前行前一行的单元格
            worksheet.merge_range(f'A{merge_start_row + 1}:A{row_idx}', prev_value)
        prev_value = current_value
        merge_start_row = row_idx

# 处理最后一组数据的合并
worksheet.merge_range(f'A{merge_start_row + 1}:A{len(table) + 1}', prev_value)

writer.close()

方案2：使用openpyxl引擎 #

# 先导出未设置格式的表格
table.to_excel('透视表结果.xlsx', sheet_name='Sheet1', engine='openpyxl')

# 加载工作簿并设置合并单元格
from openpyxl import load_workbook
wb = load_workbook('透视表结果.xlsx')
ws = wb['Sheet1']

# 合并A列相同值
prev_val = ws['A2'].value
start_row = 2

for row in range(3, ws.max_row + 1):
    current_val = ws[f'A{row}'].value
    if current_val != prev_val:
        ws.merge_cells(f'A{start_row}:A{row - 1}')
        prev_val = current_val
        start_row = row

# 合并最后一组
ws.merge_cells(f'A{start_row}:A{ws.max_row}')

wb.save('透视表结果.xlsx')

注意事项 #

• 两种方案都会针对索引第一级（A列）完成相同值合并，符合期望的显示效果；
• 如果需要对第二级索引（B列）做类似处理，可参照上述逻辑修改列范围（比如处理B列）。

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