Python 玩转 Excel 案例【第 15 期】

📌 案例说明

原始数据表：

这是包含多笔订单的原始对账单，同一个产品（名称+单价相同）可能有多条分散的记录。

目标任务：

在日常对账工作中，我们经常需要将名称和单价相同的订单合并成一条记录，数量相加，其他信息（订单号、日期等）保留第一条即可。

本期案例将跟大家一起学习如何用 Python 自动完成对账单合并，让数据更清爽、汇总更高效：

核心操作：分组聚合 + 列级聚合规则

• • 关键函数：groupby()、agg()
• • 核心逻辑：按指定列分组，数量求和，其他列取首行

📜 完整代码

"""  
对账单合并工具  
功能：按名称+单价合并订单，数量求和，其他信息取首行  
"""
import pandas as pd

# ========== 参数配置 ==========
file_path = "对账单.xlsx"
output_sheet = "合并结果"

# ========== 读取数据 ==========
df = pd.read_excel(file_path, header=0)

print(f"📊 原始数据：{len(df)} 行")
print(f"   列名：{df.columns.tolist()}")

# ========== 配置分组列和聚合规则 ==========
# 分组列：名称和单价相同的行合并
group_cols = ["名称", "单价"]

# 数量列：需要求和
qty_col = "数量"

# 其他列：取第一行
other_cols = [col for col in df.columns if col not in group_cols + [qty_col]]

# ========== 核心：分组聚合 ==========
agg_dict = {qty_col: "sum"}
for col in other_cols:
    agg_dict[col] = "first"

result = df.groupby(group_cols, as_index=False).agg(agg_dict)

print(f"\n✅ 合并后：{len(result)} 行")

# ========== 保存结果（写入新Sheet，不覆盖原数据）==========
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    result.to_excel(writer, sheet_name=output_sheet, index=False)

print(f"\n💾 结果已保存到：{file_path} 的 [{output_sheet}] 工作表")

第一步：导入所需库

import pandas as pd

代码解释：

• • import pandas as pd
  导入 pandas 库，这是 Python 数据处理的核心工具。它提供了 read_excel() 读取 Excel 文件、DataFrame 数据结构、groupby() 分组聚合等强大功能。

第二步：参数配置

file_path = "对账单.xlsx"
output_sheet = "合并结果"

代码解释：

• • file_path
  源文件路径，程序从这里读取原始对账单。可以根据实际情况修改文件名或路径。
• • output_sheet
  输出工作表名称，合并后的结果将保存到该工作表中。为了避免覆盖原数据，我们会将结果写入新工作表。

💡 小贴士：将参数放在代码开头，方便后续修改和维护，也便于他人理解和使用你的代码。

第三步：读取数据

df = pd.read_excel(file_path, header=0)

print(f"📊 原始数据：{len(df)} 行")
print(f"   列名：{df.columns.tolist()}")

代码解释：

• • pd.read_excel(file_path, header=0)
  读取 Excel 文件并返回一个 DataFrame 对象。header=0 表示第一行作为列名（这是默认值，可以省略）。
• • len(df)
  获取 DataFrame 的行数，即订单记录总数。本例中原始数据有 7 条记录。
• • df.columns.tolist()
  将列名转换为列表并打印，方便确认数据结构和列名是否正确。

输出示例：

📊 原始数据：7 行
   列名：['名称', '数量', '单价', '订单号', '日期']

💡 这里再复习下获取行数和列数的方法：

• • len(df)：获取总行数
• • df.shape[0]：也是获取行数（shape 返回 (行数, 列数) 元组）
• • df.shape[1]：获取总列数
• • len(df.columns)：也是获取列数

第四步：配置分组列和聚合规则

# 分组列：名称和单价相同的行合并
group_cols = ["名称", "单价"]

# 数量列：需要求和
qty_col = "数量"

# 其他列：取第一行
other_cols = [col for col in df.columns if col not in group_cols + [qty_col]]

代码详解

确定分组依据

group_cols = ["名称", "单价"]

• • 我们要求”名称和单价相同”的订单合并，所以这两列作为分组依据。

确定需要求和的列

qty_col = "数量"

• • 数量列需要累加，合并后显示总数量。

动态获取其他列

other_cols = [col for col in df.columns if col not in group_cols + [qty_col]]

• • group_cols + [qty_col]：
  将 ["名称", "单价"] 和 ["数量"] 合并为 ["名称", "单价", "数量"]
• • for col in df.columns：
  遍历所有列名
• • if col not in [...]：
  只保留不在上述列表中的列
• • 最终 other_cols = ["订单号", "日期"]

