如何使用 Pandas 合并 CSV 与 Excel 文件并识别数据差异

本文介绍如何用 Python 的 Pandas 库读取结构不一致的 CSV 和 Excel 文件，基于共同键（如 id 和 date）智能对齐、合并，并计算数值差异及条件标记。

在日常的数据处理工作中，你是否也遇到过这样的烦恼？手头有两份数据，一份是CSV格式，另一份是Excel表格，它们来自不同的系统或部门，不仅字段名对不上，连日期格式都五花八门。直接打开比对？效率低下且容易出错。今天，我们就来聊聊如何用Python的Pandas库，优雅地解决这个“异构表格合并与差异识别”的经典问题。

这个场景非常典型：你需要将一份标准的CSV文件，与一份表头藏在第5行的Excel报表进行合并，不仅要按ID和日期对齐，还得算出数值差异，并打上“是否超差”的业务标签。听起来复杂？别担心，跟着下面的步骤走，你会发现一切都有章可循。

✅ 关键步骤解析

整个流程可以拆解为几个核心环节，每个环节都藏着一些需要留意的细节。

1. 统一列名与数据类型：这是合并的基石。CSV里的列叫`test date`和`values 1`，而Excel里对应的是`date`和`values 2`。更棘手的是，Excel的有效表头从第5行才开始，读取时需要用`skiprows=4`参数跳过前几行。
2. 日期标准化：格式不一致是数据合并的“头号杀手”。Excel中的`3/12/2024`需要与CSV中的`2024-03-12`精确匹配。解决方案是将它们都统一转换为`YYYY-MM-DD`的字符串格式，避免因日期对象解析差异导致匹配失败。
3. 多键合并：这里有个关键点。题目要求按`date`和`id`联合匹配，但如果你的数据中`id`本身具有唯一性，且每个`id`只对应一个日期，那么仅用`id`合并也能得到正确结果。然而，更严谨、更通用的做法是显式指定双键（`["date", "id"]`）进行合并，这能有效防止同一ID有多条不同日期记录时产生的数据错配。
4. 差异计算与逻辑标记：合并后，计算差值（`discrepancy`）水到渠成。对于“是否超差2”这样的业务判断列，建议使用`"yes"/"no"`这样的字符串，而不是`True/False`，这样在导出给业务人员查看时，可读性会强得多。

? 完整可运行代码（含健壮性优化）

理论说完了，是时候亮出代码了。下面的脚本不仅实现了核心功能，还融入了一些提升健壮性的优化。

import pandas as pd

# 1. 读取 CSV（自动推断日期列，后续统一处理）
df_csv = pd.read_csv("test.csv", parse_dates=["test date"], dayfirst=False)
df_csv = df_csv.rename(columns={"test date": "date", "values 1": "value_1"})

# 2. 读取 Excel（跳过前4行，获取真实表头）
df_excel = pd.read_excel("test.xlsx", skiprows=4, parse_dates=["date"], dayfirst=False)
df_excel = df_excel.rename(columns={"values 2": "value_2"})

# 3. 日期标准化：统一为 YYYY-MM-DD 字符串（便于合并与展示）
for col in ["date"]:
    df_csv[col] = pd.to_datetime(df_csv[col]).dt.strftime("%Y-%m-%d")
    df_excel[col] = pd.to_datetime(df_excel[col]).dt.strftime("%Y-%m-%d")

# 4. 基于 date + id 双键合并（推荐：更严谨）
merged = pd.merge(df_csv, df_excel, on=["date", "id"], how="inner")

# 5. 计算差异与条件列
merged["discrepancy"] = merged["value_2"] - merged["value_1"]
merged["Over 2?"] = merged["discrepancy"].apply(lambda x: "yes" if x > 2 else "no")

# 6. 整理最终列顺序（按题目示例）
result = merged[["date", "id", "value_1", "value_2", "discrepancy", "Over 2?"]]
print(result)

⚠️ 注意事项与常见陷阱

代码跑通了固然可喜，但要想在实际项目中游刃有余，下面这些“坑”最好提前了解。

• 缺失值处理：代码中使用的`how="inner"`合并方式，只会保留两个表格中都存在的`id+date`组合。如果你想保留所有记录，看看哪些数据在另一边缺失了，可以改用`how="outer"`，但别忘了后续用`fillna()`处理因此产生的空值。
• 重复键风险：务必确保`["date", "id"]`这个组合在各自的数据集中是唯一的。否则，合并会产生笛卡尔积，导致数据量异常膨胀。一个良好的习惯是在合并前用`df.duplicated(subset=["date","id"]).any()`检查一下。
• Excel 日期解析：`pd.read_excel(..., parse_dates=["date"])`在解析类似`3/12/2024`的日期时，默认会按照月/日/年的美国格式处理。如果你的源数据是日/月/年格式，一定要记得加上`dayfirst=True`参数，否则日期会全部错乱。
• 列名冲突：如果两个表里除了合并键，还有其他同名的列（比如都有一个`note`列），Pandas在合并时会自动添加`_x`和`_y`后缀来区分。你可以通过`suffixes=("_csv", "_xlsx")`这样的参数来自定义后缀，让列名含义更清晰。

✅ 总结

说到底，处理这类异构数据比对，核心思路就是“先标准化，再对齐”。充分利用Pandas的`read_csv`/`read_excel`参数（如`skiprows`跳过行、`parse_dates`解析日期、`rename`重命名列）完成数据清洗和标准化，然后依靠`merge`函数强大而灵活的多键对齐能力进行合并，最后进行差异计算和业务逻辑判断。

掌握了这个模式，它的扩展性非常强。你可以轻松地将这个流程嵌入到自动化脚本中，实现定时数据稽核、自动生成带高亮差异的Excel报告，或者直接与数据库流水记录进行比对。这不仅是解决了一个具体问题，更是向自动化、规范化的数据工作流迈出的关键一步。