Skip to content

    Chapter 25, Intermediate

    MultiIndex

    An index with more than one level appears alone after any `groupby` with two keys. It is powerful for selecting slices, and confusing when you don't know what it is. Here are the few movements that matter.

    Where it comes from

    Grouping by two columns returns a result with a two-level index, and aggregating with two functions returns two-level columns:

    intermediario/cap25_multiindex.pylinhas 10 a 24
    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"}
df = pd.read_csv("dados/funcionarios_100.csv").drop_duplicates("id_funcionario", keep="last")
df["salario"] = pd.to_numeric(df["salario"].str.replace("R$ ", "", regex=False))
df["departamento"] = df["departamento"].str.strip().str.lower().map(CANONICO)

m = df.groupby(["departamento", "cidade"])["salario"].agg(["mean", "count"]).round(0)
print(type(m.index).__name__, m.index.names)
print(m.head(4))
print(type(df.groupby("departamento").agg({"salario": ["mean", "max"]}).columns).__name__)
    Output
    MultiIndex ['departamento', 'cidade']
                                    mean  count
departamento     cidade                        
Ciência de Dados Belo Horizonte   9790.0      4
                 Curitiba         9242.0      4
                 Porto Alegre     8213.0      3
                 Rio de Janeiro  11610.0      6
MultiIndex

    Select by level

    A tuple selects an exact row. The xs selects by level, crossing the other. The IndexSlice allows slices, but requires the index sorted:

    intermediario/cap25_multiindex.pylinhas 29 a 34
    print(float(m.loc[("Engenharia", "São Paulo"), "mean"]))
print(m.xs("Engenharia", level="departamento").shape)

ordenado = m.sort_index()
em_sp = ordenado.loc[pd.IndexSlice[:, "São Paulo"], :]
print(em_sp.index.get_level_values("departamento").tolist())
    Output
    9040.0
(5, 2)
['Ciência de Dados', 'Engenharia', 'Finanças', 'Marketing', 'RH']

    Change the shape

    The unstack takes one level for the columns and the reset_index returns all levels to common columns. The swaplevel swaps the order of the levels:

    intermediario/cap25_multiindex.pylinhas 39 a 42
    largo = m["mean"].unstack("cidade")
print(largo.shape, largo.columns.tolist())
print(m.swaplevel().sort_index().index.names)
print(m.reset_index().columns.tolist())
    Output
    (5, 5) ['Belo Horizonte', 'Curitiba', 'Porto Alegre', 'Rio de Janeiro', 'São Paulo']
['cidade', 'departamento']
['departamento', 'cidade', 'mean', 'count']

    When to avoid

    The MultiIndex it is not necessary to group, summarize or join, and most of the confusion with it comes from carrying the double index forward unnecessarily. My rule: use it to read the result of a groupby or a pivot table, and remove it with reset_index() as soon as you continue the analysis, filter, join or save the result to a file.

    Exercise 1

    A point from the table

    Write media_do_grupo(m, depto, cidade), which returns the average salary of a department in a city based on m, or None if the combination does not exist.