Python 玩转 Excel 案例【第 3 期】

大家好！今天我们来学习一个实用的Python小案例：如何按省份分组统计Excel业绩数据并生成独立报表。

案例背景

在日常工作中，我们经常需要处理销售或业绩数据。比如公司有一张包含全国各区域销售记录的Excel表，领导要求按省份统计每个客户的交易次数和总金额，并生成独立的省份报表。手动筛选、计算、复制粘贴不仅耗时，还容易出错。

本期案例将带你用Python自动化完成这个任务：读取原始业绩表，按省份和客户分组统计，最后为每个省份生成一个独立的Excel统计文件。通过这个案例，你将掌握pandas最核心的数据处理技巧。

导入模块和配置

import logging
from pathlib import Path
from typing import Dict, List, Tuple
import pandas as pd

# 定义数据所在的文件夹
DATA_FOLDER = Path('数据表')

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

知识点讲解：

• • import pandas as pd: 导入pandas库，这是Python最强大的数据处理工具
• • from pathlib import Path: 导入Path类，用于优雅地处理文件路径
• • logging: Python标准日志模块，可以输出带时间戳的信息，便于追踪程序运行状态
• • DATA_FOLDER = Path('数据表'): 创建一个Path对象，指向”数据表”文件夹

Path对象的优点

1. 1. 跨平台兼容：自动处理Windows的\和Linux/mac的/，不用写判断逻辑
2. 2. 简洁直观：用/拼接路径，就像在文件管理器里点文件夹一样自然

   path = DATA_FOLDER / 'day3.xlsx'

创建类型别名

# 类型别名
ProvinceCustomerKey = Tuple[str, str]
AmountList = List[float]

知识点讲解：

类型别名的使用

1. ProvinceCustomerKey = Tuple[str, str]

ProvinceCustomerKey = Tuple[str, str]

• • 定义了一个类型别名 ProvinceCustomerKey
• • 表示这是一个包含两个字符串的元组
• • 用于表示(省份, 客户名称)的组合键

2. AmountList = List[float]

AmountList = List[float]

• • 定义了一个类型别名 AmountList
• • 表示这是一个元素类型为浮点数的列表
• • 用于存储金额数据列表

类型注解的写法

变量名: 类型注解 = 变量值

具体示例

变量名 : 变量类型 = 值                      
key : ProvinceCustomerKey = ("北京", "天宇科技")   

拆解说明

三种等价写法：

# 写法1：使用类型别名（代码中定义的）
key: ProvinceCustomerKey = ("北京", "天宇科技")

# 写法2：直接使用Tuple类型
key: Tuple[str, str] = ("北京", "天宇科技")

# 写法3：不写类型注解（纯Python）
key = ("北京", "天宇科技")

💡 小知识：关于Tuple的定义

• • 创建元组数据时用圆括号：data = ("北京", "天宇科技")
• • 类型注解时用首字母大写的Tuple：data: Tuple[str, str] = ("北京", "天宇科技")
• • 类似地：列表创建用[]，类型注解用List；字典创建用{}，类型注解用Dict

函数定义和参数

def process_excel_file(
    input_file: str = str(DATA_FOLDER/'day3.xlsx'),
    sheet_name: str = '业绩表',
    province_col: str = '省份',
    customer_col: str = '客户名称',
    amount_col: str = '金额',
    output_prefix: str = str(DATA_FOLDER/'统计结果')
) -> None:

知识点讲解：

• • 类型注解: input_file: str 表示参数应该是字符串类型
• • 默认参数: = str(DATA_FOLDER/'day3.xlsx') 给参数提供默认值，注意这里一定要进行str类型转换，因为函数参数类型注解是 str，但 DATA_FOLDER/’day3.xlsx’ 是 Path 对象，类型不匹配会报错
• • DATA_FOLDER/'day3.xlsx': Path对象使用/拼接路径，自动处理系统路径分隔符
• • -> None: 表示函数没有返回值

文件存在性检查

# 检查文件是否存在
input_path = Path(input_file)
if not input_path.exists():
    raise FileNotFoundError(f"文件不存在: {input_file}")

知识点讲解：

• • Path(input_file): 将字符串路径转换为Path对象
• • .exists(): Path对象的方法，检查文件或文件夹是否存在
• • raise FileNotFoundError: 如果文件不存在，主动抛出异常，终止程序执行
• • f"文件不存在: {input_file}": f-string格式化字符串，可以在字符串中直接嵌入变量

读取Excel文件

# 1. 读取数据
df = pd.read_excel(input_file, sheet_name=sheet_name)
logger.info(f"读取文件: {input_file}, 共{len(df)}行数据")

