# Pandas Expert > Writes correct, vectorized pandas for cleaning, joining, reshaping, and aggregating tabular data, with validated joins and deliberate dtype and missing-data handling. Use when someone asks "why did my merge duplicate rows", "how do I clean this CSV in pandas", "my groupby numbers look wrong", "this apply is too slow", or is transforming a DataFrame for analysis or a pipeline. Do NOT use for first-pass profiling of an unfamiliar dataset - use eda-playbook instead; for data too large for one machine use spark-jobs; for answering business questions directly in SQL use sql-to-insights. - Skill: `skillmedev/pandas-expert` (Agent Skill) - Install (CLI): `npx skillmds@latest add skillmedev/pandas-expert` - Raw SKILL.md: https://api.skillmd.com/api/skills/skillmedev/pandas-expert/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: DevOps & Infra - Author: SkillMedev (https://skillmd.com/u/skillmedev) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/skillmedev/pandas-expert --- # Pandas Expert Most pandas bugs are silent: a join that fans out rows, a dtype that upcast to object, a NaN that vanished from a sum. The output looks plausible and is wrong. This skill produces transformations that validate themselves - every join asserted, every coercion counted, every aggregate reconciled - so errors surface at the line that caused them instead of in a dashboard three weeks later. ## Operating procedure Follow the steps in order. Types must be fixed before cleaning, cleaning before joining, joining before aggregating - each later step silently produces wrong numbers if an earlier one was skipped. ### Step 1: gather inputs Before writing any transformation, establish: - The grain of each table: what one row represents. If the user cannot state it, derive it with `df.duplicated(subset=candidate_keys).sum()` and label the result a guess until confirmed. - The expected output: shape, grain, and one or two spot-check numbers the user already trusts (last month's total, a known customer's count). - Whether the source can be mutated. Default to no: work on a copy, never mutate the caller's DataFrame. - Approximate size. Pandas wants roughly 3-5x the on-disk dataset size in free RAM for comfortable transformation work. Above a few GB in memory, reach for chunked reads (`pd.read_csv(..., chunksize=...)`), pyarrow-backed dtypes, or hand off to spark-jobs. ### Step 2: inspect before touching Run `df.info()`, `df.describe()`, `df.isna().sum()`, and `df.nunique()` first. You are looking for: object columns that should be numeric or datetime, null counts, and cardinality. This is a two-minute sanity pass, not a full audit - for a genuinely unfamiliar dataset, run eda-playbook first. ### Step 3: fix dtypes early - Parse dates with `pd.to_datetime`; pass `format=` when known (it is faster and catches malformed rows), `format="mixed"` only when formats genuinely vary. - Coerce numerics with `pd.to_numeric(col, errors="coerce")`, then **count and report** what failed: `col.isna().sum()`. Silent coercion is how "N/A" strings become invisible revenue holes. - Cast low-cardinality strings to `category` when distinct values are under roughly 50% of row count - it cuts memory and speeds groupbys. - Downcast large numeric columns (`pd.to_numeric(col, downcast="integer")`) when memory is tight. ### Step 4: handle missing data deliberately Choose drop, fill, or flag - and state which and why in a comment. If more than ~5% of a key column is null, stop and ask whether the nulls are structural (a pipeline branch) before imputing; that determination changes the right treatment entirely. ### Step 5: join with validation, never on faith - Always state `how=` and `on=` explicitly. - Always pass `validate=` ("one_to_one", "many_to_one", "many_to_many") so pandas raises on unexpected key duplication instead of silently fanning out. - After the join, assert the row count: a left join must preserve `len(left)`. - Check match rate with `indicator=True` when a low match rate would be a data problem. ### Step 6: reshape and aggregate - `pivot_table` for long→wide, `melt` for wide→long. - `groupby().agg()` with **named aggregations** - `agg(revenue=("amount", "sum"))` - so output columns are self-describing. - Remember `sum()` skips NaN silently; if a NaN-contaminated group should show as missing, use `min_count=1`. ### Step 7: verify the output Reconcile at least one aggregate against the pre-transformation source (total rows, total revenue). Show the transformation step by step with a comment on each step explaining intent, not mechanics. ## Worked example Messy orders joined to a customer table with a duplicate key - the validation catches it: ```python import pandas as pd orders = pd.DataFrame({ "order_id": [1001, 1002, 1003, 1004, 1005], "customer_id": ["C01", "C02", "C01", "C03", "C02"], "amount": ["120.50", "80.00", "N/A", "45.25", "200.00"], }) customers = pd.DataFrame({ "customer_id": ["C01", "C02", "C03", "C03"], # C03 duplicated upstream "region": ["West", "East", "South", "South-dup"], }) # Coerce and count failures instead of letting them vanish orders["amount"] = pd.to_numeric(orders["amount"], errors="coerce") print(orders["amount"].isna().sum(), "amounts failed to parse") # validate= raises instead of silently duplicating order 1004 try: orders.merge(customers, on="customer_id", how="left", validate="many_to_one") except pd.errors.MergeError as e: print("MergeError:", str(e).splitlines()[0]) customers = customers.drop_duplicates("customer_id", keep="first") merged = orders.merge(customers, on="customer_id", how="left", validate="many_to_one") assert len(merged) == len(orders) summary = ( merged.groupby("region", as_index=False) .agg(orders=("order_id", "count"), revenue=("amount", "sum")) .sort_values("revenue", ascending=False) ) print(summary.to_string(index=False)) ``` Output: ``` 1 amounts failed to parse MergeError: Merge keys are not unique in right dataset; not a many-to-one merge region orders revenue East 2 280.00 West 2 120.50 South 1 45.25 ``` Read it: the "N/A" string was counted, not swallowed; the duplicate C03 row raised at the merge line instead of inflating South's revenue; West shows 2 orders but only 120.50 revenue because the failed parse became NaN and `sum` skipped it - a fact the failure count already disclosed. ## Performance rules - Avoid `apply` over rows when a vectorized op, `np.where`, or `np.select` exists - vectorized versions are commonly 10-100x faster. - Avoid chained indexing (`df[a][b] = ...`); use `.loc[rows, cols]` for all assignment. - Watch for silent upcasts to `object` dtype after concatenation or fillna - they destroy both speed and memory. - Prefer `merge` over row-wise lookups; prefer `groupby().transform` over apply-per-group when the output is row-aligned. ## Deliverable Produce a runnable, commented transformation script (or notebook cells) containing: the dtype-fixing block with coercion-failure counts, every join carrying `how=`, `on=`, `validate=`, and a row-count assertion, named aggregations, and a final reconciliation check against a number the user already trusts. ## Do NOT - Do not merge without `validate=` - fan-out from duplicated keys is the single most common source of inflated metrics. - Do not use `errors="coerce"` without counting what was coerced; a clean-looking column can hide hundreds of destroyed values. - Do not use `apply` with a lambda for arithmetic a vectorized expression can do. - Do not mutate the input DataFrame in place inside a function; copy when in doubt. - Do not trust `sum()` on a column with NaN to mean "complete total" - it means "total of what parsed". - Do not profile an unknown dataset with ad-hoc snippets; run eda-playbook and come back with its decision log. ## Quality bar A finished transformation ships only when: every join is validated and row-count-asserted; every type coercion reports its failure count; at least one output aggregate reconciles to a trusted source number; no chained indexing or row-wise `apply` remains where a vectorized form exists; and a reader can follow the intent from comments without running the code.