我要投稿

如何使用 LLM规范电子表格并实现数据有效性检测及纠正

发布日期：2025-05-12 13:58:11 浏览次数： 2139

作者：数据驱动智能

微信搜一搜，关注“数据驱动智能”

一如何使用 LLM实现电子表格规范化

请观察以下电子表格：

从“为什么”开始

让我们看一下这个Excel电子表格，其中包含电影奖项的信息。这是一个典型的、常见的电子表格，每个人都可能在日常工作中使用它。但它有什么问题呢？

要回答这个问题，我们首先要回顾一下使用数据的最终目标：获得洞察力，指导我们在个人或商业生活中的决策。这个过程至少需要两个关键要素：

可靠的数据：干净的数据，没有问题、不一致、重复、缺失值等。

整洁的数据：一个规范化的数据表，便于处理和操作。

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

回到我们的例子，假设我们要执行以下操作：
1. 对于每部获得多个奖项的电影，列出其相关的奖项和年份。

2. 对于每一位获得多个奖项的演员/女演员，列出他们相关的电影和奖项。

3. 检查所有演员/女演员的姓名是否正确且规范。

当然，如果我们将其结构化，这个示例数据集足够小，可以通过目测或手动获取这些洞察。但现在想象一下，如果数据集包含整个奖项历史记录；如果没有自动化，这将非常耗时、痛苦且容易出错。

机器读取此电子表格并直接理解其结构非常困难，因为它不符合良好的数据排列规范。正因如此，整理数据才如此重要。通过确保数据以机器友好的方式构建，我们可以简化解析、自动化质量检查并增强业务分析——所有这些都无需更改数据集的实际内容。

重塑此数据的示例

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

主要的挑战是如何将美观且看起来舒适的电子表格转变为机器可读的整洁版本。

接下来是什么？整洁数据指南

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

整洁数据这个术语在 Hadley Wickham 于 2014 年发表在《统计软件杂志》上的一篇著名文章《整洁数据》中有所描述。以下是更好地理解其基本概念所需的关键引述。

数据整洁

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

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

数据结构

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

数据语义

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

在给定的分析中，可能存在多个层次的观察。例如，在一种新的过敏药物的试验中，我们可能会有三种类型的观察结果：

收集每个人的人口统计数据（年龄、性别、种族），

每天从每个人收集的医疗数据（打喷嚏的次数、眼睛发红的情况），以及

每天收集的气象数据（温度、花粉数量）。”

整洁数据

整洁数据是将数据集的含义映射到其结构的标准方法。数据集的整洁程度取决于其行、列和表格与观测值、变量和类型的对应关系。在整洁数据中：

每个变量形成一列。

每个观察结果形成一行。

每种类型的观察单元都形成一个表格。”

混乱数据集的常见问题

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

混乱的示例：表格中的列标题是年份（2019、2020、2021）而不是“年份”列。

整洁版本：一个带有“年份”列的表格，每行代表给定年份的观察结果。

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

混乱的例子：名为“Age_Gender”的列包含诸如 28_Female 之类的值

整洁版本： “年龄”和“性别”单独列

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

混乱的例子：跟踪学生考试成绩的数据集，其中科目（数学、科学、英语）存储为列标题并在行中重复，而不是使用单个“科目”列。

整洁版本：一个包含“学号”、“科目”和“分数”列的表格，其中每一行代表一名学生一门科目的分数。

多种类型的观察单元可能存储在同一张表中。

混乱的示例：在同一个表中同时包含客户信息和商店库存的销售数据集。

整洁版本：为“客户”和“库存”设置单独的表格。

单个观察单元可能存储在多个表中。

混乱的例子：患者的医疗记录分散在多个表（诊断表、药物表）中，而没有通用的患者 ID 将它们关联起来。

整洁版本：使用唯一“患者 ID”的单个表或正确链接的表。

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

思考如何做？

“整洁的数据集都相似，但每个混乱的数据集都有其自身的混乱。”—— Hadley Wickham

