tidysheet 0.2.0

Local-first data profiling and cleaning engine with reproducible, exportable pipelines

🧹 tidysheet

Local-first data profiling & cleaning with reproducible, exportable pipelines.
Point it at a messy CSV / Excel / Parquet / JSON file — it profiles the data, proposes a cleaning pipeline you review as plain YAML, runs it on DuckDB, and exports the whole thing as standalone pandas, Polars, or SQL code you own.

Your data never leaves your machine. Every change is an inspectable step. Nothing is ever fixed silently.

pip install -e .          # from this repo (v0.2 — engine core + web UI)

tidysheet open     data.csv                     # interactive UI at localhost
tidysheet profile  data.csv -o report.html      # what's in here, what's wrong
tidysheet suggest  data.csv -o pipeline.yaml    # proposed cleaning steps — review/edit
tidysheet infer    original.csv edited.csv -o pipeline.yaml   # reverse-engineer a pipeline from a hand-edit
tidysheet run      pipeline.yaml data.csv -o cleaned.csv
tidysheet run      pipeline.yaml next_months_file.csv -o cleaned2.csv --strict   # re-apply, fail on drift
tidysheet export   pipeline.yaml --to pandas -o clean_data.py           # code you own

Already cleaning in Excel? Record it once (tidysheet infer)

Clean a file however you like — including by hand in Excel — then hand back both versions. tidysheet reverse-engineers the steps that reproduce your edits, verifies them (it replays the inferred pipeline on the original and checks it matches your edited file, cell by cell), and saves a pipeline you re-run forever:

tidysheet infer messy.xlsx messy_cleaned.xlsx -o pipeline.yaml
#   ✓ customer: stripped surrounding whitespace (12 cell(s))
#   ✓ country:  8 value replacement(s) (usa→United States, uk→United Kingdom, …)
#   ✓ status:   blanked null-like token(s) ['n/a'] (3 cell(s))
#   Reproduced 214/214 edited cell(s) (100%).

It detects trim, case, value maps, null-token blanking, dropped/renamed columns and de-duplication. Anything it can't explain (a one-off manual fix, an added row) is reported honestly as residual — never silently faked.

Guarded re-runs: drift never passes silently

When you suggest or infer a pipeline, tidysheet snapshots what the data looked like (the category values a step was built against). On every re-run it validates the new file against those snapshots and reports value drift loudly instead of cleaning silently:

• a new category a map_values step never saw (passes through unmapped),
• a value that silently coerces to NULL because it stopped parsing ("1.2K").

tidysheet run … --strict exits non-zero on drift, so scheduled re-runs fail loudly rather than hand back quietly-wrong data.

The interactive UI (tidysheet open)

A local web app (FastAPI + a zero-dependency, no-build frontend served from the package; binds to 127.0.0.1 only):

• Virtualized data grid — scrolls millions of rows via windowed fetching; click a column header for its stats, semantic type, issues, and facets.
• Suggestion cards with confidence and evidence; every step previews before it applies: rows added/removed with samples, per-column before → after cell diffs, new-column samples.
• Facet panel per column (value counts with bars; click a value to keep or drop those rows).
• Cluster & merge review — OpenRefine-style: inspect each cluster of near-identical values, edit the canonical form, merge to a map_values step.
• Step history — toggle steps on/off or remove them; downstream state recomputes; undo removes the last step. The history is the pipeline.
• Column actions: trim, case, parse numbers/dates (auto format detection), cast, fill, split, rename, outlier flags, drop.
• Export pipeline YAML / pandas / Polars / SQL and download cleaned CSV/Parquet, all from the toolbar. Light/dark follows the OS.

What the engine does

• Fast profiling on DuckDB: types, missing values, distincts, stats, duplicates, whitespace/case issues, null-like tokens (N/A, -, …), IQR outliers — plus a self-contained HTML report (works offline, light/dark).
• Semantic type detection (deterministic, no AI): emails, URLs, phones, zips, identifiers, booleans-as-text, numbers-as-text (currency $1,234.50, percents 12.5%, parenthesized negatives, locale decimal commas), dates-as-text with multi-format detection (2024-01-15 + 15/01/2024 in one column), countries and US states via bundled reference tables.
• Rule-based suggestions with evidence and confidence on every step — including OpenRefine-style fuzzy value clustering ("Shipped", "shipped ", "SHIPPED" → one canonical value) that runs on distinct values via DuckDB, so it scales past OpenRefine's in-memory ceiling.
• A declarative pipeline IR (YAML): 18 ops, validated against the source schema before execution, git-diffable, re-appliable. Schema drift on re-apply fails loudly, never silently.
• Code export to pandas, Polars, and DuckDB SQL — standalone scripts, no tidysheet dependency, verified by a differential test suite to reproduce the engine's output exactly.
• Explainable quality score (completeness / validity / consistency / uniqueness) — every number decomposes into named issues; no black-box scores.
• A cleaning changelog on every run: rows and columns affected per step.

Principles (from the product plan)

1. The pipeline is the product. Chat tools clean a file once; a pipeline cleans every month's file. Everything serializes, diffs, and re-runs.
2. Suggest, preview, approve. The engine never auto-fixes. Outliers are flagged, not deleted. Every suggested step carries its evidence.
3. Deterministic core; AI as optional suggester. Everything above runs offline with no model. An LLM may only plug into one seam (tidysheet.suggester): it sees a small, data-light context, only proposes IR steps for the residuals the deterministic layer can't explain, and those proposals pass back through the same verify gate. The default is a no-op, so the core keeps its "data never leaves this machine" promise and is never required. Bring your own model — nothing in pyproject.toml depends on a provider.
4. No lock-in. The export is the escape hatch: if you stop using the tool tomorrow, you keep working code.

Pipeline format

tidysheet_pipeline:
  version: '0.1'
  name: messy_sales_clean
  source: {path: demo/messy_sales.csv, schema: [...]}
  steps:
  - id: 3f2a9c1d
    op: map_values
    params:
      column: country
      mapping: {usa: United States, U.S.: United States, DE: Germany}
    provenance: rule          # user | rule | ai — you always know who proposed it
    note: 'standardize country names: 16 variant(s) mapped to canonical form'
    confidence: 0.85

Ops in v0.1: standardize_missing, trim_whitespace, change_case, find_replace, regex_extract, split_column, rename_columns, drop_columns, filter_rows, cast_type, fill_missing, drop_missing, deduplicate, parse_dates, parse_numbers, date_decompose, flag_outliers, map_values — run tidysheet ops.

Try the demo

tidysheet suggest demo/messy_sales.csv -o demo/pipeline.yaml
tidysheet run demo/pipeline.yaml demo/messy_sales.csv -o demo/cleaned.csv
tidysheet export demo/pipeline.yaml --to sql

Development

python -m venv .venv && .venv/Scripts/pip install -e .[dev]
.venv/Scripts/python -m pytest

The test suite includes a differential export suite: every suggested pipeline is executed by the engine and by the exported pandas / Polars / SQL code, and outputs are compared cell-by-cell. That contract is the point of the project; keep it green.

Status & roadmap

This covers roadmap Phases 1–4 of RESEARCH_AND_IMPLEMENTATION_PLAN.md (profiling engine, cleaning engine + IR runtime, interactive UI, pipeline export), plus early recurrence hardening — value-drift checks on re-run (--strict) and diff-inference (tidysheet infer) that turns a before/after pair into a verified pipeline. The AI seam is in place (tidysheet.suggester, no-op by default) but ships no model adapter yet. Next: an opt-in local/BYO-model suggester for residuals, schema-drift remapping, and richer dataset diffing.

MIT license.