为什么这样设计？
这样写的好处是自动适配列的变化。即使后续 Excel 增加了”备注”、”经手人”等新列，代码无需修改也能正确处理——新列会自动归入”其他列”，取第一行值。

💡 小贴士：为什么 [qty_col] 要加方括号，而 group_cols 不用？

因为 group_cols 本身已经是列表 ["名称", "单价"]，而 qty_col 是字符串 "数量"。列表只能和列表拼接，所以需要把字符串 qty_col 用 [] 包起来变成列表 ["数量"]，才能用 + 运算符进行拼接。

简单记忆：列表 + 列表 = 合并后的列表，字符串不能直接和列表相加。

第五步：核心分组聚合

agg_dict = {qty_col: "sum"}
for col in other_cols:
    agg_dict[col] = "first"

result = df.groupby(group_cols, as_index=False).agg(agg_dict)

5.1 agg_dict 的构建

agg_dict = {qty_col: "sum"}        # 初始：{"数量": "sum"}
for col in other_cols:              # other_cols = ["订单号", "日期"]
    agg_dict[col] = "first"         # 依次添加：{"数量": "sum", "订单号": "first", "日期": "first"}

最终 agg_dict = {"数量": "sum", "订单号": "first", "日期": "first"}

含义：

• • "数量": "sum" → 数量列求和
• • "订单号": "first" → 订单号取该组第一条记录的值
• • "日期": "first" → 日期取该组第一条记录的值

5.2 groupby() 分组

df.groupby(group_cols, as_index=False)

• • group_cols = ["名称", "单价"]：按名称和单价分组
• • as_index=False：分组列不作为行索引，保持为普通列（方便后续查看和使用）

5.3 agg() 聚合

.agg(agg_dict)

对每组应用聚合规则：

• • 数量列 → 求和
• • 订单号列 → 取第一条
• • 日期列 → 取第一条

执行过程示意

原始数据（ATX-FN528 这一组）：

应用聚合规则：

• • 数量：2+3+1+2 = 8
• • 订单号：取第一条 = ORD001
• • 日期：取第一条 = 2025-03-01

合并结果：

第六步：保存结果

with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    result.to_excel(writer, sheet_name=output_sheet, index=False)

print(f"\n💾 结果已保存到：{file_path} 的 [{output_sheet}] 工作表")

代码详解

pd.ExcelWriter() 参数说明

result.to_excel() 参数说明

输出示例：

💾 结果已保存到：对账单.xlsx 的 [合并结果] 工作表

⚠️ 注意：这里使用追加模式（mode="a"），原始数据工作表 Sheet1 会被保留，新增 合并结果 工作表。这样既保留了原始数据，又生成了汇总结果，方便对比验证。

📚 本期核心知识点

📍 知识点 1：groupby() 分组聚合

result = df.groupby(group_cols, as_index=False).agg(agg_dict)

• • 分组列：["名称", "单价"]，相同的行归为一组
• • as_index=False：分组列保持为普通列，不变成行索引
• • agg()：对每组应用聚合规则

📍 知识点 2：agg() 聚合函数详解

agg_dict = {"数量": "sum", "订单号": "first", "日期": "first"}

agg() 接受一个字典，键是列名，值是该列的聚合方式：

📍 知识点 3：动态列筛选

other_cols = [col for col in df.columns if col not in group_cols + [qty_col]]

• • 使用列表推导式自动识别”其他列”
• • 无需手动列举列名，代码更健壮
• • 即使 Excel 增加新列，代码也无需修改

📍 知识点 4：ExcelWriter 追加模式

with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    result.to_excel(writer, sheet_name=output_sheet, index=False)

• • mode="a"：追加模式，不覆盖原文件的其他工作表
• • if_sheet_exists="replace"：同名工作表存在时自动替换
• • with 语句：自动管理资源，写入完成后自动关闭文件

🔄 本案例核心流程

① 读取数据 → ② 配置分组列 → ③ 构建聚合字典 → ④ 分组聚合 → ⑤ 保存结果

读取原始对账单 (pd.read_excel)
        ↓
确定分组列：["名称", "单价"]
确定数量列："数量" → 求和
确定其他列：动态识别 → 取首行
        ↓
构建聚合字典 {"数量": "sum", "订单号": "first", "日期": "first"}
        ↓
分组聚合 (groupby + agg)
        ↓
保存到新工作表 (ExcelWriter + to_excel)

📦 资源获取提示

关注「码农自习室」，后台回复关键词 PythonExcel案例，即可获取本文完整代码及配套练习数据集，一起动手掌握高效数据操作的核心技巧！

❤️ 支持我们

如果本文对你有帮助，欢迎点赞 + 关注 + 点亮小红心，你的支持就是我们持续创作的最大动力～