tidysheet 0.4.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.

Install

uv tool install tidysheet      # or: pipx install tidysheet   (one command, isolated)
pip install tidysheet          # into the current environment

No Python? Download the standalone binary. Each release attaches a single-file executable (tidysheet-windows-x64.exe, tidysheet-macos-arm64, tidysheet-linux-x64) with no dependencies to install — grab it from the Releases page. Double-click it and the interactive UI opens in your browser — no terminal, no commands: just upload a file and start cleaning. Build it yourself with pyinstaller packaging/tidysheet.spec.

_{First-run OS warning (the binaries are unsigned): Windows — SmartScreen may say "Windows protected your PC" → More info → Run anyway. macOS — Gatekeeper may block it → right-click → Open (or xattr -d com.apple.quarantine tidysheet-macos-arm64 && chmod +x it). Linux — chmod +x tidysheet-linux-x64, then run it.}

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 run      pipeline.yaml data.csv -o cleaned.csv --receipt               # write a JSON audit receipt
tidysheet run      pipeline.yaml data.csv -o cleaned.csv --contract contract.yaml --receipt  # validate output too
tidysheet run      pipeline.yaml "months/*.csv" -o all.csv   # union a whole folder of monthly files
tidysheet run      pipeline.yaml "sqlite://shop.db?table=orders" -o "sqlite://shop.db?table=clean"  # DB in, DB out
tidysheet check    --init cleaned.csv -o contract.yaml   # capture the shape you expect
tidysheet check    contract.yaml next_months_file.csv    # validate it — exit 1 on any violation
tidysheet watch    ./inbox -p pipeline.yaml --out-dir ./clean   # clean files as they land
tidysheet schedule pipeline.yaml data.csv -o clean.csv --every monthly  # emit a cron/CI/Task job
tidysheet export   pipeline.yaml --to pandas -o clean_data.py           # code you own
tidysheet diff     pipeline.yaml pipeline_v2.yaml           # semantic diff (exit 1 if changed)
tidysheet diff-data last_month.csv this_month.csv          # what changed in the DATA
tidysheet docs     pipeline.yaml -o pipeline.md             # readable docs for stakeholders

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.

Optional AI for the residuals. Add --ai to let a model propose steps for what the deterministic layer couldn't explain — a local model with no new dependency, or Claude via the optional [ai] extra:

tidysheet infer messy.csv cleaned.csv -o pipeline.yaml --ai ollama   # 100% local
tidysheet infer messy.csv cleaned.csv -o pipeline.yaml --ai claude   # pip install tidysheet[ai]
tidysheet infer messy.csv cleaned.csv --ai claude --ai-dry-run       # print what would be sent; call nothing

The default is still no model. AI proposals pass back through the same verify gate — a step that doesn't reproduce your edit is rejected, so the model can suggest but never fake a result. --ai-dry-run prints the exact prompt (only column names, residual descriptions, and a few sample rows — never the full table) so you can see what would leave the machine before it does.

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.

Data contracts (tidysheet check)

The drift guard protects one recorded pipeline. A data contract protects a dataset — a declarable statement of the shape you expect (schema, types, value ranges, allowed domains, null rates, uniqueness), validated on every new file. It's the CI guardrail for recurring jobs: exit non-zero the moment next period's data violates the shape you agreed to, instead of quietly passing it downstream.

Generate a starter contract from a known-good file — like suggest proposes a pipeline, check --init proposes a contract you review and edit:

tidysheet check --init cleaned.csv -o contract.yaml

tidysheet_contract:
  version: '0.1'
  name: cleaned
  rows: {min: 1}
  duplicates: {max: 0}
  columns:
    order_id: {type: integer, not_null: true, unique: true}
    amount:   {type: numeric, not_null: true}     # generation is conservative:
    country:  {type: text, semantic: country}     # it won't assert min:0 if the
    status:   {type: text, allowed: [Shipped, Pending, Cancelled, Refunded]}

Then validate any file against it:

