You paste a 300-line Pandas script into an LLM, ask it to speed things up, and get back code that looks tidy but silently drops rows on a merge or blows memory on a groupby. The problem is rarely the model — it is the prompt. Vague requests like "optimize this dataframe" produce vague output. Precise prompts that specify dtypes, null policy, and expected shape produce code you can actually ship.
Below is a working set of prompts I use for tabular work in Python: cleaning, joins, resampling, translating heavy Pandas into Polars, diagnosing memory leaks, and replacing apply with vectorized operations. Each one is written to be pasted as-is, with a concrete example of what to send and what to expect back.
Prompt 1: Make the LLM Declare the Schema Before It Writes Anything
The single most effective prompt is the one that stops the model from guessing. Ask it to restate the schema first, then code. This catches type mismatches (dates stored as strings, IDs as floats) before they cascade.
Prompt:
You are a senior data engineer. I will describe a DataFrame problem.
Step 1: restate the column names, dtypes, and null policy you infer.
Step 2: list edge cases (duplicates, NaN, mixed types) that could break the logic.
Step 3: only then write the code. Use pandas 2.x. Do not use inplace=True.
Usage example: send it a df.info() dump plus a one-line goal: "collapse orders to one row per user_id, keep the latest order_date, sum amount." You get back an explicit schema list, a note that user_id may arrive as int64 with NaN forcing float64, and then a sort_values().drop_duplicates() chain that is correct the first time.
Prompt 2: Force a GroupBy Chain That Respects NaN and Duplicates
groupby().agg() is where subtle bugs hide: sum() turns all-NaN groups into 0, mean() skips NaN by default, and dropna=True on the grouping key silently removes rows.
Prompt:
Write a groupby using pandas 2.x that:
- groups by [keys]
- computes: n_rows, sum of amount (min_count=1 so all-NaN stays NaN), max of order_date
- keeps groups where the key is NaN visible in the output
- returns a flat DataFrame with reset_index()
Explain each non-default parameter you use.
The min_count=1 detail is the non-obvious part: most models omit it, and you end up with zero-filled revenue for customers who never ordered. Ask for the explanation — it turns the prompt into a mini-lesson on aggregation semantics.
merge without a row-count check. Always ask the model to print len(left), len(right), and len(result) and assert the expected relationship — many-to-one should never grow the left frame.
Prompt 3: Diagnose a Join That Multiplies Rows
A classic failure: two tables joined on user_id where one has duplicate keys, and the result explodes. The prompt below makes the model reason about cardinality explicitly.
Prompt:
I have left_df and right_df joined on [keys]. Result has more rows than left_df.
1. Explain the cardinality of each side (1:1, 1:N, N:M).
2. Show me how to detect duplicate keys on both sides with code.
3. Give me a version that keeps left_df row count stable, and tell me what data I lose.
Example output: the model suggests left_df.merge(right_df.drop_duplicates(subset=keys, keep='last'), how='left', validate='many_to_one'). The validate argument is the real prize — it raises immediately if the assumption breaks.
Prompt 4: Convert a Heavy Pandas Script to Polars
Polars is lazy and columnar; a naive translation that keeps apply and chained assignments throws away the speedup. The prompt must demand lazy evaluation.
Prompt:
Translate this pandas code to Polars. Requirements:
- use pl.scan_parquet / pl.LazyFrame and .collect() only at the end
- replace apply with pl.Expr expressions
- keep column names identical
- add a comment wherever Polars semantics differ from pandas (null handling, dtype inference)
[paste pandas code]
Before (Pandas):
df['margin'] = df.apply(lambda r: r['revenue'] - r['cost'] if r['revenue'] else 0, axis=1)
After (Polars, lazy):
lf = lf.with_columns(
pl.when(pl.col('revenue').is_not_null() & (pl.col('revenue') != 0))
.then(pl.col('revenue') - pl.col('cost'))
.otherwise(0)
.alias('margin')
)
The trade-off: Polars gives you multi-core execution and lower memory on large files, but you pay in stricter dtypes (no silent object columns) and a smaller ecosystem of third-party helpers. Ask the model to flag every place where a Pandas-only library is called — those are your migration blockers.
Prompt 5: Explain a Memory Blowup
"My kernel died on a 2 GB CSV" is a prompt in disguise. Make the model compute the footprint instead of guessing.
Prompt:
A 2 GB CSV with 40M rows causes MemoryError in pandas.
1. Estimate in-memory size: rows x columns x bytes per dtype.
2. Identify the three biggest memory consumers (object columns, copies, merges).
3. Rewrite the pipeline using chunked reads or Polars scan, and show where peak memory drops.
Worked example: 40M rows × 10 columns × 8 bytes ≈ 3.2 GB just for numeric data; an object string column can be several times that because each Python string carries overhead. The model should recommend category dtype for low-cardinality strings and pl.scan_csv for out-of-core processing.
Prompt 6: Kill apply With Vectorization
Row-wise apply is the slowest common pattern. The prompt asks for a vectorized equivalent plus a benchmark.
Prompt:
Rewrite this .apply(axis=1) as vectorized pandas operations.
- show np.select or np.where version
- keep identical output for NaN inputs
- add a timeit comparison on 1M synthetic rows
[paste apply code]
Example: a lambda that buckets ages into categories becomes pd.cut(df['age'], bins=[0,18,35,60,120]). The model should warn you that np.where evaluates both branches, so for expensive operations np.select with default is safer.
apply depends on the previous row's result, ask the model for shift() or cumsum() instead — a vectorized rewrite will be wrong there.
Prompt 7: Build a Resample Pipeline With Timezone Awareness
resample breaks on naive vs aware timestamps and on gaps. Demand timezone handling explicitly.
Prompt:
Build a resample to weekly frequency on a timestamp column.
- convert to UTC first, store tz-aware
- aggregate: sum(amount), count(orders)
- fill missing weeks with 0 for count, NaN for sum
- show the index dtype in the output
This surfaces the classic trap: resample('W') labels weeks by their end date, so a Monday order lands in the previous bucket. Ask the model to state the labeling convention.
Prompt 8: Clean Messy Strings and Dates Safely
Prompt:
Clean these columns: phone, email, signup_date (mixed formats like '2024-03-01', '01.03.2024', 'March 1, 2024').
- use pd.to_datetime with format='mixed' and errors='coerce'
- report how many rows fail to parse
- normalize phones to E.164 with a regex, flag invalid ones
format='mixed' (pandas 2.x) is the pivot: without it, one bad format poisons the whole column. The prompt forces a parse-failure count so you see the damage before it reaches a model.
Prompt 9: Review My DataFrame Code for Silent Data Loss
Use the LLM as a second pair of eyes with a checklist, not an open-ended "review this."
Prompt:
Review this pandas code for silent data loss. Check specifically:
- merges without validate=
- dropna() called before a fill step
- .astype() that truncates floats to ints
- boolean indexing with & vs and
List each risk with line number and a fix.
astype(int) on a column that still has NaN — it raises, or if cast from float, silently rounds. Ask for Int64 (nullable) instead.
Prompt 10: Generate a Reproducible Test for the Transform
Every generated transform needs a test. Ask for it in the same breath.
Prompt:
Write a pytest test for this transform using a small inline DataFrame.
Cover: empty input, all-NaN column, duplicate keys, and a normal case.
Assert exact dtypes and row counts, not just values.
This is where LLMs earn their keep: writing four edge-case fixtures by hand is tedious, and the model does it in seconds — you just verify the assertions are honest.
Prompt 11: Choose Between Pandas, Polars, and DuckDB
Sometimes the right answer is not a rewrite but a different tool. Make the model argue.
Prompt:
Given: 5 GB Parquet, 200M rows, aggregations + one join, single machine, 16 GB RAM.
Compare pandas, Polars, and DuckDB on: peak memory, code complexity, and ecosystem risk.
Recommend one and name the failure mode that would flip the choice.
DuckDB often wins for SQL-shaped aggregations on files larger than RAM; Polars wins for complex column expressions; Pandas stays for small data and library compatibility. The flip condition — e.g. needing a pandas-only ML wrapper — is the useful part.
Prompt 12: Explain the Error, Not Just Fix It
When a KeyError or SettingWithCopyWarning appears, ask for the mechanism.
Prompt:
I got this traceback. Explain the root cause in two sentences, then give the minimal fix.
Then tell me how to detect this class of bug earlier in the pipeline.
[traceback]
Understanding that SettingWithCopyWarning stems from chained indexing on a slice teaches you to use .loc and .copy() deliberately — a lesson that prevents the next ten bugs.
When to Trust the Output and When to Re-Check
LLMs are reliable for boilerplate you already know how to verify: merge skeletons, resample scaffolding, string-cleaning regexes, and test fixtures. They are unreliable — and must be re-checked — on type inference, null semantics, and cardinality. Always inspect dtypes after a transform, assert row counts around joins, and run the model's own tests before merging.
The practical takeaway: a good prompt is a spec. If you can state the schema, the null policy, and the expected shape, the model writes correct code; if you cannot, no prompt will save you. Pair these with a review pass — the same discipline behind catching bugs before merge and the query-tuning mindset from SQL on steroids — and your data pipelines get faster without getting fragile.
Start with Prompt 1 on your next messy DataFrame. It costs thirty seconds and usually saves an afternoon of debugging a silent row-count drift.