Python 玩转 Excel 案例【第 6 期】

今天我们来解决一个绩效考核常见问题：如何快速找出每个人哪些月份没有达标？

一、案例背景

年底复盘需要找出每个人未达标的月份（1-12月中没有记录的那些月）。

Excel数据结构：

• • 第1行：标题”达标情况统计”（需跳过）
• • 第2行：列名”姓名”和”月份”
• • 第3行起：具体数据

手动做法：对着12个月份挨个比对，20个人就要想20遍。

今天这个脚本：双击运行，1秒出结果！

二、整体功能预览

这个脚本主要完成以下任务：

• • ✅ 读取Excel文件的指定工作表
• • ✅ 跳过标题行，正确识别表头
• • ✅ 数据清洗（去空、去空格）
• • ✅ 计算每个人未达标的月份
• • ✅ 将结果保存到新的Excel文件

三、准备工作

安装必要的库：

pip install pandas openpyxl

• • pandas：核心数据处理库
• • openpyxl：Excel文件读写引擎

四、代码整体结构

from pathlib import Path
import pandas as pd
import logging

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

# 核心函数
def find_missing_months(input_file: str) -> None: ...

if __name__ == "__main__":
    find_missing_months('Chapter-6.xlsx')

五、核心函数详解

1. 函数定义与文件检查

def find_missing_months(input_file: str) -> None:
    """找出每个人未达标的月份"""

    # 检查文件
    if not Path(input_file).exists():
        logger.error(f"文件不存在: {input_file}")
        return

参数说明：

• • input_file：Excel文件路径（字符串类型）

知识点：

• • Path(input_file).exists()：检查文件是否存在
• • 提前返回：如果文件不存在，直接结束函数，不执行后续代码

2. 读取数据（关键！）

# 1. 读取数据（跳过第一行，因为那是标题"达标情况统计"）
df = pd.read_excel(
    input_file,
    sheet_name='达标表',
    header=1,  # 关键：跳过第一行，把第二行作为表头
)

📝 header 参数详解

我们的Excel文件结构：

第1行: 达标情况统计  (空)     ← 要跳过
第2行: 姓名        月份     ← 作为表头
第3行: 张三        01月     ← 数据开始

header=1 的含义：

• • header 参数指定哪一行作为列名
• • 行号从0开始计数
• • header=1 表示把第2行作为表头

两种写法对比：

两种方法效果完全一样，选择你喜欢的风格即可。

3. 数据预览（调试用）

print("原始数据预览：")
print(df.head())
print(f"列名：{df.columns.tolist()}")
print("-" * 40)

作用：

• • df.head()：显示前5行数据，确认读取是否正确
• • df.columns.tolist()：显示列名列表，确保表头识别正确
• • 这些打印信息帮助我们在运行时代码看到数据状态

4. 数据清洗

# 2. 数据清洗
df = df.dropna()
df['姓名'] = df['姓名'].astype(str).str.strip()
df['月份'] = df['月份'].astype(str).str.strip()

if df.empty:
    logger.warning("文件中没有有效数据")
    return

print(f"清洗后数据：共 {len(df)} 条，{df['姓名'].nunique()} 人")
print("-" * 40)

📝 dropna() 和 strip() 的区别

如果没有 strip()：

• • “张三” 和 ”   张三   ” 会被视为不同的人
• • “01月” 和 “01月   ” 无法正确匹配

如果没有 dropna()：

• • NaN 值在 astype(str) 时会变成 “nan”，造成错误数据

所以两者配合使用，才能得到干净的数据！

📝 unique()  vs  nunique()

在打印语句中，我们用到了 nunique()：

print(f"清洗后数据：共 {len(df)} 条，{df['姓名'].nunique()} 人")

这两个函数都是处理唯一值的，但返回值完全不同：

快速记忆口诀：

• • unique() = Unique values → 返回数组（看到所有值）
• • nunique() = Number of unique → 返回数字（只统计个数）

简单记：

• • 要看内容用 unique()
• • 要数个数用 nunique()

