Combinar tabelas: merge e join

As três tabelas

O modelo de vendas tem três tabelas: clientes (60), produtos (20) e pedidos (800), ligadas pelos códigos id_cliente e id_produto. Os pedidos têm uma peculiaridade escondida: alguns pedidos citam um cliente que não existe na tabela de clientes.

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)

Os tipos de junção

Cada tipo decide o que fazer com as linhas sem par:

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

A junção interna devolveu 788 pedidos, e não 800: os 12 pedidos de clientes inexistentes sumiram em silêncio. Em um relatório de vendas, isso é dinheiro faltando. A junção left mantém os 800. O parâmetro indicator=True diz de onde veio cada linha, e permite achar os órfãos:

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]

Aqui, 12 pedidos apontam para os códigos 61 a 65, que não existem. A regra que eu sigo: depois de qualquer junção, conferir o número de linhas (e, de preferência, descobrir o porquê de cada diferença).

O perigo maior: a chave duplicada multiplica linhas

Se a chave da tabela da direita não for única, cada linha da esquerda é copiada para cada par. As linhas se multiplicam, e os totais ficam inflados, sem nenhum erro:

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

Três clientes repetidos transformaram 788 linhas em 821. O parâmetro validate verifica a relação esperada e falha se ela não valer. Aqui, a relação é "muitos pedidos para um cliente" (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

Escrever o validate em toda junção é o hábito que mais evita o erro de totais inflados.

Chaves com nomes ou tipos diferentes

Quando a chave tem nomes diferentes nas duas tabelas, use left_on e right_on. Quando as colunas não chave têm o mesmo nome, o Pandas as distingue com sufixos:

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

E uma chave com tipos diferentes (um inteiro de um lado, um texto do outro) é recusada, em vez de resultar em uma junção vazia. Essa recusa é uma proteção:

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

Calcular a receita: duas junções em sequência

Para saber quanto cada pedido valeu, juntamos os produtos (para ter o preço). O desconto ausente significa sem desconto:

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

E para a receita por segmento de cliente, uma junção left mantém os pedidos dos clientes que não existem, que aparecem como um grupo próprio, em vez de sumirem:

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

A soma por segmento fecha com a receita total (o True), porque nenhum pedido foi descartado. Esse é o teste que importa: o total depois da junção é igual ao total antes.

`join`, `map` e o mesmo resultado

O join junta pelo índice, e é a forma curta quando a chave já é o índice. Para trazer uma só coluna de uma tabela de consulta, o map com uma Series faz o mesmo que uma junção, em uma linha:

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

As três perguntas depois de um merge

Antes de confiar em uma junção, eu respondo: quantas linhas eu tinha, e quantas tenho? Se mudou, por quê? A chave é única onde eu esperava (validate)? Sobrou algum órfão (indicator=True)? Um merge que "funcionou" sem erro e deu um número diferente do esperado é o erro mais caro desta ferramenta.