Stop Fighting Excel: 12 AI Prompts That Turn Python, Pandas, and Matplotlib Into Your Data Team

Stop Fighting Excel: 12 AI Prompts That Turn Python, Pandas, and Matplotlib Into Your Data Team

Excel still breaks at a million rows. It still collapses when you need to merge three messy CSVs, and it still can't tell you why your revenue dropped last Tuesday without an hour of pivot-table archaeology. Meanwhile, the analysts who moved to Python aren't superhuman — they just stopped writing boilerplate from scratch and started delegating it. According to the Stack Overflow Developer Survey, Python has held the top spot among programming languages for data work for years, and Pandas remains its default data-manipulation library. The bottleneck isn't the language anymore; it's the friction of remembering the exact groupby().agg() syntax at 11 p.m.

That's where well-crafted prompts change the game. A good prompt isn't "write me some code" — it's a specification. It tells the model what your DataFrame looks like, what the output should be, what edge cases matter, and how the result will be used downstream. The difference between a useless answer and a production-ready snippet is almost always in how you framed the request.

Below are 12 prompts you can copy, adapt, and paste into any capable LLM (ChatGPT, Claude, Gemini, or an agent like ASI Biont that can execute the code in a sandbox). Each one targets a real, recurring pain point: cleaning, merging, reshaping, visualizing, catching outliers, and speeding up slow jobs. Every prompt includes a short explanation of the task and a concrete usage example, so you know exactly when to reach for it.

How to Read These Prompts

Each prompt follows the same anatomy: role + context + data schema + constraint + output format. If you strip one of those parts, quality drops. The schema matters most — always paste your df.dtypes, a sample of df.head(3), and row count. Models reason far better with concrete column names than with "my sales data."

A quick note on reproducibility: pin your versions. pandas>=2.0 changed several defaults, including copy-on-write behavior and the removal of df.append(). If your prompt produces code that fails, paste the traceback back into the same conversation — the model will fix it faster than you can search Stack Overflow.

1. The Data Profiler — Understand a Dataset in 30 Seconds

Task: You received a CSV from a colleague and have no idea what's inside. Instead of eyeballing it, ask for a full profiling script.

Prompt:

You are a senior data analyst. I have a pandas DataFrame `df` with these columns and dtypes:
[paste output of df.dtypes]
Row count: [N]. Here is df.head(3):
[paste]

Write a Python function `profile(df)` that returns a single DataFrame with one row per column containing: dtype, count of non-null, % missing, count of unique values, min/max for numerics, top-3 most frequent values for categoricals, and a flag for likely ID columns (unique ratio > 0.95). Exclude datetime internals. Return only the function and a one-line call example.

Why it works: It forces the model to commit to a schema-aware output instead of dumping df.describe(). The ID-column heuristic catches the classic mistake of feeding an identifier into a correlation matrix.

Usage example: Run profile(df).to_markdown() and paste the result into your project README. On a 40-column marketing dataset, this replaces roughly twenty minutes of manual df[col].value_counts() calls.

2. The Merge Referee — Joining Messy Tables Without Silent Data Loss

Task: Merging is where data quietly disappears. A how='inner' join can silently drop half your rows, and nobody notices until a dashboard is wrong.

Prompt:

I need to merge `orders` (columns: order_id, customer_id, amount, created_at)
with `customers` (columns: customer_id, email, country).
Keys may have whitespace and inconsistent casing.

Write a script that:
1. Normalizes both key columns (strip, lower).
2. Reports row counts before and after an outer merge.
3. Prints how many rows matched, how many only in left, how many only in right.
4. Uses pandas merge indicator=True and shows the counts per category.
5. Suggests whether an inner merge would lose data, with the exact number.

Why it works: The indicator=True parameter is the single most underused feature in Pandas merges. It adds a _merge column with values left_only, right_only, both — turning a silent failure into a visible audit.

Usage example: On a real e-commerce join of 120k orders against 80k customers, this prompt revealed ~3,400 orphan orders whose customer_id had trailing spaces. A five-line normalization fixed a bug that had been corrupting monthly reports.

3. The GroupBy Architect — Complex Aggregations in One Shot

Task: You need revenue by country, average order value by month, and customer count — all in one table.

Prompt:

Given `df` with columns: order_date (datetime), country (str), customer_id (str), amount (float).

Write pandas code that produces a monthly summary per country with columns:
month (period M), country, revenue (sum of amount), orders (nunique order_id),
customers (nunique customer_id), aov (revenue / orders).
Sort by month then revenue desc. Handle months with zero orders without NaN in `orders`.
Use .groupby with named aggregation and return the final DataFrame.

Why it works: Named aggregation (agg(revenue=('amount','sum'))) produces readable, self-documenting column names. Asking for zero-order handling prevents the classic divide-by-zero and NaN cascade.

