Skip to content

    Chapter 31, Advanced

    Alignment and joins by time

    Pandas aligns by **label** in almost everything, and this is both a quality and a trap. Here are the places where alignment acts without warning, and the time join, which solves a problem that the common `merge` does not solve.

    Assigning a column aligns by index

    Assigning a Series to a column does not copy in order: Pandas looks for, for each row, the value with the same label. If the Series arrives in a different order, the values go to the right rows by label, and what seems like an error is the correct behavior:

    avancado/cap31_alinhamento_asof.pylinhas 10 a 20
    import numpy as np
import pandas as pd

pd.set_option("display.width", 170)
pd.set_option("display.max_columns", 20)

base = pd.DataFrame({"nome": ["Ana", "Bruno", "Carla"]}, index=[0, 1, 2])
notas = pd.Series([9, 7, 8], index=[2, 0, 1])
base["nota_por_rotulo"] = notas
base["nota_por_posicao"] = notas.to_numpy()
print(base)
    Output
        nome  nota_por_rotulo  nota_por_posicao
0    Ana                7                 9
1  Bruno                8                 7
2  Carla                9                 8

    In the column nota_por_rotulo, Ana (index 0) received 7, the grade that had the label 0. In the column nota_por_posicao, she received 9, the first in the list. Both are "correct" for your model, and the confusion between the two is the classic cause of swapped grades.

    And a label that does not exist becomes NaN, without error:

    avancado/cap31_alinhamento_asof.pylinhas 22 a 24
    parcial = pd.Series([1, 2], index=[0, 5])
base["parcial"] = parcial
print(base["parcial"].tolist())
    Output
    [1.0, nan, nan]

    Repeated labels break alignment

    Alignment only makes sense if each label is unique. With repetition, Pandas refuses to guess:

    avancado/cap31_alinhamento_asof.pylinhas 29 a 34
    com_repeticao = pd.Series([1, 2, 3], index=[0, 0, 1])
try:
    com_repeticao.reindex([0, 1])
except ValueError as erro:
    print("ValueError:", erro)
print(base.index.is_unique, com_repeticao.index.is_unique)
    Output
    ValueError: cannot reindex on an axis with duplicate labels
True False

    Before an operation that aligns, index.is_unique is a cheap check.

    `reindex`, `align` and `combine_first`

    The reindex imposes a new set of labels, and what is missing becomes NaN (or the fill_value). The combine_first fills the gaps of one table with the values of another:

    avancado/cap31_alinhamento_asof.pylinhas 39 a 42
    principal = pd.Series([10.0, np.nan, 30.0], index=["a", "b", "c"])
reserva = pd.Series([1.0, 2.0, 3.0, 4.0], index=["a", "b", "c", "d"])
print(principal.reindex(["a", "b", "c", "d"]).tolist())
print(principal.combine_first(reserva).to_dict())
    Output
    [10.0, nan, 30.0, nan]
{'a': 10.0, 'b': 2.0, 'c': 30.0, 'd': 4.0}

    `merge_asof`: the most recent value

    A common problem: each order needs the exchange rate in effect on its date, but the exchange table only has one row per quarter. A merge common only house dates identical. The merge_asof house each line with the more recent previous (direction="backward"):

    avancado/cap31_alinhamento_asof.pylinhas 47 a 56
    pedidos = pd.read_csv("dados/pedidos.csv", parse_dates=["data_pedido"]).sort_values("data_pedido")
cambio = pd.DataFrame(
    {
        "data": pd.to_datetime(["2025-01-01", "2025-04-01", "2025-07-01", "2025-10-01"]),
        "usd": [6.1, 5.8, 5.5, 5.9],
    }
)
com_cambio = pd.merge_asof(pedidos, cambio, left_on="data_pedido", right_on="data", direction="backward")
print(com_cambio.groupby("usd").size().to_dict())
print(com_cambio.loc[com_cambio["data_pedido"] == "2025-03-31", "usd"].unique().tolist())
    Output
    {5.5: 209, 5.8: 215, 5.9: 201, 6.1: 175}
[6.1]

    An order from March 31 receives the rate from January 1 (6.1), because the one from April did not exist yet. With the tolerance, you say how far back a rate is still valid, and more distant orders remain unmatched:

    avancado/cap31_alinhamento_asof.pylinhas 58 a 62
    com_prazo = pd.merge_asof(
    pedidos, cambio, left_on="data_pedido", right_on="data",
    direction="backward", tolerance=pd.Timedelta("30D"),
)
print(int(com_prazo["usd"].isna().sum()) > 0, int(com_cambio["usd"].isna().sum()))
    Output
    True 0

    The two tables need to be sorted by the key. Without this, Pandas refuses:

    avancado/cap31_alinhamento_asof.pylinhas 64 a 67
    try:
    pd.merge_asof(pedidos.sort_values("quantidade"), cambio, left_on="data_pedido", right_on="data")
except ValueError as erro:
    print("ValueError:", erro)
    Output
    ValueError: left keys must be sorted

    `explode`: a cell with multiple values

    A column with lists (or texts separated by commas) becomes one row per value with the explode, which allows counting, grouping, and joining:

    avancado/cap31_alinhamento_asof.pylinhas 72 a 74
    produtos = pd.DataFrame({"produto": ["A", "B", "C"], "tags": ["novo,oferta", "oferta", "novo,oferta,verao"]})
em_linhas = produtos.assign(tag=produtos["tags"].str.split(",")).explode("tag")
print(len(em_linhas), em_linhas["tag"].value_counts().to_dict())
    Output
    6 {'oferta': 3, 'novo': 2, 'verao': 1}

    Exercise 1

    The current discount

    Given a table tabela_descontos with data and percentual, and the pedidos, use the merge_asof to give each order the current discount on its date, and check that no order prior to the first date in the table receives a discount.