Python 玩转 Excel 案例【第 12 期】

📌 案例说明

原始数据表（合同台账）：

这是企业的合同台账数据，记录了每份合同的编号、名称、客户、负责人、金额、签订日期和到期日期。我们需要定期监控合同到期情况，及时跟进即将到期的合同，避免因合同过期带来的业务风险。

目标任务：

在日常工作中，合同管理人员需要定期对合同到期情况进行预警分析：

• • 哪些合同已经过期？
• • 哪些合同即将到期（30天内）？
• • 每位负责人手头有多少份即将到期的合同？

本期案例将跟大家学习如何用 Python 自动完成合同到期预警分析，生成包含三个工作表的预警报告：

核心操作：日期状态判断 + 分组统计 + 多表导出

• • 关键函数：pd.read_excel() + datetime + groupby()
• • 核心逻辑：根据当前日期判断合同状态，按负责人统计即将到期的合同数量

📜 完整代码

"""合同到期预警分析自动判断合同状态，生成预警报告"""import pandas as pdfrom datetime import datetime, timedelta# 读取合同数据df = pd.read_excel("合同台账.xlsx", parse_dates=["到期日期"])# 获取今天的日期today = datetime.now().date()# 计算到期状态def get_expiry_status(expiry_date):    if expiry_date.date() < today:        return "已过期"    elif expiry_date.date() <= today + timedelta(days=30):        return "即将到期"    else:        return "正常"df["到期状态"] = df["到期日期"].apply(get_expiry_status)# 统计各状态数量status_count = df["到期状态"].value_counts()print("到期状态统计：")print(status_count)# 筛选即将到期的合同expiring_soon = df[df["到期状态"] == "即将到期"]# 按负责人分组统计grouped = expiring_soon.groupby("负责人")["合同编号"].count().reset_index()grouped.columns = ["负责人", "即将到期数量"]# 保存结果with pd.ExcelWriter("合同到期预警.xlsx") as writer:    # 全部合同（带状态列）    df.to_excel(writer, sheet_name="全部合同", index=False)    # 即将到期合同    expiring_soon.to_excel(writer, sheet_name="即将到期", index=False)    # 负责人统计    grouped.to_excel(writer, sheet_name="负责人统计", index=False)print(f"\n✅ 预警报告已生成")print(f"   - 已过期：{status_count.get('已过期', 0)} 份")print(f"   - 即将到期：{status_count.get('即将到期', 0)} 份")

第一步：导入所需库

import pandas as pdfrom datetime import datetime, timedelta

代码解释：

• • import pandas as pd导入 pandas 库，用于读取 Excel、数据处理和导出结果。
• • from datetime import datetime, timedelta导入日期时间模块：
• • datetime：获取当前日期、处理日期对象
• • timedelta：进行日期加减运算（如计算30天后）

第二步：读取合同数据

df = pd.read_excel("合同台账.xlsx", parse_dates=["到期日期"])

代码解释：

• • pd.read_excel("合同台账.xlsx", parse_dates=["到期日期"])
• • 第一个参数是 Excel 文件名
• • parse_dates=["到期日期"] 是关键参数，指定将“到期日期”列自动解析为日期格式（datetime64）
• • 为什么需要 parse_dates？如果不加这个参数，Excel 中的日期会被读成字符串或数字，无法直接进行日期比较运算。加上后，该列就变成了真正的日期类型，可以使用 .date()、<、> 等日期相关操作。

💡 小贴士：parse_dates 可以传入列表，一次处理多个日期列，如 parse_dates=["签订日期", "到期日期"]。

第三步：获取今天的日期

today = datetime.now().date()

代码解释：

• • datetime.now()获取当前的日期和时间（精确到秒），例如 2026-03-25 21:30:00
• • .date()提取日期部分，去掉具体时间，得到 2026-03-25 这样的纯日期对象
• • 为什么需要 .date()？因为后面要跟 expiry_date.date() 比较，两者都是纯日期格式才能准确判断。如果不加 .date()，today 会包含时间信息，可能导致比较结果不准确。

第四步：定义到期状态判断函数

def get_expiry_status(expiry_date):    if expiry_date.date() < today:        return "已过期"    elif expiry_date.date() <= today + timedelta(days=30):        return "即将到期"    else:        return "正常"

代码解释：

这个函数接收一个到期日期，根据与今天的对比返回状态：