tidysheet check contract.yaml next_months_file.csv
#   Contract check: 23/24 expectation(s) passed, 1 FAILED
#     ✗ status: allowed — 1 value(s) not allowed (1 row(s))
#         e.g. 'Bogus'

• Exits 1 on any violation (use in CI / scheduled runs); --no-fail reports without failing; --json out.json writes machine-readable results.
• Column expectations: required, type (integer / numeric / temporal / boolean / text), not_null, max_null_pct, unique, min / max, allowed (value domain), regex, semantic. Table-level: rows.min/max, duplicates.max.
• Generation is conservative — it asserts only what the sample clearly shows (no nulls → not_null; a small closed vocabulary → allowed), so a starter contract rarely false-fails and is meant to be tightened by hand.
• Like the drift guard, a contract never mutates data — it only observes and reports.

Recurring jobs (schedule / watch)

A pipeline that cleans one file is a script; a pipeline that cleans every month's file is the product. Two ways to make a saved pipeline recur:

Emit a scheduled job for whatever runs your cron:

tidysheet schedule pipeline.yaml data.csv -o cleaned.csv --every monthly --strict
#   0 8 1 * *  cd /work && tidysheet run pipeline.yaml data.csv -o cleaned.csv --strict

--to cron (default), --to github-actions (a full workflow that installs tidysheet, runs the pipeline, uploads the cleaned output, and — with --strict — fails the build on value drift), or --to windows (a schtasks command). --every daily|weekly|monthly + --at HH:MM set the cadence.

Or watch a folder and clean files as they arrive — no scheduler needed:

tidysheet watch ./inbox -p pipeline.yaml --out-dir ./clean
#   ✓ january.csv → january.cleaned.csv
#   ✓ february.csv → february.cleaned.csv  ⚠ drift

Every new or changed file matching --pattern (default *.csv) is run through the pipeline; unchanged files are skipped, and its own outputs are never re-ingested. --once sweeps the current contents and exits (handy in a job); --strict counts value drift as a failure. Dependency-free — it polls, so there's nothing extra to install.

Run receipts (run --receipt)

A scheduled job that cleans every month's file runs unattended — and, by default, silently. --receipt leaves an audit trail: one JSON file per run in .tidysheet/runs/ (or --receipt-dir) recording exactly what happened, so you can reconstruct any past run without re-executing it.

tidysheet run pipeline.yaml data.csv -o cleaned.csv --receipt
#   ... cleaning changelog ...
#   receipt: .tidysheet/runs/20260711T132024.832326_messy_sales_clean.json  [OK]

Each receipt records:

• which pipeline ran — its name and a sha256 of the pipeline definition,
• which inputs it read — every file (globs expanded) by path, sha256, and size, so the exact bytes are verifiable later,
• the output — path, sha256, row and column counts,
• per-step deltas — rows/columns before→after for each step, and whether the step drifted,
• value drift — the same issues --strict guards, captured in full,
• an optional contract result — add --contract contract.yaml to also validate the output (exactly like tidysheet check) and fold the pass/fail into the receipt,
• a verdict — OK, DRIFT, CONTRACT_FAILED, or a combination.

The receipt is written even when the run fails loudly, and exit codes compose for CI: 2 on strict value drift, 3 on a contract violation. Writing a receipt is pure observation — it never changes cleaning output.

Connectors — read & write beyond local files

Meet data where it lives. Any command that takes a data file also takes a connector URI, for both input and output, via DuckDB's sqlite, postgres, and httpfs extensions (auto-installed on first use):

tidysheet profile  "postgres://user:pass@host:5432/db?table=orders"
tidysheet run pipeline.yaml "sqlite://shop.db?table=orders" -o "sqlite://shop.db?table=clean"
tidysheet run pipeline.yaml "s3://bucket/raw/2026-07.parquet" -o "s3://bucket/clean/2026-07.parquet"
tidysheet suggest "https://example.com/export.csv" -o pipeline.yaml   # read-only

