When pandas is slow: vectorising, Polars and DuckDB

You can already do the work in pandas. This is what to do when a step that used to take seconds takes twenty minutes, or dies with a memory error. Four things usually fix it: replacing a Python loop with a vectorised expression, storing the data as Parquet rather than CSV, and moving to Polars or DuckDB, both of which run on one machine and handle files larger than memory. Knowing them means you reach for a cluster when you genuinely need one, which is far rarer than people assume.

Measure#

Find the slow step before changing anything, because the answer is routinely not where people assume. Time the stages separately rather than the whole script, and look at memory as well as seconds: a job that swaps to disk looks like a slow job and is really a memory problem.

%timeit df["total"] = [a * b for a, b in zip(df["qty"], df["price"])]   # Python loop
%timeit df["total"] = df["qty"] * df["price"]                            # vectorised

# Measured on 200,000 rows:
#   df.apply(..., axis=1)   1,445 ms
#   df["a"] * df["b"]           0.32 ms      about 4,500x

import time
t = time.perf_counter()
df = pd.read_parquet("calls.parquet")
print(f"load {time.perf_counter() - t:.1f}s  {df.memory_usage(deep=True).sum()/1e9:.1f} GB")

What is slow                          What actually fixes it
a per-row apply or an explicit loop   vectorise, or np.select for branching
reading the same CSV repeatedly       convert to Parquet once
reading 30 columns to use 3           Parquet plus a column list
merging on a string key               narrow the dtype, or merge on an integer id
it fits, but only just                narrower dtypes and categoricals
it does not fit at all                Polars lazy, or DuckDB over the files
none of the above                     then, and only then, a cluster

Parquet#

CSV is text: every read parses every value. Parquet stores typed columns, compressed, and lets readers load only the columns and row groups they need. Converting a large CSV to Parquet once often makes every later step several times faster and the files much smaller.

Three properties do the work. It is columnar, so reading two columns of thirty reads two columns of data rather than every byte of every row. It carries a schema, so a numeric column comes back numeric with no inference, and a type error in the data is caught at write time rather than discovered a week later. And it stores statistics per row group, so a reader can skip whole blocks that cannot match a filter. Partitioning the files by date on disk takes that further: a query for one day opens one directory.

df.to_parquet("calls.parquet", compression="zstd")
df.to_parquet("calls/", partition_cols=["date"])     # calls/date=2026-03-11/...

pd.read_parquet("calls.parquet", columns=["user_id", "cost_usd"])   # two columns only
pd.read_parquet("calls/", filters=[("date", "=", "2026-03-11")])    # one partition

Polars#

import polars as pl

result = (
    pl.scan_parquet("calls/*.parquet")            # lazy: nothing is read yet
      .filter(pl.col("status") == "ok")
      .group_by("model")
      .agg(pl.col("latency_ms").quantile(0.95).alias("p95"), pl.len().alias("calls"))
      .collect()                                  # plan optimised, then run in parallel
)

The word doing the work is scan rather than read. Nothing happens until collect, so Polars sees the whole chain first and can push the filter down into the file reader and load only the columns the query mentions. A pandas version of the same chain reads every column of every file into memory and then discards most of it, which is why the difference is often large rather than marginal. Polars also uses every core by default, where pandas uses one.

The API is different enough to be worth an hour of adjustment. There is no index, which removes a whole category of alignment surprises. Expressions are built with pl.col rather than by subscripting the frame, so a filter is written as a value passed to filter rather than a boolean frame. And there is no in-place mutation: you add columns with with_columns and get a new frame back.

pandas                                    Polars
df[df.status == "ok"]                     df.filter(pl.col("status") == "ok")
df["c"] = df["a"] * df["b"]               df.with_columns((pl.col("a") * pl.col("b")).alias("c"))
df.groupby("k").agg(n=("a", "sum"))       df.group_by("k").agg(pl.col("a").sum().alias("n"))
pd.read_parquet(p)                        pl.read_parquet(p), or scan_parquet for lazy
df.merge(o, on="k", how="left")           df.join(o, on="k", how="left")
df.to_pandas() is not needed              df.to_pandas() when a library demands pandas

DuckDB#

import duckdb

duckdb.sql("""
    SELECT model, count(*) AS calls, quantile_cont(latency_ms, 0.95) AS p95
    FROM 'calls/*.parquet'
    WHERE status = 'ok'
    GROUP BY model
""").df()

DuckDB is a database engine that runs inside your process with no server to install and no data to load. It reads Parquet, CSV and JSON files directly, and it also reads a pandas or Polars frame that is already in your session by name, which makes it easy to drop into an existing script for the one step that is slow. It streams, so it will happily group by over files larger than memory, and it uses every core.

calls = pd.read_parquet("calls.parquet")     # an ordinary pandas frame

duckdb.sql("SELECT model, sum(cost_usd) FROM calls GROUP BY model").df()
# 'calls' is found in the enclosing Python scope; nothing was copied in

duckdb.sql("COPY (SELECT * FROM 'raw/*.csv') TO 'out.parquet' (FORMAT parquet)")
# convert a directory of CSVs to Parquet without loading any of it

con = duckdb.connect("analysis.duckdb")      # or persist it to a file

Choosing between them is mostly about which language you want to write. DuckDB when the operation is naturally a query and especially when it joins several files, Polars when it is a chain of transformations inside Python code. Both run on one machine, and a single machine with fast disks handles tens of gigabytes comfortably, which is more than most jobs that get moved to a cluster.