Mathew K Analytics

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…

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 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)
541909
df = pd.read_csv(PATH)
print(df.shape)
df.head(3)
(541909, 8)
InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice CustomerID Country
0 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 6 12/1/2010 8:26 2.55 17850.0 United Kingdom
1 536365 71053 WHITE METAL LANTERN 6 12/1/2010 8:26 3.39 17850.0 United Kingdom
2 536365 84406B CREAM CUPID HEARTS COAT HANGER 8 12/1/2010 8:26 2.75 17850.0 United Kingdom

Measure before you optimise#

Problem: find out which columns are eating the memory.

df.info(memory_usage="deep")
<class 'pandas.DataFrame'>
RangeIndex: 541909 entries, 0 to 541908
Data columns (total 8 columns):
 #   Column       Non-Null Count   Dtype  
---  ------       --------------   -----  
 0   InvoiceNo    541909 non-null  str    
 1   StockCode    541909 non-null  str    
 2   Description  540455 non-null  str    
 3   Quantity     541909 non-null  int64  
 4   InvoiceDate  541909 non-null  str    
 5   UnitPrice    541909 non-null  float64
 6   CustomerID   406829 non-null  float64
 7   Country      541909 non-null  str    
dtypes: float64(2), int64(1), str(5)
memory usage: 67.3 MB
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)
dtype unique mb
Description str 4223 18.8
InvoiceDate str 23260 12.5
Country str 38 11.6
InvoiceNo str 25900 7.6
StockCode str 4070 7.1
Quantity int64 722 4.3
UnitPrice float64 1630 4.3
CustomerID float64 4372 4.3

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()
Country        0.0001
Quantity       0.0013
UnitPrice      0.0030
StockCode      0.0075
Description    0.0078
CustomerID     0.0081
InvoiceDate    0.0429
InvoiceNo      0.0478
dtype: float64

Your turn#

Which columns have missing values, and how many?

gaps = df.isna().sum()
gaps[gaps > 0]
Description      1454
CustomerID     135080
dtype: int64

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())
['InvoiceNo', 'StockCode', 'Description', 'Quantity', 'InvoiceDate', 'UnitPrice', 'CustomerID', 'Country']

Common mistake: guessing a column name in usecols

pd.read_csv(PATH, usecols=["Country", "Price"])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[9], line 1
----> 1 pd.read_csv(PATH, usecols=["Country", "Price"])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\readers.py:872, in read_csv(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, skip_blank_lines, parse_dates, date_format, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, on_bad_lines, low_memory, memory_map, float_precision, storage_options, dtype_backend)
    860 kwds_defaults = _refine_defaults_read(
    861     dialect,
    862     delimiter,
   (...)
    868     dtype_backend=dtype_backend,
    869 )
    870 kwds.update(kwds_defaults)
--> 872 return _read(filepath_or_buffer, kwds)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\readers.py:300, in _read(filepath_or_buffer, kwds)
    297 _validate_names(kwds.get("names", None))
    299 # Create the parser.
--> 300 parser = TextFileReader(filepath_or_buffer, **kwds)
    302 if chunksize or iterator:
    303     return parser

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\readers.py:1643, in TextFileReader.__init__(self, f, engine, **kwds)
   1640     self.options["has_index_names"] = kwds["has_index_names"]
   1642 self.handles: IOHandles | None = None
-> 1643 self._engine = self._make_engine(f, self.engine)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\readers.py:1925, in TextFileReader._make_engine(self, f, engine)
   1922     raise ValueError(msg)
   1924 try:
-> 1925     return mapping[engine](f, **self.options)
   1926 except Exception:
   1927     if self.handles is not None:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\c_parser_wrapper.py:137, in CParserWrapper.__init__(self, src, **kwds)
    133 assert self.orig_names is not None
    134 if self.usecols_dtype == "string" and not set(usecols).issubset(
    135     self.orig_names
    136 ):
--> 137     self._validate_usecols_names(usecols, self.orig_names)
    139 if len(self.names) > len(usecols):
    140     self.names = [
    141         n
    142         for i, n in enumerate(self.names)
    143         if (i in usecols or n in usecols)
    144     ]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\base_parser.py:662, in ParserBase._validate_usecols_names(self, usecols, names)
    660 missing = [c for c in usecols if c not in names]
    661 if len(missing) > 0:
--> 662     raise ValueError(
    663         f"Usecols do not match columns, columns expected but not found: "
    664         f"{missing}"
    665     )
    667 return usecols

