Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Pandas 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)
(541909, 8)
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()
No description has been provided for this image

Measure what each column costs#

Problem: you can't shrink memory until you know where it goes.

df.dtypes
InvoiceNo          str
StockCode          str
Description        str
Quantity         int64
InvoiceDate        str
UnitPrice      float64
CustomerID     float64
Country            str
dtype: object

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))
4335272
8.0
mb = df.memory_usage(deep=True) / 1e6
mb.sort_values(ascending=False).round(1)
Description    18.8
InvoiceDate    12.5
Country        11.6
InvoiceNo       7.6
StockCode       7.1
UnitPrice       4.3
Quantity        4.3
CustomerID      4.3
Index           0.0
dtype: float64
text = df.select_dtypes(exclude="number").columns
text_share = mb[text].sum() / mb.sum()
print("Text uses over half:", text_share > 0.5)
Text uses over half: True

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()
InvoiceNo      25900
StockCode       4070
Description     4223
Country           38
dtype: int64

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)
category
['Australia', 'Austria', 'Bahrain', 'Belgium']
0    36
1    36
2    36
dtype: int8
codes = country.cat.codes
print(codes.dtype)
print(codes.memory_usage(index=False))
int8
541909
before = df["Country"].memory_usage(deep=True)
after = country.memory_usage(deep=True)
print("At least 10x smaller:", before / after >= 10)
At least 10x smaller: True
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)
Category is faster: True
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
InvoiceNo      True
StockCode      True
Description    True
Country        True
dtype: bool

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)
int16
At least 5x smaller: True

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)
531225
Category is bigger: True

Quick check: which of our four text columns is the weakest candidate?

unique_share = df[text_cols].nunique() / len(df)
unique_share.round(4)
InvoiceNo      0.0478
StockCode      0.0075
Description    0.0078
Country        0.0001
dtype: float64

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))
True
False

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"
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[18], line 1
----> 1 country[country == "EIRE"] = "Ireland"

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:1130, in Series.__setitem__(self, key, value)
   1127 # otherwise with listlike other we interpret series[mask] = other
   1128 #  as series[mask] = other[mask]
   1129 try:
-> 1130     self._where(~key, value, inplace=True)
   1131 except InvalidIndexError:
   1132     # test_where_dups
   1133     self.iloc[key] = value

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:10128, in NDFrame._where(self, cond, other, inplace, axis, level)
  10122     align = self._get_axis_number(axis) == 1
  10124 if inplace:
  10125     # we may have different type blocks come out of putmask, so
  10126     # reconstruct the block manager
> 10128     new_data = self._mgr.putmask(mask=cond, new=other, align=align)
  10129     result = self._constructor_from_mgr(new_data, axes=new_data.axes)
  10130     self._update_inplace(result)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:491, in BaseBlockManager.putmask(self, mask, new, align)
    488     align_keys = ["mask"]
    489     new = extract_array(new, extract_numpy=True)