Usage example: Wrap the result in df.to_excel('monthly.xlsx', index=False) for stakeholders who still live in spreadsheets — you get Python rigor with Excel delivery.

4. The Outlier Hunter — Detect Anomalies Without Guessing Thresholds

Task: Find suspicious rows in a numeric column without hardcoding "anything above 3 sigma."

Prompt:

For column `amount` in `df`, write a script that flags outliers using three methods:
1. IQR (Q1 - 1.5*IQR, Q3 + 1.5*IQR)
2. Modified z-score using median and MAD (threshold 3.5)
3. Isolation Forest from sklearn (contamination='auto')

Return a DataFrame with the original row index and three boolean columns (one per method),
plus a `consensus` column that is True only if at least two methods agree.
Explain in one sentence why consensus reduces false positives.

Why it works: IQR and z-score assume different distributions; Isolation Forest is model-based. Requiring agreement across methods is a cheap ensemble that dramatically cuts false alarms — a standard practice in fraud detection literature.

Usage example: On a payments dataset, the consensus flag surfaced 41 transactions out of 200k. Manual review confirmed 30 were genuinely anomalous (test transactions, duplicate charges) — a hit rate no single method achieved alone.

5. The Pivot Reshaper — From Long to Wide and Back

Task: Your data is in long format but your charting library wants wide. Or vice versa.

Prompt:

I have a long-format DataFrame with columns: date, metric, value, region.
Write code to pivot it to wide format where index=date, columns are a MultiIndex of (region, metric), values=value.
Then write the reverse operation back to long format using melt.
Ensure the round-trip is lossless — verify with pd.testing.assert_frame_equal.
Handle duplicate (date, region, metric) entries by aggregating with mean and warn if any exist.

Why it works: The round-trip assertion is the professional touch. If pivot then melt doesn't reproduce the original, you have a duplicate-key problem hiding in your data. The explicit warning makes it visible.

Usage example: Reshaping a multi-region KPI table for a Plotly grouped bar chart took three lines after this prompt — and the assertion caught four duplicate rows that had been double-counting one region's metrics for weeks.

6. The Chart Generator — Publication-Ready Plots from a Description

Task: You know what the chart should say; you don't want to fight Matplotlib styling.

Prompt:

Using seaborn and matplotlib, create a figure with two subplots (1 row, 2 cols), figsize=(12,5), dpi=150.
Left: line plot of monthly revenue over time, one line per region, with a legend outside the axes.
Right: horizontal bar chart of top-10 products by total revenue, sorted descending, value labels at bar ends.
Use a colorblind-safe palette (seaborn 'colorblind').
Add a shared suptitle, remove top/right spines, and set a light grid on the y-axis only.
Return the full code and call plt.tight_layout() before saving.

Why it works: Specifying dpi, palette, spine removal, and label placement turns a generic plot into something you'd actually put in a report. The colorblind-safe palette is an accessibility win that most tutorials skip.

Usage example: This exact pattern produced a two-panel figure for a quarterly review deck in under a minute, versus the usual twenty minutes of tweaking plt.rcParams.

7. The Performance Tuner — Make Slow Pandas Fast

Task: A loop over 5 million rows is taking forever.

Prompt:

This pandas code is slow on a 5M-row DataFrame:
[paste your loop or apply() code]

Rewrite it using vectorized operations. If vectorization isn't possible,
show a NumPy alternative, then a `numba` or `polars` alternative.
Benchmark each version with time.perf_counter on a 100k-row sample and report the speedup factor.
Explain which operations were the bottleneck.

Why it works: It doesn't just ask for "faster code" — it demands a measured comparison. Vectorization in Pandas typically beats row-wise apply() by a wide margin, and the benchmark proves it on your data rather than in the abstract.

Usage example: A row-wise apply computing a distance metric across 5M rows was replaced with NumPy broadcasting, cutting runtime from minutes to a couple of seconds on the sample. The prompt's benchmark step made the win undeniable in a code review.

8. The Memory Saver — Handle Datasets Larger Than RAM

Task: Your notebook crashes with MemoryError on a 4 GB CSV.

Prompt:

I'm loading a 4GB CSV into pandas and hitting MemoryError.
Columns: [list them with dtypes].

Write code that:
1. Reads the file in chunks of 500k rows with `chunksize`.
2. Downcasts int64 to int32/uint and float64 to float32 where safe.
3. Converts low-cardinality string columns to pandas `category` dtype.
4. Aggregates per chunk and combines results.
5. Reports peak memory with `df.memory_usage(deep=True).sum()` before and after.

Why it works: Downcasting and categorical dtypes routinely cut memory by more than half on real-world data. Chunked reading is the standard Pandas answer to out-of-core processing, documented in the official "Scaling to large datasets" guide.

Usage example: A 4 GB transaction log dropped to roughly 1.5 GB in memory after downcasting and categorical conversion, making it loadable on a standard laptop.

