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.

Examples

Reading efficiently, and joining safely:

import pandas as pd

orders = pd.read_csv(
    "orders.csv",
    usecols=["order_id", "customer_id", "status", "total_cents", "created_at"],
    dtype={
        "order_id": "string",
        "customer_id": "string",
        "status": "category",        # 4 distinct values: 90% less memory than object
        "total_cents": "int64",
    },
    parse_dates=["created_at"],
)

customers = pd.read_csv("customers.csv", usecols=["customer_id", "segment"],
                        dtype={"customer_id": "string", "segment": "category"})

# validate= turns a silent row explosion into a loud, immediate error.
enriched = orders.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",          # many orders, one customer. Anything else raises.
)

Vectorized instead of looped — and correct:

# Slow (~100x) and easy to get wrong.
for idx, row in df.iterrows():
    df.at[idx, "band"] = "high" if row["total_cents"] > 10_000 else "low"

# Vectorized, readable, and it does not mutate while iterating.
df["band"] = pd.cut(
    df["total_cents"],
    bins=[0, 10_000, 50_000, float("inf")],
    labels=["low", "mid", "high"],
)

# Named aggregation: the output columns are named, not a MultiIndex to unpick.
summary = (
    df.groupby(["segment", "band"], observed=True)
      .agg(
          order_count=("order_id", "count"),
          revenue_cents=("total_cents", "sum"),
          median_cents=("total_cents", "median"),
      )
      .reset_index()
)

Notes

• observed=True on a groupby with categorical keys is important: without it, pandas produces a row for every possible category combination, including the ones with no data. On two categoricals with many levels this can generate an enormous, mostly empty frame.
• Polars is typically 5-30x faster than pandas on the same operations, uses less memory, and has a stricter API that catches errors pandas silently permits. For new analysis code on non-trivial data, it is the better default.
• DuckDB queries Parquet and CSV files directly with SQL, without loading them into memory. For "I need one aggregate from a 20 GB file", it is far simpler than any pandas approach.