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

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

本文详细讲解如何运用 Python Pandas 库，高效合并结构不同的 CSV 与 Excel 文件，通过关键字段（如 ID 和日期）智能匹配数据，并精确计算数值差异及进行业务条件标记。

在数据分析的日常工作中，你是否常被这样的问题困扰？手头有两份来源不同的数据：一份是 CSV 格式，另一份是 Excel 表格。它们的字段名称不统一，日期格式也各异。手动比对不仅耗时，还极易出错。本文将为你展示如何利用 Python 的 Pandas 库，系统性地解决这一“异构数据合并与差异比对”的经典难题。

我们将模拟一个典型场景：你需要将一份标准 CSV 文件，与一份表头起始于第 5 行的 Excel 报表进行整合。目标不仅是依据 ID 和日期对齐数据，还要计算出具体数值差异，并根据业务规则标记出“是否超差”。跟随以下清晰的步骤，你将掌握一套可复用的高效方法。

✅ 关键步骤解析

整个数据处理流程可分解为几个核心环节，每个环节都包含需要特别注意的细节。

1. 统一列名与数据类型：这是成功合并的前提。例如，CSV 文件中的列名为 `test date` 和 `values 1`，而 Excel 中对应的可能是 `date` 和 `values 2`。对于表头不规范的 Excel 文件，需使用 `skiprows=4` 参数跳过前几行无效信息。
2. 日期格式标准化：日期格式不一致是导致数据匹配失败的主要原因。例如，Excel 中的 `3/12/2024` 需要与 CSV 中的 `2024-03-12` 精确对应。可靠的解决方案是将所有日期统一转换为 `YYYY-MM-DD` 格式的字符串，从而规避日期对象解析可能带来的歧义。
3. 多键合并策略：这是合并操作的关键。虽然在某些情况下仅使用 `id` 字段也能合并，但更严谨、通用的做法是显式指定双键（如 `["date", "id"]`）进行合并。这能有效防止同一 ID 在不同日期有多条记录时出现的数据错配问题。
4. 差异计算与业务标记：数据合并后，计算差值（如 `discrepancy`）变得简单直接。对于“是否超差”这类业务判断列，建议使用 `"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"` 内连接，仅保留两个表格共有的记录。若需查看所有数据（包括单边缺失的记录），可改用 `how="outer"` 外连接，但务必注意后续使用 `fillna()` 等方法处理产生的空值。
• 重复键风险排查：务必确认合并键（如 `["date", "id"]`）在各自数据集中是唯一的。否则合并会产生笛卡尔积，导致数据量异常增长。建议在合并前使用 `df.duplicated(subset=["date","id"]).any()` 进行检查。
• Excel 日期解析格式：使用 `pd.read_excel(..., parse_dates=["date"])` 解析类似 `3/12/2024` 的日期时，Pandas 默认按“月/日/年”处理。若你的数据是“日/月/年”格式，必须设置 `dayfirst=True` 参数，否则日期将全部错误。
• 列名冲突与后缀：如果两个表存在非合并键的同名列，Pandas 会自动添加 `_x` 和 `_y` 后缀区分。你可以通过 `suffixes=("_csv", "_xlsx")` 参数自定义后缀，使列名含义更明确。

✅ 总结

处理异构数据比对的核心方法论是“先标准化，再对齐”。充分利用 Pandas 的读取参数（如 `skiprows`、`parse_dates`、`rename`）完成数据清洗与格式统一，再借助 `merge` 函数强大的多键匹配能力进行数据整合，最后执行差异分析和业务逻辑判断。

掌握这一模式后，其扩展性极强。你可以将此流程封装为自动化脚本，用于定时数据稽核、生成带高亮差异的 Excel 报告，或与数据库记录进行比对。这不仅解决了眼前的数据合并问题，更是迈向自动化、规范化数据工作流的重要一步。