虽然这些指导原则在理论上听起来很清晰，但在实践中却很难推广到任何类型的数据集。换句话说，从杂乱的数据开始，并不存在简单或确定性的流程或算法来重塑数据。这主要源于每个数据集的奇异性。事实上，通常来说，精确定义变量和观测值，然后在不丢失内容的情况下自动转换数据，难度之大令人难以置信。正因如此，尽管过去十年数据处理技术取得了巨大进步，但数据清理和格式化仍然大部分时间都是“手动”完成的。

因此，当复杂且难以维护的基于规则的系统不适用时（即无法通过预先描述决策来精确处理所有情境），机器学习模型或许能带来一些优势。这赋予系统更大的自由度，使其能够通过泛化训练过程中学到的知识来适应任何数据。许多大型语言模型 (LLM) 已经经历了大量的数据处理实例，使其能够分析输入数据并执行诸如电子表格结构分析、表格模式估计和代码生成等任务。

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

电子表格编码器

此模块旨在将电子表格数据中所需的主要信息序列化为文本。仅保留表格布局所需的单元格子集，从而删除非必要或过度重复的格式信息。通过仅保留必要信息，此步骤可最大限度地减少令牌使用量、降低成本并增强模型性能。当前版本是一种确定性算法，其灵感来自论文《SpreadsheetLLM：用于大型语言模型的电子表格编码》，该算法依赖于启发式算法。

表结构分析

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

有多少个表格，它们在电子表格中的位置（区域）是什么？

什么定义了每个表的边界（例如，空行/列，特定标记）？

哪些行/列用作标题，以及是否有任何表格有多级标题？

是否有需要过滤或单独处理的元数据部分、汇总统计数据或注释？

是否有合并单元格？如果有，该如何处理？

表模式估算

电子表格结构分析完成后，就该开始考虑理想的目标表模式了。这需要让 LLM 进行以下迭代处理：

识别所有潜在列（多行标题、元数据等）

根据列名和数据语义比较列的域相似性

对相关列进行分组

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

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

考虑到之前的结构分析和表格模式，最后一个基于 LLM 的模块应该编写代码，将电子表格转换为符合表格模式的适当数据表。此外，不得省略任何有用的内容（例如，聚合值或计算值仍然可以从其他变量中导出）。

由于在第一次迭代时从头开始生成运行良好的代码具有挑战性，因此添加了两个内部迭代过程以在需要时修改代码：

代码检查：每当代码无法编译或执行时，就会向模型提供跟踪错误以更新其代码。

数据验证：检查创建的数据表的元数据（例如列名、第一行和最后一行以及每列的统计信息），以验证表是否符合预期。否则，将相应地修改代码。

将数据表转换为 Excel 文件

最后，如果所有数据都能正确地放入单个表格中，则会根据该数据表创建一个符合表格格式的工作表。最终返回的资源是一个 Excel 文件，其活动工作表包含整齐的电子表格数据。充分利用新整理的数据集，一切分析皆有可能。

关于工作流的最后说明

为什么提出使用工作流程而不是代理来实现该目的？

在撰写本文时，我们认为基于LLM的精确子任务工作流比自主性更强的代理更健壮、稳定、可迭代且易于维护。代理可能具有以下优势：在执行任务时拥有更大的自由度和自主性。然而，在实践中，它们可能仍然难以处理；例如，如果目标不够明确，它们可能会很快出现偏差。我相信我们的情况就是这样，但这并不意味着该模型在未来不适用。

二基于 LLM 的自动表格数据验证工作流

清洁数据，清晰洞察：无需人工干预即可检测和纠正数据质量问题。

什么是数据有效性？

数据有效性是指数据符合预期的格式、类型和值范围。单列内的标准化可确保数据根据隐含或显式要求保持一致。

与数据有效性相关的常见问题包括：

不适当的变量类型：不适合分析需要的列数据类型，例如文本格式的温度值。

