# 客户 Excel 一团乱，pandas 为何悄悄少算营收

> pandas 求和时把缺失值当 0，要清点类型转换丢掉的每个单元格，并在演示前给客户发错误报告。

原文: https://fdetimes.net/zh/guides/clean-messy-client-excel-pandas/

一千笔订单，其中四十笔的金额列写着“联系我们”。你把这一列转成数字，四十个单元格变成 NaN，然后调用 `.sum()`。返回的数字看起来整整齐齐，却恰好少算了四十笔订单，而且没有任何警告。

这个场景是假设的，但机制是真实的。pandas 文档写得很明白：求和时，NA 值或空数据按 0 处理。对前线部署工程师（FDE）来说，这是驻场第一周最危险的陷阱，因为错误的数字最终会出现在演示里。

本文介绍一套今晚就能在笔记本上跑通的流程：按原样读取文件，声明缺失值，可控地转换类型，清点损失，再把错误汇总成报告交给客户。代码片段为教学做了删减，凡是简化之处都会标明。

## 要做什么，需要准备什么

假设一家分销商发来两个文件：`orders.csv`（订单）和 `report.xlsx`（报表）。CSV 里有一列 `customer_id`（客户编号），值形如“00123”；一列 `amount`（金额）按越南习惯书写，例如“1.250.000,5”（外面套着双引号）；还有几个单元格写着“-”或“暂无”。Excel 文件顶部有三行装饰性的表头，每个分支机构一个工作表。

你需要 Python 3、pandas、运行环境自带的 Excel 读取引擎，最后一步还需要 Pandera。自己动手造这两个样例文件，每个约 20 行，故意把各类脏数据都埋进去。亲手弄脏数据，是理解每个参数作用的最快方式。

## 第 1 步：读取 CSV，别让 pandas 去猜

最常见的错误是直接写 `pd.read_csv("orders.csv")`。pandas 会推断类型，“00123”变成数字 123，你还没来得及看一眼，客户编号就已经彻底毁了。

```python
import pandas as pd

df = pd.read_csv(
    "orders.csv",
    dtype={"customer_id": str},
    na_values={"amount": ["-", "pending", "contact us"]},
    thousands=".",
    decimal=",",
    on_bad_lines="warn",
)
```

`read_csv` 文档建议使用 `str` 或 `object`，并配合合适的 `na_values`，以保留原始数据，而不是让 pandas 解释类型。`na_values` 可以接收字典，因此能为每一列单独声明缺失值，作为 pandas 默认识别为 NaN 的列表之外的补充。

`thousands` 和 `decimal` 参数用来处理特定地区的数字格式；文档中的示例针对欧洲数据，把逗号作为小数分隔符，这正是越南数字的写法。`on_bad_lines="warn"` 遇到字段过多的行时会发出警告并跳过，既不会让整个读取崩溃，也不会悄无声息地丢行。

本步检查：打印 `df.dtypes` 和 `df.head()`。`customer_id` 列必须保留开头的 0，`amount` 必须是浮点数 1250000.5。仔细阅读每一条坏行警告，记下行号，它们是给客户的报告里的第一项。

## 第 2 步：合并之前，逐个打开 Excel 工作表

客户的 Excel 文件很少从第 1 行开始。前三行通常是公司名称、报表标题和导出日期。

```python
sheets = pd.read_excel(
    "report.xlsx",
    sheet_name=None,
    skiprows=3,
    dtype=object,
)
for name, table in sheets.items():
    print(name, table.shape)
```

设置 `sheet_name=None` 后，`read_excel` 返回一个字典，每个工作表对应一个 DataFrame，各分支机构有多少行一目了然。`dtype=object` 让数据保持 Excel 中的原样，不做类型推断；代价是所有列都变成 `object`，需要你在下一步自己转换类型。

如果各工作表的垃圾行数不同，`skiprows` 也可以接收一个函数：它会对每个行索引调用一次，返回 `True` 即跳过该行。例如 `skiprows=lambda i: i in (0, 1, 2)`。本步检查：所有工作表的列数必须相同；列数不同的工作表，就是客户手动插过列的那个。

## 第 3 步：先规范化数字字符串，再转换类型

第 2 步之后，Excel 里的金额列是混杂的：客户以数字形式录入的单元格是数字，以文本形式录入的则是“1.250.000,5”这样的字符串。直接把这一列交给 `pd.to_numeric`，越南式写法的字符串一个也解析不了；加上 `errors="coerce"`，它们全部变成 NaN。完全有效的单元格和垃圾值一起丢了。

所以需要一个规范化步骤：去掉千位分隔的点号，把小数逗号换成点号，而且只对字符串单元格这样处理。已经是数字的单元格不要动，因为去掉 1250000.5 里的点号会把它变成另一个数。

```python
table = sheets["Branch_HN"]

def normalize(x):
    if isinstance(x, str):
        return x.strip().replace(".", "").replace(",", ".")
    return x

amount = table["amount"].map(normalize)
already_missing = amount.isna().sum()
table["amount"] = pd.to_numeric(amount, errors="coerce")
lost_in_coercion = table["amount"].isna().sum() - already_missing
print("原本为空：", already_missing, "| 无法读取：", lost_in_coercion)
```

（输出分别显示原本为空的单元格和无法读取的单元格。）

默认情况下，`to_numeric` 遇到第一个无法解析的值就会报错。设置 `errors="coerce"` 后，该值变成 NaN，代码继续运行。很方便，但开头那四十笔“联系我们”的订单，恰恰就是在这里从合计中消失的。

