Performance: categoricals, vectorising & method chaining

pandas is fast when you let it work on whole columns and slow when you make it loop in Python. A few habits can cut run time from minutes to seconds and memory from gigabytes to megabytes, while making code easier to read.

Measure first

df.info(memory_usage="deep")
df.memory_usage(deep=True).sort_values(ascending=False)

deep=True counts the real size of text columns, which are usually the biggest culprits.

Categoricals for repeated text

A column like state or payment_mode has a few distinct values repeated millions of times. The category dtype stores each value once plus small integer codes:

df["state"] = df["state"].astype("category")
df["payment_mode"] = df["payment_mode"].astype("category")

On a 5-million-row table, this commonly shrinks those columns by 90% or more and makes groupby on them faster. Use it when the number of unique values is small compared to the row count. It does not help for unique values like invoice numbers.

Numbers can shrink too:

df["qty"] = pd.to_numeric(df["qty"], downcast="integer")
df["price"] = pd.to_numeric(df["price"], downcast="float")

Vectorise instead of apply

apply with axis=1 calls a Python function once per row, which is slow:

# Slow: one Python call per row
df["discount"] = df.apply(lambda r: r["amount"] * 0.1 if r["member"] else 0, axis=1)

# Fast: whole-column operations
import numpy as np
df["discount"] = np.where(df["member"], df["amount"] * 0.1, 0)

For several conditions, use np.select:

conditions = [df["amount"] >= 10_000, df["amount"] >= 5_000]
choices = ["Gold", "Silver"]
df["tier"] = np.select(conditions, choices, default="Regular")

Task	Slow	Fast
If/else on a column	apply(axis=1)	np.where, Series.where, mask
Many conditions	nested apply	np.select, pd.cut
Lookups	apply with a dict	Series.map(dict) or merge
Text cleaning	apply(str.strip)	.str.strip()
Row loops	iterrows()	column arithmetic

.str methods are not as fast as numeric operations, but they are cleaner and still quicker than hand-written loops.

Method chaining

Instead of many intermediate variables, write a readable pipeline:

report = (
    pd.read_csv("orders.csv", parse_dates=["order_date"], dayfirst=True)
      .query("status != 'Cancelled'")
      .assign(
          city=lambda d: d["city"].str.strip().str.title().astype("category"),
          month=lambda d: d["order_date"].dt.to_period("M"),
          net=lambda d: d["amount"] - d["discount"],
      )
      .groupby(["city", "month"], observed=True)["net"]
      .sum()
      .reset_index()
      .sort_values(["month", "net"], ascending=[True, False])
)

assign with lambdas refers to the DataFrame at that step of the chain. pipe(func) lets you insert your own functions. Pass observed=True when grouping by categoricals so empty combinations are not created.

Avoid chained assignment

df[df["city"] == "Pune"]["amount"] = 0 may modify a temporary copy and leave df unchanged. Use one loc call: df.loc[df["city"] == "Pune", "amount"] = 0. With Copy-on-Write (the default behaviour from pandas 3.0, and available in 2.x via pd.options.mode.copy_on_write = True), chained assignment never modifies the original.

Key takeaways

• Profile memory with deep=True, then convert low-cardinality text to category.
• Replace apply(axis=1) and iterrows with vectorised expressions.
• Chains with query, assign and pipe make logic easy to read and review.
• Always assign with a single .loc[rows, cols].