Filtering with Conditions¶

A deeper dive into the patterns you use 10× a day.

Set up¶

import pandas as pd
import numpy as np

rng = np.random.default_rng(0)
n = 20

df = pd.DataFrame({
    "name":   [f"User{i}" for i in range(1, n+1)],
    "age":    rng.integers(18, 65, size=n),
    "city":   rng.choice(["Mumbai", "Delhi", "Pune", "Bangalore"], size=n),
    "salary": rng.integers(40_000, 200_000, size=n),
    "active": rng.choice([True, False], size=n, p=[0.7, 0.3]),
})
print(df.head(10))

Single condition¶

import pandas as pd
import numpy as np
rng = np.random.default_rng(0)
df = pd.DataFrame({
    "age": rng.integers(18, 65, size=10),
    "city": rng.choice(["Mumbai","Delhi","Pune"], size=10),
})

# Greater than
print(df[df["age"] > 30])
print()

# Equality
print(df[df["city"] == "Mumbai"])
print()

# Not equal
print(df[df["city"] != "Mumbai"])

AND / OR / NOT¶

import pandas as pd
import numpy as np
rng = np.random.default_rng(0)
df = pd.DataFrame({
    "age": rng.integers(18, 65, size=15),
    "salary": rng.integers(40_000, 200_000, size=15),
    "city": rng.choice(["Mumbai","Delhi"], size=15),
})

# AND
print(df[(df["age"] > 30) & (df["salary"] > 100_000)])
print()

# OR
print(df[(df["city"] == "Mumbai") | (df["age"] > 50)])
print()

# NOT
print(df[~(df["city"] == "Mumbai")])    # same as df["city"] != "Mumbai"

Rules: 1. Wrap each condition in parens. 2. Use & / | / ~, NOT and / or / not.

Membership — `.isin()`¶

import pandas as pd

df = pd.DataFrame({
    "name": ["Alice","Bob","Carol","Dave","Eve"],
    "city": ["Mumbai","Delhi","Pune","Mumbai","Bangalore"],
})

# In a list
print(df[df["city"].isin(["Mumbai", "Pune"])])
print()

# Not in
print(df[~df["city"].isin(["Mumbai", "Pune"])])

Range — `.between()`¶

import pandas as pd

df = pd.DataFrame({
    "name": ["Alice","Bob","Carol","Dave","Eve","Frank"],
    "age":  [22, 28, 35, 42, 51, 65],
})

# Inclusive of both ends (default)
print(df[df["age"].between(25, 50)])
print()

# Exclusive — explicit
print(df[df["age"].between(25, 50, inclusive="neither")])

String filtering — `.str` accessor¶

import pandas as pd

df = pd.DataFrame({
    "email": ["alice@gmail.com","bob@yahoo.com","carol@gmail.com","dave@outlook.com"],
    "name":  ["Alice","Bob","Carol","Dave"],
})

# Contains a substring
print(df[df["email"].str.contains("gmail")])
print()

# Starts with / ends with
print(df[df["name"].str.startswith("A")])
print()

# Regex
print(df[df["email"].str.match(r".+@(gmail|outlook)\.com")])

# Case-insensitive
print(df[df["email"].str.contains("GMAIL", case=False)])

.str has dozens of methods: lower(), upper(), strip(), replace(), split(), len(), match(), etc.

Filtering by NULL / missing¶

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "name": ["Alice","Bob","Carol","Dave"],
    "age":  [25, np.nan, 35, np.nan],
    "city": ["Mumbai","Delhi",None,"Pune"],
})

# Rows where age is missing
print(df[df["age"].isna()])
print()

# Rows where age is NOT missing
print(df[df["age"].notna()])
print()

# Drop rows with ANY missing
print(df.dropna())
print()

# Drop rows with missing in specific cols
print(df.dropna(subset=["age"]))

`.query()` — SQL-like string¶

import pandas as pd
import numpy as np
rng = np.random.default_rng(0)
df = pd.DataFrame({
    "age": rng.integers(20, 60, size=15),
    "salary": rng.integers(40_000, 200_000, size=15),
    "city": rng.choice(["Mumbai","Delhi","Pune"], size=15),
})

# Simple
print(df.query("age > 30 and salary < 100000"))
print()

# `in` and `not in`
print(df.query("city in ['Mumbai', 'Pune']"))
print()

# Reference a Python variable with @
threshold = 50000
print(df.query("salary > @threshold").head())

Pros: more readable for complex conditions. Cons: slightly slower, less flexible with edge cases.

`.where()` — keep matching, NaN-fill the rest¶

df.where(cond) returns the DataFrame with NaN wherever the condition is False:

import pandas as pd

df = pd.DataFrame({
    "x": [1, 2, 3, 4, 5],
    "y": [10, 20, 30, 40, 50],
})

# Keep values > 2, the rest become NaN
print(df.where(df > 2))
print()

# Provide a fill value
print(df.where(df > 2, 0))

`.mask()` — opposite of `.where()`¶

import pandas as pd

df = pd.DataFrame({"x": [1, 2, 3, 4, 5]})

# Hide values > 3 (replace with NaN)
print(df.mask(df > 3))
print()

# Replace > 3 with 99
print(df.mask(df > 3, 99))

Filter, then aggregate — common pattern¶

import pandas as pd
import numpy as np
rng = np.random.default_rng(0)