上面的 `normalize`（规范化）函数是简化版：它假定整列都按越南习惯书写。如果客户还混用了美式的“1,250,000.5”，就需要额外的识别规则，与其去猜，不如先问客户。

**要点:** 强制转换类型却不清点丢失的单元格，等于把错误藏了起来。

注意清点的方式：对列本身调用 `isna().sum()`，把一开始就为空的单元格与转换后新坏掉的单元格区分开。这两个数字对客户的意义不同。如果 1,000 行中只有 960 行还有金额，你报出的每一个合计都必须附上一句“不含 40 笔金额未知的订单”。

## 第 4 步：缺失单元格怎么处理，由客户决定

pandas 提供了两个工具：`dropna()` 删除含缺失数据的行或列，`fillna()` 用其他值替换 NA。两者都只需一行代码，但两者都是业务决策。

把金额列填成 0，等于断言这些订单是免费的；删掉这些行，等于断言这些订单从未存在。建议：只在客户确认过规则的列上使用 `fillna`（例如备注为空时填成空字符串），金额列保留 NaN，并写进错误报告。

还要注意，pandas 会根据数据类型使用不同的哨兵值来表示 NA。执行 `fillna` 或类型转换之后，再打印一次 `dtypes`，确认没有哪一列意外改变了类型。

去重也属于这一步。KDnuggets 上的一篇教程给出的理由很简单：同一条记录被重复计算，会扭曲分析结果。合并多个工作表时，在两个分支机构之间转移的订单很容易出现两次；要按客户确认的关键列去重，而不是按整行去重。

## 第 5 步：把错误变成客户看得懂的报告

到这里，你已经有了一个基本干净的文件，但真正的价值在错误清单里。Pandera 是 Union.ai 的开源项目，可以为 DataFrame 声明 schema 并据此校验。分支机构文件的三列 schema 大致如下。

```python
# 简化示意；API 名称请以已安装的 Pandera 版本为准
import pandera as pa

schema = pa.DataFrameSchema({
    "customer_id": pa.Column(str, pa.Check.str_matches(r"^\d{5}$")),
    "amount": pa.Column(float, pa.Check.ge(0), nullable=False),
    "date": pa.Column("datetime64[ns]"),
})

try:
    schema.validate(table, lazy=True)
except pa.errors.SchemaErrors as err:
    failures = err.failure_cases
    print(failures[["column", "check", "index", "failure_case"]])
    failures.to_csv("errors_for_client.csv", index=False)
```

（顶部注释说明这是简化示意，API 名称需以已安装的 Pandera 版本为准。）

关键在于 `lazy=True`：Pandera 不会在第一个错误处停下，而是把所有错误汇总成一份报告。`failure_cases` 表列出每次失败对应的列、规则、行和值。你把它导出成一个文件，一次性发给客户，而不是发十封“又发现一个错误”的邮件。

本步检查：在样例文件里故意把一个客户编号改成“123”，再删掉一个金额单元格。两个错误必须出现在同一份报告里，而不是每次运行只报一个。

## 最常见的错误

第一个是先直接读取文件、之后再修：开头的 0 一旦丢失，就无法从 DataFrame 中恢复。其次是为了减少干扰，把 `on_bad_lines` 设成静默跳过，结果没人知道丢了多少行。

更隐蔽的一个，是第 3 步提到的：对未经规范化的 Excel 列用 `coerce` 模式调用 `to_numeric`，代码运行顺畅，半列数据却不见了。最危险的是为了让图表看起来完整，对金额列执行 `fillna(0)`。再加上 pandas 求和时把 NA 当 0，你会得到一个必错无疑、却看起来完全可信的合计。

## 在客户现场，什么样的回答能赢得信任？

设想项目第一天：你通过邮件收到一个装满 Excel 文件的文件夹，第二天开会时客户问：“这和我们的账对得上吗？”

弱的回答是“清洗已经做完了”。好的回答是：“我们读取了 1,000 行，其中 40 行缺少金额，3 行格式有误。这是清单，请从源头修正。”这样的回答让客户看到，你对他们的数据了解到了每一行。

简历上不要写“精通 pandas”。要描述一条具体的数据管道：用固定 dtype 读取多工作表文件，处理越南格式的数字，用 Pandera 报告错误。

阅读岗位描述时，留意有没有提到接入客户数据的内容；如果有，面试时就讲这个故事。

第一次开会时，你能带去的最干净的数字，是附有一份“尚未计入哪些行”清单的数字。

**本周动手试试:**

- 创建一个 20 行的 CSV，客户编号以 00 开头，金额写成 1.250.000,5，再放几个 '-' 单元格，然后分别在使用和不使用 dtype、thousands、decimal 的情况下读取，对比差异。
- 写一个规范化越南式数字字符串的函数，对金额列运行 pd.to_numeric(errors='coerce')，并用 isna().sum() 打印新变成 NaN 的单元格数量。
- 安装 Pandera，为这个文件写一个三列的 schema，用 lazy=True 运行 validate，打印 failure_cases，看看汇总后的错误报告是什么样子。

## 信源

- [Working with missing data — pandas 3.0.6 documentation](https://pandas.pydata.org/docs/user_guide/missing_data.html)

- [pandas.read_csv — pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html)

- [pandas.read_excel — pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html)

- [pandas.to_numeric — pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.to_numeric.html)

- [How to Clean Messy CSV Files with Python: A Beginner's Guide](https://kdnuggets.com/how-to-clean-messy-csv-files-with-python-a-beginners-guide)

- [Pandera documentation](https://pandera.readthedocs.io/en/stable/)