--> 491 return self.apply(
    492     "putmask",
    493     align_keys=align_keys,
    494     mask=mask,
    495     new=new,
    496 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:445, in BaseBlockManager.apply(self, f, align_keys, **kwargs)
    443         applied = b.apply(f, **kwargs)
    444     else:
--> 445         applied = getattr(b, f)(**kwargs)
    446     result_blocks = extend_blocks(applied, result_blocks)
    448 out = type(self).from_blocks(result_blocks, [ax.view() for ax in self.axes])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\blocks.py:1776, in EABackedBlock.putmask(self, mask, new)
   1772     values = values.T
   1774 try:
   1775     # Caller is responsible for ensuring matching lengths
-> 1776     values._putmask(mask, new)
   1777 except OutOfBoundsDatetime:
   1778     raise

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\_mixins.py:404, in NDArrayBackedExtensionArray._putmask(self, mask, value)
    390 def _putmask(self, mask: npt.NDArray[np.bool_], value) -> None:
    391     """
    392     Analogue to np.putmask(self, mask, value)
    393 
   (...)
    402         If value cannot be cast to self.dtype.
    403     """
--> 404     value = self._validate_setitem_value(value)
    406     np.putmask(self._ndarray, mask, value)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\categorical.py:1653, in Categorical._validate_setitem_value(self, value)
   1651     return self._validate_listlike(value)
   1652 else:
-> 1653     return self._validate_scalar(value)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\categorical.py:1678, in Categorical._validate_scalar(self, fill_value)
   1676     fill_value = self._unbox_scalar(fill_value)
   1677 else:
-> 1678     raise TypeError(
   1679         "Cannot setitem on a Categorical with a new "
   1680         f"category ({fill_value}), set the categories first"
   1681     ) from None
   1682 return fill_value

TypeError: Cannot setitem on a Categorical with a new category (Ireland), set the categories first
country = country.cat.rename_categories({"EIRE": "Ireland"})
print("EIRE" in country.cat.categories)
print((country == "Ireland").sum())
False
8196
trio = ["France", "Germany", "Spain"]
europe = country[country.isin(trio)]
print(len(europe), len(europe.cat.categories))
europe.value_counts().head(5)
20585 38
Country
Germany      9495
France       8557
Spain        2533
Australia       0
Austria         0
Name: count, dtype: int64
europe = europe.cat.remove_unused_categories()
print(list(europe.cat.categories))
['France', 'Germany', 'Spain']
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()
Quantity
Small     317418
Medium    201549
Bulk       12318
Name: count, dtype: int64

Common mistake: comparing an unordered category with >

(band > "Small").sum()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[23], line 1
----> 1 (band > "Small").sum()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py:85, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     82     other = sanitize_array(other, None)
     83     other = ensure_wrapped_if_datetimelike(other)
---> 85 return method(self, other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arraylike.py:58, in OpsMixin.__gt__(self, other)
     56 @unpack_zerodim_and_defer("__gt__")
     57 def __gt__(self, other):
---> 58     return self._cmp_method(other, operator.gt)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:6735, in Series._cmp_method(self, other, op)
   6732 lvalues = self._values
   6733 rvalues = extract_array(other, extract_numpy=True, extract_range=True)
-> 6735 res_values = ops.comparison_op(lvalues, rvalues, op)
   6737 return self._construct_result(res_values, name=res_name, other=other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\array_ops.py:341, in comparison_op(left, right, op)
    332         raise ValueError(
    333             "Lengths must match to compare", lvalues.shape, rvalues.shape
    334         )
    336 if should_extension_dispatch(lvalues, rvalues) or (
    337     (isinstance(rvalues, (Timedelta, BaseOffset, Timestamp)) or right is NaT)
    338     and lvalues.dtype != object
    339 ):
    340     # Call the method on lvalues
--> 341     res_values = op(lvalues, rvalues)
    343 # TODO: but not pd.NA?
    344 elif (is_scalar(rvalues) or rvalues_is_zerodim) and isna(rvalues):
    345     # numpy does not like comparisons vs None

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py:85, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     82     other = sanitize_array(other, None)
     83     other = ensure_wrapped_if_datetimelike(other)
---> 85 return method(self, other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\categorical.py:138, in _cat_compare_op.<locals>.func(self, other)
    136 if not self.ordered:
    137     if opname in ["__lt__", "__gt__", "__le__", "__ge__"]:
--> 138         raise TypeError(
    139             "Unordered Categoricals can only compare equality or not"
    140         )
    141 if isinstance(other, Categorical):
    142     # Two Categoricals can only be compared if the categories are
    143     # the same (maybe up to ordering, depending on ordered)
    145     msg = "Categoricals can only be compared if 'categories' are the same."

TypeError: Unordered Categoricals can only compare equality or not
band = band.cat.as_ordered()
print(band.cat.ordered)
print((band > "Small").sum())
True
213867
dates = pd.to_datetime(df["InvoiceDate"], format="%m/%d/%Y %H:%M")
day = dates.dt.day_name()
day.value_counts().sort_index()
InvoiceDate
Friday        82193
Monday        95111
Sunday        64375
Thursday     103857
Tuesday      101808
Wednesday     94565
Name: count, dtype: int64
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)
InvoiceDate
Monday        95111
Tuesday      101808
Wednesday     94565
Thursday     103857
Friday        82193
Saturday          0
Sunday        64375
Name: count, dtype: int64
sales = df.assign(Day=day)
by_day = sales.groupby("Day", observed=False)["Quantity"].sum()
by_day.tail(3)
Day
Friday      794440
Saturday         0
Sunday      467732
Name: Quantity, dtype: int64
sales.groupby("Day", observed=True)["Quantity"].sum().tail(3)
Day
Thursday    1167823
Friday       794440
Sunday       467732
Name: Quantity, dtype: int64

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))
-80995 80995
int32 2167636
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)
9747747.93
Off by under 1 pound: True

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]))
float64
2.55 2.549999952316284

Common mistake: casting a column with gaps to a plain integer

df["CustomerID"].astype("int32")
---------------------------------------------------------------------------
IntCastingNaNError                        Traceback (most recent call last)
Cell In[32], line 1
----> 1 df["CustomerID"].astype("int32")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:6529, in NDFrame.astype(self, dtype, copy, errors)
   6525     results = [ser.astype(dtype, errors=errors) for _, ser in self.items()]
   6527 else:
   6528     # else, only a single dtype is given
