Metadata-Version: 2.4
Name: tidysheet
Version: 0.2.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: 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"

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

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

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