Lesson 45 · Pandas Full Course
Cut Pandas Memory Use by 90%: Category dtype and Downcasting | Pandas Course #45
In this lesson you'll be able to: Measure how much memory each column of a DataFrame uses Shrink repeated text with the category dtype Rename, order and…
- CoursePandas Full Course
- Lesson45 of 65
- FormatJupyter notebook · 42 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 45: Categorical Data and Memory Optimization#
In this lesson you'll be able to:
- Measure how much memory each column of a DataFrame uses
- Shrink repeated text with the category dtype
- Rename, order and tidy categories without errors
- Downcast numbers, and spot when category backfires
- Pick dtypes while loading, so big files arrive slim Dataset: Online Retail, 541,909 real shop sales (online_retail.csv.gz)
Warm-up: a quick chart of today's data#
Lesson 44 built interactive charts. Today, a quick static one.
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_csv("data/pandas/online_retail.csv.gz")
print(df.shape)
top = df["Country"].value_counts().head(6)
ax = top.plot(kind="bar", rot=30, figsize=(7, 4))
ax.set_title("Order lines by country (top 6)")
ax.set_xlabel("Country")
ax.set_ylabel("Order lines")
plt.show()
Measure what each column costs#
Problem: you can't shrink memory until you know where it goes.
df.dtypes
Quick check: how many bytes does an int64 column use per row?
qty_bytes = df["Quantity"].memory_usage(index=False)
print(qty_bytes)
print(qty_bytes / len(df))
mb = df.memory_usage(deep=True) / 1e6
mb.sort_values(ascending=False).round(1)
text = df.select_dtypes(exclude="number").columns
text_share = mb[text].sum() / mb.sum()
print("Text uses over half:", text_share > 0.5)
Shrink repeated text with category#
Problem: the same few words are stored on every single row.
text_cols = ["InvoiceNo", "StockCode", "Description", "Country"]
df[text_cols].nunique()
A category column stores two things:
- categories: each distinct value, kept once
- codes: a small integer per row, pointing at its category
country = df["Country"].astype("category")
print(country.dtype)
print(list(country.cat.categories[:4]))
country.cat.codes.head(3)
codes = country.cat.codes
print(codes.dtype)
print(codes.memory_usage(index=False))
before = df["Country"].memory_usage(deep=True)
after = country.memory_usage(deep=True)
print("At least 10x smaller:", before / after >= 10)
from timeit import timeit
t_text = timeit(lambda: df["Country"] == "France", number=20)
t_cat = timeit(lambda: country == "France", number=20)
print("Category is faster:", t_cat < t_text)
cats = df[text_cols].astype("category")
old = df[text_cols].memory_usage(deep=True, index=False)
new = cats.memory_usage(deep=True, index=False)
new < old
Your turn#
Convert StockCode to category. What type are its codes? Is it at least 5x smaller?
stock = df["StockCode"].astype("category")
print(stock.cat.codes.dtype)
ratio = (df["StockCode"].memory_usage(deep=True)
/ stock.memory_usage(deep=True))
print("At least 5x smaller:", ratio >= 5)
Spot when category backfires#
Problem: category only pays off when values repeat.
line_key = df["InvoiceNo"] + "-" + df["StockCode"]
print(line_key.nunique())
key_cat = line_key.astype("category")
bigger = (key_cat.memory_usage(deep=True)
> line_key.memory_usage(deep=True))
print("Category is bigger:", bigger)
Quick check: which of our four text columns is the weakest candidate?
unique_share = df[text_cols].nunique() / len(df)
unique_share.round(4)
Your turn#
Write worth_category(s): True when under half the values are unique.
def worth_category(s):
return s.nunique() / len(s) < 0.5
print(worth_category(df["Country"]))
print(worth_category(line_key))
Rename, order and tidy categories#
Problem: categories have rules, and breaking them raises errors.
Common mistake: assigning a value that isn't a category yet
country[country == "EIRE"] = "Ireland"
country = country.cat.rename_categories({"EIRE": "Ireland"})
print("EIRE" in country.cat.categories)
print((country == "Ireland").sum())
trio = ["France", "Germany", "Spain"]
europe = country[country.isin(trio)]
print(len(europe), len(europe.cat.categories))
europe.value_counts().head(5)
europe = europe.cat.remove_unused_categories()
print(list(europe.cat.categories))
qty = df.loc[df["Quantity"] > 0, "Quantity"]
band = pd.cut(qty, bins=[0, 5, 50, 100_000],
labels=["Small", "Medium", "Bulk"], ordered=False)
band.value_counts()
Common mistake: comparing an unordered category with >
(band > "Small").sum()
band = band.cat.as_ordered()
print(band.cat.ordered)
print((band > "Small").sum())
dates = pd.to_datetime(df["InvoiceDate"], format="%m/%d/%Y %H:%M")
day = dates.dt.day_name()
day.value_counts().sort_index()
week = ["Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Saturday", "Sunday"]
week_type = pd.CategoricalDtype(week, ordered=True)
day = day.astype(week_type)
day.value_counts(sort=False)
sales = df.assign(Day=day)
by_day = sales.groupby("Day", observed=False)["Quantity"].sum()
by_day.tail(3)
sales.groupby("Day", observed=True)["Quantity"].sum().tail(3)
Downcast numbers to smaller types#
Problem: int64 spends 8 bytes on numbers that fit in 4.
print(df["Quantity"].min(), df["Quantity"].max())
qty32 = pd.to_numeric(df["Quantity"], downcast="integer")
print(qty32.dtype, qty32.memory_usage(index=False))
price32 = df["UnitPrice"].astype("float32")
rev64 = (df["Quantity"] * df["UnitPrice"]).sum()
rev32 = (qty32 * price32).sum()
print(round(rev64, 2))
print("Off by under 1 pound:", abs(rev64 - rev32) < 1)
Quick check: will downcast="float" shrink UnitPrice to float32?
price_dc = pd.to_numeric(df["UnitPrice"], downcast="float")
print(price_dc.dtype)
print(df["UnitPrice"].iloc[0], float(price32.iloc[0]))
Common mistake: casting a column with gaps to a plain integer
df["CustomerID"].astype("int32")
cust = df["CustomerID"].astype("Int32")
print(cust.isna().sum())
print(cust.memory_usage(index=False))
Choose dtypes while loading#
Problem: converting after loading needs room for the fat version first.
dtypes = {
"InvoiceNo": "category", "StockCode": "category",
"Description": "category", "Country": "category",
"Quantity": "int32", "UnitPrice": "float32",
"CustomerID": "Int32",
}
opt = pd.read_csv("data/pandas/online_retail.csv.gz", dtype=dtypes)
opt["InvoiceDate"] = pd.to_datetime(opt["InvoiceDate"],
format="%m/%d/%Y %H:%M")
opt.dtypes
old_mb = df.memory_usage(deep=True).sum() / 1e6
new_mb = opt.memory_usage(deep=True).sum() / 1e6
print("At least 3x smaller:", old_mb / new_mb >= 3)
Your turn#
Load only Country, Quantity and UnitPrice, with Country as a category.
small = pd.read_csv("data/pandas/online_retail.csv.gz",
usecols=["Country", "Quantity", "UnitPrice"],
dtype={"Country": "category"})
print(small.shape)
small.dtypes
Mini-project: top export markets on the slim table#
Which countries outside the UK bring in the most revenue?
price = opt["UnitPrice"].astype("float64")
opt["Revenue"] = opt["Quantity"] * price
abroad = opt[opt["Country"] != "United Kingdom"]
print(len(abroad))
by_country = abroad.groupby("Country", observed=True)["Revenue"]
by_country = by_country.sum()
top5 = by_country.sort_values(ascending=False).head(5)
top5.round(0)
fat = df[df["Country"] != "United Kingdom"]
fat_rev = fat["Quantity"] * fat["UnitPrice"]
fat_top = fat_rev.groupby(fat["Country"]).sum().nlargest(5)
print(list(fat_top.index) == list(top5.index))
ax = (top5 / 1000).sort_values().plot(kind="barh", figsize=(7, 4))
ax.set_title("Top 5 export markets by revenue")
ax.set_xlabel("Revenue (thousand GBP)")
ax.set_ylabel("Country")
abroad_days = abroad["InvoiceDate"].dt.day_name().astype(week_type)
day_rev = abroad.groupby(abroad_days, observed=True)["Revenue"]
day_rev = day_rev.sum().round(0)
print(day_rev)
lead = top5.index[0]
share = top5.iloc[0] / by_country.sum()
print(f"{lead}: {share:.0%} of revenue outside the UK")
Recap#
- memory_usage(deep=True) shows what each column really costs
- category stores each value once, plus a small code per row
- Use it when values repeat; near-unique columns get bigger
- rename_categories, remove_unused_categories, as_ordered
- Downcast numbers, use Int32 for gaps, and set dtype= when loading
Challenges#
- Which column of opt is now the largest? Can you shrink it?
- Make an ordered band from UnitPrice: Cheap, Mid, Premium.
- Rename the category 'Unspecified' and count its rows.
What's next#
Lesson 46: Using eval() and query() for Performance Write filters and formulas as readable strings, and time them.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



