Lesson 47 · Pandas Full Course
Process Huge CSV Files in Pandas with chunksize and Dask | Pandas Course #47
In this lesson you'll be able to: Read a big CSV piece by piece with chunksize Combine per-chunk results correctly: sums, counts and means Filter a large…
- CoursePandas Full Course
- Lesson47 of 65
- FormatJupyter notebook · 39 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- online_retail.csv.gz preview7.1 MB
📓 Full notebook
Download .ipynbPandas 47: Handling Large Files with Chunking and Dask#
In this lesson you'll be able to:
- Read a big CSV piece by piece with chunksize
- Combine per-chunk results correctly: sums, counts and means
- Filter a large file and save the result chunk by chunk
- Split a file into parts and analyse them with Dask
- Choose between pandas, chunks and Dask Dataset: Online Retail, 541,909 sales (online_retail.csv.gz). Needs: pip install "dask[dataframe]"
Warm-up: the answers to check against#
Lesson 46 used eval and query. We'll use them for reference answers.
import pandas as pd
DATA = "data/pandas/online_retail.csv.gz"
df = pd.read_csv(DATA)
df = df.eval("Revenue = Quantity * UnitPrice")
print(round(df["Revenue"].sum(), 2))
print(len(df.query("Country == 'France'")))
Read a file in chunks#
Problem: a file bigger than memory can't be loaded in one go.
peek = pd.read_csv(DATA, nrows=5)
peek[["InvoiceNo", "Quantity", "Country"]]
reader = pd.read_csv(DATA, chunksize=100_000)
print(type(reader).__name__)
Common mistake: treating the reader like a DataFrame
reader.head()
first = reader.get_chunk()
print(first.shape)
reader.close()
first[["InvoiceNo", "Description", "Quantity"]].head(3)
Quick check: 541,909 rows in chunks of 100,000. How many chunks?
for i, chunk in enumerate(pd.read_csv(DATA, chunksize=100_000)):
print(i, len(chunk), chunk.index[0])
for size in [50_000, 250_000]:
n = sum(1 for _ in pd.read_csv(DATA, chunksize=size))
print(f"chunksize {size:,}: {n} chunks")
Choosing a chunksize:
- Bigger chunks: fewer loops, more memory per chunk
- Smaller chunks: less memory, more overhead
chunk_mb = first.memory_usage(deep=True).sum()
full_mb = df.memory_usage(deep=True).sum()
print("A chunk needs under a quarter:", chunk_mb < full_mb / 4)
Combine chunk results correctly#
Problem: each chunk only sees part of the data.
total = 0
for chunk in pd.read_csv(DATA, chunksize=100_000):
total += (chunk["Quantity"] * chunk["UnitPrice"]).sum()
print(round(total, 2))
print(abs(total - df["Revenue"].sum()) < 0.01)
Quick check: is the average of the chunk averages the true average?
means = []
for chunk in pd.read_csv(DATA, chunksize=100_000):
means.append(chunk["UnitPrice"].mean())
print(round(sum(means) / len(means), 4))
print(round(df["UnitPrice"].mean(), 4))
price_sum, rows = 0, 0
for chunk in pd.read_csv(DATA, chunksize=100_000):
price_sum += chunk["UnitPrice"].sum()
rows += len(chunk)
print(round(price_sum / rows, 4))
parts = []
for chunk in pd.read_csv(DATA, chunksize=100_000):
parts.append(chunk["Country"].value_counts())
counts = pd.concat(parts).groupby(level=0).sum()
print(counts.nlargest(3))
print(counts.equals(df["Country"].value_counts().sort_index()))
sums, cnts = [], []
for chunk in pd.read_csv(DATA, chunksize=100_000):
rev = chunk["Quantity"] * chunk["UnitPrice"]
sums.append(rev.groupby(chunk["Country"]).sum())
cnts.append(chunk["Country"].value_counts())
by_sum = pd.concat(sums).groupby(level=0).sum()
by_cnt = pd.concat(cnts).groupby(level=0).sum()
(by_sum / by_cnt).nlargest(3).round(2)
true_rev = df.groupby("Country")["Revenue"].mean()
diff = (by_sum / by_cnt - true_rev).abs().max()
print("All 38 countries match:", diff < 1e-9)
Your turn#
Count the returned lines (Quantity < 0) chunk by chunk. Check it against df.
returns = 0
for chunk in pd.read_csv(DATA, chunksize=100_000):
returns += (chunk["Quantity"] < 0).sum()
print(returns)
print(returns == len(df.query("Quantity < 0")))
Read less, filter as you go, and save#
Problem: often you only need a small slice of a huge file.
cols = ["InvoiceNo", "Quantity", "UnitPrice", "Country"]
pieces = []
for chunk in pd.read_csv(DATA, chunksize=100_000, usecols=cols,
dtype={"Country": "category"}):
pieces.append(chunk.query("Country == 'France'"))
france = pd.concat(pieces)
print(france.shape)
import os
os.makedirs("output/pandas", exist_ok=True)
RET = "output/pandas/l47_returns.csv"
Common mistake: writing the header with every chunk
BAD = "output/pandas/l47_returns_bad.csv"
for i, chunk in enumerate(pd.read_csv(DATA, chunksize=100_000)):
back = chunk.query("Quantity < 0")
back.to_csv(BAD, mode="w" if i == 0 else "a", index=False)
pd.read_csv(BAD, dtype={"Quantity": "int64"})
for i, chunk in enumerate(pd.read_csv(DATA, chunksize=100_000)):
back = chunk.query("Quantity < 0")
back.to_csv(RET, mode="w" if i == 0 else "a",
header=(i == 0), index=False)
saved = pd.read_csv(RET, dtype={"Quantity": "int64"})
print(saved.shape)
Your turn#
Save every line worth over 500 pounds to l47_big_lines.csv, chunk by chunk.
BIG = "output/pandas/l47_big_lines.csv"
for i, chunk in enumerate(pd.read_csv(DATA, chunksize=100_000)):
big = chunk[chunk["Quantity"] * chunk["UnitPrice"] > 500]
big.to_csv(BIG, mode="w" if i == 0 else "a",
header=(i == 0), index=False)
big_lines = pd.read_csv(BIG)
print(len(big_lines))
big_lines["Country"].value_counts().head(3)
Split a big file into parts#
Problem: one compressed file can't be shared out between workers. Many smaller Parquet files can, and Dask loves them.
Quick check: why fix the type of InvoiceNo for every part?
head100 = pd.read_csv(DATA, nrows=100)
print(head100["InvoiceNo"].dtype, df["InvoiceNo"].dtype)
print(df["InvoiceNo"].iloc[141])
PARTS = "output/pandas/l47_parts"
os.makedirs(PARTS, exist_ok=True)
text = {"InvoiceNo": "str", "StockCode": "str"}
reader = pd.read_csv(DATA, chunksize=100_000, dtype=text)
for i, chunk in enumerate(reader):
chunk.to_parquet(f"{PARTS}/part_{i}.parquet")
sorted(os.listdir(PARTS))
p0 = pd.read_parquet(os.path.join(PARTS, "part_0.parquet"))
print(p0.shape)
print(p0["InvoiceNo"].iloc[0], p0["Quantity"].dtype)
Analyse the parts with Dask#
Problem: chunk loops get long. Dask writes the loop for you. A Dask DataFrame is many pandas pieces. Nothing runs until .compute().
import dask.dataframe as dd
ddf = dd.read_parquet(PARTS)
print(ddf.npartitions)
ddf
ddf.head(3)
rows = ddf.map_partitions(len).compute()
print(list(rows))
rev = (ddf["Quantity"] * ddf["UnitPrice"]).sum()
print(type(rev).__name__)
print(round(rev.compute(), 2))
Common mistake: asking Dask for a row by position
ddf.iloc[5]
ddf.head(6).iloc[5]
top = ddf.groupby("Country")["Quantity"].sum().nlargest(3)
top = top.compute()
print(top)
print(top.equals(df.groupby("Country")["Quantity"].sum().nlargest(3)))
avg = ddf.groupby("Country")["UnitPrice"].mean().compute()
true_avg = df.groupby("Country")["UnitPrice"].mean()
print((avg.sort_index() - true_avg).abs().max() < 1e-9)
n_cust = ddf["CustomerID"].nunique()
n_inv = ddf["InvoiceNo"].nunique()
cust, inv = dd.compute(n_cust, n_inv)
print(cust, inv)
Your turn#
Use query on ddf to count the French lines. Does it match the warm-up?
fr = ddf.query("Country == 'France'")
print(len(fr))
Which tool when?
- Fits in memory: plain pandas, simplest and fastest
- Too big, one simple pass: a chunk loop
- Too big, lots of analysis: Dask over Parquet parts
Mini-project: monthly revenue, without loading the file#
Which month brought the shop the most revenue?
fmt = "%m/%d/%Y %H:%M"
when = dd.to_datetime(ddf["InvoiceDate"], format=fmt)
ddf = ddf.assign(Month=when.dt.strftime("%Y-%m"),
Revenue=ddf["Quantity"] * ddf["UnitPrice"])
monthly = ddf.groupby("Month")["Revenue"].sum().compute()
monthly = monthly.sort_index()
(monthly / 1e6).round(2)
orders = ddf.groupby("Month")["InvoiceNo"].nunique().compute()
per_order = (monthly / orders.sort_index()).round(0)
print(orders["2011-11"], orders["2011-02"])
print(per_order["2011-11"], per_order["2011-02"])
check = 0
for chunk in pd.read_csv(DATA, chunksize=100_000):
nov = chunk[chunk["InvoiceDate"].str.startswith("11/")]
check += (nov["Quantity"] * nov["UnitPrice"]).sum()
print(abs(check - monthly["2011-11"]) < 0.01)
ax = (monthly / 1e6).plot(kind="bar", figsize=(8, 4), rot=45)
ax.set_title("Online Retail: revenue by month")
ax.set_xlabel("Month")
ax.set_ylabel("Revenue (million GBP)")
peak = monthly.idxmax()
full = monthly.drop(["2010-12", "2011-12"])
lift = monthly[peak] / full.mean()
print(f"{peak}: {monthly[peak] / 1e6:.2f}m, {lift:.1f}x the average")
Recap#
- chunksize returns a reader; loop over it for DataFrame slices
- Carry running sums and counts; never average the averages
- Filter each chunk, and write the header only once
- Dask is lazy pandas over many parts; .compute() runs it
- Fits in memory: pandas. Simple pass: chunks. Lots of work: Dask
Challenges#
- With chunks, find the total revenue for Germany.
- With chunks, compute the true mean Quantity, then check it.
- With Dask, find the five products sold in the largest quantities.
What's next#
Lesson 48: Hands-on - Optimizing Large Datasets in Pandas Put dtypes, query, eval and chunks together on one real dataset.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