ValueError: Usecols do not match columns, columns expected but not found: ['Price']
cols = ["InvoiceNo", "StockCode", "Quantity", "InvoiceDate",
        "UnitPrice", "CustomerID", "Country"]
slim = pd.read_csv(PATH, usecols=cols)
print(slim.shape)
(541909, 7)

Common mistake: a plain integer type for a column with gaps

pd.read_csv(PATH, usecols=cols, dtype={"CustomerID": "int32"})
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[11], line 1
----> 1 pd.read_csv(PATH, usecols=cols, dtype={"CustomerID": "int32"})

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\readers.py:872, in read_csv(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, skip_blank_lines, parse_dates, date_format, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, on_bad_lines, low_memory, memory_map, float_precision, storage_options, dtype_backend)
    860 kwds_defaults = _refine_defaults_read(
    861     dialect,
    862     delimiter,
   (...)
    868     dtype_backend=dtype_backend,
    869 )
    870 kwds.update(kwds_defaults)
--> 872 return _read(filepath_or_buffer, kwds)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\readers.py:306, in _read(filepath_or_buffer, kwds)
    303     return parser
    305 with parser:
--> 306     return parser.read(nrows)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\readers.py:1950, in TextFileReader.read(self, nrows)
   1943 nrows = validate_integer("nrows", nrows)
   1944 try:
   1945     # error: "ParserBase" has no attribute "read"
   1946     (
   1947         index,
   1948         columns,
   1949         col_dict,
-> 1950     ) = self._engine.read(  # type: ignore[attr-defined]
   1951         nrows
   1952     )
   1953 except Exception:
   1954     self.close()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\parsers\c_parser_wrapper.py:238, in CParserWrapper.read(self, nrows)
    236 try:
    237     if self.low_memory:
--> 238         chunks = self._reader.read_low_memory(nrows)
    239         # destructive to chunks
    240         data = _concatenate_chunks(
    241             chunks, self._low_memory_column_labels(chunks)
    242         )

File pandas/_libs/parsers.pyx:832, in pandas._libs.parsers.TextReader.read_low_memory()

File pandas/_libs/parsers.pyx:911, in pandas._libs.parsers.TextReader._read_rows()

File pandas/_libs/parsers.pyx:1009, in pandas._libs.parsers.TextReader._convert_column_data()

File pandas/_libs/parsers.pyx:1048, in pandas._libs.parsers.TextReader._convert_tokens()

File pandas/_libs/parsers.pyx:1181, in pandas._libs.parsers.TextReader._convert_with_dtype()

ValueError: Integer column has NA values in column 6
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
InvoiceNo                 str
StockCode            category
Quantity                int64
InvoiceDate    datetime64[us]
UnitPrice             float64
CustomerID              Int32
Country              category
dtype: object
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())
(1000, 7)
['str', 'category', 'int64', 'datetime64[us]', 'float64', 'Int32', 'category']
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")
541909 rows kept
lean uses 36% 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)
38
int8
0    36
1    36
2    36
dtype: int8
q = pd.to_numeric(df["Quantity"], downcast="integer")
print(q.dtype)
print(q.min(), q.max())
int32
-80995 80995

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())
4455
127 -128

Common mistake: converting a column with gaps to a plain int

df["CustomerID"].astype("int32")
---------------------------------------------------------------------------
IntCastingNaNError                        Traceback (most recent call last)
Cell In[18], 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')
ids = df["CustomerID"].astype("Int32")
print(ids.dtype)
print(ids.isna().sum())
ids.head(3)
Int32
135080
0    17850
1    17850
2    17850
Name: CustomerID, dtype: Int32

Common mistake: writing a brand new value into a category

country.iloc[0] = "Atlantis"
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[20], line 1
----> 1 country.iloc[0] = "Atlantis"

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:938, in _LocationIndexer.__setitem__(self, key, value)
    933 self._has_valid_setitem_indexer(key)
    935 iloc: _iLocIndexer = (
    936     cast("_iLocIndexer", self) if self.name == "iloc" else self.obj.iloc
    937 )
--> 938 iloc._setitem_with_indexer(indexer, value, self.name)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1955, in _iLocIndexer._setitem_with_indexer(self, indexer, value, name)
   1953     self._setitem_with_indexer_split_path(indexer, value, name)
   1954 else:
-> 1955     self._setitem_single_block(indexer, value, name)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:2219, in _iLocIndexer._setitem_single_block(self, indexer, value, name)
   2216     value = self._align_frame(indexer, value)._values
   2218 # actually do the set
