pandas

Pandas

Purpose

Transform and analyze tabular data correctly and at speed. Pandas makes it easy to write code that is slow, and easier still to write code that is silently wrong.

When to Use

• Cleaning, transforming, or analyzing tabular data in Python.
• A pandas operation that is slow or exhausting memory.
• Reviewing analysis code for correctness.
• Deciding whether the dataset has outgrown pandas.

Capabilities

• Vectorized operations and eliminating row-wise loops.
• Memory reduction through dtype selection.
• Merge and join semantics, including the ones that silently duplicate rows.
• Groupby, aggregation, and window functions.
• Chunked processing and the migration path to Polars or DuckDB.

Inputs

• The data source, its size, and its schema.
• The transformation or analysis required.
• The memory available.

Outputs

• Vectorized transformations with no iterrows.
• Explicit dtypes, including categoricals for low-cardinality strings.
• Joins with verified cardinality.

Workflow

1. Set dtypes at read time — Reading a CSV without dtype gives you object columns and float64 for everything numeric. This is usually a 5-10x memory difference.
2. Vectorize — Any for loop or iterrows over a DataFrame should be a vectorized expression, a groupby, or a merge. apply is a loop with better syntax.
3. Verify every join — merge(..., validate="one_to_many"). An unvalidated join that is secretly many-to-many silently multiplies your rows, and the resulting totals will be wrong in a way that is hard to notice.
4. Aggregate with groupby, not with loops — And use named aggregation so the output columns are readable.
5. Chunk or switch when it does not fit — Pandas holds everything in memory, typically at several times the file size. Above a few gigabytes, use chunked processing, Polars, or DuckDB.

Best Practices

• df.iterrows() is roughly a hundred times slower than the vectorized equivalent and should essentially never appear in production code.
• Chained assignment (df[df.a > 1]["b"] = 0) may modify a copy and silently do nothing. Use .loc[]. In pandas 3.0 copy-on-write makes this an error rather than a silent no-op — which is an improvement.
• A merge without validate= is a bet that the join keys are unique. When that bet is wrong, you get more rows than you started with and no warning.
• category dtype for a string column with few distinct values can reduce memory by 90% and speeds up groupby substantially.
• inplace=True does not save memory (it usually still copies) and prevents method chaining. It has no advantages.
• Read only the columns you need with usecols. The cheapest optimization is not loading the data.