pandas Wrangling skill for Codex

Done well means: every column has an intended dtype, every join states its expected cardinality and is checked, row counts are accounted for at each step, and no Python-level loop runs where a vectorised operation exists.

Before writing code

1. Read AGENTS.md and the lockfile to learn the pandas version and package manager. python -c "import pandas, sys; print(pandas.__version__, sys.version)".
2. Version matters:

   Version	Behaviour that changes your code
   2.x	Copy-on-Write opt-in (pd.options.mode.copy_on_write = True); strings are object by default
   3.0+	Copy-on-Write always on, chained assignment never writes; strings default to the str dtype (Arrow-backed when pyarrow is installed); datetimes default to microsecond resolution
   Code that checks dtype == object for strings breaks on 3.0. Use pd.api.types.is_string_dtype.

3. Look at the data before transforming it: shape, dtypes, null counts, a sample, and the candidate key’s uniqueness.

df.info(memory_usage="deep")
df.isna().mean().sort_values(ascending=False).head(15)
df.sample(5, random_state=0)
df["order_id"].is_unique, df.duplicated(["order_id"]).sum()

Workflow checklist

- [ ] 1. Read with explicit dtypes, parse_dates, and usecols
- [ ] 2. Profile: nulls, duplicates on the intended key, value ranges
- [ ] 3. Clean in a single function (method chain), no in-place edits of shared frames
- [ ] 4. Join with validate= and indicator=, reconcile row counts
- [ ] 5. Reshape / aggregate
- [ ] 6. Assert invariants (keys unique, totals preserved, no unexpected nulls)
- [ ] 7. Write Parquet (not CSV) for intermediate outputs

Reading with intent

import pandas as pd

orders = pd.read_csv(
    "orders.csv",
    usecols=["order_id", "customer_id", "status", "amount", "created_at"],
    dtype={"order_id": "string", "customer_id": "string", "status": "category"},
    parse_dates=["created_at"],
    na_values=["", "NULL", "n/a"],
    keep_default_na=True,
)
# Optional: whole frame on Arrow dtypes (faster strings, nullable ints)
orders = pd.read_parquet("orders.parquet", dtype_backend="pyarrow")

• IDs are strings, never ints: leading zeros vanish and 19-digit IDs lose precision as float.
• Integer columns with nulls become float64 under NumPy dtypes. Use "Int64" (nullable) when the column is a count or key.
• engine="pyarrow" in read_csv is much faster for large files but supports fewer options; fall back to the C engine if it rejects a parameter.
• For files over roughly a third of available RAM, read in chunksize= batches, or hand off to $polars-dataframes / $duckdb-analytics.

Cleaning as a method chain

def clean_orders(raw: pd.DataFrame) -> pd.DataFrame:
    return (
        raw
        .rename(columns=str.lower)
        .assign(
            status=lambda d: d["status"].str.strip().str.lower().astype("category"),
            amount=lambda d: pd.to_numeric(d["amount"].str.replace(",", ""), errors="coerce"),
            created_at=lambda d: pd.to_datetime(d["created_at"], utc=True, errors="coerce"),
        )
        .dropna(subset=["order_id"])
        .drop_duplicates(subset=["order_id"], keep="last")
        .query("amount >= 0")
    )

• errors="coerce" turns bad values into NaN/NaT silently. Count them before and after and report how many rows were coerced; never coerce without that number.
• Use .loc[mask, "col"] = value in one step. df[mask]["col"] = value is chained assignment: it warns on 2.x and does nothing on 3.0.
• Mixed timezones: parse with utc=True, convert for display only with .dt.tz_convert.

Joins with guarantees

before = len(orders)
merged = orders.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",   # raises MergeError if customers has duplicate keys
    indicator=True,
)
assert len(merged) == before, f"row count changed: {before} -> {len(merged)}"
unmatched = merged["_merge"].eq("left_only").sum()
print(f"{unmatched} orders without a customer")
merged = merged.drop(columns="_merge")