• • 条件1：expiry_date.date() < today如果到期日期在今天之前，返回 “已过期”
• • 条件2：expiry_date.date() <= today + timedelta(days=30)如果到期日期在今天到30天之内（含今天），返回 “即将到期”
• • 其他情况：如果到期日期在30天以后，返回 “正常”

关键语法：

• • expiry_date.date()将 pandas 的 Timestamp 对象转为 Python 的 date 对象，便于比较
• • today + timedelta(days=30)计算30天后的日期，timedelta 表示时间差。常用的参数包括 days（天数）、weeks（周数）、hours（小时）、minutes（分钟）、seconds（秒数）等，可以根据需要灵活组合使用。

示例（假设今天是 2026-03-25）：

第五步：应用函数添加状态列

df["到期状态"] = df["到期日期"].apply(get_expiry_status)

代码解释：

• • .apply(get_expiry_status)将 get_expiry_status 函数应用到“到期日期”列的每一个值上，返回对应的状态，并赋值给新的“到期状态”列。
• • 效果：原始 DataFrame 新增一列，每行都标记了该合同的到期状态。

执行过程示意：

第六步：统计各状态数量

status_count = df["到期状态"].value_counts()print("到期状态统计：")print(status_count)

代码解释：

• • .value_counts()统计“到期状态”列中每个不同值出现的次数，返回一个 Series，索引是状态名称，值是数量。
• • 输出示例：

到期状态统计：即将到期    38已过期      32正常        30Name: 到期状态, dtype: int64

💡 小贴士：value_counts() 默认按数量降序排列，方便直观看到最多的状态。

好的，帮你把这段内容整合成更清晰的结构：

第七步：筛选即将到期的合同

expiring_soon = df[df["到期状态"] == "即将到期"]

代码解释：

• • 布尔索引：df["到期状态"] == "即将到期" 返回一个布尔 Series，True 表示该行状态是“即将到期”
• • 筛选：用这个布尔 Series 作为索引，选出所有符合条件的行

三种筛选方式对比

在 pandas 中，筛选数据有多种写法，常见的有以下三种：

等价写法示例：

# 方法1：直接布尔索引（最简洁）expiring_soon = df[df["到期状态"] == "即将到期"]# 方法2：loc + 布尔索引（效果相同）expiring_soon = df.loc[df["到期状态"] == "即将到期"]# 方法3：query() 方法（字符串条件）expiring_soon = df.query("到期状态 == '即将到期'")

补充说明：

• • 直接布尔索引：最简洁，也是日常使用最多的方式
• • loc + 布尔索引：loc 主要用于标签索引，但也支持布尔数组，适合在筛选的同时指定要保留的列
• • query() 方法：当条件比较复杂（如多个条件组合）时，query() 写起来更直观，字符串格式也更易读

💡 小贴士：三种方式最终效果相同，选择哪种主要看个人习惯和具体场景。对于简单的单条件筛选，直接用 df[df["列"] == 值] 就足够了。

第八步：按负责人分组统计

grouped = expiring_soon.groupby("负责人")["合同编号"].count().reset_index()grouped.columns = ["负责人", "即将到期数量"]

代码解释：

这是一个典型的“分组聚合”操作，分步拆解：

1. 1. expiring_soon.groupby("负责人")按“负责人”列分组，相同负责人的合同归为一组
2. 2. ["合同编号"]选择“合同编号”列进行操作
3. 3. .count()统计每组中合同编号的数量（即每个负责人有多少份即将到期的合同）
4. 4. .reset_index()将结果从 Series 转为 DataFrame，负责人从索引变成普通列
5. 5. 重命名列grouped.columns = ["负责人", "即将到期数量"] 让列名更清晰

分组过程示意（假设数据）：

↓ 分组统计后

第九步：保存结果到 Excel（多工作表）

with pd.ExcelWriter("合同到期预警.xlsx") as writer:    # 全部合同（带状态列）    df.to_excel(writer, sheet_name="全部合同", index=False)    # 即将到期合同    expiring_soon.to_excel(writer, sheet_name="即将到期", index=False)    # 负责人统计    grouped.to_excel(writer, sheet_name="负责人统计", index=False)

代码解释：