知识点讲解：

• • pd.read_excel(): pandas读取Excel文件的函数
• • 第一个参数：文件路径
• • sheet_name: 指定要读取的工作表名称
• • df: 约定俗成的变量名，代表DataFrame（pandas的核心数据结构，类似Excel表格）
• • len(df): 获取DataFrame的行数
• • logger.info(): 输出信息级别的日志

DataFrame获取行列数的方法

1. 获取行数

len(df)              # 最常用，返回行数
df.shape[0]          # shape返回(行数, 列数)，取第一个元素
df.shape             # 返回(行数, 列数)，如(30, 6)

2. 获取列数

len(df.columns)      # 列名的数量就是列数
df.shape[1]          # shape取第二个元素
df.columns           # 返回所有列名，如['省份', '客户名称', '日期'...]

3. 同时获取

rows, cols = df.shape  # 一次性获取行数和列数
print(f"共{rows}行，{cols}列")

记忆要点：

• • len(df) → 行数（最常用）
• • len(df.columns) → 列数
• • df.shape → (行数, 列数)

验证必需列

# 2. 验证必需列
required_cols = [province_col, customer_col, amount_col]
missing_cols = [col for col in required_cols if col not in df.columns]
if missing_cols:
    raise ValueError(f"必需的列不存在: {missing_cols}")

知识点讲解：

missing_cols = [col for col in required_cols if col not in df.columns]

等同于：

missing_cols = []
for col in required_cols:
    if col not in df.columns:
        missing_cols.append(col)

• • df.columns: 获取DataFrame的所有列名
• • col not in df.columns: 检查列名是否不存在于DataFrame中
• • 如果missing_cols不为空（有缺失列），抛出ValueError异常

核心数据处理（链式操作）

# 3. 使用pandas链式操作：分组 -> 聚合 -> 整理数据
summary = (
    df.groupby([province_col, customer_col])[amount_col]
    .agg(['count', 'sum'])
    .rename(columns={'count': '交易次数', 'sum': '总金额'})
    .reset_index()
)

知识点讲解：

这是一个pandas链式操作，我们来一步步拆解：

步骤1：分组df.groupby([province_col, customer_col])

• • 按”省份”和”客户名称”两列分组
• • 分组后，相同省份和相同客户的记录会被归为一组
• • ⚠️ 注意：分组后，这两列会变成索引（index）

步骤2：选择列[amount_col]

• • 只对”金额”列进行后续的聚合操作

步骤3：聚合计算.agg(['count', 'sum'])

• • 对每组数据进行两种计算：
• • 'count': 统计每组有多少条记录（交易次数）
• • 'sum': 计算每组金额的总和（总金额）

聚合后的数据结构：

这是groupby后agg(['count', 'sum'])的结果：

注意：此时”省份”和”客户名称”是多层索引(MultiIndex)，不是普通列。

步骤4：重命名列.rename(columns={'count': '交易次数', 'sum': '总金额'})

• • 将默认的列名改为更易懂的中文名

步骤5：重置索引.reset_index()

• • 将”省份”和”客户名称”从索引变回普通列
• • 让数据结构更规整，方便后续处理

.reset_index() 参数总结

通俗理解：

• • .reset_index()：把”标签”变成”内容”
• • .reset_index(drop=True)：扔掉”标签”，只要”内容”

💡 重要概念：关于索引
当使用groupby()后，分组列会变成索引（index）。reset_index()的作用就是把索引重新变回普通列。

agg() 方法知识点总结

1. 基本作用

agg() 是 aggregate（聚合） 的缩写，用于对分组后的数据进行多种统计计算。

2. 常用聚合函数

3. 三种使用方式

方式1：单个函数（返回Series）

df.groupby('省份')['金额'].agg('sum')
# 结果：每个省份的金额总和

方式2：多个函数（返回DataFrame）

df.groupby('省份')['金额'].agg(['sum', 'mean', 'count'])
# 结果：每个省份的总和、平均值、计数

4. 与直接计算的区别

# ❌ 只能得到一个指标
df.groupby('客户')['金额'].sum()     # 只有总金额
df.groupby('客户')['金额'].count()   # 只有交易次数

# ✅ 一次性得到多个指标
df.groupby('客户')['金额'].agg(['sum', 'count'])  # 同时得到两个

5. 记忆要点

按省份保存文件

# 4. 按省份分组保存为独立文件
for province, group in summary.groupby(province_col):
    output_file = f"{output_prefix}{province}.xlsx"

    # 准备输出数据：只保留需要的列
    output_data = group[[customer_col, '交易次数', '总金额']]

    # 保存为Excel文件
    output_data.to_excel(output_file, index=False, sheet_name=province)
    logger.info(f"已生成文件: {output_file}, 客户数: {len(output_data)}")

