本文是关于自动清理任何表格数据集的数据的文章。

你可以使用CleanMyExcel.io服务在你自己的数据集上测试本文中描述的功能，该服务是免费的，无需注册。

从“为什么”开始

让我们考虑一下这个 Excel 电子表格，其中包含了授予电影的奖项信息。该表格来源于《Cleaning Data for Effective Data Science》（《为有效数据科学清理数据》）一书。

这是一个典型的、常见的电子表格，每个人在日常任务中可能都会拥有并处理这样的表格。但它有什么问题呢？

要回答这个问题，让我们首先回顾一下使用数据的最终目标：得出有助于指导我们个人或商业生活中决策的见解。这一过程至少需要两个关键要素：

• 可靠的数据：没有错误、不一致、重复、缺失值等问题的干净数据。
• 整洁的数据：一个经过良好规范化的数据框，便于处理和操作。

第二点是任何分析（包括处理数据质量）的主要基础。

回到我们的例子，假设我们想要执行以下操作：

• 对于参与多个奖项的电影，列出与之相关的奖项和年份。
• 对于赢得多个奖项的演员，列出与之相关的电影和奖项。
• 检查所有演员的姓名是否正确且已标准化。

自然地，如果我们结构化这个示例数据集（尽可能快速地编码），它足够小，可以通过肉眼或手动得出这些见解。但现在想象一下，数据集包含了整个奖项历史；如果没有任何自动化，这将是耗时、痛苦且容易出错的。

机器很难读取这个电子表格并直接理解其结构，因为它没有遵循良好的数据排列实践。这就是为什么整理数据如此重要的原因。通过确保数据以机器友好的方式结构化，我们可以简化解析、自动化质量检查并增强业务分析——所有这些都不需要更改数据集的实际内容。

对该数据进行重塑的示例

现在，任何人都可以使用低代码/无代码工具或基于代码的查询（SQL、Python 等）轻松地与该数据集进行交互并得出见解。

主要挑战在于如何将一个外观精美、适合人眼查看的电子表格转换为机器可读的整洁版本。

接下来，我们谈谈“是什么？”——整洁数据指南

什么是整洁数据？一个结构良好的数据框吗？

“整洁数据”这一术语由 Hadley Wickham 在 2014 年发表于《统计软件杂志》（Journal of Statistical Software）的一篇著名文章《整洁数据》（Tidy Data）中提出。以下是更好地理解其基本概念的关键引文。

数据整理

“构建数据集以促进操作、可视化和建模。”

“整洁的数据集提供了一种标准化的方式，将数据集的结构（其物理布局）与其语义（其含义）联系起来。”

数据结构

“大多数统计数据集都是由行和列组成的矩形表格。列几乎总是有标签的，而行有时也有标签。”

数据语义

“数据集是值的集合，通常是数字（如果是定量的）或字符串（如果是定性的）。值有两种组织方式。每个值都属于一个变量和一个观测值。变量包含跨单位测量同一基本属性（如身高、温度或持续时间）的所有值。观测值包含跨属性在同一单位（例如，一个人、一天或一场比赛）上测量的所有值。”

在给定的分析中，可能存在多个观测级别。例如，在一项新过敏药物的试验中，我们可能有三种类型的观测值：

• 从每个人收集的人口统计数据（年龄、性别、种族），
• 从每个人每天收集的医疗数据（打喷嚏次数、眼睛发红程度），以及
• 每天收集的气象数据（温度、花粉计数）。”

整洁数据

整洁数据是一种将数据集的含义映射到其结构的标准方式。数据集根据其行、列和表如何对应于观测值、变量和类型而被视为混乱或整洁的。在整洁数据中：

• 每个变量形成一列。
• 每个观测值形成一行。
• 每种观测单位类型形成一个表。”

混乱数据集的常见问题

列标题可能是值而不是变量名。

• 混乱示例：一个表格，其中列标题是年份（2019、2020、2021），而不是一个“年份”列。
• 整洁版本：一个带有“年份”列的表格，每行代表给定年份的一个观测值。

多个变量可能存储在一个列中。

• 混乱示例：一个名为“年龄_性别”的列，其中包含值如 28_Female。
• 整洁版本：分别为“年龄”和“性别”设置单独的列。

变量可能同时存储在行和列中。

