Topolearnpandas is in-memory and mostly single-threaded; knowing the escape routes avoids crashes and day-long jobs.
pandas loads everything into memory and most operations run on one core. Intermediate copies during joins or groupbys can need several times the input size, so trouble starts well before data reaches your RAM size. Options, roughly from least to most change:
Load less. Read only needed columns (usecols, Parquet columns=), filter rows at read time (read_parquet(filters=[("year", "=", 2024)]) skips row groups and partitions), and use compact dtypes (category, downcast numerics, Arrow strings). A 20 GB CSV is often 2 GB as typed, pruned Parquet.
Stream in chunks. pd.read_csv(path, chunksize=1_000_000) (and read_sql(chunksize=...)) yields DataFrames one at a time. This works for operations that decompose: filters, per-chunk aggregates you combine afterwards (sums and counts, not medians), or converting a big CSV into partitioned Parquet once.
Use Arrow as the hub. PyArrow datasets read partitioned Parquet lazily with predicate pushdown, and because Arrow is a shared memory format, data passes between PyArrow, Polars, DuckDB and pandas (with Arrow dtypes) with little or no copying.
Switch engines for the heavy step, then come back to pandas for the small result:
- DuckDB: an in-process SQL engine that queries Parquet files and even pandas DataFrames directly, in parallel and out of core.
- Polars: a multi-threaded DataFrame library with a lazy query optimiser and streaming execution.
- Dask DataFrame: a pandas-like API over many partitions, scaling to clusters.
- PySpark (with its pandas API) for distributed data already in a Spark platform.
Measure peak memory, not just runtime. And question the premise: a database query, a sample, or pre-aggregation often removes the need to load everything.
Example
import numpy as np
import pandas as pd
from pathlib import Path
path = Path("big.csv")
rng = np.random.default_rng(0)
pd.DataFrame({"store": rng.integers(0, 50, 300_000),
"amount": rng.random(300_000).round(2),
"note": "x" * 20}).to_csv(path, index=False)
# 1) stream: combine per-chunk partial aggregates (sums and counts compose)
partials = []
for chunk in pd.read_csv(path, usecols=["store", "amount"],
dtype={"store": "int16", "amount": "float32"},
chunksize=100_000):
partials.append(chunk.groupby("store")["amount"].agg(["sum", "count"]))
agg = pd.concat(partials).groupby(level=0).sum()
agg["mean"] = agg["sum"] / agg["count"]
print(agg.shape) # (50, 3)
# 2) convert once to partitioned Parquet, then read with pruning and filters
df = pd.read_csv(path, usecols=["store", "amount"])
df.assign(bucket=df.store % 5).to_parquet("big_pq", partition_cols=["bucket"])
part = pd.read_parquet("big_pq", columns=["amount"], filters=[("bucket", "=", 3)])
print(len(part) < len(df)) # True: only one partition read
# 3) heavy step elsewhere, e.g. DuckDB (pip install duckdb):
# import duckdb; duckdb.sql("SELECT store, avg(amount) FROM 'big_pq/**/*.parquet' GROUP BY 1").df()Remember
- Load less: prune columns, filter at read time, use compact dtypes
- Chunked reading works for decomposable operations like sums and counts
- Partitioned Parquet plus Arrow enable pushdown and cheap interchange
- Use DuckDB, Polars or Dask for the heavy step, pandas for the result
Watch out for
- Averaging per-chunk means to get an overall mean, which is wrong when chunks differ in size; combine sums and counts instead.