Excel Didn't Die — It Moved Into Your Terminal: 12 Pandas Prompts That Turn Messy CSVs Into a Clean DataFrame
Every analyst knows the ritual: open a 40 MB CSV exported from a CRM, discover that dates are stored as "12/03/2026 00:00:00 AM", that three columns are named Unnamed: 5, and that someone typed "N/A", "n/a", and "—" into the same revenue field. You spend two hours clicking through filters in Excel, build a pivot table, and then the source file updates next Monday and you start over.
Pandas fixes the repeatability problem — but writing the cleaning code itself is still manual work. That's where LLMs earn their keep: they're excellent at translating a fuzzy description of your data mess into working pandas code, and terrible at guessing your business logic. The prompts below exploit exactly that split.
All examples use real pandas 2.x API (pd.read_csv, DataFrame.pipe, pd.api.types, df.convert_dtypes). If you want to verify any method signature, the official docs live at pandas.pydata.org/docs. For the missing-data conventions used below, see the pandas guide on Working with missing data.
1. The Data Profiler — Before You Clean, Understand
Why it works: You can't write a cleaning plan for data you haven't seen. This prompt forces the model to output a structured audit instead of jumping straight to code.
Prompt:
You are a senior data engineer. I will paste the output of df.info(), df.head(20),
and df.describe(include='all'). Do NOT write cleaning code yet.
Produce a table with columns: column_name, inferred_semantic_type, suspected_issues,
proposed_fix, risk_if_wrong. Flag any column where the dtype looks wrong for its
semantic type (e.g. numeric stored as object). Be explicit about what you cannot
determine from the sample.
Usage: Run df.info() and df.describe(include='all') on your raw load, paste the output. You get a triage table back in seconds instead of discovering problems mid-pipeline.
2. Parse Dates That Refuse to Parse
Why it works: Date parsing is the #1 source of silent corruption. The model knows about dayfirst, format, and the errors= argument.
Prompt:
Column `order_date` contains mixed formats: '2026-03-12', '12/03/2026',
'Mar 12, 2026', and some empty strings. Write pandas code to parse it into
datetime64 with explicit format detection. Show me a value_counts() of the
original distinct patterns first so I can confirm the day/month order.
Do not use errors='coerce' until we've logged the unparseable rows.
The key instruction is show me the patterns first. Ambiguous 12/03/2026 is either 12 March or 3 December depending on locale — the model can't know, but it can force you to check.
3. Normalize Column Names Into a Predictable Schema
Prompt:
My columns are: ['Order ID ', 'customer_name', 'Total Amount ($)',
'Unnamed: 4', 'createdAt']. Write a function `normalize_columns(df)` that:
- strips whitespace and lowercases
- converts to snake_case
- removes currency symbols and units from names
- drops columns matching r'^unnamed'
Return the new column list and assert no duplicates remain.
Usage: df = df.pipe(normalize_columns). This becomes the first cell of every notebook, so downstream code never breaks when the export header changes casing.
4. Build a Cleaning Pipeline With pipe()
Why it works: LLMs happily generate ten disconnected snippets. Asking for a single composable pipeline is what makes the result maintainable.
Prompt:
Rewrite my cleaning steps as a single pandas pipeline using DataFrame.pipe().
Each step must be a named function with a docstring, taking and returning a
DataFrame. Include: column normalization, date parsing, numeric coercion,
category casting, and deduplication. I want to be able to call
df.pipe(clean_pipeline) and get a typed, deduplicated frame.
def clean_pipeline(df: pd.DataFrame) -> pd.DataFrame:
return (df
.pipe(normalize_columns)
.pipe(parse_dates)
.pipe(coerce_numeric)
.pipe(cast_categories)
.pipe(drop_duplicates))
5. Standardize Missing Values Across Many Representations
Prompt:
My dataset uses '', 'N/A', 'n/a', '-', 'null', 'None', and 'unknown' to mean
missing. Write code that replaces all of these with pd.NA across all object
columns, then reports how many values were changed per column. Do not touch
columns where 'unknown' is a legitimate category — I'll list those: [status].
This is the prompt where you must inject domain knowledge. unknown in a status column is data; in a phone column it's missing. The model can't infer that.
6. Type Inference You Can Actually Trust
Prompt:
Use df.convert_dtypes() and compare the resulting dtypes to the original.
For every column that changed, explain whether the change is safe.
Pay special attention to integer columns containing NaN — explain why they
become Int64 instead of int64 and what that costs in memory.
Pandas' nullable extension dtypes (Int64, string, boolean) are the correct answer for real-world data, but the memory trade-off is real. Forcing the model to explain it keeps you honest. Background: Nullable integer data type.
7. Deduplicate With a Business Key
Prompt:
I have duplicates on `email` but the rows differ in `updated_at`. Write code
that keeps the row with the latest `updated_at` per email, and separately
returns a DataFrame of the discarded rows so I can audit them. Use
sort_values + drop_duplicates(keep='last'), not groupby.apply.
Usage: drop_duplicates(keep='last') after sorting is dramatically faster than groupby().apply() on large frames — a detail worth having the model commit to explicitly.
8. Split One Messy Column Into Several
Prompt:
Column `address` contains free text like 'Berlin, DE 10115' and
'Munich, DE 80331'. Split it into city, country_code, postal_code using a
vectorized approach (str.extract with a named-group regex). Show me the rows
that fail to match so I can handle them manually.
Named groups in str.extract return a DataFrame directly:
parts = df['address'].str.extract(r'(?P<city>[^,]+),\s*(?P<cc>[A-Z]{2})\s*(?P<zip>\d{5})')
9. Validate Against Rules, Not Vibes
Prompt:
Write a validation function using assertions that checks:
- no nulls in [order_id, customer_id]
- order_date <= ship_date
- amount >= 0
- country_code in ISO-3166 alpha-2 (I'll supply the list)
Return a report of violations instead of raising, so the pipeline can log and
continue. Print counts per rule.
This turns your cleaning script into a contract. Run it in CI on every new export.
10. Reshape Wide Exports Into Tidy Data
Prompt:
My CSV has one column per month: 'revenue_2026_01' ... 'revenue_2026_12'.
Convert it to long format with columns [id, month, revenue] using
pd.wide_to_long or melt — pick whichever fits and explain why. Parse the
month into a real period dtype.
Tidy long format is what makes groupby, plotting, and joins trivial. See Reshaping and pivot tables.
11. Generate a Reproducible Cleaning Script
Prompt:
Take everything we did interactively and write a standalone .py script:
- reads from a path passed as argv[1]
- applies clean_pipeline
- writes to parquet with pyarrow
- logs row counts before/after each step using the logging module
- no notebook-only syntax (no display(), no cell magics)
This is the difference between a one-off analysis and a pipeline. Parquet preserves dtypes, so the next step in your stack doesn't re-trigger the whole mess.
12. Write Tests for the Cleaner Itself
Prompt:
Generate pytest tests for clean_pipeline covering: mixed date formats,
all missing-value representations, duplicate emails with different timestamps,
and a column that is entirely NaN. Use small inline DataFrames as fixtures.
Assert on dtypes, not just values.
Data cleaning code without tests is a landmine. This prompt is the one that pays for itself the second time the source format silently changes.
A Note on Where the Model Stops Helping
LLMs are strong at syntax recall — the exact argument name, the right str accessor, the nullable dtype. They are weak at your semantics: whether 12/03 is March or December, whether unknown is a category or a gap, whether two rows with the same email are a duplicate or a legitimate re-purchase.
The pattern that works is consistent: let the model write the code, but force it to surface the assumptions. Every prompt above asks for a report, a diff, or a list of unparseable rows before it asks for a fix. That single habit is what separates a prompt that saves you an afternoon from one that quietly corrupts your dataset.
Start with prompt #1 on your ugliest CSV. If the audit table comes back and you immediately see three problems you'd have missed, you already know the rest of the list will hold up.
Comments