How to Clean Messy CSV Data (A Practical Checklist)

上周二，我看到一位初级分析师花了四个小时手动修复一个 CSV 文件，而清理这个文件应只需要二十分钟。她一一复制粘贴单元格，试图修复从三个不同系统导出的日期格式。她完成时，眼神恍惚，而文件中仍然有她没有注意到的不一致之处。作为一名数据工程师，我已经工作了十二年，而这个场景我看到的无数次——聪明的人们在混乱的 CSV 文件上浪费整个下午，因为他们没有系统的方法。

事实是，CSV 文件同时是最通用和最棘手的数据格式。每个系统都可以导出它们，每个工具都可以导入它们，但它们却是编码问题、分隔符混淆和结构不一致的雷区。根据数据质量研究所 2023 年的调查，数据专业人士平均每周花费 19.2 小时用于数据清理任务，其中 CSV 处理大约占 40％ 的时间。这几乎是每周八小时——整个工作日——都浪费在修复可避免的问题上。

我是马库斯·陈，我在过去的十年里为从小型初创企业到财富 500 强企业的公司构建数据管道。我处理了数十亿行的 CSV 数据，我见过这些文件可能破损的所有方式。我学到的就是，清理混乱的 CSV 数据不是聪明，而是有条理。你需要一个清单，一个可重复的流程，可以在问题级联到你的分析或数据库之前捕捉到这些问题。本文就是那个清单，提炼了数千小时的真实数据处理经验。

理解为什么 CSV 文件如此棘手

在我们深入清理过程之前，您需要了解为什么 CSV 文件会造成如此多的头疼。这个格式看似简单：用逗号分隔的值，每行一行。但这种简单性恰恰是问题所在。与 JSON 或 XML 等结构化格式不同，CSV 没有一个大家都遵循的正式规范。虽然 RFC 4180 标准存在，但它更多的是建议而非规则，大多数系统完全忽略它。

我曾经收到一个来自客户遗留会计系统的 CSV 文件，它使用分号作为分隔符，在数字值中包含逗号（如 "1,234.56"），使用单引号和双引号来进行文本限定，并在同一个文件中混合了 Windows 和 Unix 换行符。光是正确解析它就花了我三个小时，而那是使用专业工具的结果。这个文件在他们的系统中“工作正常”多年，因为他们的软件有定制逻辑来处理这些所有的奇怪情况。

CSV 文件的核心问题可以分为几个类别。首先是分隔符问题——数据中常见的逗号，系统使用制表符、管道、分号或其他字符，但并不总是告诉你使用的是哪一个。其次是文本限定——什么时候需要在值周围加引号，当您的数据中包含引号字符时会发生什么？第三是编码——这是 UTF-8、Latin-1、Windows-1252，还是其他完全不同的东西？错误的话，你会在本应有重音字母的地方看到乱码字符。

然后还有结构性问题。CSV 文件没有内置的方式表示数据类型，因此所有内容都是文本，直到你解析它。日期可能是 "2024-01-15"、"01/15/2024"、"15-Jan-24" 或 "January 15, 2024"——而有时这四种格式在同一列中出现，因为不同的用户以不同的方式输入数据。数字可能包含货币符号、百分号或千位分隔符。布尔值可能是 "true/false"、"yes/no"、"1/0"、"Y/N" 或任何组合。

最糟糕的是，许多 CSV 问题都是无声的。您的导入工具可能成功而没有错误，但您的数据却在悄然中被损坏。我见过的案例是产品代码的前导零被剥去，导致 "00123" 变成 "123"，进而破坏了库存系统。我见过日期被错误地解释，因为系统默认了 MM/DD/YYYY，而文件使用的是 DD/MM/YYYY，造成订单安排在如 2 月 31 日这样的不可能的日期。这些错误不会自我宣布——它们隐藏在数据中，直到有人注意到数字不符。

第一步：导入前检查

人们在处理 CSV 文件时最大的错误是直接在 Excel 中打开它们或直接导入到数据库中。不要这样做。你第一步应该始终是在文本编辑器或命令行工具中进行检查。我使用在 Unix 系统上组合使用 head、tail 和 wc -l，或者使用能够处理大文件而不会崩溃的程序员文本编辑器，如 VS Code 或 Sublime Text。

"CSV 文件就像数据格式的蟑螂——它们能够幸存于一切，随处工作，造成你意想不到的问题。”

首先查看文件的前 20-30 行。这会告诉你分隔符是什么，是否有标题行，以及一般结构是什么样的。但不要止步于此——还要查看最后 20-30 行。我无法统计多少次我发现一个文件开头很干净，但结束时却是垃圾：部分行、导出过程中的错误消息，或是某人很乐意附加到数据上的汇总统计信息。一个客户的 ERP 系统在每次导出结束时添加了一个脚注"总记录：45,892"，这会让任何天真的导入过程崩溃。

用 wc -l filename.csv 检查行数，并与您期望的进行比较。如果文件应该有 10,000 条记录加一个标题，您应该看到 10,001 行。如果你看到 10,247 行，说明有什么问题——可能是文本字段中嵌入的换行符。这在包含用户生成内容（如评论或描述）的 CSV 文件中非常常见。有人写道"这个产品很好\n强烈推荐"，然后突然你就有一个行中间的换行符。

通过检查前几行来查找分隔符。计算每行中的逗号、制表符、管道或分号。它们应该是一致的。如果第一行有 12 个逗号，而第二行有 15 个，那就有问题——要么分隔符不对，要么你有未限定的文本字段包含了分隔符字符。我曾花了一个小时调试一个文件，才意识到它使用了管道字符 (|) 作为分隔符，而这在我的终端字体中几乎隐形。

通过查找非 ASCII 字符来检查编码。如果你看到奇怪的符号如 ’ 而不是撇号，或 é 而不是 é，那么就有编码不匹配。这个文件可能是 UTF-8，但被当作 Latin-1 读取，反之亦然。使用像 file -i filename.csv 这样的工具在 Unix 上检测编码，或者使用你的文本编辑器的编码检测功能。早期搞错这个意味着你数据中的每个文本字段都将被损坏。

第二步：验证结构

在你手动检查文件后，下一步是结构验证。每行应该有相同数量的字段，并且这个数量应该与标题行匹配。这听起来显而易见，但在现实世界数据中常常违反。我使用 Python 的 csv 模块或 pandas 以编程方式进行此验证，因为依靠肉眼检查成千上万的行是不可能的。

这是我在每个收到的 CSV 文件上运行的一个简单 Python 脚本：它计算每行中的字段数，并报告任何与标题不匹配的行。在一个最近的项目中，这个脚本揭示了 50,000 行中有 347 行由于导出过程中的一个错误在某些条件下重复了最后一列而多出字段。如果没有这个检查，这些行在导入期间将悄无声息地损坏，额外的数据要么被截断，要么被推入错误的列。

特别注意带引号的字段。CSV 使用引号来允许字段值中出现分隔符和换行符，但引号规则复杂，且经常被不正确地实施。像 "Smith, John" 这样的字段是正确引用的，但 "He said "hello" to me" 又怎么办？正确格式是 "He said ""hello"" to me"，使用双引号，但许多系统错误处理，生成 "He said "hello" to me"，从而导致解析失败。

检查空行，这些行通常出现在文件末尾或数据部分之间。这些应该