具有混合数据类型的列：包含数字和文本数据的单个列。

不符合预期格式：例如，无效的电子邮件地址或 URL。

超出范围的值：超出允许范围或正常范围的列值，例如，负的年龄值或高中生的年龄大于 30 岁。

时区和日期时间格式问题：数据集内的日期格式不一致或异构。

缺乏测量标准化或统一尺度：对同一变量使用的测量单位存在差异，例如混合使用摄氏度和华氏度值作为温度。

数字字段中的特殊字符或空格：数字数据被非数字元素污染。

诸如此类的例子还有很多。

重复记录或实体以及缺失值等错误类型不属于此类别。

但是识别此类数据有效性问题的典型策略是什么？

数据满足预期。

数据清理虽然非常复杂，但通常可以分为两个关键阶段：

1. 检测数据错误
2. 纠正这些错误。

数据清理的核心是识别和解决数据集中的差异。具体来说，违反预定义约束的值，这些约束来自对数据的期望。

必须承认一个基本事实——在现实世界中，几乎不可能彻底识别所有潜在的数据错误——数据问题的来源几乎无穷无尽——从人为输入错误到系统故障——因此不可能完全预测。然而，我们可以做的是定义我们认为数据中合理的规律模式，即数据预期——即对“正确”数据应该是什么样子的合理假设。例如：

如果使用高中生数据集，我们可能预计年龄在 14 岁到 18 岁之间。

客户数据库可能要求电子邮件地址遵循标准格式（例如，[email protected]）。

通过建立这些期望，我们创建了一个用于检测异常的结构化框架，使数据清理过程易于管理和可扩展。

这些预期源自语义分析和统计分析。我们理解，“年龄”这一列名指的是众所周知的“生长时间”概念。其他列名可能取自高中的词汇领域，而列统计数据（例如最小值、最大值、平均值等）则提供了对数值分布和范围的洞察。综合起来，这些信息有助于确定我们对该列的预期：

年龄值应为整数

值应介于 14 到 18 之间

预期的准确性往往与分析数据集所花费的时间一样高。当然，如果团队每天都会定期使用某个数据集，那么发现细微数据问题（从而改进预期）的可能性就会显著增加。即便如此，在大多数环境中，即使是简单的预期也很少得到系统性的检查，这通常是由于时间限制，或者仅仅是因为它并非待办事项清单上最令人愉快或优先级最高的任务。

一旦我们定义了期望，下一步就是检查数据是否真正满足这些期望。这意味着应用数据约束并查找违规行为。对于每个期望，可以定义一个或多个约束。这些数据质量规则可以转换为返回二元决策的编程函数——一个布尔值，指示给定值是否违反了测试的约束。

这种策略在许多数据质量管理工具中很常见，这些工具提供了基于定义的约束检测数据集中所有数据错误的方法。然后，一个迭代过程开始解决每个问题，直到满足所有预期，即不再存在违规行为。

这一策略在理论上看似简单易行。然而，实践往往并非如此——数据质量仍然是许多组织面临的一项重大挑战，也是一项耗时的任务。

基于 LLM 的工作流程，用于生成数据预期、检测违规并解决它们。

此验证工作流程分为两个主要部分：列数据类型的验证和是否符合预期。
有人可能会同时处理这两项，但在我们的实验中，事先在数据表中正确转换每列的值是至关重要的准备步骤。通过将整个过程分解为一系列连续的操作，可以简化数据清理工作，从而提高性能、理解力和可维护性。当然，这种策略有些主观，但它倾向于尽可能避免一次性处理所有数据质量问题。

为了说明和理解整个过程的每个步骤，我们将考虑这个生成的示例：

数据有效性问题的示例遍布整个表格。每一行都刻意地隐藏了一个或多个问题：

第 1 行：使用非标准日期格式和无效的 URL 方案（不符合预期格式）。

第 2 行：包含文本（“二十”）的价格值而不是数字值（不合适的变量类型）。

