如何将多个 Excel 工作表中的数据聚合并生成结构化汇总表

本文介绍如何使用 pandas 读取同一 excel 文件中多个已有工作表的数据，按业务维度（如交易对手、商品、交易方向）进行分组聚合与透视，并将结果写入新工作表，实现跨脚本数据的自动化整合。

本文介绍如何使用 pandas 读取同一 excel 文件中多个已有工作表的数据，按业务维度（如交易对手、商品、交易方向）进行分组聚合与透视，并将结果写入新工作表，实现跨脚本数据的自动化整合。

在实际业务场景中，多个独立 Python 脚本每日分别处理不同来源的 CSV/Excel 数据，并将结果写入同一 Excel 文件的不同工作表（如 Sheet1、Sheet2）。此时，需新增一个“汇总脚本”，不修改原有逻辑，仅从该 Excel 中读取已有数据，提取关键字段（如 Counterparty、Commodity、Deal、Total），完成跨表聚合与结构化输出——例如按 Deal（Buy/Sell）分块、以 Counterparty 为行、Commodity 为列生成汇总透视表，并写入 Sheet3。

以下是完整可执行的解决方案：

import pandas as pd

# 步骤1：读取目标Excel中指定工作表（注意：sheet_name支持字符串或列表）
file_path = "daily_reports.xlsx"
df_sheet1 = pd.read_excel(file_path, sheet_name='Sheet1')
df_sheet2 = pd.read_excel(file_path, sheet_name='Sheet2')

# 步骤2：合并所有相关数据（保留原始结构，便于后续分组）
df_all = pd.concat([df_sheet1, df_sheet2], ignore_index=True)

# 步骤3：按 'Deal' 分组处理，分别生成 Buy 和 Sell 的透视表
pivot_tables = {}
for deal_type in ['Buy', 'Sell']:
    # 筛选当前交易类型
    df_filtered = df_all[df_all['Deal'] == deal_type]
    # 按 Counterparty + Commodity 对 Total 求和，并透视
    df_pivot = df_filtered.groupby(['Counterparty', 'Commodity'])['Total'].sum() \
                           .unstack(fill_value=0) \
                           .rename_axis(None, axis=1) \
                           .reset_index()
    # 可选：重命名 Counterparty 列为更规范格式（如 "party1" → "party 1"）
    df_pivot['Counterparty'] = df_pivot['Counterparty'].str.replace(r'(\w+)(\d+)', r'\1 \2', regex=True)
    pivot_tables[deal_type] = df_pivot

# 步骤4：将 Buy/Sell 结果拼接为带标题的复合结构（模拟“分块”效果）
with pd.ExcelWriter(file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    # 写入 Sheet3：先写 Buy 块，再空一行，再写 Sell 块
    start_row = 0

    # 写 Buy 标题与数据
    pd.DataFrame([['Buy']]).to_excel(writer, sheet_name='Sheet3', index=False, header=False, startrow=start_row, startcol=0)
    pivot_tables['Buy'].to_excel(writer, sheet_name='Sheet3', index=False, startrow=start_row + 2, startcol=0)

    # 写 Sell 块（间隔两行）
    start_row += len(pivot_tables['Buy']) + 4
    pd.DataFrame([['Sell']]).to_excel(writer, sheet_name='Sheet3', index=False, header=False, startrow=start_row, startcol=0)
    pivot_tables['Sell'].to_excel(writer, sheet_name='Sheet3', index=False, startrow=start_row + 2, startcol=0)

✅ 关键注意事项：

Python对Excel操作详解 中文WORD版

本文档主要介绍如何通过python对office excel进行读写操作，使用了xlrd、xlwt和xlutils模块。另外还演示了如何通过Tcl tcom包对excel操作。感兴趣的朋友可以过来看看

• 使用 mode='a'（append 模式）和 if_sheet_exists='replace' 确保 Sheet3 被安全覆盖，避免重复写入；
• openpyxl 引擎必须提前安装：pip install openpyxl；
• 若原始数据中 Counterparty 值含空格或特殊字符，建议在 groupby 前做清洗（如 str.strip()）；
• 如需保留原始日期维度（如按日汇总），可在 groupby 中加入 'Date' 字段，并在透视前转换为字符串格式；
• 生产环境中建议添加异常处理（如 try/except）和日志记录，确保脚本失败时不影响上游流程。

该方案完全解耦于原有脚本——无需修改任何数据生成逻辑，仅通过一次读取+聚合+写入，即可实现多源数据的标准化汇总，是构建稳健 ETL 流程的理想实践。