-> 6529     new_data = self._mgr.astype(dtype=dtype, errors=errors)
   6530     res = self._constructor_from_mgr(new_data, axes=new_data.axes)
   6531     return res.__finalize__(self, method="astype")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:627, in BaseBlockManager.astype(self, dtype, errors)
    626 def astype(self, dtype, errors: str = "raise") -> Self:
--> 627     return self.apply("astype", dtype=dtype, errors=errors)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:445, in BaseBlockManager.apply(self, f, align_keys, **kwargs)
    443         applied = b.apply(f, **kwargs)
    444     else:
--> 445         applied = getattr(b, f)(**kwargs)
    446     result_blocks = extend_blocks(applied, result_blocks)
    448 out = type(self).from_blocks(result_blocks, [ax.view() for ax in self.axes])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\blocks.py:607, in Block.astype(self, dtype, errors, squeeze)
    604         raise ValueError("Can not squeeze with more than one column.")
    605     values = values[0, :]  # type: ignore[call-overload]
--> 607 new_values = astype_array_safe(values, dtype, errors=errors)
    609 new_values = maybe_coerce_values(new_values)
    611 refs = None

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:240, in astype_array_safe(values, dtype, copy, errors)
    237     dtype = dtype.numpy_dtype
    239 try:
--> 240     new_values = astype_array(values, dtype, copy=copy)
    241 except (ValueError, TypeError):
    242     # e.g. _astype_nansafe can fail on object-dtype of strings
    243     #  trying to convert to float
    244     if errors == "ignore":

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:185, in astype_array(values, dtype, copy)
    182     values = values.astype(dtype, copy=copy)
    184 else:
--> 185     values = _astype_nansafe(values, dtype, copy=copy)
    187 # in pandas we don't store numpy str dtypes, so convert to object
    188 if isinstance(dtype, np.dtype) and issubclass(values.dtype.type, str):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:102, in _astype_nansafe(arr, dtype, copy, skipna)
     97     return lib.ensure_string_array(
     98         arr, skipna=skipna, convert_na_value=False
     99     ).reshape(shape)
    101 elif np.issubdtype(arr.dtype, np.floating) and dtype.kind in "iu":
--> 102     return _astype_float_to_int_nansafe(arr, dtype, copy)
    104 elif arr.dtype == object:
    105     # if we have a datetime/timedelta array of objects
    106     # then coerce to datetime64[ns] and use DatetimeArray.astype
    108     if lib.is_np_dtype(dtype, "M"):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:146, in _astype_float_to_int_nansafe(values, dtype, copy)
    142 """
    143 astype with a check preventing converting NaN to a meaningless integer value.
    144 """
    145 if not np.isfinite(values).all():
--> 146     raise IntCastingNaNError(
    147         "Cannot convert non-finite values (NA or inf) to integer."
    148         "Replace or remove non-finite values or cast to an integer type"
    149         "that supports these values (e.g. 'Int64')"
    150     )
    151 if dtype.kind == "u":
    152     # GH#45151
    153     if not (values >= 0).all():

IntCastingNaNError: Cannot convert non-finite values (NA or inf) to integer.Replace or remove non-finite values or cast to an integer typethat supports these values (e.g. 'Int64')
cust = df["CustomerID"].astype("Int32")
print(cust.isna().sum())
print(cust.memory_usage(index=False))
135080
2709545

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
InvoiceNo            category
StockCode            category
Description          category
Quantity                int32
InvoiceDate    datetime64[us]
UnitPrice             float32
CustomerID              Int32
Country              category
dtype: object
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)
At least 3x smaller: True

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
(541909, 3)
Quantity        int64
UnitPrice     float64
Country      category
dtype: object

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))
46431
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)
Country
Netherlands    284662.0
EIRE           263277.0
Germany        221698.0
France         197404.0
Australia      137077.0
Name: Revenue, dtype: float64
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))
True
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")
No description has been provided for this image
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)
InvoiceDate
Monday       224530.0
Tuesday      282818.0
Wednesday    297195.0
Thursday     392377.0
Friday       264578.0
Sunday        98444.0
Name: Revenue, dtype: float64
lead = top5.index[0]
share = top5.iloc[0] / by_country.sum()
print(f"{lead}: {share:.0%} of revenue outside the UK")
Netherlands: 18% 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#

  1. Which column of opt is now the largest? Can you shrink it?
  2. Make an ordered band from UnitPrice: Cheap, Mid, Premium.
  3. 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.