• 混乱示例：一个跟踪学生考试成绩的数据集，其中科目（数学、科学、英语）既作为列标题存储，又在行中重复出现，而不是使用单一的“科目”列。
• 整洁版本：一个带有“学生 ID”、“科目”和“分数”列的表格，其中每行代表一个学生在一个科目上的分数。

多种类型的观测单位可能存储在同一个表中。

• 混乱示例：一个销售数据集，其中同时包含客户信息和商店库存。
• 整洁版本：分别为“客户”和“库存”设置单独的表。

单个观测单位可能存储在多个表中。

• 混乱示例：一个病人的医疗记录被拆分到多个表中（诊断表、药物表），而没有使用共同的患者 ID 将它们链接起来。
• 整洁版本：一个单独的表或使用唯一的“患者 ID”正确链接的表。

现在我们已经对整洁数据有了更好的理解，让我们看看如何将混乱的数据集转换为整洁的数据集。

思考“怎么做？”

整洁的数据集都是相似的，但每个混乱的数据集都有其自身的混乱之处。” Hadley Wickham（借用了列夫·托尔斯泰的话）

尽管这些指导原则在理论上听起来很清晰，但在实践中很难对任何类型的数据集进行轻松的概括。换句话说，从混乱的数据开始，没有简单或确定性的过程或算法来重塑数据。这主要是由于每个数据集的独特性。事实上，要精确地定义变量和观测值，然后在不丢失内容的情况下自动转换数据，这是非常困难的。这就是为什么尽管在过去十年中数据处理取得了巨大进步，但数据清理和格式化在大多数时候仍然是“手动”完成的。

因此，当复杂且难以维护的基于规则的系统不适合时（即，通过预先描述决策来精确处理所有上下文），机器学习模型可能会提供一些好处。这赋予了系统更大的自由度，使其能够通过概括在训练期间所学到的内容来适应任何数据。许多大型语言模型（LLMs）已经接触过大量的数据处理示例，使它们能够分析输入数据并执行诸如电子表格结构分析、表模式估计和代码生成等任务。

然后，让我们描述一个由代码和基于 LLM 的模块以及业务逻辑组成的工作流程，以重塑任何电子表格。

电子表格编码器

本模块旨在将电子表格数据中的主要信息序列化为文本格式。仅保留构成表格布局所需的必要单元格子集，移除非必要或过于重复的格式信息。通过仅保留必要信息，此步骤可最小化令牌使用量、降低成本并提升模型性能。当前版本是一个受论文《SpreadsheetLLM：为大型语言模型编码电子表格》（SpreadsheetLLM: Encoding Spreadsheets for Large Language Models）启发而设计的确定性算法，依赖于启发式方法。

表格结构分析

在继续之前，让大型语言模型（LLM）提取电子表格结构是构建后续步骤的关键。以下是提出的问题示例：

• 存在多少个表格，它们在电子表格中的位置（区域）是什么？
• 每个表格的边界是如何定义的（例如，空行/列、特定标记）？
• 哪些行/列作为表头，是否有任何表格具有多级表头？
• 是否存在需要过滤掉或单独处理的元数据部分、汇总统计信息或注释？
• 是否存在合并单元格，如果是，应如何处理？

表格模式估计

一旦完成对电子表格结构的分析，现在就可以开始考虑理想的目标表格模式。这涉及让LLM通过以下步骤进行迭代处理：

• 识别所有潜在列（多行表头、元数据等）
• 基于列名和数据语义比较列的领域相似性
• 将相关列分组

该模块输出一个最终模式，其中包含每个保留列的名称和简要描述。

生成格式化电子表格的代码

考虑到先前的结构分析和表格模式，此最后一个基于LLM的模块应起草代码，将电子表格转换为符合表格模式的数据框。此外，不得遗漏任何有用内容（例如，汇总值或计算值可能仍可从其他变量中推导出来）。

由于从头开始生成一次就能正常工作的代码具有挑战性，因此添加了两个内部迭代过程以在必要时修订代码：

• 代码检查：每当代码无法编译或执行时，将跟踪错误提供给模型以更新其代码。
• 数据框验证：检查所创建数据框的元数据（如列名、第一行和最后一行以及每列的统计信息），以验证表格是否符合预期。否则，将相应修订代码。

将数据框转换为Excel文件

最后，如果所有数据都适合放入单个表格中，则从该数据框创建一个工作表以保持表格格式。返回的最终资产是一个Excel文件，其活动工作表包含整洁的电子表格数据。