Combine tables: merge and join

The three tables

The sales model has three tables: customers (60), products (20) and orders (800), linked by the codes id_cliente and id_produto. Orders have a hidden peculiarity: some orders mention a customer that does not exist in the customers table.

import numpy as np
import pandas as pd

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

clientes = pd.read_csv("dados/clientes.csv")
produtos = pd.read_csv("dados/produtos.csv")
pedidos = pd.read_csv("dados/pedidos.csv", parse_dates=["data_pedido"])
print(clientes.shape, produtos.shape, pedidos.shape)

(60, 5) (20, 4) (800, 7)

The types of join

Each type decides what to do with the unpaired rows:

interna = pedidos.merge(clientes, on="id_cliente")
esquerda = pedidos.merge(clientes, on="id_cliente", how="left")
print(len(pedidos), len(interna), len(esquerda))

800 788 800

The join internal returned 788 orders, and not 800: the 12 orders from non-existent customers disappeared silently. In a sales report, this is missing money. The join left keeps the 800. The parameter indicator=True indicates where each row came from, and allows to find the orphans:

conferencia = pedidos.merge(clientes, on="id_cliente", how="left", indicator=True)
print(conferencia["_merge"].value_counts().to_dict())
orfaos = conferencia.loc[conferencia["_merge"] == "left_only", "id_cliente"].unique()
print(sorted(int(x) for x in orfaos))

{'both': 788, 'left_only': 12, 'right_only': 0}
[61, 62, 63, 64, 65]

Here, 12 orders point to codes 61 to 65, which do not exist. The rule I follow: after any join, check the number of rows (and, preferably, find out the reason for each difference).

The greater danger: the duplicate key multiplies rows

If the key of the right table is not unique, each row from the left is copied for each pair. The rows multiply, and the totals become inflated, without any error:

clientes_com_repeticao = pd.concat([clientes, clientes.head(3)])
multiplicada = pedidos.merge(clientes_com_repeticao, on="id_cliente")
print(len(interna), len(multiplicada))

788 821

Three repeated customers turned 788 rows into 821. The parameter validate checks the expected relationship and fails if it is not valid. Here, the relationship is "many orders for one customer" (m:1):

from pandas.errors import MergeError

try:
    pedidos.merge(clientes_com_repeticao, on="id_cliente", validate="m:1")
except MergeError as erro:
    print("MergeError:", str(erro).splitlines()[0])
print(len(pedidos.merge(clientes, on="id_cliente", validate="m:1")))

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
788

Writing the validate in any join is the habit that most prevents the error of inflated totals.

Keys with different names or types

When the key has different names in both tables, use left_on and right_on. When the columns are not key have the same name, Pandas distinguishes them with suffixes:

a = pd.DataFrame({"id": [1, 2], "valor": [10, 20]})
b = pd.DataFrame({"codigo": [1, 2], "valor": [1, 2]})
print(a.merge(b, left_on="id", right_on="codigo", suffixes=("_a", "_b")))

   id  valor_a  codigo  valor_b
0   1       10       1        1
1   2       20       2        2

And a key with different types (an integer on one side, text on the other) is rejected, instead of resulting in an empty join. This rejection is a protection:

try:
    pd.DataFrame({"k": [1, 2]}).merge(pd.DataFrame({"k": ["1", "2"]}), on="k")
except ValueError as erro:
    print("ValueError:", str(erro)[:50])

ValueError: You are trying to merge on int64 and str columns f

Calculate the revenue: two joins in sequence

To know how much each order was worth, we join the products (to get the price). The missing discount means no discount:

vendas = pedidos.merge(produtos, on="id_produto", how="left", validate="m:1")
vendas["receita"] = vendas["quantidade"] * vendas["preco_unitario"] * (1 - vendas["desconto"].fillna(0))
print(round(float(vendas["receita"].sum()), 2))
print(vendas.groupby("categoria")["receita"].sum().round(2).sort_values(ascending=False).head(3))

970851.1
categoria
Esporte        270915.28
Informática    247345.06
Casa           237620.67
Name: receita, dtype: float64

And for the revenue by customer segment, a join left keeps the orders from non-existent customers, which appear as their own group, instead of disappearing:

completo = vendas.merge(clientes[["id_cliente", "segmento"]], on="id_cliente", how="left", validate="m:1")
completo["segmento"] = completo["segmento"].fillna("Sem cadastro")
por_segmento = completo.groupby("segmento")["receita"].sum().round(2)
print(por_segmento.to_dict())
print(round(float(por_segmento.sum()), 2) == round(float(vendas["receita"].sum()), 2))

{'Atacado': 159214.69, 'Online': 382015.17, 'Sem cadastro': 17060.07, 'Varejo': 412561.17}
True

The sum by segment closes with the total revenue (the True), because no order was discarded. This is the test that matters: the total after the join is equal to the total before.

`join`, `map` and the same result

The join joins by index, and it is the short form when the key is already the index. To bring a single column from a query table, the map with a Series does the same as a join, in one line:

por_indice = pedidos.join(produtos.set_index("id_produto")["preco_unitario"], on="id_produto")
por_map = pedidos["id_produto"].map(produtos.set_index("id_produto")["preco_unitario"])
print(bool(np.allclose(por_indice["preco_unitario"], por_map)))

True

The three questions after a merge

Before trusting a join, I ask myself: how many rows did I have, and how many do I have now? If it changed, why? Is the key unique where I expected (validate)? Are there any orphans left (indicator=True)? An merge that "worked" without error and gave a different number than expected is the most expensive error of this tool.