---
name: "pandas_patterns"
description: "Efficient, correct pandas patterns: vectorized ops over loops, groupby aggregations, merge/join semantics with validate, datetime handling, read_csv dtype/parse_dates/chunksize, categorical memory savings, avoiding SettingWithCopyWarning, and when pandas is the wrong tool. Triggers on: pandas dataframe, vectorize a loop, groupby aggregation, merge dataframes, SettingWithCopyWarning, read_csv memory, too big for pandas."
metadata: { "includeInPrompt": true }
---

## Purpose

Write pandas code that is fast *and* correct: vectorized column ops instead of row loops, one-pass groupby aggregations, merges with explicit cardinality checks, timezone-naive-safe datetime handling, memory-aware CSV reads, categorical columns for low-cardinality text, and SettingWithCopy-safe assignment. Scoped to the *transformation/analysis* step — dirty-file repair is a different skill; extraction from nested JSON is jq-yq territory. All patterns below were run against pandas 2.1.4 on Linux; behavior may differ on other versions.

## Workflow

1. **Vectorize, don't loop.** A Python `for` over rows is ~100x slower than the equivalent column op (measured: 0.80 ms vs 110 ms on 100k rows). Reach for `df["a"] * 2 + df["b"]`, `.str` / `.dt` accessors, `.where`, and `np.where` before `apply` — `apply(lambda)` was 48x slower than the same vectorized op in testing. `apply` is still the right escape hatch for genuinely row-local logic that can't be vectorized.

2. **Aggregate in one groupby.** One `.groupby(...).agg(...)` with named aggregations beats chained groupbys or per-group loops. Use `agg(mean_a=("a", "mean"), total_b=("b", "sum"))` — names the output columns directly, no post-hoc rename. For multi-key groups pass a list; for weighted or rolling stats use `.transform` to broadcast back to row shape.

3. **Merge with explicit semantics.** Always state `how` (inner/left/right/outer) deliberately — the default `inner` silently drops non-matching rows. Use `validate=` to assert cardinality: `validate="many_to_one"` raised `MergeError` in testing when the right side had duplicate keys, which is exactly when you want the loud failure instead of a fan-out. For index-aligned joins, `.join` on `set_index` is the idiom; for lookups by value, `merge` is clearer.

4. **Handle datetimes explicitly.** Parse with `pd.to_datetime(col, format="%Y-%m-%d %H:%M:%S")` when you know the format — inference silently parses `"01/02/2026"` as January 2nd (month-first default), so `format=` (or `dayfirst=True`) is about correctness, not just speed. Access components via `.dt` (`dt.date`, `dt.day_name()`). Compare and subtract as datetimes; never hand-roll date math on strings.

5. **Read CSVs with dtypes.** `pd.read_csv(path, dtype={"a": "float32", "grp": "category"}, parse_dates=["ts"])` cut memory 13.4 MB → 1.7 MB on a 100k-row test (the parse pass costs some time, the memory win is the point). For files that don't fit RAM, iterate `pd.read_csv(path, chunksize=250_000)` and aggregate per chunk — never read the whole file and hope.

6. **Categorize low-cardinality text.** A 1M-row string column of 4 distinct values used 53.8 MB as object vs 1.0 MB as `category` (54x). Rule of thumb: fewer than ~100 distinct values on a large frame → `.astype("category")`; watch `observed=False` in groupby if unobserved categories showing up matters.

7. **Never chain-assign.** `df[df["a"] > 500]["flag"] = 1` raises `SettingWithCopyWarning` — it may or may not write. Two safe patterns: write on the frame with `.loc` (`df.loc[df["a"] > 500, "flag"] = 1`), or take an explicit `.copy()` of the slice first and assign freely (plus `.assign()` for chained, warning-free column creation).

8. **Know when pandas is the wrong tool.** Trivial streaming reads (row counts, one-column scans) were 2.5x faster with the stdlib `csv` module than a full `read_csv` (99 ms vs 243 ms) and use O(1) memory — reach for `csv`/`itertools` when you never need a DataFrame. Files far beyond RAM need chunking or a distributed frame (dask/polars out-of-core); pandas eagerly materializes everything.

## Output Contract

After applying this skill, an agent should produce:

- Vectorized code (no row loops) with any `apply` use justified as non-vectorizable.
- Groupby calls using named `.agg(...)`; merges with `how` stated and `validate=` where cardinality is assumed.
- `read_csv` calls with explicit `dtype`/`parse_dates` (or a stated reason to skip), and `chunksize` for large files.
- Datetime parsing with `format=` or `dayfirst` when strings are ambiguous; no string date math.
- `.loc`/`.copy()` assignments only — no chained assignment.
- A one-line justification if pandas itself is the tool (vs csv module / chunked / out-of-core).

## Operating Rules

- **Vectorize first, apply second.** If the same logic is expressible as column ops, `apply` is the wrong choice — measured 48x slower.
- **State merge semantics explicitly.** `how` on every merge; `validate=` whenever you assume a cardinality. Silent fan-outs are data corruption.
- **Dates are parsed, not guessed.** Ambiguous formats get `format=` or `dayfirst`; `.dt` for components; strings never carry date math.
- **Budget memory on read.** Low-cardinality text columns become `category`; oversized reads become `chunksize` iteration. The frame you don't materialize can't OOM you.
- **Chained assignment is a bug.** If you see `df[mask][col] = ...`, rewrite with `.loc` or `.copy()`.
- **Soften version claims.** Patterns are verified on pandas 2.1.4; note when behavior might differ elsewhere rather than asserting universals.