Metadata-Version: 2.4
Name: tidysheet
Version: 0.3.0
Summary: Local-first data profiling and cleaning engine with reproducible, exportable pipelines
License: MIT
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: duckdb>=1.0.0
Requires-Dist: polars>=1.0.0
Requires-Dist: PyYAML>=6.0
Requires-Dist: Jinja2>=3.1
Requires-Dist: click>=8.1
Requires-Dist: fastapi>=0.110
Requires-Dist: uvicorn>=0.29
Requires-Dist: python-multipart>=0.0.9
Provides-Extra: excel
Requires-Dist: fastexcel>=0.10; extra == "excel"
Provides-Extra: build
Requires-Dist: pyinstaller>=6.0; extra == "build"
Provides-Extra: ai
Requires-Dist: anthropic>=0.69; extra == "ai"
Provides-Extra: dev
Requires-Dist: pytest>=8.0; extra == "dev"
Requires-Dist: pandas>=2.0; extra == "dev"
Requires-Dist: pyarrow>=15.0; extra == "dev"
Requires-Dist: fastexcel>=0.10; extra == "dev"
Requires-Dist: httpx; extra == "dev"
Requires-Dist: httpx2; extra == "dev"

<h1 align="center">🧹 tidysheet</h1>

<p align="center">
  <strong>Local-first data profiling &amp; cleaning with reproducible, exportable pipelines.</strong><br>
  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
  <strong>exports the whole thing as standalone pandas, Polars, or SQL code you own.</strong>
</p>

<p align="center">
  <a href="https://github.com/rohanbeingsocial/tidysheet/actions/workflows/ci.yml"><img alt="CI" src="https://github.com/rohanbeingsocial/tidysheet/actions/workflows/ci.yml/badge.svg"></a>
  <a href="https://pypi.org/project/tidysheet/"><img alt="PyPI" src="https://img.shields.io/pypi/v/tidysheet?color=2563eb"></a>
  <a href="https://github.com/rohanbeingsocial/tidysheet/releases/latest"><img alt="Latest release" src="https://img.shields.io/github/v/release/rohanbeingsocial/tidysheet?label=binaries"></a>
  <img alt="Python 3.10+" src="https://img.shields.io/badge/python-3.10%2B-3776ab">
  <img alt="License: MIT" src="https://img.shields.io/badge/license-MIT-2563eb">
</p>

<p align="center">
  <img alt="Engine: DuckDB" src="https://img.shields.io/badge/engine-DuckDB-fff000?labelColor=000">
  <img alt="Export: pandas · Polars · SQL" src="https://img.shields.io/badge/export-pandas%20%C2%B7%20polars%20%C2%B7%20sql-15803d">
  <img alt="Local-first" src="https://img.shields.io/badge/local--first-no%20data%20leaves%20your%20machine-6a7280">
</p>

![The tidysheet interactive UI](assets/ui-dark.png)

> **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](https://github.com/rohanbeingsocial/tidysheet/releases).
**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`.

<sub>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.</sub>

```
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 "months/*.csv" -o all.csv   # union a whole folder of monthly files
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 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
```

```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.

## 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): 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.
- **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.

## 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

```yaml
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 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`](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`).
Next up: database & S3 connectors (via DuckDB's `postgres` / `sqlite` / `httpfs`
extensions) and schema-drift remapping.

MIT license.
