如何使用 Pandas 合并 CSV 与 Excel 文件并识别数据差异
本文介绍如何用 Python 的 Pandas 库读取结构不一致的 CSV 和 Excel 文件，基于共同键（如 id 和 date）智能对齐、合并，并计算数值差异及条件标记。
本文介绍如何用 python 的 pandas 库读取结构不一致的 csv 和 excel 文件，基于共同键（如 `id` 和 `date`）智能对齐、合并，并计算数值差异及条件标记。
在实际数据分析任务中，常需比对来自不同来源（如导出报表、系统日志）的数据集，尤其当字段命名不统一、日期格式不一致、或存在表头偏移时，直接合并易出错。本教程以一个典型场景为例：将一份 CSV 文件与一份从第 5 行开始才含有效表头的 Excel 文件进行对齐合并，并生成包含差异值和业务判断（如“是否超差2”）的新结果表。
✅ 关键步骤解析
- 统一列名与数据类型：CSV 中列为 test date 和 values 1，Excel 中为 date 和 values 2，且 Excel 表头位于第 5 行（即 skiprows=4）；
- 日期标准化：Excel 中 3/12/2025 需转为 YYYY-MM-DD 格式，确保与 CSV 的 2025-03-12 可精确匹配；
- 多键合并：题目目标要求按 date 和 id 联合匹配，但原始答案仅用 on="id"——这在 id 唯一且日期天然一一对应时可行；若存在同一 id 多日期记录，则必须显式合并双键（见下方增强版代码）；
- 差异计算与逻辑标记：新增列 discrepancy（差值）和布尔型列 Over 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)
⚠️ 注意事项与常见陷阱
- 缺失值处理：若某 id+date 组合在任一文件中不存在，how="inner" 将自动排除该行；如需保留所有记录，改用 how="outer" 并配合 fillna() 处理空值；
- 重复键风险：确保 ["date", "id"] 在各自数据集中组合唯一，否则会产生笛卡尔积——可用 df.duplicated(subset=["date","id"]).any() 预检；
- Excel 日期解析：pd.read_excel(..., parse_dates=["date"]) 对 3/12/2025 默认按 M/D/YYYY 解析（美国格式），若源数据为 D/M/YYYY，请添加 dayfirst=True；
- 列名冲突：若两表存在同名列（非合并键），merge 会自动加 _x/_y 后缀，可通过 suffixes=("_csv", "_xlsx") 显式控制。
✅ 总结
通过合理利用 pandas.read_csv / read_excel 的参数（如 skiprows, parse_dates, rename），再结合 merge 的多键对齐能力与链式数据处理，即可高效完成异构表格的比对分析。本方案不仅复现了目标输出，更具备扩展性——例如轻松接入邮件自动报告、差异高亮导出 Excel 或对接数据库校验流水。掌握此模式，是迈向自动化数据稽核的关键一步。
MusicArt
AI音乐生成器
下载