Chapter 23, Intermediate
Advanced groupby
The `agg` reduces each group to one row. There are three other operations per group that solve the rest: `transform` (one value per original row), `filter` (discard entire groups), and `apply` (anything), and each has a cost.
`transform`: the result returns with the original shape
The transform calculates by group and returns one row for each row of the table, aligned with it. This allows comparing each person with the department: group: the deviation from the department average, the participation in the total of the department:
import numpy as np
import pandas as pd
pd.set_option("display.width", 170)
pd.set_option("display.max_columns", 20)
CANONICO = {"engenharia": "Engenharia", "marketing": "Marketing", "finanças": "Finanças",
"ciência de dados": "Ciência de Dados", "rh": "RH"}
def carregar_100() -> pd.DataFrame:
df = pd.read_csv("dados/funcionarios_100.csv")
df["salario"] = pd.to_numeric(df["salario"].str.replace("R$ ", "", regex=False))
df["departamento"] = df["departamento"].str.strip().str.lower().map(CANONICO)
iso = pd.to_datetime(df["data_admissao"], format="%Y-%m-%d", errors="coerce")
df["data_admissao"] = iso.fillna(pd.to_datetime(df["data_admissao"], format="%d/%m/%Y", errors="coerce"))
return df.drop_duplicates("id_funcionario", keep="last").reset_index(drop=True)
df = carregar_100()
grupo = df.groupby("departamento")["salario"]
df["z_no_depto"] = (df["salario"] - grupo.transform("mean")) / grupo.transform("std")
df["participacao"] = df["salario"] / grupo.transform("sum")
print(df[["nome", "departamento", "salario", "z_no_depto"]].head(3).round(2))
print(round(float(df.groupby("departamento")["participacao"].sum().round(6).mean()), 3))
nome departamento salario z_no_depto
0 Lucas Nunes RH 4520.0 -1.20
1 João Rocha Engenharia 10740.0 0.81
2 Olívia Nunes Finanças 8610.0 -0.34
1.0
The z_no_depto indicates how many standard deviations the person is above or below their own department. And the participation, summed within each department, equals 1 (the 1.0 printed). I build the z-score with the functions internal of transform ("mean" and "std"), instead of a lambda, because the internals are vectorized and much faster:
com_lambda = grupo.transform(lambda s: (s - s.mean()) / s.std())
print(bool(np.allclose(com_lambda.dropna(), df["z_no_depto"].dropna())))
True
The result is the same. Preferring the function by name is the habit that most accelerates a groupby.
`filter`: discard entire groups
The filter keeps all the rows of the groups that pass in a test and discards the others. It differs from the common filter (which looks row by row): here the test is about the group:
grandes = df.groupby("departamento").filter(lambda g: len(g) >= 20)
print(df["departamento"].nunique(), grandes["departamento"].nunique(), len(df), len(grandes))
5 3 100 70
`apply`: when nothing else works
The apply delivers each group as a table to your function, and combines what it returns. It is the most flexible, and the slowest. A legitimate use: the two highest salaries of each department:
top2 = df.groupby("departamento").apply(lambda g: g.nlargest(2, "salario")).reset_index(level=0)
print(top2[["nome", "departamento", "salario"]].sort_values(["departamento", "salario"], ascending=[True, False]).head(4))
nome departamento salario
32 Renata Carvalho Ciência de Dados 13620.0
88 Marina Carvalho Ciência de Dados 13470.0
5 Isabela Gomes Engenharia 13680.0
43 Olívia Pereira Engenharia 11660.0
Notice the reset_index(level=0): the departamento is not within each group that the function receives (a change from Pandas 3, explained further below), and that’s why it comes in the index of the result. The reset_index returns it as a column.
But here there is an alternative that are not uses apply, faster and equally clear: sort and take the n first rows of each group with the head:
top2_rapido = df.sort_values("salario", ascending=False).groupby("departamento").head(2)
print(sorted(top2["id_funcionario"]) == sorted(top2_rapido["id_funcionario"]), len(top2_rapido))
True 10
Change from Pandas 3: the function of apply no longer receives the grouping column. Old code that used g["departamento"] inside the function breaks, and the parameter include_groups=True has been removed:
def tem_coluna_de_grupo(g):
return pd.Series({"tem_departamento": "departamento" in g.columns, "linhas": len(g)})
print(bool(df.groupby("departamento").apply(tem_coluna_de_grupo)["tem_departamento"].iloc[0]))
try:
df.groupby("departamento").apply(tem_coluna_de_grupo, include_groups=True)
except ValueError as erro:
print("ValueError:", erro)
False
ValueError: include_groups=True is no longer allowed.
If the function needs the group value, it is in the name of the group (the index of the result), and not in the table.
Ranking and position within the group
The rank works by group, and the cumcount numbers the rows within each group:
df["posicao_no_depto"] = df.groupby("departamento")["salario"].rank(method="min", ascending=False)
print(df.loc[df["posicao_no_depto"] == 1, ["nome", "departamento", "salario"]].sort_values("departamento"))
nome departamento salario
32 Renata Carvalho Ciência de Dados 13620.0
5 Isabela Gomes Engenharia 13680.0
89 João Moreira Finanças 13890.0
64 Lucas Pereira Marketing 12020.0
78 Lucas Souza RH 11050.0
Multiple functions, including yours
The named aggregation accepts custom functions. And when there is several column functions, the result columns gain two levels, which can be flattened:
resumo = df.groupby("departamento").agg(
media=("salario", "mean"),
p90=("salario", lambda s: s.quantile(0.9)),
funcionarios=("id_funcionario", "count"),
).round(0)
print(resumo)
duplo = df.groupby("departamento").agg({"salario": ["mean", "max"]})
duplo.columns = ["_".join(par) for par in duplo.columns]
print(duplo.columns.tolist())
media p90 funcionarios
departamento
Ciência de Dados 10152.0 13110.0 20
Engenharia 9012.0 11198.0 23
Finanças 9305.0 11488.0 27
Marketing 8470.0 11665.0 16
RH 7924.0 10564.0 11
['salario_mean', 'salario_max']
Within the group, over time: accumulate and shift
With a table ordered by date, the cumsum accumulates by group, and the shift brings the value of the previous row of the same group. This is how the interval between the current and previous purchase of each customer is calculated:
def carregar_vendas() -> pd.DataFrame:
pedidos = pd.read_csv("dados/pedidos.csv", parse_dates=["data_pedido"])
produtos = pd.read_csv("dados/produtos.csv")
v = pedidos.merge(produtos, on="id_produto", how="left", validate="m:1")
v["receita"] = v["quantidade"] * v["preco_unitario"] * (1 - v["desconto"].fillna(0))
return v.sort_values(["id_cliente", "data_pedido", "id_pedido"]).reset_index(drop=True)
vendas = carregar_vendas()
por_cliente = vendas.groupby("id_cliente")
vendas["receita_acumulada"] = por_cliente["receita"].cumsum()
vendas["numero_da_compra"] = por_cliente.cumcount() + 1
vendas["dias_desde_a_anterior"] = (vendas["data_pedido"] - por_cliente["data_pedido"].shift(1)).dt.days
cliente_1 = vendas[vendas["id_cliente"] == 1][["numero_da_compra", "dias_desde_a_anterior", "receita_acumulada"]]
print(cliente_1.head(3).round(1))
numero_da_compra dias_desde_a_anterior receita_acumulada
0 1 NaN 1541.5
1 2 58.0 5245.1
2 3 21.0 6223.8
The first purchase of each customer has NaN in days, because there is no previous one.
The order of the rows matters
The
shift, thecumsumand thecumcountdepends on the ) or the next one ( in which the rows are. If the table is not ordered by date, the "previous" is just the row above, and the result is wrong without any error. Order You will see, in much material, (as I did above) and, in a report, check with a sample.
Exercise 1
The three highest from each department
Write tres_maiores_por_depto(tabela), without apply, which returns, for each department, the 3 highest salaries (sort_values and groupby().head(3)), and check that no department has more than 3 rows.