Verified against ChatGPT · 2026-07-23
Turn a messy DataFrame into a documented, reproducible cleaning pipeline
A prompt for building a pandas cleaning pipeline as small, named, chainable functions with logged row drops and a schema check at the end, instead of one long unexplained block of reassignments.
The prompt
Ready to copy — highlighted parts are example details you can swap.
Write a pandas data-cleaning pipeline for the DataFrame described below. The output must be a set of small, named, testable functions chained together — not one long block of unexplained df[...] reassignments that only make sense read top to bottom in one sitting. INPUT DATA orders.csv, ~200k rows: order_id, customer_email, order_date (string), amount, status KNOWN ISSUES TO FIX duplicate order_id rows, order_date in mixed formats, some amount values as "$1,200.00" strings TARGET SCHEMA order_id: unique int; order_date: datetime64; amount: float, no nulls; status: one of a fixed set of values MEMORY CONSTRAINT Runs on a CI worker with 4GB RAM; the CSV is currently ~180MB but expected to grow 3x within a year. REQUIREMENTS 1. Each cleaning step is its own function taking a DataFrame and returning a DataFrame (e.g. def drop_duplicate_orders(df: pd.DataFrame) -> pd.DataFrame), so the pipeline reads as df.pipe(step_a).pipe(step_b)... — reviewable and testable step by step, not one monolithic function nobody can unit test in isolation. 2. Never mutate the input DataFrame in place inside a step; return a new one (with .copy() where needed) so steps stay composable and order-independent bugs are easier to spot — a step that mutates in place breaks the moment someone reorders the pipe() chain. 3. For every row dropped or value changed, log a count — a silent drop of bad rows is a data-loss bug wearing a clean-code costume, and a "cleaning" step that drops 40% of rows without a trace is worse than the messy data it replaced. 4. Validate the final shape against order_id: unique int; order_date: datetime64; amount: float, no nulls; status: one of a fixed set of values: correct dtypes (don't leave a date column as object), no unexpected nulls in required columns, and fail loudly with a clear message if the result doesn't match, rather than returning a DataFrame that's quietly wrong. 5. Use vectorized pandas operations; only drop to apply() with a Python-level loop if you can state why a vectorized approach isn't possible, and if Runs on a CI worker with 4GB RAM; the CSV is currently ~180MB but expected to grow 3x within a year. rules out loading the whole thing at once, say explicitly which steps would need chunked or streaming reads instead. 6. Give every step function a short docstring stating which entry in duplicate order_id rows, order_date in mixed formats, some amount values as "$1,200.00" strings it fixes — a reviewer should be able to match each function to a known problem without re-deriving what it does from the code alone. 7. Order the steps deliberately, not incidentally: a step that depends on another step's output (parsing dates before filtering on a date range, say) must come after it, and any such ordering dependency should be stated in a comment so nobody reorders the pipe() chain later assuming the steps are independent when they aren't. OUTPUT FORMAT 1. Each step function, in order, with its logging and its docstring naming the issue it fixes. 2. The pipe() chain that composes them. 3. One validation function that checks the result against order_id: unique int; order_date: datetime64; amount: float, no nulls; status: one of a fixed set of values and raises if it doesn't match. 4. One sentence on whether Runs on a CI worker with 4GB RAM; the CSV is currently ~180MB but expected to grow 3x within a year. is actually satisfied by this design, or what would need to change if not. 5. Any ordering dependency between steps, stated explicitly, or confirmation the steps are genuinely order-independent.
Customize
Optional — swap in your own details for the highlighted parts above.
Why this works
Composing the pipeline with pipe() over small, pure, named functions means each step can be reviewed, unit-tested, and reordered independently — a single 40-line block of chained df[...] assignments cannot be tested at all except end-to-end, which hides exactly which step introduced a bug when the final output looks wrong three transformations later. Requiring a logged count for every row dropped or value changed targets the single most common and most dangerous pandas cleaning mistake: a filter or dropna() that silently removes far more data than intended, discovered weeks later when a downstream report looks wrong with no trail back to the cause, at which point nobody remembers which of the six cleaning steps did it. The schema validation at the end catches a specific, very real pandas failure mode — a date column that parsed as object instead of datetime64 because one row had a malformed value that to_datetime silently coerced to NaT without raising, which then breaks every downstream .dt accessor call with a confusing error far from the actual cause. The memory_constraint field exists because the correct pandas approach genuinely changes shape once the dataset stops comfortably fitting in RAM — vectorized operations on a full in-memory DataFrame are usually the right default, but a pipeline that will run against triple the current row count on a memory-capped CI worker needs chunked reads (chunksize in read_csv) or a columnar engine considered from the start, not bolted on after the first out-of-memory crash in production. Requiring each step's docstring to name the specific known_issues entry it addresses, and requiring ordering dependencies to be stated rather than left implicit, matters for the same underlying reason pipe() composition matters in the first place: the whole design's value is that a future engineer can reorder, remove, or add a step with confidence, and that confidence depends entirely on dependencies between steps being written down rather than living only in the current order of a pipe() chain that looks, on the surface, like a list of independent, freely reorderable transformations.
What you get back
def parse_order_dates(df: pd.DataFrame) -> pd.DataFrame: df = df.copy() before_na = df["order_date"].isna().sum() df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce") dropped = df["order_date"].isna().sum() - before_na if dropped: logger.warning(f"parse_order_dates: {dropped} rows had unparseable order_date, now NaT") return df cleaned = ( raw_df .pipe(drop_duplicate_orders) .pipe(parse_order_dates) .pipe(clean_amount_column) .pipe(validate_against_schema) ) Memory: satisfied for the current ~180MB file on a 4GB worker; flagged that clean_amount_column's regex-based string cleanup would need to move to a chunked read_csv(chunksize=...) loop once the file exceeds roughly 1GB.
Verified against
ChatGPT GPT-5.1 · 2026-07-23
Changelog
- 2026-07-23 — Initial publish, verified against ChatGPT (GPT-5.1) on pandas 2.2.
Need this built into your business?
If a prompt isn't enough — custom software, built and maintained for you — that's Scult's day job.
EXPLORE CUSTOM SOFTWARE
