← All lessons

Filter rows

Boolean masks, query, isin, between, and combining conditions.

A filter is a True/False Series aligned to the index. Put it inside [] or pass it to loc.

Goal

Combine conditions with & | ~, and use isin, between, and query without precedence bugs.

One condition

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Nairobi", "Mombasa", "Kisumu", "Eldoret"],
        "product": ["A", "B", "A", "B", "A"],
        "units": [12, 7, 9, 11, 6],
        "price": [10.5, 22.0, 10.5, 22.0, 10.5],
    }
)
df["revenue"] = df["units"] * df["price"]
print(df[df["units"] >= 9])
print()
print(df.loc[df["city"] == "Nairobi"])

Combine with & and |

Python and / or do not work on Series. Use & and |, and parentheses around each comparison.

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Nairobi", "Mombasa", "Kisumu", "Eldoret"],
        "product": ["A", "B", "A", "B", "A"],
        "units": [12, 7, 9, 11, 6],
        "price": [10.5, 22.0, 10.5, 22.0, 10.5],
    }
)
print(df[(df["city"] == "Nairobi") | (df["city"] == "Kisumu")])
print()
print(df[(df["product"] == "A") & (df["units"] >= 9)])
print()
print(df[~(df["product"] == "B")])

isin and between

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Nairobi", "Mombasa", "Kisumu", "Eldoret"],
        "product": ["A", "B", "A", "B", "A"],
        "units": [12, 7, 9, 11, 6],
        "price": [10.5, 22.0, 10.5, 22.0, 10.5],
    }
)
print(df[df["city"].isin(["Nairobi", "Mombasa"])])
print()
print(df[df["units"].between(7, 11)])

between is inclusive on both ends by default.

query

A string expression. Handy when column names are simple identifiers.

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Nairobi", "Mombasa", "Kisumu", "Eldoret"],
        "product": ["A", "B", "A", "B", "A"],
        "units": [12, 7, 9, 11, 6],
        "price": [10.5, 22.0, 10.5, 22.0, 10.5],
    }
)
print(df.query("units >= 9 and product == 'A'"))
print()
min_units = 8
print(df.query("units >= @min_units"))

@min_units pulls in a Python variable.

Filter on a computed column

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Mombasa", "Kisumu", "Eldoret"],
        "product": ["A", "A", "B", "A"],
        "units": [12, 9, 11, 6],
        "price": [10.5, 10.5, 22.0, 10.5],
    }
)
df["revenue"] = df["units"] * df["price"]
print(df[df["revenue"] > 100][["city", "product", "revenue"]])
Pitfall

df.units >= 9 & df.product == "A" is parsed as df.units >= (9 & df.product) == "A" and explodes. Always write (df["units"] >= 9) & (df["product"] == "A").