Lesson 48 · Pandas Full Course
Optimize Large Datasets in Pandas: Load Faster, Use Less Memory | Pandas Course #48
In this lesson you'll be able to: Measure where a DataFrame's memory really goes Load only the columns and types you need with readcsv Shrink columns safely…
- CoursePandas Full Course
- Lesson48 of 65
- FormatJupyter notebook · 43 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 48: Hands-on - Optimizing Large Datasets in Pandas#
In this lesson you'll be able to:
- Measure where a DataFrame's memory really goes
- Load only the columns and types you need with read_csv
- Shrink columns safely with category, downcasting and nullable integers
- Replace loops with vectorised maths and aggregate in chunks
- Save to Parquet so your types survive the round trip Dataset: a year of UK online retail sales (data/pandas/online_retail.csv.gz)
Warm-up: count rows chunk by chunk#
Lesson 47 read big files in chunks. Same trick, our new file.
import pandas as pd
PATH = "data/pandas/online_retail.csv.gz"
reader = pd.read_csv(PATH, chunksize=100_000)
rows = sum(len(chunk) for chunk in reader)
print(rows)
df = pd.read_csv(PATH)
print(df.shape)
df.head(3)
Measure before you optimise#
Problem: find out which columns are eating the memory.
df.info(memory_usage="deep")
report = pd.DataFrame({
"dtype": df.dtypes.astype(str),
"unique": df.nunique(),
"mb": df.memory_usage(deep=True, index=False) / 1e6,
})
report.round(1).sort_values("mb", ascending=False)
Quick check: Country has 38 values but over 500,000 rows. What does that suggest?
share = df.nunique() / len(df)
share.round(4).sort_values()
Your turn#
Which columns have missing values, and how many?
gaps = df.isna().sum()
gaps[gaps > 0]
Load only what you need#
Problem: avoid loading columns and types you will throw away.
header = pd.read_csv(PATH, nrows=0)
print(header.columns.tolist())
Common mistake: guessing a column name in usecols
pd.read_csv(PATH, usecols=["Country", "Price"])
cols = ["InvoiceNo", "StockCode", "Quantity", "InvoiceDate",
"UnitPrice", "CustomerID", "Country"]
slim = pd.read_csv(PATH, usecols=cols)
print(slim.shape)
Common mistake: a plain integer type for a column with gaps
pd.read_csv(PATH, usecols=cols, dtype={"CustomerID": "int32"})
types = {
"CustomerID": "Int32",
"Country": "category",
"StockCode": "category",
}
fmt = "%m/%d/%Y %H:%M"
lean = pd.read_csv(PATH, usecols=cols, dtype=types,
parse_dates=["InvoiceDate"], date_format=fmt)
lean.dtypes
test = pd.read_csv(PATH, usecols=cols, dtype=types, nrows=1000,
parse_dates=["InvoiceDate"], date_format=fmt)
print(test.shape)
print(test.dtypes.astype(str).tolist())
before = df.memory_usage(deep=True).sum()
after = lean.memory_usage(deep=True).sum()
print(len(lean), "rows kept")
print(f"lean uses {after / before:.0%} of the original memory")
Shrink columns after loading#
Problem: make an existing frame smaller without changing any value.
country = df["Country"].astype("category")
print(len(country.cat.categories))
print(country.cat.codes.dtype)
country.cat.codes.head(3)
q = pd.to_numeric(df["Quantity"], downcast="integer")
print(q.dtype)
print(q.min(), q.max())
Quick check: what if we force Quantity into int8, which only holds -128 to 127?
forced = df["Quantity"].astype("int8")
changed = (forced != df["Quantity"]).sum()
print(changed)
print(forced.max(), forced.min())
Common mistake: converting a column with gaps to a plain int
df["CustomerID"].astype("int32")
ids = df["CustomerID"].astype("Int32")
print(ids.dtype)
print(ids.isna().sum())
ids.head(3)
Common mistake: writing a brand new value into a category
country.iloc[0] = "Atlantis"
country = country.cat.add_categories(["Atlantis"])
country.iloc[0] = "Atlantis"
print(len(country.cat.categories))
print(country.iloc[0])
Your turn#
Make Description a category. How many categories does it have?
desc = df["Description"].astype("category")
print(len(desc.cat.categories))
print(desc.cat.codes.dtype)
Vectorise instead of looping#
Problem: calculate on every row without a slow Python loop.
import time
small = lean.head(20_000)
t0 = time.perf_counter()
slow = small.apply(lambda r: r["Quantity"] * r["UnitPrice"], axis=1)
t1 = time.perf_counter()
fast = small["Quantity"] * small["UnitPrice"]
t2 = time.perf_counter()
print(f"apply: {t1 - t0:.3f}s vectorised: {t2 - t1:.4f}s")
print(slow.equals(fast))
lean["revenue"] = lean["Quantity"] * lean["UnitPrice"]
lean["cancelled"] = lean["InvoiceNo"].str.startswith("C")
print(lean["cancelled"].sum())
print(round(lean["revenue"].sum(), 2))
import numpy as np
big = lean["Quantity"] >= 12
lean["order_size"] = np.where(big, "pack", "small")
lean["order_size"].value_counts()
Common mistake: an if statement on a whole column
if lean["Quantity"] > 0:
print("positive")
positive = lean["Quantity"] > 0
print(positive.sum(), (~positive).sum())
bulk = lean.query("Quantity >= 1000 and not cancelled")
print(len(bulk))
bulk[["Quantity", "UnitPrice", "Country"]].head(3)
Your turn#
Total revenue from lines that were NOT cancelled.
kept = lean.loc[~lean["cancelled"], "revenue"]
print(round(kept.sum(), 2))
Aggregate a big file in chunks#
Problem: summarise a file that might not fit in memory.
parts = []
use = ["Country", "Quantity", "UnitPrice"]
for chunk in pd.read_csv(PATH, usecols=use, chunksize=100_000):
chunk["revenue"] = chunk["Quantity"] * chunk["UnitPrice"]
parts.append(chunk.groupby("Country")["revenue"].sum())
print(len(parts))
by_country = pd.concat(parts).groupby(level=0).sum()
by_country.sort_values(ascending=False).head(4).round(2)
full = lean.groupby("Country", observed=True)["revenue"].sum()
diff = (full - by_country).abs().max()
print(round(diff, 6))
Quick check: can we average the chunk averages to get the overall average price?
means = [c["UnitPrice"].mean()
for c in pd.read_csv(PATH, usecols=use, chunksize=150_000)]
print(round(sum(means) / len(means), 4))
print(round(lean["UnitPrice"].mean(), 4))
total, count = 0.0, 0
for c in pd.read_csv(PATH, usecols=use, chunksize=150_000):
total += c["UnitPrice"].sum()
count += len(c)
print(round(total / count, 4))
Your turn#
Count the lines per country, chunk by chunk, reading only Country.
counts = []
for c in pd.read_csv(PATH, usecols=["Country"], chunksize=100_000):
counts.append(c["Country"].value_counts())
lines = pd.concat(counts).groupby(level=0).sum()
lines.sort_values(ascending=False).head(3)
Save in a format that keeps your types#
Problem: don't lose all that optimisation the next time you load.
import os
os.makedirs("output/pandas", exist_ok=True)
lean.head(1000).to_csv("output/pandas/l48_sample.csv", index=False)
back = pd.read_csv("output/pandas/l48_sample.csv")
back[["Country", "CustomerID", "InvoiceDate"]].dtypes
pq = "output/pandas/l48_retail.parquet"
lean.to_parquet(pq, index=False)
back = pd.read_parquet(pq, columns=["Country", "CustomerID"])
print(back.shape)
back.dtypes
Your turn#
Read only InvoiceDate from the Parquet file and find the first and last dates.
dates = pd.read_parquet(pq, columns=["InvoiceDate"])["InvoiceDate"]
print(dates.min())
print(dates.max())
Mini-project: which months and countries drive sales?#
Plan: load lean from Parquet, keep real sales, group, chart, conclude.
sales = pd.read_parquet(pq)
real = ~sales["cancelled"] & (sales["UnitPrice"] > 0)
sales = sales[real]
print(len(sales))
print(round(sales["revenue"].sum(), 2))
month = sales["InvoiceDate"].dt.to_period("M")
monthly = sales.groupby(month)["revenue"].sum().round(0)
monthly.tail(4)
import matplotlib.pyplot as plt
ax = (monthly / 1000).plot(kind="bar", figsize=(8, 4))
ax.set_title("Online retail revenue by month")
ax.set_xlabel("Month")
ax.set_ylabel("Revenue (thousands)")
plt.show()
abroad = sales[sales["Country"] != "United Kingdom"]
top = abroad.groupby("Country", observed=True)["revenue"].sum()
top = top.sort_values(ascending=False).head(5).round(0)
top
keys = ["Country", "InvoiceNo"]
orders = sales.groupby(keys, observed=True)["revenue"].sum()
aov = orders.groupby(level=0).mean()
aov[["United Kingdom"] + top.index[:3].tolist()].round(0)
uk = sales.loc[sales["Country"] == "United Kingdom", "revenue"].sum()
share = uk / sales["revenue"].sum()
best = monthly.idxmax()
print(f"UK share: {share:.0%}, best month: {best}")
print(f"Top export market: {top.index[0]}")
Recap#
- Measure first: memory_usage(deep=True) and nunique show the targets
- Choose usecols, dtype and parse_dates when you read the file
- category for repeated text, to_numeric downcast, Int32 for gaps
- Vectorise with column maths and masks, never row loops
- Combine chunk sums and counts; save to Parquet to keep types
Challenges#
- Load only Country and Quantity with types, and find total units per country.
- Use chunks to count cancelled lines per country.
- Find the busiest hour of the day from the Parquet file.
What's next#
Lesson 49: Working with API Data and JSON Normalization Turn nested JSON from web APIs into flat, analysable tables.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



