Schemata

Schemata merges messy lab spreadsheets into one clean dataset, catches unit mix-ups, gives you the script that did it, and re-runs only what changed when new data arrives.

Comment

Inspiration

Two of the slowest parts of research aren't the science. They're chores. Every lab member names columns differently (Temp (C) vs temperature_K), mixes units (mg vs g, °C vs K) and writes dates their own way. Someone spends hours merging those spreadsheets in Excel. Unit mistakes slip through silently, and the cleaning can't be reproduced because nobody wrote down what they did. Then new data arrives, and nobody remembers which notebooks and figures depend on which files. People either re-run everything (slow) or miss a step and end up with stale results.

We wanted a tool that does the merge, shows its work, and keeps everything downstream up to date on its own.

What it does

Schemata is a local web app. Each project moves through six tabs:

  1. Files: Drag in CSV, TSV, TXT or Excel files, or save them into the project's watch folder. Schemata detects each file's format: the separator, the encoding, decimal commas (21,5), metadata lines above the real header, and the right sheet in a workbook. Originals are never modified.
  2. Schema: Define the clean columns you want: IDs, datetimes, quantities with units, unitless numbers like pH, and text. Or click Suggest from my files, and Schemata groups columns that mean the same thing and proposes a schema.
  3. Review: Every column of every file shows what it maps to, its unit or date format, why it was matched that way, and how real values change (74.2 → 23.44 °C). Fix anything with a dropdown, with undo and redo. Nothing is applied until you approve.
  4. Results: The merged table, searchable and sortable, downloadable as CSV or Excel. It comes with clean.py, a standalone pandas script that reproduces the merge. Every conversion in it is written out as a commented formula.
  5. Pipeline: A live graph of clean → your analysis steps: summary tables, bar, line and scatter charts, or your own Python scripts. Out-of-date steps are marked with the reason, and only those re-run.
  6. Activity: A permanent audit trail of every upload (with file hashes), every mapping change with its previous value, every approval and every run.

With Auto-run on, a file saved to the watch folder is merged within seconds and only the affected steps re-run, as long as all its columns map cleanly. A file that needs a person (a unit conflict, an unknown column, missing columns) waits for review, and the log says why. Export bundle downloads a zip with the raw files, clean.py, every setting, every output, the activity log, and a manifest with the SHA-256 hash of every file plus the Python and package versions.

How we built it

  • Backend: Python with FastAPI, pandas, Pint for units and openpyxl for Excel. Project metadata, the audit log and the run history live in SQLite.
  • Frontend: plain HTML, CSS and JavaScript modules. No React, no Node.js, no build step.
  • Header matching: exact synonyms first, then fuzzy matching with difflib (temprature → temperature). Units are read from headers like Temp (C), mass_mg and Conc (mM). Parsing is case-aware, so mM is millimolar and mm is millimetres.
  • Unit inference across files: a bare Temp column is tested against every candidate unit, and the reading whose median matches the other files wins. That's how the sample project's secretly Fahrenheit sensor file gets caught.
  • Dates: each file gets one explicit format. When a date is ambiguous (09/01 could be Sep 1 or Jan 9), Schemata follows a file whose format is unambiguous. If there isn't one, it picks the reading with the tighter date range. Either way, it tells you which it chose.
  • Overrides, not edits: user decisions are stored as overrides on top of the automatic plan, so adding a file or changing the schema never throws away a choice you already made.
  • Pipeline engine: a small dependency-graph runner we wrote ourselves. Each step declares its inputs and outputs, and files are compared by SHA-256 hash. If a step re-runs but produces byte-identical output, the steps after it are skipped too.
  • Charts: drawn by our own SVG renderer instead of matplotlib. They're vector graphics for papers and slides, use a colour-blind-safe palette, have hover tooltips, and every figure ships with a CSV of exactly the plotted numbers.
  • Security: pipeline steps run Python, so the app is locked down for local use. The server listens on 127.0.0.1 only and answers only to localhost, which blocks DNS rebinding. Every request that changes something needs a custom header, which blocks CSRF. It also sets a strict Content-Security-Policy and sanitizes file paths.
  • Privacy: no telemetry, no CDNs, no web fonts, no outbound connections. We checked this by running a full session with an empty home directory: the only files written were in the data folder.
  • Tests: 52 pytest tests covering file reading, units, the merge plan, code generation, the pipeline, the API and auto-run.

We borrowed design ideas from tools researchers already know: PhET simulations' guided tabs, the audit trails of electronic lab notebooks, and OpenRefine and Wrangler's preview-before-commit with undo. We also aimed for output that goes straight into a paper.

Challenges we ran into

  • Units with no label. A header that just says Temp gives you nothing to go on, so we had to infer the unit from the values by comparing them with the other files.
  • Ambiguous dates. 09/01/2026 is valid in two formats. We settled on one explicit format per file, plus tie-breaking rules, and we always show the choice instead of guessing silently.
  • Case-sensitive units. mM and mm differ only in case but mean completely different things, so unit parsing couldn't just lowercase everything.
  • Keeping user decisions stable. Rebuilding the plan whenever a file or the schema changes could wipe out someone's manual fixes. That's why edits are stored as overrides.
  • Running scripts safely. A local app that runs Python is a target for malicious websites, so we had to protect the server against DNS rebinding and CSRF.

What's next

  • An optional matcher for headers that rules and fuzzy matching can't resolve. The hook already exists in the code, but nothing is connected yet.
  • Support for legacy .xls files.
  • Shared, multi-user projects for whole labs.
  • Datasets larger than memory.
Share this project:

Updates

Submission history