Skip to content
Tutorials Center
My learning

Topics

Lesson 51 of 64 ยท Performance and Scale

When Data Gets Big

Learn what to do when a DataFrame no longer fits in memory. Chunked processing, sampling, Parquet, PyArrow, Polars, DuckDB and Dask, and when to leave pandas.

pandas works well for tables that fit comfortably in your computer's memory, which today means millions of rows and often tens of millions. Beyond that you meet a wall: loading takes minutes, operations make temporary copies, and eventually Python stops with a memory error. This lesson maps the options, from cheap tricks to different tools, so that you can choose the smallest change that solves the problem.

Most of the tools in this lesson need packages or files that the browser editors do not have, so their code is shown read-only. Where a runnable stand-in can teach the underlying idea (chunking, sampling, partitions, pushing work into a database), we run it with plain pandas and the standard library.

Rule of thumb: try the cheap options first

flowchart TD
    A["Data is too big or too slow"] --> B{"Can you load fewer<br/>columns or rows?"}
    B -->|yes| B1["usecols, dtypes, filters,<br/>smaller dtypes"]
    B -->|no| C{"Is an estimate enough?"}
    C -->|yes| C1["Sample the data"]
    C -->|no| D{"Can the result be built<br/>piece by piece?"}
    D -->|yes| D1["Process in chunks"]
    D -->|no| E{"Fits on one machine<br/>but not in pandas?"}
    E -->|yes| E1["DuckDB or Polars<br/>(out-of-core engines)"]
    E -->|no| F["Dask or a database<br/>or a cluster"]

Work from left to right and top to bottom: stop at the first option that solves your problem.

Before any of it, apply Memory Usage and Smaller Dtypes: loading only the needed columns with compact dtypes often shrinks a table ten times, and no new tool is needed.

Chunked processing

Reading a file in chunks (see Reading CSV Data) lets you handle files larger than memory, as long as the answer can be assembled from per-chunk results. A mean needs a running sum and count, not the mean of chunk means, because chunks may have different sizes. A count of distinct values needs a set (or an approximate sketch) that carries over between chunks.

Python A running mean and distinct count across chunks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The memory used at any moment is one chunk plus the small running state. The set of seen users grows with the number of distinct users, so for hundreds of millions of distinct ids you would need an approximate counter instead. Operations that need all rows at once, such as an exact median, a full sort or a join between two huge tables, do not fit this pattern directly.

Sampling

Many questions do not need every row. A random sample of 1 percent often gives the same answer, with a quantifiable error, in a hundredth of the time. df.sample(frac=..., random_state=...) takes a reproducible random subset. A stratified sample (the same fraction from each group) keeps rare groups represented.

Python Estimating from a sample

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The sample mean lands close to the true mean. The stratified version keeps the segment shares fixed (about 1,700 free, 240 plus and 60 pro rows here), while a plain random sample leaves the number of pro rows to chance. Always fix random_state so results can be reproduced, and remember that sampling gives estimates: do not use it for exact counts, totals or billing.

Push the work into a database

If the data lives in a database, let the database do the heavy lifting and bring only the result into pandas. A SQL GROUP BY over 100 million rows returns a few hundred rows that pandas handles easily. The standard library's SQLite is available in the editors, so here is the pattern in miniature:

Python Aggregate in SQL, finish in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

This is exactly the idea behind DuckDB (below): the engine scans and aggregates, and only a small table reaches your DataFrame.

Parquet and PyArrow (read-only)

CSV is a poor format for large data: text is bulky, and every read has to parse every character of every column. Parquet is a binary, columnar, compressed format. Columns are stored separately, so a query that needs three of fifty columns reads only those three, and each value keeps its type, so there is no guessing and no date parsing. The usual engine is PyArrow (pip install pyarrow):

Python
# needs pyarrow (or fastparquet); not available in the browser editors
df.to_parquet("events.parquet", compression="zstd")

# read only what you need
subset = pd.read_parquet(
    "events.parquet",
    columns=["country", "amount"],
    filters=[("country", "==", "NO")],
)

# columns with Arrow dtypes
arrow_df = pd.read_parquet("events.parquet", dtype_backend="pyarrow")

For data that you write once and read many times, converting from CSV to Parquet is often the largest single speed-up available. Large datasets are commonly split into many Parquet files in folders such as year=2026/month=03/, which tools can skip by filtering on the folder names.