• • pd.ExcelWriter("合同到期预警.xlsx")创建一个 Excel 写入器对象，用于将多个 DataFrame 写入同一个 Excel 文件的多个工作表。
• • with ... as writer:使用 with 语句管理资源，确保文件自动关闭，避免内存泄漏。
• • .to_excel(writer, sheet_name="...", index=False)
• • sheet_name：指定工作表名称
• • index=False：不写入行索引，只写数据

生成的 Excel 文件结构：

合同到期预警.xlsx├── 全部合同    (所有合同，带状态列)├── 即将到期    (只包含即将到期的合同)└── 负责人统计  (每人即将到期的合同数量)

第十步：输出统计报告

print(f"\n✅ 预警报告已生成")print(f"   - 已过期：{status_count.get('已过期', 0)} 份")print(f"   - 即将到期：{status_count.get('即将到期', 0)} 份")

代码解释：

• • .get('已过期', 0)从 status_count 字典中获取“已过期”的数量，如果该状态不存在（比如没有已过期的合同），返回默认值 0，避免报错。
• • f-string 格式化f"..." 允许在字符串中直接嵌入变量，用 {} 包裹。

输出示例：

✅ 预警报告已生成   - 已过期：32 份   - 即将到期：38 份

运行结果预览

生成的 合同到期预警.xlsx 包含三个工作表：

工作表1：全部合同（部分展示）

工作表2：即将到期（部分展示）

工作表3：负责人统计

📚 本期核心知识点

📍 知识点 1：pd.read_excel() 的 parse_dates 参数

df = pd.read_excel("文件.xlsx", parse_dates=["日期列"])

指定将哪些列解析为日期类型，便于后续进行日期运算和比较。

📍 知识点 2：datetime.now().date() 获取当前日期

from datetime import datetimetoday = datetime.now().date()  # 返回纯日期对象，不含时间

📍 知识点 3：timedelta 日期加减

from datetime import timedeltafuture_date = today + timedelta(days=30)   # 30天后past_date = today - timedelta(days=7)      # 7天前

📍 知识点 4：.apply() 逐行应用函数

df["新列"] = df["旧列"].apply(函数名)

将自定义函数应用到每一行，返回结果作为新列。

📍 知识点 5：布尔索引筛选数据

filtered_df = df[df["列名"] == "条件"]

根据条件筛选行，返回满足条件的 DataFrame。

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

grouped = df.groupby("分组列")["统计列"].count().reset_index()

• • groupby("分组列")：按指定列分组
• • ["统计列"]：选择要统计的列
• • .count()：计数聚合
• • .reset_index()：将分组键从索引转为普通列

📍 知识点 7：pd.ExcelWriter 多工作表导出

with pd.ExcelWriter("输出文件.xlsx") as writer:    df1.to_excel(writer, sheet_name="表1", index=False)    df2.to_excel(writer, sheet_name="表2", index=False)

将多个 DataFrame 写入同一个 Excel 文件的不同工作表。

📍 知识点 8：value_counts() 统计频次

counts = df["列名"].value_counts()

统计列中每个唯一值出现的次数，常用于快速了解数据分布。

🔄 本案例核心流程

① 读取数据 → ② 获取当前日期 → ③ 定义状态函数 → ④ 应用函数添加状态列 → ⑤ 统计状态分布 → ⑥ 筛选即将到期 → ⑦ 分组统计负责人 → ⑧ 导出多工作表 → ⑨ 输出统计报告

读取合同台账 (pd.read_excel)        ↓获取当前日期 (datetime.now().date())        ↓定义状态判断函数 (get_expiry_status)        ↓添加到期状态列 (df.apply())        ↓统计各状态数量 (value_counts())        ↓筛选即将到期合同 (df[条件])        ↓按负责人分组统计 (groupby + count)        ↓导出到 Excel (pd.ExcelWriter)        ↓输出统计报告 (print)

📝 总结

通过本期案例，我们学习了：

1. 1. 日期处理：用 parse_dates 读取日期列，用 datetime 和 timedelta 进行日期比较和计算
2. 2. 状态判断：用自定义函数 + apply() 为数据添加状态标签
3. 3. 数据筛选：用布尔索引筛选特定状态的合同
4. 4. 分组统计：用 groupby() 按负责人统计即将到期的合同数量
5. 5. 多表导出：用 pd.ExcelWriter 将多个 DataFrame 输出到同一个 Excel 文件的不同工作表

📦 资源获取提示

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

❤️ 支持我们

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