CSV data cleaner

---
name: csv-data-cleaner
description: "Profiles and cleans messy CSV files: trims whitespace, normalizes dates and numbers, removes duplicates and reports what changed. Use when the user shares a CSV that needs cleaning or asks to fix spreadsheet data."
---

# CSV data cleaner

## Workflow

1. Profile first: run `python scripts/profile_csv.py input.csv` and read the report (columns, types, empty cells, duplicates).
2. Show the user the issues found and the cleaning plan. Wait for confirmation before changing data.
3. Clean in this order: trim whitespace → normalize empty values → fix types (dates to ISO 8601, numbers without thousands separators) → remove exact duplicate rows.
4. Write the result to a **new** file (`input.clean.csv`); never overwrite the original.
5. Report: rows before/after, changes per column, and any values that could not be parsed.

## Rules

- Never drop rows silently; list every removed row count with the reason.
- Ambiguous dates (03/04/2024): ask which format the data uses.
- Keep the original column order and header names.

Review `scripts/profile_csv.py` before running it.

"""Prints a profile of a CSV file: columns, empty cells, inferred types and duplicate rows."""
import csv
import sys
from collections import Counter


def infer(values):
    kinds = set()
    for v in values:
        try:
            float(v.replace(",", ""))
            kinds.add("number")
        except ValueError:
            kinds.add("text")
    return "/".join(sorted(kinds)) or "empty"


def main(path):
    try:
        with open(path, newline="", encoding="utf-8-sig") as f:
            rows = list(csv.reader(f))
    except (OSError, UnicodeDecodeError) as e:
        sys.exit(f"Cannot read {path}: {e}")
    if not rows:
        sys.exit("The file is empty.")
    header, data = rows[0], rows[1:]
    print(f"Rows: {len(data)}  Columns: {len(header)}")
    for i, name in enumerate(header):
        column = [r[i].strip() if i < len(r) else "" for r in data]
        filled = [v for v in column if v]
        print(f"- {name!r}: {len(column) - len(filled)} empty, type {infer(filled)}")
    duplicates = sum(n - 1 for n in Counter(tuple(r) for r in data).values() if n > 1)
    print(f"Duplicate rows: {duplicates}")


if __name__ == "__main__":
    if len(sys.argv) != 2:
        sys.exit("Usage: python profile_csv.py <file.csv>")
    main(sys.argv[1])