-> 2219 self.obj._mgr = self.obj._mgr.setitem(indexer=indexer, value=value)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:620, in BaseBlockManager.setitem(self, indexer, value)
    616     # No need to split if we either set all columns or on a single block
    617     # manager
    618     self = self.copy(deep=True)
--> 620 return self.apply("setitem", indexer=indexer, value=value)

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:1669, in EABackedBlock.setitem(self, indexer, value)
   1666 check_setitem_lengths(indexer, value, values)
   1668 try:
-> 1669     values[indexer] = value
   1670 except (ValueError, TypeError):
   1671     if isinstance(self.dtype, IntervalDtype):
   1672         # see TestSetitemFloatIntervalWithIntIntervalValues

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\_mixins.py:272, in NDArrayBackedExtensionArray.__setitem__(self, key, value)
    269     raise ValueError("Cannot modify read-only array")
    271 key = check_array_indexer(self, key)
--> 272 value = self._validate_setitem_value(value)
    273 self._ndarray[key] = 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 (Atlantis), set the categories first
country = country.cat.add_categories(["Atlantis"])
country.iloc[0] = "Atlantis"
print(len(country.cat.categories))
print(country.iloc[0])
39
Atlantis

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)
4223
int16

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))
apply: 0.365s  vectorised: 0.0007s
True
lean["revenue"] = lean["Quantity"] * lean["UnitPrice"]
lean["cancelled"] = lean["InvoiceNo"].str.startswith("C")
print(lean["cancelled"].sum())
print(round(lean["revenue"].sum(), 2))
9288
9747747.93
import numpy as np
big = lean["Quantity"] >= 12
lean["order_size"] = np.where(big, "pack", "small")
lean["order_size"].value_counts()
order_size
small    410234
pack     131675
Name: count, dtype: int64

Common mistake: an if statement on a whole column

if lean["Quantity"] > 0:
    print("positive")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_20092\2309997627.py in ?()
----> 1 if lean["Quantity"] > 0:
      2     print("positive")

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py in ?(self)
   1499     @final
   1500     def __bool__(self) -> NoReturn:
-> 1501         raise ValueError(
   1502             f"The truth value of a {type(self).__name__} is ambiguous. "
   1503             "Use a.empty, a.bool(), a.item(), a.any() or a.all()."
   1504         )

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
positive = lean["Quantity"] > 0
print(positive.sum(), (~positive).sum())
531285 10624
bulk = lean.query("Quantity >= 1000 and not cancelled")
print(len(bulk))
bulk[["Quantity", "UnitPrice", "Country"]].head(3)
129
Quantity UnitPrice Country
4850 1824 0.55 United Kingdom
4945 2880 0.18 United Kingdom
4946 1400 1.06 United Kingdom

Your turn#

Total revenue from lines that were NOT cancelled.

kept = lean.loc[~lean["cancelled"], "revenue"]
print(round(kept.sum(), 2))
10644560.42

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))
6
by_country = pd.concat(parts).groupby(level=0).sum()
by_country.sort_values(ascending=False).head(4).round(2)
Country
United Kingdom    8187806.36
Netherlands        284661.54
EIRE               263276.82
Germany            221698.21
Name: revenue, dtype: float64
full = lean.groupby("Country", observed=True)["revenue"].sum()
diff = (full - by_country).abs().max()
print(round(diff, 6))
0.0

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))
4.5714
4.6111
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))
4.6111

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)
Country
United Kingdom    495478
Germany             9495
France              8557
Name: count, dtype: int64

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
Country            str
CustomerID     float64
InvoiceDate        str
dtype: object
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
(541909, 2)
Country       category
CustomerID       Int32
dtype: object

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())
2010-12-01 08:26:00
2011-12-09 12:50:00

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))
530104
10666684.54
month = sales["InvoiceDate"].dt.to_period("M")
monthly = sales.groupby(month)["revenue"].sum().round(0)
monthly.tail(4)
InvoiceDate
2011-09    1058590.0
2011-10    1154979.0
2011-11    1509496.0
2011-12     638793.0
Freq: M, Name: revenue, dtype: float64
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()
No description has been provided for this image
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
Country
Netherlands    285446.0
EIRE           283454.0
Germany        228867.0
France         209715.0
Australia      138521.0
Name: revenue, dtype: float64
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)
Country
United Kingdom     501.0
Netherlands       3037.0
EIRE               984.0
Germany            501.0
Name: revenue, dtype: float64
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]}")
UK share: 85%, best month: 2011-11
Top export market: Netherlands

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#

  1. Load only Country and Quantity with types, and find total units per country.
  2. Use chunks to count cancelled lines per country.
  3. 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.