知识点讲解：

# 再次使用groupby，这次只按省份分组
for province, group in summary.groupby(province_col):
    # province: 省份名（如"北京"）
    # group: 该省份所有客户的DataFrame

💡 关于双中括号 [[]] 的重要知识点：

单中括号 [] – 选择单列，返回Series（一维数据）

single_col = group['客户名称']  # 结果只有一列

双中括号 [[]] – 选择多列，返回DataFrame（二维表格）

output_data = group[['客户名称', '交易次数', '总金额']]  # 结果有三列

为什么需要DataFrame？因为要保存到Excel的多列表格，必须用DataFrame格式。

• • f"{output_prefix}{province}.xlsx": 动态生成文件名，如”统计结果北京.xlsx”
• • to_excel()参数:
• • index=False: 不保存行索引到Excel
• • sheet_name=province: 工作表命名为省份名

输出汇总信息

# 5. 输出汇总信息
province_counts = summary[province_col].value_counts()
logger.info(f"处理完成！共{len(province_counts)}个省份")
for province, count in province_counts.items():
    logger.info(f"  - {province}: {count}个客户")

知识点讲解：

• • value_counts(): pandas方法，统计每个唯一值出现的次数
• • 返回结果是一个Series，索引是省份名，值是对应的客户数量
• • .items(): 遍历Series的键值对

异常处理和主程序入口

except Exception as e:
    logger.error(f"处理失败: {e}")
    raise


if __name__ == "__main__":
    """
    使用示例
    """
    print("=" * 50)
    print("Excel业绩表处理程序")
    print("=" * 50)

    try:
        # 使用默认参数运行
        process_excel_file()

    except Exception as e:
        logger.error(f"程序执行失败: {e}")

知识点讲解：

• • try...except: 异常处理机制，捕获可能发生的错误
• • Exception as e: 捕获所有异常，并将异常信息赋值给变量e
• • raise: 重新抛出异常，让调用者也知道发生了错误，此时程序会停止执行
• • if __name__ == "__main__":: Python特殊语法，表示当直接运行此文件时才执行下面的代码（如果是被导入则不执行）

运行效果示例

原始数据（day3.xlsx）

控制台输出

==================================================
Excel业绩表处理程序
==================================================
2026-03-08 10:30:25 - INFO - 读取文件: 数据表/day3.xlsx, 共30行数据
2026-03-08 10:30:25 - INFO - 数据分组完成，共24个客户
2026-03-08 10:30:25 - INFO - 已生成文件: 数据表/统计结果北京.xlsx, 客户数: 6
2026-03-08 10:30:25 - INFO - 已生成文件: 数据表/统计结果上海.xlsx, 客户数: 5
2026-03-08 10:30:25 - INFO - 已生成文件: 数据表/统计结果广东.xlsx, 客户数: 3
2026-03-08 10:30:25 - INFO - 已生成文件: 数据表/统计结果深圳.xlsx, 客户数: 3
2026-03-08 10:30:25 - INFO - 已生成文件: 数据表/统计结果浙江.xlsx, 客户数: 4
2026-03-08 10:30:25 - INFO - 已生成文件: 数据表/统计结果江苏.xlsx, 客户数: 3
2026-03-08 10:30:25 - INFO - 处理完成！共6个省份
2026-03-08 10:30:25 - INFO -   - 北京: 6个客户
2026-03-08 10:30:25 - INFO -   - 上海: 5个客户
2026-03-08 10:30:25 - INFO -   - 浙江: 4个客户
2026-03-08 10:30:25 - INFO -   - 广东: 3个客户
2026-03-08 10:30:25 - INFO -   - 江苏: 3个客户
2026-03-08 10:30:25 - INFO -   - 深圳: 3个客户

生成的文件：统计结果北京.xlsx

总结

这个程序演示了pandas处理Excel数据的完整流程：

1. 1. 读取数据：从Excel文件加载到DataFrame
2. 2. 数据验证：检查必要列是否存在
3. 3. 数据聚合：使用groupby按省份和客户分组统计
4. 4. 数据拆分：按省份拆分成多个文件
5. 5. 结果输出：生成独立的Excel文件并打印统计信息

通过这个例子，你可以掌握pandas中最常用的数据处理技巧，为后续更复杂的数据分析任务打下基础。

📦 资源获取提示

关注「码农自习室」，后台回复关键词 PythonExcel案例，即可获取本文完整代码及所有示例数据集，动手实践批量处理Excel的技巧！

❤️ 支持我们

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