k77040062 / data-janitor

DataJanitor — Safe CSV Cleaning, With an Audit Trail

The one rule that defines this skill

No silent changes. Ever.

Every value that changes, every row that is removed, and every type that is
converted must be (1) announced in a plan before it happens, (2) counted,
and (3) written into a change log. The original file is never overwritten.
If a change cannot be explained in one plain sentence, do not make it.

Agents break datasets most often not by cleaning badly, but by cleaning
quietly: imputing (filling) missing values nobody approved, dropping rows
nobody counted, turning an ID like 00742 into 742. This skill exists to
make that impossible.

When to use this

• A .csv file (or a pasted table / spreadsheet export) is shared with words
  like: clean, fix, dedupe, remove duplicates, missing values, prepare data,
  data quality, standardise, ready for analysis / ML / a dashboard.

• The user asks "what is wrong with this data?" — that is Step 1 and 2 only.

When NOT to use this

• The file is not tabular (images, free text, PDFs) — say so and stop.

• The user only wants an analysis or a chart from data they say is already
  clean — do the analysis; offer a profile, do not re-clean unasked.

• Any row deletion or value change is legally or medically sensitive — do
  Steps 1–2, then require explicit human approval for every risky rule.

Workflow — always in this order

Step 1 — Profile first, touch nothing

Report, before changing anything:

• File name, rows, columns, file size if known.

• Per column: name, guessed type (text / number / date / identifier),
  missing count and %, number of unique values, 3 sample values.

• Whole dataset: duplicate rows after normalisation count, duplicate column names,
  mixed-type columns, inconsistent date formats, stray whitespace,
  empty rows / columns.

Use python scripts/profile.py <file.csv> if Python is available —
it profiles and plans without writing anything.

Step 2 — Present the cleaning plan and get a yes on the risky parts

Write the plan as a table: Issue found | Rows/values affected | Proposed
fix | Risk.
Then stop and ask about anything in the Risky list below. Safe fixes
(whitespace trim, column-name tidy, date format standardisation to
ISO = International Organization for Standardization format YYYY-MM-DD)
may proceed after the plan is shown. Risky fixes need an explicit yes.

Risky — always needs approval, with the default being DO NOT DO IT:

• Filling (imputing) missing values with mean / median / mode / a constant.

• Dropping rows or columns (other than duplicate rows after normalisation and fully
  empty rows, which must still be counted and logged).

• Merging categories ("Hyd", "hyd.", "Hyderabad" → one) or fixing
  spellings — show the mapping table first and wait.

• ANY ambiguous date: 03/04/2026 could be 3 April (DD/MM/YYYY, the normal
  Indian order) or March 4 (MM/DD/YYYY). Never guess an ambiguous date.
  If a date column contains no value that proves the order (a day > 12),
  leave ambiguous values UNCHANGED, log them as
  ambiguous_date_NOT_converted_user_decision_needed, and list them under
  Questions in the report. Convert a date only when the order is proven by
  the column itself, or the user has answered.

Protected — never convert, even if they look numeric:
Identifier columns: anything named or behaving like id, roll, phone,
mobile, pin, pincode, code, account, aadhaar. Detection is
TOKEN-based, never substring: student_id and pin_code are identifiers,
but Fees Paid, Candidate, Valid Till and Middle Name are NOT —
substring matching misclassified all of them in adversarial testing. Numbers here are
names, not quantities: converting them destroys leading zeros and can
corrupt phone numbers and PIN codes. Clean them as text only (trim spaces).

India-specific rules (built in, not optional)

• Missing markers: NA, N/A, -, nil, NULL (plus -- and empty)
  mean missing. Deliberately NOT markers: nan and none — 'Nan' is a
  person's name and was being erased in testing; when in doubt, a value is data in Indian form/portal exports. Normalise
  every one to truly empty, and count them per column.

• Money and large numbers: values arrive as ₹1,20,000 (Indian lakh
  grouping), Rs. 4,500, INR 25000. Strip the currency symbol/prefix and
  the commas — Indian and western grouping both — and log each strip.
  Never leave a currency column as text.

• PIN / phone / ID columns are ALWAYS read as string. PIN codes, phone
  numbers, roll numbers, account and ID columns keep leading zeros exactly
  (001234 stays 001234). This is a hard rule, not a detection result.

• Accounting negatives: (1,200) is read as -1200. That is the
  accounting convention — an ASSUMPTION, not a fact about your file — so it
  is always logged per cell with reason accounting_parentheses_mean_negative,
  and the report lists it under Known Limitations.

• State names: normalise state columns ONLY via
  references/state_names.csv (official renames and known variants:
  Orissa → Odisha, Uttaranchal → Uttarakhand, Pondicherry → Puducherry,
  abbreviations like TN → Tamil Nadu). A canonical name in ANY case
  (odisha, GOA) also normalises to the canonical spelling. A state value not in the mapping is
  left unchanged and reported — do not invent a normalisation.

