Mathew K Analytics

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…

pandasDask

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 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))
9747747.93
print(len(df.query("Country == 'France'")))
8557

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"]]
InvoiceNo Quantity Country
0 536365 6 United Kingdom
1 536365 6 United Kingdom
2 536365 8 United Kingdom
3 536365 6 United Kingdom
4 536365 6 United Kingdom
reader = pd.read_csv(DATA, chunksize=100_000)
print(type(reader).__name__)
TextFileReader

Common mistake: treating the reader like a DataFrame

reader.head()
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[6], line 1
----> 1 reader.head()

AttributeError: 'TextFileReader' object has no attribute 'head'
first = reader.get_chunk()
print(first.shape)
reader.close()
first[["InvoiceNo", "Description", "Quantity"]].head(3)
(100000, 8)
InvoiceNo Description Quantity
0 536365 WHITE HANGING HEART T-LIGHT HOLDER 6
1 536365 WHITE METAL LANTERN 6
2 536365 CREAM CUPID HEARTS COAT HANGER 8

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])
0 100000 0
1 100000 100000
2 100000 200000
3 100000 300000
4 100000 400000
5 41909 500000
for size in [50_000, 250_000]:
    n = sum(1 for _ in pd.read_csv(DATA, chunksize=size))
    print(f"chunksize {size:,}: {n} chunks")
chunksize 50,000: 11 chunks
chunksize 250,000: 3 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)
A chunk needs under a quarter: True

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

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))
4.6321
4.6111
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))
4.6111
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()))
Country
United Kingdom    495478
Germany             9495
France              8557
Name: count, dtype: int64
True
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)
Country
Netherlands    120.06
Australia      108.88
Japan           98.72
dtype: float64
true_rev = df.groupby("Country")["Revenue"].mean()
diff = (by_sum / by_cnt - true_rev).abs().max()
print("All 38 countries match:", diff < 1e-9)
All 38 countries match: True

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")))
10624
True

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)
(8557, 4)
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"})
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
File pandas/_libs/parsers.pyx:1116, in pandas._libs.parsers.TextReader._convert_tokens()

TypeError: Cannot cast array data from dtype('O') to dtype('int64') according to the rule 'safe'

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
Cell In[20], line 5
      3     back = chunk.query("Quantity < 0")
      4     back.to_csv(BAD, mode="w" if i == 0 else "a", index=False)
----> 5 pd.read_csv(BAD, dtype={"Quantity": "int64"})

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:1122, in pandas._libs.parsers.TextReader._convert_tokens()

ValueError: invalid literal for int() with base 10: 'Quantity'
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)
(10624, 8)

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)
1155
Country
United Kingdom    997
EIRE               45
Netherlands        35
Name: count, dtype: int64

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])
int64 str
C536379
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))
['part_0.parquet',
 'part_1.parquet',
 'part_2.parquet',
 'part_3.parquet',
 'part_4.parquet',
 'part_5.parquet']
p0 = pd.read_parquet(os.path.join(PARTS, "part_0.parquet"))
print(p0.shape)
print(p0["InvoiceNo"].iloc[0], p0["Quantity"].dtype)
(100000, 8)
536365 int64

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
6
Dask DataFrame Structure:
InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice CustomerID Country
npartitions=6
string string string int64 string float64 float64 string
... ... ... ... ... ... ... ...
... ... ... ... ... ... ... ... ...
... ... ... ... ... ... ... ...
... ... ... ... ... ... ... ...
Dask Name: read_parquet, 1 expression
ddf.head(3)
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
rows = ddf.map_partitions(len).compute()
print(list(rows))
[100000, 100000, 100000, 100000, 100000, 41909]
rev = (ddf["Quantity"] * ddf["UnitPrice"]).sum()
print(type(rev).__name__)
print(round(rev.compute(), 2))
Scalar
9747747.93

Common mistake: asking Dask for a row by position

ddf.iloc[5]
---------------------------------------------------------------------------
NotImplementedError                       Traceback (most recent call last)
Cell In[30], line 1
----> 1 ddf.iloc[5]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\dask\dataframe\dask_expr\_indexing.py:49, in ILocIndexer.__getitem__(self, key)
     44 msg = (
     45     "'DataFrame.iloc' only supports selecting columns. "
     46     "It must be used like 'df.iloc[:, column_indexer]'."
     47 )
     48 if not isinstance(key, tuple):
---> 49     raise NotImplementedError(msg)
     51 if len(key) > 2:
     52     raise ValueError("Too many indexers")

NotImplementedError: 'DataFrame.iloc' only supports selecting columns. It must be used like 'df.iloc[:, column_indexer]'.
ddf.head(6).iloc[5]
InvoiceNo                            536365
StockCode                             22752
Description    SET 7 BABUSHKA NESTING BOXES
Quantity                                  2
InvoiceDate                  12/1/2010 8:26
UnitPrice                              7.65
CustomerID                          17850.0
Country                      United Kingdom
Name: 5, dtype: object
top = ddf.groupby("Country")["Quantity"].sum().nlargest(3)
top = top.compute()
print(top)
print(top.equals(df.groupby("Country")["Quantity"].sum().nlargest(3)))
Country
United Kingdom    4263829
Netherlands        200128
EIRE               142637
Name: Quantity, dtype: int64
True
avg = ddf.groupby("Country")["UnitPrice"].mean().compute()
true_avg = df.groupby("Country")["UnitPrice"].mean()
print((avg.sort_index() - true_avg).abs().max() < 1e-9)
True
n_cust = ddf["CustomerID"].nunique()
n_inv = ddf["InvoiceNo"].nunique()
cust, inv = dd.compute(n_cust, n_inv)
print(cust, inv)
4372 25900

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))
8557

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)
Month
2010-12    0.75
2011-01    0.56
2011-02    0.50
2011-03    0.68
2011-04    0.49
2011-05    0.72
2011-06    0.69
2011-07    0.68
2011-08    0.68
2011-09    1.02
2011-10    1.07
2011-11    1.46
2011-12    0.43
Name: Revenue, dtype: float64
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"])
3462 1393
422.0 358.0
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)
True
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)")
No description has been provided for this image
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")
2011-11: 1.46m, 1.9x 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#

  1. With chunks, find the total revenue for Germany.
  2. With chunks, compute the true mean Quantity, then check it.
  3. 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.