5. 创建所有月份的集合

# 3. 创建所有月份的集合
all_months = [f"{i:02d}月" for i in range(1, 13)]

📝 列表推导式详解

[f"{i:02d}月" for i in range(1, 13)]
#  ③表达式       ①循环

执行顺序：①循环 → ③表达式

分解步骤：

1. 1. for i in range(1, 13)：i 从 1 到 12
2. 2. f"{i:02d}月"：格式化字符串，02d 表示两位数字，不足补0
• • i=1 → “01月”
• • i=2 → “02月”
• • …
• • i=12 → “12月”

结果：['01月', '02月', '03月', ..., '12月']

6. 核心pandas操作（重点！）

# 4. 核心pandas操作：分组计算未达标月份
result = (
    df.groupby('姓名')['月份']           # 第1步：按姓名分组，取月份列
    .agg(lambda x: sorted(set(all_months) - set(x)))  # 第2步：聚合计算未达标月份
    .reset_index()                       # 第3步：索引转回普通列
    .rename(columns={'月份': '未达标月份'})  # 第4步：重命名列名
)

🔍 第1步：df.groupby('姓名')['月份']

# 作用：按姓名分组，只关注月份列
# 结果类型：SeriesGroupBy对象（可遍历的分组对象）

# 执行效果：
张三 → ['01月', '02月', '03月']
李四 → ['01月', '04月', '07月']
王五 → ['05月', '06月']
赵六 → ['01月', '02月', ... '12月']  # 12个月全有

📝 GroupBy 对象是什么？

它不是新的DataFrame，也不是新的Series，而是一个”等待计算的中间对象”

# 你可以把它想象成一个"分组字典"的懒加载版本
grouped = df.groupby('姓名')

# 它有这些特性：
print(grouped.groups)  # 查看分组情况（字典形式）
# {'张三': [0, 1, 2], '李四': [3, 4, 5], ...}

# grouped.groups 得到的是索引的字典
# 键：分组的值（姓名）
# 值：该组在原始DataFrame中的行索引列表

# 只有当你明确要做什么计算时，才生成值：
result1 = grouped.agg(list)     # 现在生成列表
result2 = grouped.count()        # 现在生成计数  
result3 = grouped.sum()          # 现在生成总和

不同阶段的数据类型对比：

关键理解：GroupBy对象是”计算前的状态“，只有调用了agg、count、sum等方法后，才会真正计算出结果！

🔍 第2步：.agg(lambda x: sorted(set(all_months) - set(x)))

# 作用：对每组的月份进行聚合计算
# agg = aggregate（聚合）：把一组数据压缩成一个结果

# 分解lambda函数：
lambda x:                     # x 是每个人的月份列表
    set(all_months) -         # 所有月份的集合
    set(x)                    # 减去已达标月份的集合
    sorted(...)                # 排序后转回列表

# 执行效果（以张三为例）：
输入 x = ['01月', '02月', '03月']
set(x) = {'01月', '02月', '03月'}
set(all_months) - set(x) = {'04月', '05月', '06月', '07月', '08月', '09月', '10月', '11月', '12月'}
sorted(...) = ['04月', '05月', '06月', '07月', '08月', '09月', '10月', '11月', '12月']

💡 为什么要用集合？

• • 自动去重：一个人同一个月可能有多次打卡
• • 集合减法：set(A) - set(B) 找出A有B没有的元素
• • 速度快：集合运算是数学运算，比循环快

📝 agg 最常用方法举例

import pandas as pd

# 简单数据
df = pd.DataFrame({
    '姓名': ['张三', '张三', '李四', '李四', '王五'],
    '分数': [85, 90, 78, 92, 88]
})

# 1. 单个聚合
df.groupby('姓名')['分数'].agg('sum')    # 求和
df.groupby('姓名')['分数'].agg('mean')   # 平均
df.groupby('姓名')['分数'].agg('count')  # 计数
df.groupby('姓名')['分数'].agg('max')    # 最大
df.groupby('姓名')['分数'].agg('min')    # 最小