Step 3 — Clean into a NEW file, original untouched

Preferred: run the bundled scripts (standard library only), which enforce
all of this — scripts/profile.py, then scripts/clean.py, then
scripts/verify.py:

python scripts/profile.py input.csv
python scripts/clean.py input.csv --out-dir cleaned/
python scripts/verify.py input.csv cleaned/

clean.py refuses to overwrite the input, and writes only safe fixes by
default: trim whitespace, normalise empty markers (see India rules below) to
truly empty, standardise column names to snake_case, parse proven date
columns to YYYY-MM-DD, normalise numbers and currency, and move duplicate
rows to removed_rows.csv — logging every changed cell.
If Python / the script is not available, do the same fixes by hand or with
pandas, following references/cleaning-rules.md, and produce the same
three outputs manually.
Outputs, in a different folder from the original: <name>_cleaned.csv,
<name>_report.md, change_log.csv (CELL-LEVEL: one row per changed cell —
row, column, old_value, new_value, reason; row is the 1-based data-row
number in the original, row 0 = header) and removed_rows.csv (every dropped
row with its full original values — rows are moved, never deleted).
Then run scripts/verify.py: it reconstructs the original using ONLY the
cleaned file + changelog.csv + removedrows.csv and confirms it matches
cell-by-cell. If verify fails, silent changes exist — say so, do not ship.

Step 4 — Before / after statistics

Always report a small table:
| Measure | Before | After |
Rows, columns, missing values (total and per affected column),
duplicate rows, date columns standardised, numeric columns fixed.
Reconcile the rows: before − duplicates removed − empty rows removed = after.
If the numbers do not reconcile, say so — do not present a clean result.

Step 5 — Change log (the audit trail) — cell level, machine-checkable

change_log.csv, one row per changed CELL (not per rule):
row, column, old_value, new_value, reason — row = 1-based data-row number
in the original file, row 0 = a header rename. Reason is a short machine
label (whitespace_trimmed, date_standardised_to_iso_from_%d/%m/%Y, …).
This exact format is what scripts/verify.py uses to reconstruct the
original, so an agent producing its own log must match these columns.
Mask sensitive values in any human-readable summary of the log (show the
shape of the change for phone/ID columns), while the CSV log itself stays
with the user's own files, next to their data.

Step 6 — Hand over, with what was NOT done

End with: (1) the cleaned file, report and change log, (2) every issue you
found but did not fix and why (needs approval, ambiguous, or out of scope),
(3) one line the user can use to undo everything: "Delete the cleaned files;
your original <name> was never changed."

Hard rules

1. Never overwrite, rename, move or delete the original file.
2. No silent imputation, deletion or category merging — plan, approval, log.

3. Never invent values. A missing value you cannot derive from the row
   itself stays missing and is reported, not filled with a plausible guess.

4. Never treat identifiers as numbers (see Protected list, Step 2).

5. Count everything. "Some duplicates were removed" fails this skill;
   "14 duplicate rows after normalisation removed (rows 31, 58, … in the original)" passes.

6. Dates: one output format only — YYYY-MM-DD. Log the input formats
   you found and flag ambiguous day/month order instead of guessing quietly.

7. If the CSV is malformed (ragged rows, broken quotes, wrong encoding),
   report the exact rows that fail to parse in Step 1 and stop. Do not
   quietly skip broken rows to make the file load.

8. Large files: if the file is too large to load fully, profile a stated
   sample plus full-file counts, and say which numbers are sample-based.

Known limitations (state these in every report)

• pandas eats names by default. If you clean with pandas instead of the
  bundled scripts, read_csv treats Nan, NaN, None, NULL and more
  as missing AUTOMATICALLY. You MUST pass keep_default_na=False and then
  apply only this skill's marker list yourself — otherwise a person named
  Nan silently becomes a missing value, logged nowhere. The bundled scripts
  use only the standard library and treat nan/none as DATA for this reason.

• If a file genuinely comes from a pandas export where NaN means missing,
  that reclassification needs the user's approval and a log entry — it is
  never the default.

• (1,200) → -1200 is an accounting-convention assumption (see above).

Evals bundled with this skill

evals/ contains 10 dirty CSVs with hand-written ground truth and
evals/score.py, which measures: silent unlogged changes, rows dropped
without notice, leading zeros preserved, and ground-truth match %.
An agent (or the scripts) passes a case only at 100% match with 0 silent
changes and 0 unnoticed drops. Run it before claiming the skill works.

Why the original angle matters

Anyone can delete duplicates. Trust comes from being able to answer, weeks
later: "What exactly changed between the file I gave you and this one?"
The plan, the reconciliation and the change log are the product — the
cleaned CSV is just the by-product.