Effortless Spreadsheet Normalisation With LLM

This article details automating data cleaning for tabular datasets, focusing on transforming messy spreadsheets into tidy, machine-readable formats. You can test this using the free, registration-free CleanMyExcel.io service.

Why Tidy Data Matters

Consider an Excel spreadsheet containing film award data (sourced from Cleaning Data for Effective Data Science). The goal of data analysis is to derive actionable insights, requiring reliable (clean) and tidy (well-normalized) data. This example, while small, highlights the challenges of manual data cleaning when scaled to larger datasets. Directly interpreting its structure is difficult for machines, emphasizing the importance of tidy data for efficient processing and analysis.

Reshaped Data Example:

This tidy version facilitates easier data interaction and insight extraction using various tools. The challenge lies in converting human-readable spreadsheets into machine-friendly tidy versions.

Tidy Data Principles

Based on Hadley Wickham's "Tidy Data" (Journal of Statistical Software, 2014), tidy data adheres to these principles:

• Each variable is a column.
• Each observation is a row.
• Each type of observational unit is a table.

Common messy data problems include:

• Column headers as values (e.g., years as column headers instead of a "Year" column).
• Multiple variables in one column (e.g., "Age_Gender").
• Variables in both rows and columns.
• Multiple observational units in one table.
• A single unit split across multiple tables.

How to Tidy Data: A Workflow

Transforming messy data into tidy data isn't easily automated due to the unique nature of each dataset. While rules-based systems are often insufficient, machine learning models, particularly Large Language Models (LLMs), offer advantages. This workflow uses LLMs and code:

1. Spreadsheet Encoder: Serializes spreadsheet information into text, retaining only essential data for efficient LLM processing.
2. Table Structure Analysis: The LLM analyzes the spreadsheet structure, identifying tables, headers, boundaries, and potential issues like merged cells.
3. Table Schema Estimation: The LLM iteratively identifies columns, groups related columns, and proposes a final schema.
4. Code Generation: The LLM generates code to transform the spreadsheet into a tidy data frame, incorporating iterative code checking and data frame validation.
5. Data Frame to Excel: The tidy data frame is converted into an Excel file.

Why a Workflow, Not an Agent?

Currently, a workflow is more robust and maintainable than a fully autonomous agent, although agent-based approaches may offer future advantages.

Future Articles

Future articles will cover:

• Detailed spreadsheet encoding.
• Data validity and uniqueness checks.
• Handling missing values.
• Evaluating data reshaping and quality.

Thank you to Marc Hobballah for reviewing this article. All images, unless otherwise noted, are by the author.

The above is the detailed content of Effortless Spreadsheet Normalisation With LLM. For more information, please follow other related articles on the PHP Chinese website!