# 2. 多个聚合
df.groupby('姓名')['分数'].agg(['sum', 'mean', 'count'])

# 3. 自定义聚合
df.groupby('姓名')['分数'].agg(list)                    # 转列表
df.groupby('姓名')['分数'].agg(lambda x: x.max() - x.min())  # 极差

记住：agg 就是把一组数据（多行）压缩成你想要的一个结果（一行）！

🔍 第3步：.reset_index()

# 作用：把姓名从索引变成普通列

# 为什么需要？
# 聚合后，姓名自动变成了索引：
# 姓名
# 张三    [04月, 05月, ...]    ← 姓名是索引
# 李四    [02月, 03月, ...]

# reset_index后：
#   姓名             月份
# 0  张三  [04月, 05月, 06月, ...]    ← 姓名变成普通列
# 1  李四  [02月, 03月, 05月, ...]

🔍 第4步：.rename(columns={'月份': '未达标月份'})

# 作用：把列名从'月份'改为'未达标月份'，更符合语义
# 因为现在这一列存放的是"未达标月份"列表

# 重命名后：
#   姓名                                      未达标月份
# 0  张三  [04月, 05月, 06月, 07月, 08月, 09月, 10月, 11月, 12月]
# 1  李四  [02月, 03月, 05月, 06月, 08月, 09月, 10月, 11月, 12月]

📊 数据流转过程

📊 数据流转过程（优化版）

🔄 操作流程

原始数据 ── groupby ──> 分组 ──> agg(集合减法) ──> 聚合结果 ──> filter ──> 最终输出

这行代码虽然短，但包含了pandas最核心的功能：分组、聚合、索引操作、链式调用！

7. 过滤掉没有未达标月份的人

# 5. 过滤掉没有未达标月份的人
result = result[result['未达标月份'].apply(len) > 0]

if result.empty:
    logger.info("所有人均达标，没有未达标记录")
    return

📝 apply(len) 的作用

# 1. 先看看 apply(len) 做了什么
print(result['未达标月份'].apply(len))

输出：

0     9
1     9
2    10
3     0
Name: 未达标月份, dtype: int64

apply(len) 把每个列表转换成了它的长度：

• • [04月, 05月, ...] → 9
• • [] → 0

# 2. 现在可以比较了
mask = result['未达标月份'].apply(len) > 0
print(mask)

输出：

0     True
1     True
2     True
3    False
Name: 未达标月份, dtype: bool

# 3. 过滤
result = result[mask]

赵六被过滤掉了，因为他全达标了（空列表）。

8. 将月份列表合并为字符串

# 6. 将月份列表合并为字符串
result['未达标月份'] = result['未达标月份'].apply(lambda x: '、'.join(x))

作用：把 ['04月', '05月', '06月'] 变成 "04月、05月、06月"，更方便阅读。

'、'.join(x)：

• • '、' 是分隔符
• • join(x) 把列表x中的所有元素用这个分隔符连接起来

9. 保存结果

# 7. 保存结果
output_file = Path(input_file).stem + '_结果.xlsx'
result.to_excel(output_file, index=False)

logger.info(f"处理完成！共 {len(result)} 人有未达标月份")
logger.info(f"结果保存至: {output_file}")

📝 Path(input_file).stem 详解

为什么用stem？ 为了生成新文件名：原文件名 + “_结果.xlsx”

六、运行效果展示

输入文件内容

达标表：

运行程序

python 数据处理.py

控制台输出

原始数据预览：
   姓名 月份
0  张三  01月
1  张三  02月
2  张三  03月
3  李四  01月
4  李四  04月
列名：['姓名', '月份']
----------------------------------------
清洗后数据：共 20 条，4 人
----------------------------------------
处理完成！共 3 人有未达标月份
结果保存至: Chapter-6_结果.xlsx

输出结果

Chapter-6_结果.xlsx：

赵六没有出现在结果中，因为他12个月全部达标！

七、知识点总结

📦 资源获取提示

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

❤️ 支持我们

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