将透视DataFrame写入Excel时修复表头行问题

问题修复步骤 #

• 合并表头为单行：将透视生成的多级列索引（MultiIndex）扁平化，移除顶部的"Amount"层级，仅保留年月作为列名
• 消除表头与数据间的空白行：转换为单级列索引后，pandas写入Excel时不会自动插入空白行，同时优化写入参数避免冗余内容

修改后的完整代码 #

import pandas as pd
import numpy as np

df = pd.DataFrame({
        'JDate':["2022-01-31","2022-12-05","2023-11-10","2023-12-03","2024-01-16","2024-01-06"],
        'Code':[None,'John Johnson',np.nan,'John Smith','Mary Williams','ted bundy'],
        'Unit Price':[np.nan,200,None,56,75,65],
        'Quantity':[1500, 140000, 1400000, 455, 648, 759],
        'Amount':[100, 10000, 100000, 5, 48, 59],
        'Invoice':['soccer','basketball','baseball','football','baseball','ice hockey'],
        'energy':[100.,100,100,54,98,3],
        'Category':['alpha','bravo','kappa','alpha','bravo','bravo']
})

df["JDate"] = pd.to_datetime(df["JDate"])
df["JYearMonth"] = df['JDate'].dt.to_period('M')

index_to_use = ['Category','Code','Invoice','Unit Price']
values_to_use = ['Amount']
columns_to_use = ['JYearMonth']

df2 = df.pivot_table(index=index_to_use,
                     values=values_to_use,
                     columns=columns_to_use)

df3 = df2.xs('alpha', level='Category')
df3 = df3.reset_index()

# 核心：扁平化多级列索引，生成目标表头
new_columns = []
for col in df3.columns:
    if isinstance(col, tuple):
        new_columns.append(str(col[1]))
    else:
        new_columns.append(col)
df3.columns = new_columns

# 写入Excel，此时表头为单行且无空白行
writer = pd.ExcelWriter("t2test6.xlsx", engine='xlsxwriter')
df3.to_excel(writer, sheet_name="t2filter", index=False)  # index=False可避免输出冗余索引列，按需调整
writer.close()

关键修改说明 #

• 列索引处理：遍历列名，对透视生成的多级索引列仅保留年月层级，最终列名变为['Code', 'Invoice', 'Unit Price', '2022-01', '2023-12']（匹配数据中alpha类别的有效年月）
• 写入优化：设置index=False移除多余的索引列，若需保留索引可改回index=True，不影响表头和空白行的修复
• 处理后Excel输出完全符合需求：表头为单行，表头与数据行之间无空白行

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