• Databases — sqlite://<path>?table=<t> (use sqlite:///abs/path.db for an absolute path) and postgres://user:pass@host:port/db?table=<t>. Read from a table, write to a table (created/replaced).
• Object stores / URLs — s3://bucket/key.parquet (read + write) and http(s)://… (read-only). Type is inferred from the extension (.csv / .parquet / .json).
• Plain filesystem paths (including Windows C:\…) are never mistaken for connectors — the local-file path is unchanged.

Connectors slot into the recurring-job commands too: tidysheet schedule pipeline.yaml "postgres://…?table=orders" -o "s3://…/clean.parquet" --to github-actions --strict emits a ready-to-run workflow (URIs are shell-quoted so query strings survive), and tidysheet check contract.yaml "postgres://…" validates a table in CI.

Credentials in a URI (user:pass@host) are redacted (user:***@host) before being written to a run receipt. Note they still appear in the command line itself — in CI/cron, source them from a secret (e.g. build the URI from an environment variable) rather than hard-coding the password. For private S3, configure DuckDB's credentials (a DuckDB SECRET or the standard AWS environment variables) in the job.

Exported pandas/Polars/SQL scripts still target a file source: a connector wires tidysheet's engine to your database; the standalone code is yours to point wherever you like.

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

• Multi-file input: profile, suggest, and run accept a single file, a glob (data/*.csv), or several schema-compatible files — unioned in a deterministic order (union_by_name tolerates column reordering), so a folder of monthly extracts profiles or cleans in one command. DuckDB under the hood means CSV / Parquet / JSON / Excel all work the same way.
• 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): 19 ops, validated against the source schema before execution, git-diffable, re-appliable. Schema drift on re-apply fails loudly, never silently.
• Lookups (lookup): enrich rows from a CSV reference table (VLOOKUP / XLOOKUP) — a left join on the trimmed text key that brings in the reference columns you name; unmatched rows get nulls, and the reference is deduped to one row per key. Like every op, it exports identically to pandas/Polars/SQL.
• 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.
• Review & document: tidysheet diff gives a semantic, param-level diff of two pipelines (aligned by step id; exits non-zero on change — a CI signal), and tidysheet docs renders a pipeline as Markdown or HTML you can hand to a stakeholder instead of the YAML. Both are pure reads — nothing executes.
• Dataset diff: tidysheet diff-data last.csv this.csv reports what changed in the data between two files — row-count and schema changes, per-column null-rate shifts, new / dropped category values, and numeric distribution shifts (min/max/mean). The "what changed in the data" complement to diff; --json for machines, --fail-on-change as a CI signal.

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 — the core pyproject.toml depends on no provider; a local --ai ollama needs nothing, and --ai claude is the optional [ai] extra.
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, lookup — run tidysheet ops.

A lookup step (VLOOKUP against a CSV reference table) looks like:

- id: 7a1e
  op: lookup
  params:
    key: country_code          # column in your data to match on
    ref: refdata/countries.csv # the reference/lookup table (CSV)
    ref_key: code              # column in the reference to match (default: key)
    columns: [country_name, region]   # reference columns to bring in
    # rename: {country_name: country} # optional: rename brought columns

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 recurrence hardening — value-drift checks on re-run (--strict), diff-inference (tidysheet infer) that turns a before/after pair into a verified pipeline, and data contracts (tidysheet check) that validate next period's file against a declarable shape. The AI seam is in place (tidysheet.suggester, no-op by default) and now ships two opt-in adapters — a local Ollama backend (no dependency) and a Claude backend (the [ai] extra) — that only propose steps for residuals, still gated by verify.

The ROADMAP.md post-0.2 features are all in: data contracts (check), zero-setup install (uv/pipx + a standalone binary), recurring jobs (schedule / watch), the BYO-model AI suggester (Ollama + Claude), multi-file / glob input, and pipeline diff & docs (diff / docs) — plus run receipts (run --receipt), a per-run JSON audit trail for unattended jobs, a lookup op (VLOOKUP/XLOOKUP), diff-data (what changed in the data), and connectors — SQLite / Postgres / S3 / HTTP input and output via DuckDB's sqlite / postgres / httpfs extensions. Next up: schema-drift remapping.

MIT license.