Large files: read in chunks

The file

To have something to read, I replicate the orders up to 400 thousand lines and save in CSV. The lines are repeated, but the id_pedido is renumbered, so each order is unique:

import tracemalloc
from pathlib import Path

import numpy as np
import pandas as pd

Path("saida").mkdir(exist_ok=True)
pedidos = pd.read_csv("dados/pedidos.csv")
grande = pd.concat([pedidos] * 500, ignore_index=True)
grande["id_pedido"] = np.arange(1, len(grande) + 1)
grande.to_csv("saida/pedidos_400k.csv", index=False)
print(len(grande), round(Path("saida/pedidos_400k.csv").stat().st_size / 1024 / 1024, 1), "MB")

400000 12.9 MB

Read in chunks and combine the partials

With the chunksize=n, the read_csv returns a iterator: each loop iteration delivers a table of up to n lines. The strategy is to calculate something small in each chunk and join the small results at the end. For a sum and a count by channel:

completo = pd.read_csv("saida/pedidos_400k.csv")
esperado = completo.groupby("canal")["quantidade"].agg(["sum", "count"])

parciais = []
pedacos = 0
for pedaco in pd.read_csv("saida/pedidos_400k.csv", usecols=["canal", "quantidade"], chunksize=50_000):
    pedacos += 1
    parciais.append(pedaco.groupby("canal")["quantidade"].agg(["sum", "count"]))

total = pd.concat(parciais).groupby(level=0).sum()
print(pedacos)
pd.testing.assert_frame_equal(esperado, total)
print((total["sum"] / total["count"]).round(3).to_dict())

8
{'app': 5.246, 'loja': 5.488, 'site': 5.528}

There were 8 chunks, and the result is identical compared to reading everything at once (the assert_frame_equal did not complain). Note that the average are not was calculated in each chunk: I saved the sum and the count, and I divided it at the end. The average of the averages of pieces of different sizes are not is the overall average.

The gain: memory

The peak memory of the loop is that of one piece, and not that of the entire file. The tracemalloc measures the peak of each approach:

def pico_de_memoria(funcao):
    tracemalloc.start()
    funcao()
    _, pico = tracemalloc.get_traced_memory()
    tracemalloc.stop()
    return pico


def somar_em_pedacos():
    for pedaco in pd.read_csv("saida/pedidos_400k.csv", usecols=["canal", "quantidade"], chunksize=50_000):
        pedaco.groupby("canal")["quantidade"].sum()


pico_tudo = pico_de_memoria(lambda: pd.read_csv("saida/pedidos_400k.csv"))
pico_pedacos = pico_de_memoria(somar_em_pedacos)
print("o pico em pedaços é menos da metade:", pico_pedacos < pico_tudo / 2)

o pico em pedaços é menos da metade: True

Two other tools help to look at a large file without paying for it: the nrows reads only the first lines (to inspect the format) and the usecols reads only the columns you need (as in chapter 29).

What can be solved in chunks, and what cannot

Not every calculation can be divided. A calculation is decomposable when the overall result comes from the partial results:

The last case is misleading: summing the counts of distinct values from each piece counts the same customer appearing in different pieces twice:

verdadeiro = completo.groupby("canal")["id_cliente"].nunique()
parciais_unicos = []
for pedaco in pd.read_csv("saida/pedidos_400k.csv", usecols=["canal", "id_cliente"], chunksize=50_000):
    parciais_unicos.append(pedaco.groupby("canal")["id_cliente"].nunique())
somados = pd.concat(parciais_unicos).groupby(level=0).sum()
print(verdadeiro.to_dict())
print(bool((somados > verdadeiro).all()))

{'app': 58, 'loja': 64, 'site': 64}
True

There are between 58 and 64 distinct clients in each channel (the first line of the result), and the sum of the partials gave much more, because the same client is counted again for each piece. The correct solution keeps the set of clients seen (set) and merges the sets at the end. And, when the data gets too large even for that, it is worth considering tools designed for data that doesn't fit in memory, like DuckDB or Polars, which this course does not cover.