一、如何使用 LLM實現電子表格規范化

請觀察以下電子表格：

圖片

從“為什么”開始

圖片

讓我們看一下這個Excel電子表格，其中包含電影獎項的信息。這是一個典型的、常見的電子表格，每個人都可能在日常工作中使用它。但它有什么問題呢？

要回答這個問題，我們首先要回顧一下使用數據的最終目標：獲得洞察力，指導我們在個人或商業生活中的決策。這個過程至少需要兩個關鍵要素：

可靠的數據：干凈的數據，沒有問題、不一致、重復、缺失值等。

整潔的數據：一個規范化的數據表，便于處理和操作。

第二點是任何分析的主要基礎，包括處理數據質量。

回到我們的例子，假設我們要執行以下操作：

1. 對于每部獲得多個獎項的電影，列出其相關的獎項和年份。

2. 對于每一位獲得多個獎項的演員/女演員，列出他們相關的電影和獎項。

3. 檢查所有演員/女演員的姓名是否正確且規范。

當然，如果我們將其結構化，這個示例數據集足夠小，可以通過目測或手動獲取這些洞察。但現在想象一下，如果數據集包含整個獎項歷史記錄；如果沒有自動化，這將非常耗時、痛苦且容易出錯。

機器讀取此電子表格并直接理解其結構非常困難，因為它不符合良好的數據排列規范。正因如此，整理數據才如此重要。通過確保數據以機器友好的方式構建，我們可以簡化解析、自動化質量檢查并增強業務分析——所有這些都無需更改數據集的實際內容。

重塑此數據的示例

圖片

現在，任何人都可以使用低代碼/無代碼工具或基于代碼的查詢（SQL、Python 等）輕松地與該數據集進行交互并獲得見解。

主要的挑戰是如何將美觀且看起來舒適的電子表格轉變為機器可讀的整潔版本。

接下來是什么？整潔數據指南

什么是整潔數據？一個結構良好的數據表？

整潔數據這個術語在 Hadley Wickham 于 2014 年發表在《統計軟件雜志》上的一篇著名文章《整潔數據》中有所描述。以下是更好地理解其基本概念所需的關鍵引述。

數據整潔

“構建數據集以方便操作、可視化和建模。”

“整潔數據集提供了一種標準化的方式，將數據集的結構其物理布局與其語義或含義聯系起來?！?/span>

數據結構

大多數統計數據集都是由行和列組成的矩形表格。列幾乎總是帶有標簽，行有時也帶有標簽。

數據語義

數據集是值的集合，通常是數字（如果是定量的）或字符串（如果是定性的）。值以兩種方式組織。每個值既屬于一個變量，也屬于一個觀測值。變量包含跨單位測量相同基礎屬性（例如高度、溫度或持續時間）的所有值。觀測值包含跨屬性測量同一單位（例如，一個人、一天或一場比賽）的所有值。

在給定的分析中，可能存在多個層次的觀察。例如，在一種新的過敏藥物的試驗中，我們可能會有三種類型的觀察結果：

收集每個人的人口統計數據（年齡、性別、種族），

每天從每個人收集的醫療數據（打噴嚏的次數、眼睛發紅的情況），以及

每天收集的氣象數據（溫度、花粉數量）。”

整潔數據

整潔數據是將數據集的含義映射到其結構的標準方法。數據集的整潔程度取決于其行、列和表格與觀測值、變量和類型的對應關系。在整潔數據中：

每個變量形成一列。

每個觀察結果形成一行。

每種類型的觀察單元都形成一個表格?！?/span>

混亂數據集的常見問題

列標題可能是值而不是變量名。

混亂的示例：表格中的列標題是年份（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 歲之間。

客戶數據庫可能要求電子郵件地址遵循標準格式（例如，user@domain.com）。

通過建立這些期望，我們創建了一個用于檢測異常的結構化框架，使數據清理過程易于管理和可擴展。

這些預期源自語義分析和統計分析。我們理解，“年齡”這一列名指的是眾所周知的“生長時間”概念。其他列名可能取自高中的詞匯領域，而列統計數據（例如最小值、最大值、平均值等）則提供了對數值分布和范圍的洞察。綜合起來，這些信息有助于確定我們對該列的預期：

年齡值應為整數

值應介于 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"
    }
  ]
}

其余步驟與驗證列數據類型時使用的迭代過程類似。一旦所有違規行為都得到解決，并且沒有檢測到其他問題，數據框就完全驗證了。

小結

期望有時可能缺乏領域專業知識，整合人的輸入可以幫助實現更加多樣化、具體和可靠的期望。關鍵挑戰在于解決流程的自動化。人機交互方法可以提高透明度，尤其是在選擇替代值或估計值時。