Big Files

You double click a file, and your laptop freezes. The fan screams. Everything stops. Pandas loads the whole file into memory, and most people don't realise how wasteful that can be. Before you ask for a bigger machine, there are four simple tricks to try.

• The laptop freezes
• Pandas loads everything
• Four tricks to try first

Shrinking a table

First, use better data types. Text columns take up a lot of space, as pandas stores every copy of the word Coffee. The category type stores each word once, and uses small numbers instead. And we can squeeze whole numbers down into smaller types. Run it. From around eight megabytes to around three. That's more than half saved, with a few lines of code, and with no change in the data at all.

import pandas as pd

sales = pd.read_csv("sales.csv")
before = sales.memory_usage(deep=True).sum() / 1e6

small = sales.copy()
for col in ["product", "category", "city", "region"]:
    small[col] = small[col].astype("category")
small["units"] = pd.to_numeric(small["units"], downcast="integer")
small["store_id"] = pd.to_numeric(small["store_id"], downcast="integer")
after = small.memory_usage(deep=True).sum() / 1e6

print(f"Before: {before:.1f} MB")
print(f"After:  {after:.1f} MB")

Before: 7.9 MB
After:  3.4 MB

Reading in pieces

Second, if you don't need all of the rows at once, don't load them all at once. Chunk size makes pandas hand you the file in pieces. We add up each piece, and keep a running total, then throw the piece away. Run it. We've processed the whole file, and memory never held more than twenty thousand rows. That's how you can summarise a file that's far bigger than your computer's memory.

import pandas as pd

total = {}
rows = 0
for chunk in pd.read_csv("sales.csv", chunksize=20000):
    rows += len(chunk)
    for city, value in chunk.groupby("city")["revenue"].sum().items():
        total[city] = total.get(city, 0) + value

print(f"Read {rows:,} rows in pieces")
print(pd.Series(total).sort_values(ascending=False).head(3).round(0).to_string())

Read 70,176 rows in pieces
Osaka     786351.0
Milan     674850.0
Bergen    647906.0

Parquet

Third, stop using CSV. Parquet is a file format built for analysis. It's compressed, it remembers your data types, and it can read just the columns that you need. We save the same data both ways, and compare. Run it. The file is more than ten times smaller. On a tiny file like this one, CSV still reads faster, so don't trust the timing here. On tens of millions of rows, reading two columns from a parquet file is dramatically quicker, as it can skip all the rest.

import os, time
import pandas as pd

sales = pd.read_csv("sales.csv")
sales.to_parquet("sales.parquet")
print(f"CSV:     {os.path.getsize('sales.csv') / 1e6:.1f} MB")
print(f"Parquet: {os.path.getsize('sales.parquet') / 1e6:.1f} MB")

t = time.time(); pd.read_csv("sales.csv", usecols=["city", "profit"])
print(f"CSV read:     {time.time() - t:.3f}s")
t = time.time(); pd.read_parquet("sales.parquet", columns=["city", "profit"])
print(f"Parquet read: {time.time() - t:.3f}s")

CSV:     4.0 MB
Parquet: 0.3 MB
CSV read:     0.006s
Parquet read: 0.026s

Faster engines

Fourth, use a faster engine. You've met Duck DB. Another popular choice is Polars, which runs on all of your processor's cores, and waits until the end to plan the work. Scan csv doesn't read anything yet. Collect runs the whole plan at once. Run it. Pandas wins on this small file, because Polars pays a start up cost. The roles reverse on big data, where it can be many times faster. Use the simple tool until it hurts, then switch.

import time
import pandas as pd
import polars as pl

t = time.time()
a = pd.read_csv("sales.csv").groupby(["city", "product"])["profit"].sum()
print(f"pandas {time.time() - t:.3f}s")

t = time.time()
b = (pl.scan_csv("sales.csv").group_by(["city", "product"])
       .agg(pl.col("profit").sum()).sort("profit", descending=True).collect())
print(f"polars {time.time() - t:.3f}s")
print(b.head(2))

pandas 0.014s
polars 0.045s
shape: (2, 3)
┌───────┬─────────┬───────────┐
│ city  ┆ product ┆ profit    │
│ ---   ┆ ---     ┆ ---       │
│ str   ┆ str     ┆ f64       │
╞═══════╪═════════╪═══════════╡
│ Osaka ┆ Latte   ┆ 151186.55 │
│ Milan ┆ Latte   ┆ 129973.1  │
└───────┴─────────┴───────────┘

Summary