Symptom	Cause	Fix
Row count grew after a left join	Right side not unique on the key	validate="many_to_one"; dedupe or aggregate the right side first
Every row unmatched	Key dtypes differ (int64 vs string), whitespace, case	Normalise both keys to the same dtype and .str.strip()
_x / _y columns everywhere	Overlapping non-key columns	Select needed columns before merging or set suffixes=
Join on dates misses	One side has a time component or timezone	Normalise with .dt.normalize() or join on .dt.date
Need “latest price before the order”	Range join	pd.merge_asof(left.sort_values("ts"), right.sort_values("ts"), on="ts", by="sku")

Reshaping

# wide -> long
long = df.melt(id_vars=["store"], value_vars=["jan", "feb", "mar"],
               var_name="month", value_name="sales")

# long -> wide; pivot_table aggregates duplicates, pivot raises on them
wide = long.pivot_table(index="store", columns="month", values="sales",
                        aggfunc="sum", fill_value=0, observed=True)

# list column -> rows
tags = df.explode("tags", ignore_index=True)

# named aggregations keep column names readable
summary = (
    orders.groupby(["customer_id", "status"], observed=True, as_index=False)
    .agg(n=("order_id", "size"), revenue=("amount", "sum"), last=("created_at", "max"))
)

• Always pass observed=True when grouping by a categorical; otherwise every combination of categories appears, including empty ones, and memory explodes.
• transform returns a result aligned to the original rows (share of group total, rank within group); agg collapses rows. Use transform instead of merging an aggregate back.
• groupby drops NaN keys by default; pass dropna=False when null is a real group.

Performance traps

Slow pattern	Replace with
df.apply(lambda r: ..., axis=1) on arithmetic or conditions	Column arithmetic, np.where, np.select, Series.case_when (2.2+)
for i, row in df.iterrows() building a list	Vectorised expression or groupby
Appending to a DataFrame inside a loop	Collect dicts or frames in a list, one pd.concat at the end
Repeated string ops on low-cardinality text	astype("category") once
.str ops on object columns in 2.x	astype("string[pyarrow]")
Reading the same large CSV on every run	Convert once to Parquet

import numpy as np
df["tier"] = np.select(
    [df["revenue"] >= 10_000, df["revenue"] >= 1_000],
    ["gold", "silver"],
    default="bronze",
)

Measure before and after with %timeit or time.perf_counter() on a realistic sample, and report memory via df.memory_usage(deep=True).sum() / 1e6 MB.

Gotchas

• inplace=True rarely saves memory and blocks method chaining; avoid it.
• df.copy() sprinkled everywhere to silence warnings hides the real bug: decide which frame owns the data. Under Copy-on-Write explicit copies are rarely needed.
• astype(int) on a column with NaN raises; astype("Int64") does not.
• pd.concat of frames with different column sets fills missing columns with NaN and may upcast ints to float. Check dtypes after concat.
• Single-column sort_values defaults to quicksort, which is not stable; pass kind="stable" when the order of ties matters (for example “keep first” after sorting).
• Floating-point sums of money drift. Keep cents as integers or round at the reporting edge.
• read_excel needs openpyxl (xlsx); installing it needs network approval in a sandbox.

Verification

assert df["order_id"].is_unique
assert df["amount"].notna().all()
assert abs(df["amount"].sum() - raw_total) < 0.01   # totals preserved through transforms
pd.testing.assert_frame_equal(result, expected, check_dtype=True)

• Keep a row-count ledger: input, after dedupe, after filters, after join, output.
• Add a small pytest with a 5-10 row fixture covering nulls, duplicates and an unmatched key.
• If an assertion fails, print the offending rows (df[~mask].head()) rather than loosening it.

Report template

**Input:** orders.csv (1,204,331 rows), customers.parquet (88,410 rows)
**Output:** out/orders_enriched.parquet (1,198,002 rows, 14 columns)
**Row ledger:** -5,880 duplicate order_id (kept latest), -449 negative amounts, join 1:1 preserved
**Coerced values:** 37 unparseable amounts -> NaN (listed in out/rejects.csv)
**Unmatched:** 212 orders with unknown customer_id
**Performance:** apply loop replaced with np.select, 41 s -> 0.6 s