I get monthly CSV exports from a vendor and the columns silently drift: a header gets renamed, a numeric column arrives with stray $ and commas, dates flip between MM/DD and DD/MM. My old script assumed the shape and produced confidently wrong numbers.
The prompt I use now treats the input as hostile: it asserts the schema first, coerces types explicitly, quarantines bad rows instead of dropping them silently, and prints a data-quality report. The quarantine-don't-drop rule is what earns trust, nothing disappears without a trace.
Would love a cleaner way to prompt for the date-parsing ambiguity, I currently force an explicit format and fail loudly rather than let pandas guess.
Write a Python data-cleaning script (pandas) that reads `{INPUT_CSV}`, validates and cleans it, and writes `{OUTPUT_PARQUET}` plus a data-quality report. Treat the input as untrusted.
Steps:
1. Schema assertion: define the expected columns, dtypes, and which are required. If columns are missing/extra/renamed, fail with a diff of expected vs. actual. Do not silently proceed.
2. Explicit type coercion: strip currency symbols/thousands separators before parsing numerics; parse dates with an EXPLICIT format ({DATE_FORMAT}) and never rely on pandas' guesser; normalize whitespace and casing on categoricals.
3. Row-level validation with named rules (e.g. amount >= 0, date within {MIN_DATE}..{MAX_DATE}, id matches `{ID_REGEX}`). Rows that fail are written to `{QUARANTINE_CSV}` with a `_reason` column, NOT dropped silently.
4. Deduplicate on `{KEY_COLS}` keeping the last by `{ORDER_COL}`; report how many dupes were removed.
5. Report: print rows in, rows out, rows quarantined, and a per-column null/unique/min/max summary. Assert that rows_in == rows_out + rows_quarantined so nothing vanishes.
6. Idempotent: re-running on the same input produces byte-identical output; sort deterministically before writing.
Use vectorized operations, no `iterrows`. Keep the pipeline as small pure functions so each rule is unit-testable. Fail with exit code 1 and a clear message if any hard assertion breaks.