pandas_patterns v1.0.0

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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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).

1. 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.