df = pd.DataFrame({
    "city":   rng.choice(["Mumbai","Delhi","Pune"], size=100),
    "age":    rng.integers(18, 65, size=100),
    "salary": rng.integers(30_000, 200_000, size=100),
})

# Mean salary of high-earners in Mumbai
result = df[
    (df["city"] == "Mumbai") &
    (df["salary"] > 100_000)
]["salary"].mean()
print(f"Mean salary of Mumbai high-earners: ₹{result:,.0f}")

# Count of active employees per city
print()
print("Count per city:")
print(df["city"].value_counts())

Multiple filters — chained `.loc`¶

import pandas as pd

df = pd.DataFrame({
    "name":   ["Alice","Bob","Carol","Dave","Eve"],
    "age":    [25, 30, 35, 40, 45],
    "salary": [50000, 60000, 75000, 90000, 100000],
    "city":   ["Mumbai","Delhi","Mumbai","Pune","Delhi"],
})

# Rows where age > 30 AND city = Mumbai
# Return only name and salary
result = df.loc[
    (df["age"] > 30) & (df["city"] == "Mumbai"),
    ["name", "salary"]
]
print(result)

A real-world example — find anomalies¶

import pandas as pd
import numpy as np
rng = np.random.default_rng(0)

df = pd.DataFrame({
    "transaction_id": range(1, 101),
    "amount":         rng.normal(loc=100, scale=20, size=100),
    "user":           rng.choice(["A","B","C","D","E"], size=100),
})

# Introduce some outliers
df.loc[5, "amount"]  = 1000
df.loc[42, "amount"] = -50
df.loc[88, "amount"] = 5000

# Flag anomalies — beyond 3 std from mean
mean = df["amount"].mean()
std  = df["amount"].std()
print(f"mean={mean:.2f}, std={std:.2f}")

anomalies = df[(df["amount"] > mean + 3*std) | (df["amount"] < mean - 3*std)]
print("\nAnomalies:")
print(anomalies)

Cheatsheet¶

Need	Code
One condition	`df[df["c"] > x]`
AND	`df[(df.a > 1) & (df.b > 2)]`
OR	`df[(df.a > 1) \| (df.b > 2)]`
NOT	`df[~(df.a > 1)]`
In list	`df[df["c"].isin([...])]`
Not in	`df[~df["c"].isin([...])]`
In range	`df[df["c"].between(a, b)]`
String contains	`df[df["c"].str.contains("xy")]`
String regex	`df[df["c"].str.match(r"...")]`
Missing	`df[df["c"].isna()]`
Not missing	`df[df["c"].notna()]`
SQL-like	`df.query("a > 1 and c in ['x','y']")`
Replace non-matches	`df.where(cond, fill)`

Common pitfalls¶

❗ Using and / or — they don't work element-wise. Always & / |.
❗ Forgetting parens — df.a > 1 & df.b > 2 parses wrong. Use (df.a > 1) & (df.b > 2).
❗ Filtering loses the index — to reset, use df.reset_index(drop=True).
❗ isin() with a set — works fine, but if you pass a single value (not in a list), you'll get a confusing error. Wrap singles in [...].
❗ String filter on NaN — df["c"].str.contains("x") returns NaN for NaN rows. Pass na=False to treat them as no-match.

Practice¶

What does this print?

Expected: 2

import pandas as pd
df = pd.DataFrame({"age": [22, 35, 48, 51, 18]})
print(len(df[df["age"].between(20, 50)]))

Find users in Mumbai OR Pune (currently gives an error)

Expected: 3

import pandas as pd
df = pd.DataFrame({"city": ["Mumbai","Delhi","Pune","Mumbai","Delhi"]})
result = df[df["city"] == "Mumbai" or df["city"] == "Pune"]   # bug: must use | on Series
print(len(result))

Quiz — Quick check¶

What you remember

Q1. Why must you use & instead of and for combining boolean masks?

and is slower
and works on single booleans only — Series need element-wise operators
and returns the wrong type
& is required by Pandas

Why: Same as NumPy. A Series of booleans isn't a single boolean, so Python's and raises ValueError: ambiguous truth value. & does element-wise AND.

Q2. What does .isin(["Mumbai", "Pune"]) do?

Returns a boolean Series — True where the value is in the list
Filters the DataFrame
Returns the matching rows
Counts matches

Why: .isin() builds the mask. You still need to apply it: df[df["city"].isin(["Mumbai", "Pune"])].

Q3. When does .query("age > @threshold") use a Python variable?

Always — Python variables are auto-available
When prefixed with @ — the @ tells .query to look in the outer scope
Only if the variable is a number
Never

Why: Inside .query(), plain names refer to columns. The @ prefix means "look this up in the surrounding Python scope" — that's how you parameterize filters.

Common doubts¶

Is .query() slower than boolean indexing?

Slightly, yes — .query() parses a string and translates it to a boolean filter. For complex conditions the readability gain often outweighs the small overhead. For hot inner loops on millions of rows, prefer direct boolean masks.

Why do I get NaN rows when I use .str.contains on a column with missing values?

Because np.nan.contains("x") is NaN, not False. Pandas keeps the result as NaN — which when used in df[mask] raises or behaves oddly. Pass na=False: df["col"].str.contains("x", na=False).

How do I filter out rows where multiple columns are missing?

df.dropna(subset=["a", "b"]) drops rows where either a or b is NaN. Pass how="all" to only drop when both are missing.

What's next¶

→ Adding, Removing, Modifying Columns