Performance and Large Datasets

Speed up pandas on large datasets: measure memory, downcast dtypes, use categories and PyArrow, read CSVs in chunks, and know when to use Polars or DuckDB.

• Advanced
• 18 min read
• 4 objectives

Before this lessonLesson 10: Rolling, Expanding and Window Calculations

pandas loads your whole dataset into memory, and a DataFrame often uses several times more RAM than the file it came from. For a few hundred thousand rows nobody notices. At tens of millions of rows, your notebook crashes, or every step takes a minute. The good news is that most slowness comes from a handful of fixable habits.

This lesson follows a simple order that works in practice: measure first, then shrink the data, then stream it if it still does not fit, and finally avoid the slow patterns in your code. If all that is not enough, it is time for a different tool, and we will cover which one.

Measure memory first

df.info(memory_usage="deep") and df.memory_usage(deep=True) report how much memory each column really uses. The deep=True part matters: without it, pandas does not count the actual contents of text columns and underestimates badly.

import numpy as np
import pandas as pd

n = 100_000
rng = np.random.default_rng(0)
df = pd.DataFrame({
    "user_id": np.arange(n),                                  # int64
    "age": rng.integers(18, 80, n),                           # int64
    "country": rng.choice(["IN", "US", "GB", "DE", "BR"], n), # text
    "score": rng.random(n),                                   # float64
})

mb = df.memory_usage(deep=True) / 1_000_000
print(mb.round(2))
print(df.dtypes)

Index      0.0
user_id    0.8
age        0.8
country    1.0
score      0.8
dtype: float64
user_id      int64
age          int64
country        str
score      float64
dtype: object

The pattern is typical. Numbers default to 64-bit types (8 bytes per value) even when the values are small, and the repeated text column is the heaviest. In pandas 3 with PyArrow installed, text is stored compactly; on older versions or without PyArrow, that country column would take several times more memory. Your exact numbers may differ slightly by pandas version and platform.

Shrink: right-sized dtypes

An age between 18 and 80 fits in an 8-bit integer (range -128 to 127) instead of 64 bits, an eighth of the memory. pd.to_numeric(..., downcast=...) picks the smallest safe type for you. Low-cardinality text becomes category, as you saw in the text lesson. Floats can often be float32 if you do not need 15 significant digits.

import numpy as np
import pandas as pd

n = 100_000
rng = np.random.default_rng(0)
df = pd.DataFrame({
    "user_id": np.arange(n),
    "age": rng.integers(18, 80, n),
    "country": rng.choice(["IN", "US", "GB", "DE", "BR"], n),
    "score": rng.random(n),
})
before = df.memory_usage(deep=True).sum()

slim = df.assign(
    user_id=pd.to_numeric(df["user_id"], downcast="unsigned"),
    age=pd.to_numeric(df["age"], downcast="integer"),
    country=df["country"].astype("category"),
    score=df["score"].astype("float32"),
)
after = slim.memory_usage(deep=True).sum()

print(slim.dtypes)
print(f"memory cut by {1 - after / before:.0%}")

user_id      uint32
age            int8
country    category
score       float32
dtype: object
memory cut by 71%

The best place to apply these types is at read time, so the big version never exists: pd.read_csv(path, dtype={"age": "int8", "country": "category"}, usecols=[...]). Loading only the columns you need with usecols is often the single biggest win.

PyArrow-backed dtypes

pandas can store columns using Apache Arrow instead of NumPy. In pandas 3, text columns already use Arrow under the hood when PyArrow is installed, which is a big part of why strings got faster and smaller. You can opt in for all columns when reading: pd.read_csv(path, engine="pyarrow", dtype_backend="pyarrow"). The pyarrow CSV engine reads in parallel and is usually several times faster than the default parser, and Arrow types support missing values in integer and boolean columns without converting them to float.

pip install pyarrow

# Fast multi-threaded CSV parsing with Arrow-backed columns
df = pd.read_csv("events.csv", engine="pyarrow", dtype_backend="pyarrow")

# Parquet keeps the types you chose, so the next load is instant and typed
df.to_parquet("events.parquet")
df = pd.read_parquet("events.parquet", columns=["user_id", "event"])

Stream: process a file in chunks

If a file is bigger than your RAM, you cannot load it at once, but you usually do not need to. Pass chunksize to read_csv and you get an iterator of smaller DataFrames. Aggregate each chunk, keep only the small partial results, and combine them at the end.

import pandas as pd
from io import StringIO

# Pretend this is a 50 GB file
lines = ["country,amount"] + [f"{c},{a}" for c, a in
         [("IN", 10), ("US", 25), ("IN", 5), ("GB", 40), ("US", 15), ("IN", 20)] * 1000]
big_csv = StringIO("\n".join(lines))

partials = []
for chunk in pd.read_csv(big_csv, chunksize=1_000):
    partials.append(chunk.groupby("country")["amount"].sum())

totals = pd.concat(partials).groupby(level=0).sum()
print(len(partials), "chunks")
print(totals)

6 chunks
country
GB    40000
IN    35000
US    40000
Name: amount, dtype: int64

This works for anything that can be combined from parts: sums, counts, min and max. For a mean, sum the totals and counts separately and divide at the end. Medians and distinct counts across chunks are harder, which is a sign you may want one of the tools below.

Avoid the slow patterns

Once the data fits, most remaining slowness is in the code. These are the usual suspects, with their fast replacements:

• Looping with iterrows or apply(axis=1): replace with vectorized column math, np.where, np.select or merge.
• Growing a DataFrame in a loop with pd.concat on every iteration: collect pieces in a list and concat once at the end.
• Repeated string parsing: parse dates once with pd.to_datetime(..., format=...), not per row.
• Filtering the same big table many times: filter once early, or set_index and use .loc lookups.
• Re-reading CSVs on every run: convert to Parquet once.

import time
import pandas as pd

rows = [{"id": i, "value": i * 2} for i in range(3_000)]

start = time.perf_counter()
df = pd.DataFrame()
for r in rows:                                   # slow: copies every time
    df = pd.concat([df, pd.DataFrame([r])], ignore_index=True)
t_loop = time.perf_counter() - start

start = time.perf_counter()
df2 = pd.DataFrame(rows)                         # fast: build once
t_once = time.perf_counter() - start

print(df.equals(df2), t_once < t_loop)

True True

Growing the DataFrame row by row copies all the existing data on every iteration, so the cost grows quadratically. Building from a list once is typically hundreds of times faster.

When pandas is not the right tool

pandas is single-threaded for most operations and needs data in memory. When your data is consistently larger than about a quarter of your RAM, or queries take minutes, these tools are well worth learning in 2026:

• DuckDB: an in-process SQL engine that queries CSV and Parquet files (even ones larger than memory) and can read and return pandas DataFrames directly: duckdb.sql("SELECT ... FROM 'events.parquet'").df().
• Polars: a multi-threaded DataFrame library with a lazy API that optimizes whole queries and streams large files. Its syntax differs from pandas but the concepts carry over.
• A database or warehouse (Postgres, BigQuery, Snowflake): push heavy aggregations to where the data lives and pull only the result, as in the I/O lesson.

Recap

• Measure with memory_usage(deep=True) before optimizing.
• Load only needed columns, use small numeric types and category, ideally at read time.
• The PyArrow engine and Parquet files make loading much faster.
• Use chunksize to aggregate files that do not fit in memory.
• Replace row loops and repeated concat with vectorized code; switch to DuckDB or Polars when data outgrows RAM.

# Write your solution here