Polars (read-only)

Polars is a DataFrame library written in Rust. It runs queries in parallel on all CPU cores and has a lazy mode that builds a query plan and then reads only what is needed:

Python
# pip install polars   (not available in the browser editors)
import polars as pl

result = (
    pl.scan_parquet("events/*.parquet")        # lazy: nothing is loaded yet
    .filter(pl.col("amount") > 100)
    .group_by("country")
    .agg(pl.col("amount").sum().alias("revenue"))
    .sort("revenue", descending=True)
    .collect()                                 # run the optimised plan
)
pandas_frame = result.to_pandas()

The syntax differs from pandas, and Polars has no row index. It is a strong choice for new pipelines on a single machine and handles data larger than memory with its streaming engine.

DuckDB (read-only)

DuckDB is an in-process analytical SQL database, like SQLite but built for columns and big scans. It can query Parquet and CSV files directly, even DataFrames that are in memory, and hand the answer back as a DataFrame:

Python
# pip install duckdb   (not available in the browser editors)
import duckdb

summary = duckdb.sql("""
    SELECT country, SUM(amount) AS revenue
    FROM 'events/*.parquet'
    WHERE amount > 100
    GROUP BY country
""").df()

If you already know SQL (see the SQL tutorial), DuckDB gives you out-of-core aggregation and joins with almost no setup, and pairs naturally with pandas for the last mile.

Dask (read-only)

Dask splits a large table into many pandas partitions and runs your operations on each partition in parallel, on one machine or on a cluster. Its dask.dataframe API deliberately copies pandas:

Python
# pip install "dask[dataframe]"   (not available in the browser editors)
import dask.dataframe as dd

ddf = dd.read_parquet("events/*.parquet")
revenue = ddf.groupby("country")["amount"].sum()   # builds a task graph
print(revenue.compute())                           # runs it now

Nothing happens until .compute(). To see the idea without installing Dask, here is the same split-compute-combine scheme in plain pandas:

Python Partitions, the Dask idea in miniature

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Each partition is summarised independently (this step could run on 8 CPU cores or 8 machines), and the small partial results are combined at the end. Dask does the bookkeeping for you, but the same rule applies: aggregations combine easily, while global sorts and shuffles are expensive.

When to leave pandas

Situation Reasonable choice
A few GB, one machine, the code already uses pandas Smaller dtypes, usecols, Parquet, then maybe chunks
Analytics over big files, SQL-friendly questions DuckDB
New single-machine pipelines needing speed and parallelism Polars
Existing pandas code that must scale across cores or machines Dask
Data already in a warehouse (BigQuery, Snowflake, Postgres) Query there, fetch only results into pandas
Many TB, or streaming data A distributed engine such as Spark, outside this tutorial

pandas remains the common language of the ecosystem. Most of these tools read from and write to pandas DataFrames, so you can use them for the heavy step and pandas for exploring, plotting and modeling the result.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You read a huge CSV in chunks and want the overall mean of a column. What do you keep between chunks?

    Chunks can have different sizes, so averaging chunk means is wrong. Divide the total sum by the total count.
  2. 2. Why is Parquet usually faster than CSV for analysis?

    Columnar storage and built-in types mean less data is read and nothing is re-parsed from text.
  3. 3. Which statement about sampling is true?

    Sampling trades exactness for speed. random_state makes it repeatable, and stratifying keeps small groups represented.
  4. 4. What does Dask's .compute() do?

    Dask operations build a plan first and run nothing until you call compute().
  5. 5. A table is 3 GB and your laptop has 16 GB of memory. What should you try first?

    Cheap, local fixes often solve the problem. Switch tools only when they do not.

Summary

  • First shrink the problem: fewer columns and rows, compact dtypes.
  • Chunked reading handles files larger than memory when results combine from partial sums, counts and sets.
  • Sampling gives fast, reproducible estimates; stratify to keep small groups.
  • Let a database or engine aggregate big data and bring only the result into pandas.
  • Parquet (via PyArrow) is a compact, typed, columnar format that loads only the columns you ask for.
  • Polars (fast, lazy, parallel), DuckDB (SQL over files) and Dask (pandas-like, partitioned) extend your reach; they can exchange data with pandas.
  • Pick the smallest change that works, and measure before you switch tools.

Next: Common pandas Pitfalls