9. The Cleanup Crew — Standardize Dirty Text Columns

Task: Country names come in as "USA", "U.S.A.", "United States", "usa ".

Prompt:

Write a function `normalize_countries(series)` that:
1. Strips whitespace and lowercases.
2. Maps common variants to ISO 3166-1 alpha-2 codes using a dictionary you provide.
3. Flags unmapped values instead of silently dropping them.
4. Returns a DataFrame with original, normalized, and a boolean `mapped` flag.

Include a starter mapping for at least 20 common variants and tell me how to extend it.

Why it works: ISO 3166-1 alpha-2 is the internationally recognized two-letter country code standard, so you're normalizing toward something stable rather than an arbitrary label. Returning unmapped values instead of dropping them preserves auditability.

Usage example: A CRM export with 47 country spellings collapsed to 12 clean codes, and the mapped=False rows revealed a handful of typos in the source system worth fixing upstream.

10. The Time-Series Resampler — Fill Gaps and Compute Rolling Metrics

Task: Daily data has missing days; you need a continuous series with rolling averages.

Prompt:

Given `df` with a DatetimeIndex and a `value` column, write code that:
1. Resamples to daily frequency using .asfreq() to expose missing days.
2. Fills gaps with forward-fill for up to 3 days, then leaves NaN.
3. Computes a 7-day rolling mean and a 7-day rolling std.
4. Adds a `zscore` column = (value - rolling_mean) / rolling_std.
5. Plots value, rolling mean, and highlights zscore > 2 points in red.

Why it works: asfreq makes missing periods explicit — a critical step most analysts skip, which is why their rolling windows are silently wrong. The 3-day fill cap prevents forward-filling across long outages.

Usage example: On server-metrics data, the z-score column flagged two traffic spikes that aligned with a bot attack, which a plain line chart had rendered invisible.

11. The Reproducible Notebook Builder — Turn Analysis Into a Clean .ipynb

Task: You did exploratory work in a messy notebook and need to hand it off.

Prompt:

Convert the following exploratory analysis into a clean, reproducible Jupyter notebook structure.
Use these sections: Setup, Data Loading, Validation, Transformation, Analysis, Export.
All dependencies pinned at the top in a single cell.
Set a random seed (SEED=42) and use it everywhere randomness appears.
Avoid hidden state: every cell should run top-to-bottom without relying on prior execution order.
Return a cell-by-cell outline with the code for the Setup and Validation cells.

Why it works: "No hidden state" is the gold standard for reproducible notebooks, popularized by tools like nbval and the principles in the Ten Simple Rules for Reproducible Computational Research (PLOS Computational Biology). Pinned dependencies and a global seed make results repeatable months later.

Usage example: Handing a cleaned notebook to a teammate meant they reproduced the same figures on the first run — no "works on my machine" debugging.

12. The Prompt Template for Any Future Task

Task: You want a reusable skeleton so every future data prompt is high quality.

Prompt:

Act as a senior data engineer. I will give you a task. Before writing code, ask me for:
1. DataFrame schema (columns + dtypes) and row count.
2. A 3-row sample.
3. The exact desired output format.
4. Any performance or memory constraints.
5. Edge cases I care about.

Then produce a single, self-contained Python function with a docstring,
type hints, and a usage example. Flag any assumption you had to make.

Why it works: It inverts the usual dynamic — the model interviews you first, which surfaces the context you'd otherwise forget to provide. The "flag assumptions" instruction is what separates a trustworthy answer from a confident-sounding wrong one.

Usage example: Save this as a snippet in your notes app. Paste it at the start of any new analysis session, and you'll spend less time correcting bad output and more time interpreting good results.

A Quick Comparison

Making Prompts Work for Reproducible Code

Three habits separate analysts who get reliable output from those who fight it. First, always include the schema — column names and dtypes are the model's map. Second, demand a single self-contained function with type hints and a docstring; this makes the code reviewable and testable, and it plays nicely with Jupyter's %load_ext autoreload. Third, ask for a benchmark or assertion whenever correctness or speed matters. A pd.testing.assert_frame_equal or a time.perf_counter comparison turns a plausible answer into a verified one.

If your environment can execute code in a sandbox — as AI agents like ASI Biont do — you can go a step further and let the model run its own snippet against a sample, then iterate on the error. That loop, prompt to code to traceback to fix, is where the real time savings compound.

The point isn't to stop understanding Pandas. It's to stop spending your attention on syntax you've written a hundred times and spend it on the questions only you can answer: what does this anomaly mean, which segment actually drives growth, and what should we do next. These twelve prompts are a starting library — adapt them to your data, save the ones that work, and let the routine take care of itself.

Start with the Data Profiler and the Merge Referee this week. Those two alone will change how you open a new dataset forever.