Filter with conditions

The mask

Comparing a column with a value produces a Series of booleans. Using it as an index keeps the true rows. As True equals 1, summing the mask counts and taking the average gives the proportion:

import pandas as pd

pd.set_option("display.width", 170)
pd.set_option("display.max_columns", 20)
df = pd.read_csv("dados/funcionarios_15.csv")

mascara = df["experiencia"] > 5
print(mascara.tolist())
print(df.loc[mascara, ["nome", "experiencia"]])
print(int(mascara.sum()), round(float(mascara.mean()), 3))

[False, False, False, False, False, True, True, True, False, False, False, False, False, True, True]
              nome  experiencia
5    Felipe Araújo            6
6   Gabriela Nunes            7
7   Henrique Costa            8
13  Nelson Ribeiro            6
14       Ana Souza            7
5 0.333

Combine conditions: `&`, `|`, `~`

To combine conditions, use & (and), | (or), and ~ (not), and each condition in parentheses: these operators have higher precedence than comparison. The and and the or in Python do not work, because they need to decide if an Series integer is true or false:

experiente_e_bom = df[(df["experiencia"] > 3) & (df["nota_desempenho"] > 7)]
print(experiente_e_bom[["nome", "experiencia", "nota_desempenho"]])
print(len(df[(df["departamento"] == "Engenharia") | (df["departamento"] == "RH")]))
print(len(df[~(df["cidade"] == "Curitiba")]))

try:
    df[(df["experiencia"] > 3) and (df["nota_desempenho"] > 7)]
except ValueError as erro:
    print("ValueError:", erro)

               nome  experiencia  nota_desempenho
4   Elisa Fernandes            5                8
5     Felipe Araújo            6                9
11    Lucas Barbosa            4                9
5
13
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Notice the ~(df["cidade"] == "Curitiba"): it also keeps the rows where the city is absent, because NaN == "Curitiba" is false, and the negation of false is true. A mask is always about values, and an absent value never "is equal" to anything.

The shortcuts: `isin`, `between` and `str.contains`

Three methods replace the more verbose expressions:

print(len(df[df["departamento"].isin(["Engenharia", "Ciência de Dados"])]))
print(df[df["experiencia"].between(2, 4)]["nome"].tolist())
print(df[df["nome"].str.contains("Souza")]["nome"].tolist())

4
['Bruno Lima', 'Carla Mendes', 'Diego Rocha', 'João Pereira', 'Karina Alves', 'Lucas Barbosa']
['Ana Souza', 'Ana Souza']

The isin replaces a string of |. The between is inclusive at both ends by default (inclusive="both") and reads well in ranges of values. The str.contains is used for text searches, like a search box.

Text with missing: what happens depends on the type

In a text column with missing values, the str.contains needs to decide what to respond to them. The response depends on the column type, and it is one of the changes in Pandas 3:

busca = df["departamento"].str.contains("eng", case=False)
print(busca.dtype, bool(busca.isna().any()), bool(busca[8]))

como_objeto = df["departamento"].astype(object)
busca_objeto = como_objeto.str.contains("eng", case=False)
print(busca_objeto.dtype, bool(busca_objeto.isna().any()))
try:
    df[busca_objeto]
except ValueError as erro:
    print("ValueError:", erro)

bool False False
object True
ValueError: Cannot mask with non-boolean array containing NA / NaN values

In the type str of Pandas 3, the absent value becomes False automatically, and the filter works. In the type object (code written for older versions, or a column that mixes types), the result has NaN and the filter fails. To not depend on the type, tell what to do with the absents using the parameter na, and the same line works in any version:

com_na_false = df["departamento"].str.contains("eng", case=False, na=False)
com_na_true = df["departamento"].str.contains("eng", case=False, na=True)
print(int(com_na_false.sum()), int(com_na_true.sum()))
print(df[com_na_false]["nome"].tolist())

3 4
['Ana Souza', 'Carla Mendes', 'Karina Alves']

The na=False finds 3 employees, and the na=True finds 4: the fourth is the employee without a department, which the na=True treats as "contains". The choice is yours, and writing the na explicit makes the decision visible to the reader.

`query`: filter with text

The query writes the condition as an expression, without repeating df[...] no parentheses, and accesses Python variables with @:

limite = 7
print(df.query("experiencia > 3 and nota_desempenho > @limite")["nome"].tolist())

['Elisa Fernandes', 'Felipe Araújo', 'Lucas Barbosa']

Change only the rows that pass the filter

For change values in filtered rows, use .loc[máscara, coluna] = valor, in a single step. Chaining (df[mascara]["coluna"] = valor) alters a temporary copy, and in Pandas 3 this never affects the original table:

copia = df.copy()
copia.loc[copia["experiencia"] > 6, "nota_desempenho"] = 10
print(copia["nota_desempenho"].tolist())

encadeado = df.copy()
encadeado[encadeado["experiencia"] > 6]["nota_desempenho"] = 10
print(encadeado["nota_desempenho"].tolist() == df["nota_desempenho"].tolist())

[4, 5, 6, 7, 8, 9, 10, 10, 6, 7, 8, 9, 4, 5, 10]
True

Keep the shape with `where`

The df["coluna"].where(condição) keeps all rows and replaces with NaN those that do not pass, while the mask filter discards the rows. I use the where when I need to preserve alignment with the original table.