本文介绍如何使用 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)
✅ 关键注意事项：
- 使用 mode='a'（append 模式）和 if_sheet_exists='replace' 确保 Sheet3 被安全覆盖，避免重复写入；
- openpyxl 引擎必须提前安装：pip install openpyxl；
- 若原始数据中 Counterparty 值含空格或特殊字符，建议在 groupby 前做清洗（如 str.strip()）；
- 如需保留原始日期维度（如按日汇总），可在 groupby 中加入 'Date' 字段，并在透视前转换为字符串格式；
- 生产环境中建议添加异常处理（如 try/except）和日志记录，确保脚本失败时不影响上游流程。
该方案完全解耦于原有脚本——无需修改任何数据生成逻辑，仅通过一次读取+聚合+写入，即可实现多源数据的标准化汇总，是构建稳健 ETL 流程的理想实践。
