合并多个 CSV/Excel 表格时如何避免丢行、重复和串列

当需要把多个 CSV、TSV 或 Excel（含多工作表）文件堆叠、按 ID/邮箱做 VLOOKUP 式关联或跨来源去重时，用 pandas 安全合并并校验行数。

适用场景

解决思路

1. 先运行自带的 profiler 脚本探查输入，不改动文件：`python scripts/profile_inputs.py file1.csv file2.xlsx`，它会报告编码、分隔符、行数、表头、候选键和表头重叠情况；Excel 按工作表逐个探查，若工作簿有多个工作表，先与用户确认哪些工作表算数。
2. 选择操作类型：Append（堆叠）用于不同来源或时期的同类记录（1 月 + 2 月导出、三份线索名单），用 `pd.concat` 后再去重；Join（补充）用于同一实体的不同事实（联系人 + 成交金额），用 `pd.merge` 按键关联。不确定时：若文件共享大部分列就 append，若只共享一个 ID 列就 join。
3. 为每个文件建立显式的 `{原始列名: 统一列名}` 重命名映射（常见变体见 references/merge_strategies.md），当匹配不确定时展示给用户确认；在去重或 join 前规范化键列：去空白、邮箱转小写、电话去非数字字符、统一日期格式，否则 `A@x.com ` 和 `a@x.com` 会被当成两个人。
4. 用 `dtype=str` 读取每个文件，以保留 ID、邮编、电话的前导零，之后再转换特定列。
5. 合并代码示例：对每个文件读取（Excel 用 `pd.read_excel(path, dtype=str)`，CSV 用 `pd.read_csv(path, dtype=str, encoding="utf-8-sig", keep_default_na=False)`，编码用 profiler 报告的值），`df.rename(columns=rename)` 重命名，对键列执行 `df["email"] = df["email"].str.strip().str.lower()`，加 `df["source_file"] = path` 记录每行来源，然后 `pd.concat(frames, ignore_index=True, sort=False)`。
6. 去重前先把空键单独挑出：`has_key = combined["email"].fillna("") != ""`，因为空键彼此不算重复；去重时让后出现的文件胜出（把最新来源放在最后，用 `keep="last"`）：`deduped = combined[has_key].drop_duplicates(subset=["email"], keep="last")`，再把 `no_key` 拼回去。
7. 做 join 时用 `validate=` 强制 pandas 校验关系，让重复键报错而不是把行数乘开：`out = pd.merge(contacts, deals, on="email", how="left", validate="one_to_one", indicator=True)`，并用 `unmatched = out[out["_merge"] == "left_only"]` 找出未匹配行。
8. 冲突处理策略（保留第一条/最后一条/最完整、合并字段、标记待审）见 references/merge_strategies.md。
9. 报告前必须校验，绝不跳过：`rows_in = sum(len(f) for f in frames)`；断言 `len(merged) > 0`（合并结果非空）、`len(merged) <= rows_in`（输出行数不超过输入，否则检查 join 键）、`deduped["email"].is_unique`（去重后键唯一）；打印 `in= out= removed= blank_keys=` 以及 `merged["source_file"].value_counts()`。
10. 手工抽查三条被去掉的重复行，与源文件比对；断言只能证明行数正确，不能证明留下的是对的那条。
11. 按 references/output_template.md 的格式写出结果：给 Excel 用户用 CSV 时 `to_csv(path, index=False, encoding="utf-8-sig")`（BOM 让 Excel 正确读取重音字符）；写 Excel 用 `to_excel(path, index=False)` 并安装 openpyxl，单个工作表最多 1,048,576 行，超出则拆分或改用 CSV/Parquet。
12. 当 conflicts_review.csv 或 unmatched.csv 非空时，一并写出这些文件。
13. 注意 pandas 3.x（Python 3.11+）的差异：文本列默认是 `str` dtype 而非 `object`，用 `pd.api.types.is_string_dtype(col)` 判断；Copy-on-Write 始终开启，链式赋值 `df[col][mask] = x` 不会更新 `df`，要用 `df.loc[mask, col] = x`；解析出的日期时间默认微秒精度，若下游需要纳秒，先 `.dt.as_unit("ns")` 再转整数；`pd.read_excel(..., engine="calamine")`（需 `python-calamine`）读大工作簿比 openpyxl 快很多。本技能代码也兼容 pandas 2.2。
14. 排查常见失败模式：join 时两侧键重复导致行数爆炸（用 `validate=` 捕获）；不用 `dtype=str` 读取导致前导零丢失（`01234` 变 `1234`）；源文件中已被 Excel 显示成 `1.23E+15` 或已改格式的日期无法用 pandas 恢复，需标记出来；表头不在第 1 行的导出需要 `skiprows=` 或 `header=`；混合编码（UTF-8 中夹一个 cp1252）会出现 `é` 乱码，profiler 会报告每个文件的编码；append 后只在一个文件中存在的列会变成大量空值，应保留并报告其完整度，绝不静默丢列；超过几百 MB 的大文件用 `chunksize=` 读取或改用 Polars/DuckDB，并用键集合去重而不是全部载入内存。

适用边界 · 注意事项

• 源文件中的长 ID 已被 Excel 显示为科学计数法（如 `1.23E+15`）或日期已被改写时，pandas 无法恢复原值，只能标记出来。
• 单个工作表超过 1,048,576 行时不能直接写 Excel，需拆分或改用 CSV/Parquet。
• 文件超过几百 MB 时不应全部载入内存，应改用分块读取或 Polars/DuckDB。