Skip to content

    Chapter 28, Advanced

    Performance: vectorize and avoid the loop

    In Pandas, the same calculation can take milliseconds or minutes, depending on **how** you write it. The difference is almost never in the computer: it is in iterating over the rows in Python instead of operating on the column.

    The four ways to calculate the same column

    Four ways to calculate the revenue of each row. All give the same result, and the times are very different:

    avancado/cap28_desempenho_pandas.pylinhas 10 a 58
    import time

import numpy as np
import pandas as pd

rng = np.random.default_rng(0)
n = 50_000
base = pd.DataFrame(
    {
        "quantidade": rng.integers(1, 10, n),
        "preco": rng.uniform(5, 500, n).round(2),
        "desconto": rng.choice([0.0, 0.05, 0.1], n),
    }
)


def com_iterrows(d):
    return pd.Series([l["quantidade"] * l["preco"] * (1 - l["desconto"]) for _, l in d.iterrows()], index=d.index)


def com_itertuples(d):
    return pd.Series([t.quantidade * t.preco * (1 - t.desconto) for t in d.itertuples()], index=d.index)


def com_apply(d):
    return d.apply(lambda l: l["quantidade"] * l["preco"] * (1 - l["desconto"]), axis=1)


def vetorizada(d):
    return d["quantidade"] * d["preco"] * (1 - d["desconto"])


def medir(funcao, repeticoes=1):
    melhor = float("inf")
    for _ in range(repeticoes):
        inicio = time.perf_counter()
        resultado = funcao(base)
        melhor = min(melhor, time.perf_counter() - inicio)
    return resultado, melhor


r_vet, t_vet = medir(vetorizada, repeticoes=20)
r_tup, t_tup = medir(com_itertuples)
r_apply, t_apply = medir(com_apply)
r_iter, t_iter = medir(com_iterrows)

print(all(np.allclose(r_vet, r) for r in (r_tup, r_apply, r_iter)))
print("itertuples mais rápido que iterrows:", t_tup < t_iter)
print("vetorizada pelo menos 30 vezes mais rápida que todas:", all(t > 30 * t_vet for t in (t_tup, t_apply, t_iter)))
    Output
    True
itertuples mais rápido que iterrows: True
vetorizada pelo menos 30 vezes mais rápida que todas: True

    The vectorized version is the only one that are not iterates over the rows in Python: it delivers the entire calculation to the compiled code, like in NumPy. I time the vectorized version with the best of 20 repetitions (a single cold run makes it seem slower than it is) and the slow ones once, because they already take time. On the machine I tested, the itertuples was about 80 times slower than the vectorized version, the apply(axis=1) close to 500 times, and the iterrows above 1,000 times. The numbers vary from machine to machine, but the ) or the next one ( between them does not. The iterrows is the slowest, because it creates a Series to for each line. The itertuples is much lighter, and the apply(axis=1), underneath, also processes line by line. A rule that applies: if you wrote axis=1, iterrows or a for over lines, stop and look for the version over columns.

    Writing row by row is also slow

    The same applies to assigning in a loop. Each df.loc[i, "x"] = valor is a complete indexing operation:

    avancado/cap28_desempenho_pandas.pylinhas 63 a 84
    pequena = base.head(5000).copy()


def atribuir_no_laco():
    resultado = pequena.copy()
    resultado["total"] = 0.0
    for i in range(len(resultado)):
        resultado.loc[i, "total"] = resultado.loc[i, "quantidade"] * resultado.loc[i, "preco"]
    return resultado


def atribuir_vetorizado():
    return pequena.assign(total=pequena["quantidade"] * pequena["preco"])


inicio = time.perf_counter()
a = atribuir_no_laco()
t_laco = time.perf_counter() - inicio
inicio = time.perf_counter()
b = atribuir_vetorizado()
t_vet2 = time.perf_counter() - inicio
print(bool(np.allclose(a["total"], b["total"])), t_laco > 100 * t_vet2)
    Output
    True True

    `eval` and `query`: the calculation as text

    The eval accepts the expression as In data that has and evaluates it over the columns, which makes long expressions more readable. The result is identical to that of the direct calculation:

    avancado/cap28_desempenho_pandas.pylinhas 89 a 91
    por_eval = base.eval("quantidade * preco * (1 - desconto)")
print(bool(np.allclose(por_eval, r_vet)))
print(len(base.query("quantidade >= 5 and desconto > 0")))
    Output
    True
18417

    With the optional library numexpr installed, the eval can be faster on much large tables, because it avoids creating intermediate columns. I did not use it in this course, so I make no claims about the gain. For small or medium tables, the eval is worth it for the readability, and not for speed.

    The cost of looking without measuring

    Before optimizing, measure. A simple measurement is enough, and it is the only way to know where the time goes. The results are often surprising: the bottleneck is almost never where intuition points.

    avancado/cap28_desempenho_pandas.pylinhas 96 a 111
    def medir_etapas():
    etapas = {}
    inicio = time.perf_counter()
    d = base.assign(receita=vetorizada(base))
    etapas["conta"] = time.perf_counter() - inicio
    inicio = time.perf_counter()
    d.groupby("quantidade")["receita"].sum()
    etapas["groupby"] = time.perf_counter() - inicio
    inicio = time.perf_counter()
    d.sort_values("receita")
    etapas["ordenar"] = time.perf_counter() - inicio
    return etapas


etapas = medir_etapas()
print(sorted(etapas), all(valor > 0 for valor in etapas.values()))
    Output
    ['conta', 'groupby', 'ordenar'] True

    The order of choice

    1. Vectorized operation over columns. 2. Internal functions of Pandas (map, cut, transform("mean"), str.*). 3. np.select and np.where for rules with conditions. 4. itertuples, only if it is unavoidable. 5. apply(axis=1) and iterrows, almost never. And, between one step and another, measure.

    Exercise 1

    Replace `apply` with a vectorized calculation

    Write margem_vetorizada(d), which calculates (preco * quantidade) * 0.2 - desconto * preco * quantidade without iterate through the rows, and check that the result is equal to the version with apply(axis=1).