Pandas Data Wrangling Without Silent Errors
Correct, fast pandas code for merging, cleaning, groupby, reshaping, and time series, with checks that catch silent row loss.
Before & after
Same prompt, same agent — the only difference is the skill.
Duplicate rows after a merge usually come from the join keys not being unique. Common causes: - One or both dataframes have duplicate keys - A many-to-many relationship between the tables - Not specifying the join type I'd check whether customer_id is unique in each dataframe using .duplicated() or .nunique(). If one side has duplicates, you'll get a row for each combination. You might need to deduplicate first or aggregate. Can you share the shape of your dataframes?
Duplicates after a merge are almost always a many-to-many key. Diagnose
before you merge — pandas even has a switch to make it fail loudly.
STEP 1 — CONFIRM WHICH SIDE
left['customer_id'].duplicated().sum()
right['customer_id'].duplicated().sum()
The side with a nonzero count is the culprit. Often it's the right
(e.g. one customer, many orders), which turns a 1-to-1 into a fan-out.
STEP 2 — LET PANDAS CATCH IT FOR YOU
df.merge(right, on='customer_id', validate='one_to_one')
validate raises if the relationship isn't what you claim. Use
'one_to_many' / 'many_to_one' to assert the real shape — this turns a
silent duplicate bug into a loud error at merge time.
STEP 3 — FIX BY INTENT
You want one row per customer, right has many → aggregate right first:
right.groupby('customer_id').agg(total=('amount','sum')).reset_index()
then merge. One-to-one, no fan-out.
You genuinely want the fan-out (one row per order) → the "duplicates"
are correct; the bug is downstream expecting uniqueness.
THE TRAP
Blindly .drop_duplicates() after the merge hides the real problem and
can drop legitimately distinct rows. Fix the join shape, don't paper
over it.
Share both dataframes' shapes and what one output row should represent,
and I'll write the exact merge.About this skill
Your pandas code runs without errors, but the numbers are wrong. A merge quietly doubled your rows, groupby dropped every row with a null key, ZIP codes lost their leading zeros, and weekly totals start on the wrong day. This skill writes pandas code that is correct by construction and proves it with assertions. It also fixes slow code by replacing row-wise loops with vectorized operations.
What it does
- Writes merges with explicit key normalization,
validate=,indicator=True, and row-count checks, so unmatched or duplicated rows are reported instead of hidden - Catches the silent defaults that change results: groupby dropping NaN keys,
sum()turning empty bins into 0, week anchors, timezone-naive resampling, andpivotversuspivot_table - Handles missing data per column and per entity, for example forward-fill within each sensor rather than across sensors, time-weighted interpolation, and fill limits
- Replaces
apply(axis=1)anditerrowswithnp.select,.map,.str,transform, andmerge_asof, and cuts memory with dtypes,usecols, and categoricals - Debugs pasted code and tracebacks, including SettingWithCopyWarning, Copy-on-Write changes, mixed-type columns, and KeyErrors after reset_index
- Tells you when the data has outgrown pandas and DuckDB or Polars is the better tool
What you get
Runnable pandas code with your assumptions stated at the top, assertions that check row counts and key uniqueness, and a short note on any result that would otherwise surprise you.
Best for
Analysts, data engineers, and scientists who write pandas every day and want code they can trust before the numbers reach a dashboard or a stakeholder.
SKILL.md preview
---
name: pandas-data-wrangling-without-silent-errors
description: Writes, fixes, and speeds up pandas code for merging, cleaning, groupby aggregation, reshaping, and time series. It inspects dtypes and key cardinality first, then uses vectorized operations with assertions that catch silent row loss or duplication. Use when the user says "merge these two DataFrames", "my row count exploded after the join", "resample this to weekly", "groupby is dropping rows", "this apply is way too slow", "why do I keep getting SettingWithCopyWarning", "pivot this wide", or "fill the missing values".
---
# Pandas Data Wrangling Without Silent Errors
… (sign up to view the full skill)More development skills
View all Development skills →Apache Spark Job Tuning and Debugging
Finds why a Spark job is slow or failing from the plan and UI metrics, then gives the code and config fix plus how to verify it.
Reliable Data Pipelines and Analytics Models
Build and repair ETL/ELT pipelines with replay-safe writes, explicit data contracts, and actionable quality checks.
Statistical Tests, Power & A/B Readouts
Get hypothesis tests, effect sizes, confidence intervals, and sample-size calculations with experiment validity checks.
Answer Business Questions with Validated SQL and Charts
Turn a business question into a pinned metric definition, checked SQL, the right chart, and a plain finding with confidence and caveats.