第 3 行：评级为“4 星”，但其他地方的评级为数字（混合数据类型）。

第4行：评分值为“10”，如果评分预期在1到5之间，则该值超出范围（超出范围值）。此外，“Food”一词存在拼写错误。

第 5 行：使用带有货币符号的价格（“20€”）和带有额外空格的评级（“5”），表明缺乏测量标准化和特殊字符/空格问题。

验证列数据类型

估计列数据类型

这里的任务是根据列的语义含义和统计属性，为数据框中的每一列确定最合适的数据类型。分类仅限于以下选项：字符串、整数、浮点数、日期时间和布尔值。这些类别足够通用，可以涵盖大多数常见的数据类型。
有多种方法可以执行此分类，包括确定性方法。这里选择的方法利用大型语言模型 (LLM)，并提供有关每一列和整个数据框上下文的信息来指导其决策：

列名列表

数据集中随机抽取的代表行

描述每列的列统计信息（例如唯一值的数量、最大值的比例等）

例子

1.列名：date
描述：表示与每条记录相关的日期和时间信息。
建议的数据类型：datetime
2.列名：category
描述：包含定义项目类型或分类的分类标签。
建议的数据类型：string
3.列名：price
描述：保存以货币表示的项目数值价格。
建议的数据类型：float
4.列名：image _url
描述：存储指向项目图像的网址 (URL)。
建议的数据类型：string
5. 列名：rating
描述：使用数值分数表示对项目的评估或评级。
建议的数据类型：int

将列值转换为估计数据类型

一旦预测了每列的数据类型，就可以开始值的转换。根据所使用的表框架，此步骤可能略有不同，但底层逻辑基本相同。例如，在CleanMyExcel.io服务中，Pandas 被用作核心数据框架引擎。然而，在 Python 生态系统中，Polars 或 PySpark 等其他库也同样强大。
所有不可转换的值都留待进一步研究。

分析不可转换的值并提出替代方案

此步骤可视为一项插补任务。先前标记的不可转换值违反了列的预期数据类型。由于潜在原因多种多样，此步骤可能颇具挑战性。LLM 再次提供了一种有益的权衡，可以解释转换错误并提出可能的替代方案。
有时，更正方法很简单——例如，将年龄值 20 转换为整数 20。在许多其他情况下，替代方案并不那么明显，使用标记（占位符）值标记该值是更好的选择。例如，在 Pandas 中，特殊对象 pd.NA 就适用于这种情况。

例子

{
"violations": [
{
"index": 2,
"column_name": "rating",
"value": "4 stars",
"violation": "Contains non-numeric text in a numeric rating field.",
"substitute": "4"
},
{
"index": 1,
"column_name": "price",
"value": "twenty",
"violation": "Textual representation that cannot be directly converted to a number.",
"substitute": "20"
},
{
"index": 4,
"column_name": "price",
"value": "20€",
"violation": "Price value contains an extraneous currency symbol.",
"substitute": "20"
}
]
}

替换不可转换的值

此时，将应用程序函数将有问题的值替换为建议的替代值。然后再次测试该列，以确保所有值现在都可以转换为预估的数据类型。如果成功，工作流程将进入预期模块。否则，将重复上述步骤，直到该列通过验证。

验证列数据预期

生成所有列的期望

提供了以下元素：

数据字典：列名、简短描述和预期数据类型

数据集中随机抽取的代表行

列统计信息，例如唯一值的数量和顶部值的比例

根据每列的语义含义和统计属性，目标是定义验证规则和期望，以确保数据质量和完整性。这些期望应属于以下与标准化相关的类别之一：

有效范围或间隔

预期格式（例如电子邮件或电话号码）

允许的值（例如分类字段）

列数据标准化（例如 'Mr', 'Mister', 'Mrs', 'Mrs.' 变为 ['Mr', 'Mrs']）

例子

列名：date

