如何用循环等方法为DataFrame各月份生成独立分组表并导出至Excel

问题背景 #

已有Pandas DataFrame，需保留原有的YTD（年初至今）汇总表，同时自动识别数据中所有Year_Month值，为每个月份生成相同格式的汇总表，全部导出到同一个Excel文件的不同工作表中。

实现步骤及代码 #

1. 封装汇总逻辑为可复用函数

把生成汇总表的核心逻辑封装成函数，避免重复代码，同时保证YTD和分月报表格式一致：

import pandas as pd

def generate_summary(df):
    # 生成透视表
    result = pd.pivot_table(df, index=['Responsibility', 'Name'], columns=['Has Error'], aggfunc=len)
    # 清理数据
    result.fillna(0, inplace=True)
    result.columns = [s1 + "_" + str(s2) for (s1, s2) in result.columns.tolist()]
    # 重命名列，处理可能缺失的状态列（比如某月份只有一种错误状态）
    column_mapping = {
        'ID_Yes': 'Has Error (count)',
        'ID_No': 'Does NOT Have Error (count)'
    }
    for col in column_mapping.values():
        if col not in result.columns:
            result[col] = 0
    result = result.rename(columns=column_mapping)
    result = result.astype(int)
    # 计算占比和总计
    total = result['Has Error (count)'] + result['Does NOT Have Error (count)']
    result['Has Error (%)'] = round(result['Has Error (count)'] / total * 100, 2).astype(str) + '%'
    result['Does NOT Have Error (%)'] = round(result['Does NOT Have Error (count)'] / total * 100, 2).astype(str) + '%'
    result['Total Count'] = total
    # 调整列顺序
    result = result.reindex(columns=[
        'Has Error (%)', 
        'Does NOT Have Error (%)', 
        'Has Error (count)', 
        'Does NOT Have Error (count)', 
        'Total Count'
    ])
    return result

2. 生成并导出所有报表

先处理YTD汇总，再自动遍历所有月份生成对应报表，统一写入Excel：

# 原始数据（示例）
data = [['ABC', 'John', '123', 'Yes', '2022_Jan'], ['BCD', 'Amy', '456', 'Yes', '2022_Jan'], ['ABC', 'Michelle', '123', 'No', '2022_Feb'], ['CDE', 'John', '789', 'No', '2022_Feb'], ['ABC', 'Michelle', '012', 'Yes', '2022_Mar'], ['BCD', 'Amy', '123', 'No', '2022_Mar'], ['CDE', 'Jill', '789', 'No', '2022_Mar'], ['CDE', 'Jack', '789', 'No', '2022_Mar']]
tmp2 = pd.DataFrame(data, columns=['Responsibility', 'Name', 'ID', 'Has Error', 'Year_Month'])
tmp3 = tmp2[['Responsibility', 'Name', 'ID', 'Has Error']]

# 初始化Excel写入器，自动管理文件关闭
with pd.ExcelWriter('./output/final.xlsx', engine='xlsxwriter') as writer:
    # 生成并写入YTD汇总表
    ytd_summary = generate_summary(tmp3)
    ytd_summary.to_excel(writer, sheet_name='YTD Summary', startrow=0, startcol=0)
    
    # 获取所有唯一的月份，自动识别无需手动指定
    unique_months = tmp2['Year_Month'].unique()
    
    # 遍历每个月份生成报表
    for month in unique_months:
        # 筛选当前月份的数据
        monthly_data = tmp2[tmp2['Year_Month'] == month][['Responsibility', 'Name', 'ID', 'Has Error']]
        # 生成格式统一的汇总表
        monthly_summary = generate_summary(monthly_data)
        # 写入对应工作表，表名直接用月份命名
        monthly_summary.to_excel(writer, sheet_name=month, startrow=0, startcol=0)

代码说明 #

• 函数封装：generate_summary函数接收指定字段的DataFrame，输出格式统一的汇总表，同时处理了某类错误状态缺失的边界情况（比如某月份无错误数据）。
• 自动识别月份：通过tmp2['Year_Month'].unique()获取所有存在的月份，适配数据更新后的自动迭代。
• Excel写入：使用with语句管理ExcelWriter，确保文件正确关闭，同时依次完成YTD和分月报表的写入。

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