pandas Performance

Core Principle: Optimizing pandas performance requires replacing row-by-row iteration with vectorized column operations, utilizing categorical and compact numeric dtypes, and leveraging memory-efficient file formats like Parquet.

Measure first

%timeit df.groupby("gene")["x"].mean()          # IPython
%%time                                            # a whole cell
%prun -s cumtime my_function(df)
df.memory_usage(deep=True).sum() / 1e6           # MB
df.info(memory_usage="deep")

In bioinformatics the bottleneck is very often file parsing, not computation. Profile before you optimise anything, or you will spend a day making a 2-second step take 1 second.

The speed ladder

Same task, roughly ordered from worst to best on a 1-million-row frame:

# 1. iterrows: builds a Series per row, loses dtypes            ~60 s
for i, row in df.iterrows():
    df.at[i, "y"] = row["a"] * 2

# 2. apply(axis=1): a Python loop over rows                     ~15 s
df["y"] = df.apply(lambda r: r["a"] * 2, axis=1)

# 3. itertuples: namedtuples, no Series construction            ~2 s
df["y"] = [r.a * 2 for r in df.itertuples()]

# 4. Series.apply: a Python loop, but only over one column      ~0.4 s
df["y"] = df["a"].apply(lambda x: x * 2)

# 5. vectorised                                                  ~0.005 s
df["y"] = df["a"] * 2

That is a 10,000× spread. df.apply(..., axis=1) is the single most common performance mistake in pandas code, and it is usually replaceable by a vectorised expression.

Vectorising conditional logic

# slow
df["cat"] = df.apply(lambda r: "up" if r["fc"] > 1 else "down", axis=1)

# fast
df["cat"] = np.where(df["fc"] > 1, "up", "down")

# multiple conditions
df["cat"] = np.select(
    [df["padj"] >= 0.05, df["fc"] > 1, df["fc"] < -1],
    ["ns", "up", "down"],
    default="ns",
)

# lookup from a dict
df["name"] = df["code"].map(NAME_MAP)          # much faster than .apply

np.select and .map cover most of what people use row-apply for. See Vectorization.

groupby speed

df.groupby("gene")["x"].agg("mean")             # compiled Cython: fast
df.groupby("gene")["x"].agg(lambda s: s.mean()) # Python per group: 50–100× slower
df.groupby("gene", sort=False)["x"].mean()       # skip sorting the output
df["gene"] = df["gene"].astype("category")       # group on integer codes

Always prefer the string names for aggregations: "mean", "sum", "size", "count", "nunique", "first", "min", "max", "std", "median", "idxmax". See groupby.

Memory

df.memory_usage(deep=True).sort_values(ascending=False).head()

Typical wins on a 10-million-row variant table:

df = df.astype({
    "chrom": "category",         # ~50× on that column
    "consequence": "category",
    "ref": "category", "alt": "category",
    "pos": "int32",              # half of int64
    "qual": "float32",
    "af": "float32",
})

Set these with dtype= at read time, not astype afterwards: otherwise you still pay peak memory for the inferred version. See pandas dtypes and Categorical dtype.

IO speed

# slow: re-parsing text every run
df = pd.read_csv("variants.tsv", sep="\t")

# fast: parse once, cache typed
df.to_parquet("variants.parquet")
df = pd.read_parquet("variants.parquet")            # ~10× faster, dtypes preserved

# read only what you need
pd.read_csv(path, sep="\t", usecols=["chrom", "pos", "af"])
pd.read_parquet(path, columns=["chrom", "pos", "af"])   # Parquet reads only those columns

# chunk when the file exceeds memory
for chunk in pd.read_csv(path, sep="\t", chunksize=500_000):
    ...

The columnar read is a real advantage: Parquet can skip entire columns on disk, CSV cannot.

Merges and joins

df.merge(other, on="key")                            # hash join
df.set_index("key").join(other.set_index("key"))     # faster if already indexed
df.merge(other, on="key", validate="many_to_one")    # catches accidental row explosion

The most common "merge is slow" cause is not the merge: it is that the merge produced 50× the rows because the key was not unique. Check len() before and after. See Merging and Joining.

Query and eval

df.query("a > 1 and b < 2")            # can use numexpr on large frames
df.eval("c = a + b")

numexpr evaluates the expression without materialising intermediates, so it helps on large frames with multi-term expressions. On small frames the parsing overhead makes it slower.

Avoid growing frames

# BAD: quadratic
result = pd.DataFrame()
for path in paths:
    result = pd.concat([result, pd.read_csv(path)])

# GOOD
result = pd.concat([pd.read_csv(p) for p in paths], ignore_index=True)

See Concat.

When to leave pandas

Situation	Go to
Data > RAM	polars (streaming), DuckDB, or chunking
Heavy numeric work on a matrix	NumPy: df.to_numpy(), compute, come back
A genuinely sequential algorithm	numba @njit
Mostly-zero matrix	scipy.sparse
Millions of VCF records	cyvcf2, pysam
Interval overlaps	pyranges, bioframe
Multi-core	dask, or multiprocessing over chunks

DuckDB deserves special mention: it queries CSV and Parquet files larger than memory with SQL and hands you a DataFrame:

import duckdb
duckdb.sql("""
    SELECT chrom, count(*) AS n, avg(qual) AS mean_qual
    FROM 'variants/*.parquet'
    WHERE filter = 'PASS'
    GROUP BY chrom
""").df()

Zero setup, handles globs, and often faster than the pandas equivalent even when the data does fit.

polars is the other strong option: a pandas-like API with lazy evaluation, multi-threading and much lower memory use. The learning cost is low if you already know pandas.

A realistic optimisation session

# 1. Profile: where does the time actually go?
%prun -s cumtime run_pipeline()

# 2. Usually: IO. Cache it.
df.to_parquet("cache.parquet")

# 3. Then: dtypes. Halve the memory.
df = df.astype({"chrom": "category", "pos": "int32", "af": "float32"})

# 4. Then: kill the row-wise apply.
df["flag"] = np.select(conds, choices, default="other")

# 5. Then: check merges are not multiplying rows.
assert len(merged) == len(df)

# 6. Only then consider a different library.

Steps 2–4 typically account for most of the available speedup, and none of them require learning anything new.

Common mistakes

• apply(axis=1) as a default habit.
• iterrows at all.
• Lambdas in groupby.agg where a string name exists.
• Re-parsing CSVs every run.
• object/float64 everywhere by default.
• Growing a frame in a loop.
• Optimising before profiling.
• Reaching for dask/spark when the real problem is dtypes and a row-wise apply.

See also

Vectorization · groupby · pandas dtypes · Categorical dtype · Reading and Writing Data · Merging and Joining · Concat · Copy-on-Write

Exercise

Write classify(text). Stdin holds one differential-expression result per line: a gene name, a log2 fold change and an adjusted p-value, separated by spaces. The adjusted p-value may be the literal NA, meaning the gene was removed by independent filtering and was never tested. Return a list of strings.

Assign each gene exactly one label, using a vectorised expression such as numpy.select rather than a row-wise apply: the point of this exercise is that the row-wise form is the slowest thing in the lesson. The rules are applied in this order:

• padj is missing → filtered
• padj >= 0.05 → ns
• log2fc > 1 → up
• log2fc < -1 → down
• anything else → ns

Return exactly four lines, always in the order up, down, ns, filtered, each formatted label=<count>. An input with no records prints all four counts as zero.