Sort and rank

Sort

The sort_values sorts by one or more columns. With multiple, the first is the main key, and the following ones only break ties. The ascending accepts a list, for different directions by column:

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_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.sort_values("salario", ascending=False)[["nome", "salario"]].head(4))
por_depto = df.sort_values(["departamento", "salario"], ascending=[True, False])
print(por_depto[["nome", "departamento", "salario"]].head(6))

               nome  salario
13   Nelson Ribeiro   6450.0
12  Marina Teixeira   6300.0
11    Lucas Barbosa   6150.0
10     Karina Alves   6000.0
               nome      departamento  salario
13   Nelson Ribeiro  Ciência de Dados   6450.0
3       Diego Rocha  Ciência de Dados      NaN
10     Karina Alves        Engenharia   6000.0
2      Carla Mendes        Engenharia   4800.0
0         Ana Souza        Engenharia   4500.0
12  Marina Teixeira          Finanças   6300.0

In the second result, the departments are in alphabetical order, and only within each one the salary sorts from highest to lowest. Two details: the missing salaries (NaN) go to the end, in any direction, and you can change this with na_position="first". And sort_values returns a new table: the original does not change unless you reassign it.

print(df.sort_values("salario", na_position="first")["salario"].head(3).tolist())
print(df["nome"].head(3).tolist() == ["Ana Souza", "Bruno Lima", "Carla Mendes"])
print(df.sort_values("salario", ascending=False).index.tolist()[:4])

[nan, nan, 4500.0]
True
[13, 12, 11, 10]

The index tracks the rows: the result above, [13, 12, 11, 10], shows that the first salary in the list comes from row 13. With ignore_index=True, it is renumbered from 0 onwards. In sortings by more than one pass, use kind="stable", which maintains the original order among equal values.

Rank

The rank gives the position of each value, and what to do with ties changes the result. By default, tied values share the position (the average of the positions they would occupy):

pontos = pd.Series([100, 90, 90, 80], index=["a", "b", "c", "d"])
for metodo in ("average", "min", "dense", "first"):
    print(f"{metodo:<8}", pontos.rank(ascending=False, method=metodo).tolist())

average  [1.0, 2.5, 2.5, 4.0]
min      [1.0, 2.0, 2.0, 4.0]
dense    [1.0, 2.0, 2.0, 3.0]
first    [1.0, 2.0, 3.0, 4.0]

The default is correct when you want the average, but for a score (1st, 2nd, 2nd, 4th), what is expected almost always is the min.

df["posicao_nota"] = df["nota_desempenho"].rank(ascending=False, method="min").astype(int)
print(df.sort_values("posicao_nota")[["nome", "nota_desempenho", "posicao_nota"]].head(5))

               nome  nota_desempenho  posicao_nota
5     Felipe Araújo                9             1
11    Lucas Barbosa                9             1
10     Karina Alves                8             3
4   Elisa Fernandes                8             3
3       Diego Rocha                7             5

The largest and the smallest

The nlargest and the nsmallest return the extremes without sorting the entire column, which is faster in large tables. The result is the same as sort_values().head():

top3 = df.nlargest(3, "salario")
print(top3[["nome", "salario"]])
print(top3["salario"].tolist() == df.sort_values("salario", ascending=False).head(3)["salario"].tolist())

               nome  salario
13   Nelson Ribeiro   6450.0
12  Marina Teixeira   6300.0
11    Lucas Barbosa   6150.0
True

The tie trap in top N

When there are ties, which one enters the top N? Note the grades: there are several people with the same grade, and the nlargest needs to cut somewhere:

cinco = df.nlargest(5, "nota_desempenho")
todos = df.nlargest(5, "nota_desempenho", keep="all")
print(len(cinco), len(todos))
print(sorted(df["nota_desempenho"].nlargest(7).tolist(), reverse=True))

5 6
[9, 9, 8, 8, 7, 7, 6]

They asked for 5, and the keep="all" returned more, because the 5th grade is tied with others. The default (keep="first") chooses by the order in which the rows appear, which is an accidental criterion: if the table arrived in a different order, the top 5 would change. In any ranking that matters, define the tiebreaker explicitly:

desempate = df.sort_values(
    ["nota_desempenho", "projetos_concluidos", "id_funcionario"], ascending=[False, False, True]
).head(5)
print(desempate[["nome", "nota_desempenho", "projetos_concluidos"]])

               nome  nota_desempenho  projetos_concluidos
5     Felipe Araújo                9                    7
11    Lucas Barbosa                9                    3
4   Elisa Fernandes                8                    6
10     Karina Alves                8                    2
9      João Pereira                7                   11

Now the criteria are: highest score, then more projects, then the lowest id. Anyone can arrive at the same result.

Decide beforehand if higher is better

The performance score: higher is better. A column "days late": lower is better. Getting the direction wrong turns the score upside down without any errors. Before ranking, write in a comment what the meaning is.