Group and aggregate

Separate, apply, combine

The groupby works in three stages: separates the data into groups, applies a calculation to each group and combines the results into a table:

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_limpo() -> pd.DataFrame:
    df = pd.read_csv("dados/funcionarios_15.csv")
    df["salario"] = pd.to_numeric(df["salario"].str.replace("R$ ", "", regex=False))
    df["departamento"] = df["departamento"].str.strip().str.lower().map(CANONICO)
    return df.drop_duplicates(subset=["id_funcionario"], keep="first").reset_index(drop=True)


df = carregar_limpo()
print(df.groupby("departamento")["salario"].mean().round(1))

departamento
Ciência de Dados    6450.0
Engenharia          5100.0
Finanças            5925.0
Marketing           5362.5
RH                  5100.0
Name: salario, dtype: float64

`size` vs `count`

The most used aggregation functions are sum, mean, median, min, max, count, size and std. Two of them seem the same until they appear missing: the size counts all the rows in the group, and the count counts only the non-missing:

print(df.groupby("departamento")["salario"].agg(["size", "count"]))

                  size  count
departamento                 
Ciência de Dados     2      1
Engenharia           3      3
Finanças             2      2
Marketing            4      4
RH                   2      1

In departments where someone does not have a salary, the size is greater than the count. Which to use depends on the question: "how many people" is size, "how many known salaries" is count.

The trap: the group that disappears

By default, the groupby discards excludes the rows where the key is missing. Here, a person there is no department, and it disappears from any department summary without notice:

print(len(df), int(df.groupby("departamento")["id_funcionario"].size().sum()))
com_ausente = df.groupby("departamento", dropna=False)["id_funcionario"].size()
print(int(com_ausente.sum()), com_ausente.index.isna().sum())

14 13
14 1

The total number of employees is 14, and the sum of the groups is only 13. With dropna=False, the group of "without department" appears, and the sum matches. In a report, a missing employee in the count is an error that no one notices, and that's why I check that the sum of the groups matches the total.

Multiple columns, multiple functions

print(df.groupby(["departamento", "cidade"])["salario"].mean().round(0).head(5))
print(df.groupby("departamento")[["salario", "nota_desempenho"]].mean().round(2))

departamento      cidade        
Ciência de Dados  Rio de Janeiro    6450.0
Engenharia        Porto Alegre      4800.0
                  São Paulo         5250.0
Finanças          Porto Alegre      5925.0
Marketing         Belo Horizonte    5025.0
Name: salario, dtype: float64
                  salario  nota_desempenho
departamento                              
Ciência de Dados   6450.0             6.00
Engenharia         5100.0             6.00
Finanças           5925.0             4.50
Marketing          5362.5             6.75
RH                 5100.0             7.50

The first groups by two keys (one group for each combination), and the second calculates the average of two columns at the same time. The agg calculates several functions at once, and the named aggregation gives readable names to the columns of the result, instead of nested headers:

resumo = df.groupby("departamento").agg(
    salario_medio=("salario", "mean"),
    maior_salario=("salario", "max"),
    nota_media=("nota_desempenho", "mean"),
    funcionarios=("id_funcionario", "count"),
)
print(resumo.round(1))

                  salario_medio  maior_salario  nota_media  funcionarios
departamento                                                            
Ciência de Dados         6450.0         6450.0         6.0             2
Engenharia               5100.0         6000.0         6.0             3
Finanças                 5925.0         6300.0         4.5             2
Marketing                5362.5         6150.0         6.8             4
RH                       5100.0         5100.0         7.5             2

The group became an index

After grouping, the group column becomes the index of the result, and not a common column. Accessing it as a column gives a KeyError. The reset_index() returns (or, from the beginning, the as_index=False):

try:
    resumo["departamento"]
except KeyError as erro:
    print("KeyError:", erro)

print(resumo.reset_index().columns.tolist())
print(df.groupby("departamento", as_index=False)["salario"].mean().columns.tolist())

KeyError: 'departamento'
['departamento', 'salario_medio', 'maior_salario', 'nota_media', 'funcionarios']
['departamento', 'salario']

Use the reset_index() when you need to treat the result as a common table: sort by it, filter or join with another table.

One calculation per group, keeping the original shape

The agg reduces each group to one row. The transform calculates by group, but returns one row for each original row, which allows comparing each person with the average of their department: Chapter 23 delves into the

media_do_depto = df.groupby("departamento")["salario"].transform("mean")
df["acima_da_media_do_depto"] = df["salario"] > media_do_depto
print(df[["nome", "departamento", "salario", "acima_da_media_do_depto"]].head(5))

              nome      departamento  salario  acima_da_media_do_depto
0        Ana Souza        Engenharia   4500.0                    False
1       Bruno Lima         Marketing   4650.0                    False
2     Carla Mendes        Engenharia   4800.0                    False
3      Diego Rocha  Ciência de Dados      NaN                    False
4  Elisa Fernandes                RH   5100.0                    False

by group. transform, the filter and the apply The groupby hidden in every business question

"Which region sells the most?", "Which category has the highest margin?", "Which segment cancels the most?": almost every business question is a

disguised. When a question takes the form "what, groupby for what", it is a grouping. what", is a grouping.