• 期望：值必须是有效的日期时间。
-原因：该列表示日期和时间信息，因此每个条目应遵循标准日期时间格式（例如，ISO 8601）。
• 期望：日期时间值应包含时区信息（最好是 UTC）。
-原因：提供的示例时间戳包含明确的 UTC 时区信息。这可确保基于时间的分析的一致性。

────────────────────────────────
列名：category

• 期望：允许的值应标准化为预定义集合。
-原因：根据语义，有效类别可能包括“书籍”、“电子产品”、“食品”、“服装”和“家具”。（注意：示例中包含“Fod”，可能需要更正为“Food”。）
• 期望：条目应遵循标准化的文本格式（例如，标题大小写）。
-原因：一致的大写和拼写将改进下游分析并减少数据清理问题。

──────────────────────────────
列名：price

• 期望：值必须是浮点数。
-原因：由于此列存储的是金额，因此为了计算准确，条目应存储为数值（浮点数）。
• 期望：价格值应在有效的非负数字区间内（例如，price ≥ 0）。
-原因：在定价环境中，负价通常没有意义。即使样本中观察到的最小值是 9.99，对于定价数据来说，允许零或正值也更为现实。

────────────────────────────────
列名：image _url

• 期望：值必须是具有预期格式的有效 URL。
- 原因：由于此列存储的是图片网址，因此每个 URL 都应遵循标准 URL 格式模式（例如，包括正确的协议模式）。
• 期望：URL 应以“https://”开头。
- 原因：示例显示一个 URL 使用“htp://”，这可能是拼写错误。强制执行安全 (https) URL 标准可提高数据可靠性和用户安全性。

────────────────────────────────

列名：rating

• 期望：值必须是整数。

- 原因：评估分数是数字，并且如样本所示，评级存储为整数。
•期望：评级值应在有效区间内，例如 1 到 5 之间。

- 原因：在许多情况下，评级通常采用 1 到 5 的等级。尽管样本包含值 10，但这可能是数据质量问题。强制执行此范围可使评估尺度标准化。
生成验证代码

定义预期后，目标就是创建结构化代码，根据这些约束检查数据。代码格式可能因所选的验证库而异，例如Pandera（用于CleanMyExcel.io）、Pydantic、Great Expectations、Soda等。
为了简化调试，验证代码应逐元素应用检查，以便在发生故障时能够清楚地识别行索引和列名。这有助于有效地查明和解决问题。

分析违规行为并提出替代方案

一旦检测到违规，就必须予以解决。每个问题都会被标记，并附上简短的解释和精确的位置（行索引 + 列名）。LLM 用于根据违规描述估算最佳的替换值。由于数据问题的多样性和不可预测性，这种方法同样非常有用。如果合适的替代方案不明确，则会根据所使用的数据框包应用标记值。

例子

{
"violations": [
{
"index": 3,
"column_name": "category",
"value": "Fod",
"violation": "category should be one of ['Books', 'Electronics', 'Food', 'Clothing', 'Furniture']",
"substitute": "Food"
},
{
"index": 0,
"column_name": "image_url",
"value": "htp://imageexample.com/pic.jpg",
"violation": "image_url should start with 'https://'",
"substitute": "https://imageexample.com/pic.jpg"
},
{
"index": 3,
"column_name": "rating",
"value": "10",
"violation": "rating should be between 1 and 5",
"substitute": "5"
}
]
}

其余步骤与验证列数据类型时使用的迭代过程类似。一旦所有违规行为都得到解决，并且没有检测到其他问题，数据框就完全验证了。

小结

期望有时可能缺乏领域专业知识，整合人类输入可以帮助实现更加多样化、具体和可靠的期望。关键挑战在于解决流程的自动化。人机交互方法可以提高透明度，尤其是在选择替代值或估计值时。

53AI，企业落地大模型首选服务商

产品：场景落地咨询+大模型应用平台+行业解决方案

承诺：免费POC验证，效果达标后再合作。零风险落地应用大模型，已交付160+中大型企业