Filtering Data in Pandas

Filtering is the most common operation in data analysis. Every report starts with "give me the rows that match these criteria." Pandas has multiple ways to filter — boolean indexing, .query(), and .isin() — each with its own strengths.

Learning Objectives

• Filter rows using boolean conditions
• Combine multiple conditions with &, |, ~
• Use .query() for readable filter expressions
• Filter with .isin(), .between(), and .str methods
• Handle NaN values in filters

---

Boolean Indexing — The Core Pattern

import pandas as pd

df = pd.read_csv("orders.csv", parse_dates=["order_date"])

# Single condition — returns a boolean Series
mask = df["status"] == "completed"
print(mask.head())
# 0     True
# 1     True
# 2    False
# 3     True

# Apply the mask to filter rows
completed = df[mask]
# Or in one step:
completed = df[df["status"] == "completed"]

Sample output:

order_id	customer_id	total	status
O001	C001	298.00	completed
O002	C003	389.00	completed
O004	C001	35.99	completed

---

Comparison Operators

# Numeric comparisons
high_value   = df[df["total"] > 200]
affordable   = df[df["total"] <= 100]
mid_range    = df[df["total"].between(50, 200)]  # inclusive

# String equality
from_india   = df[df["country"] == "India"]
not_cancelled = df[df["status"] != "cancelled"]

# Date comparison
recent = df[df["order_date"] >= "2024-01-01"]
january = df[df["order_date"].between("2024-01-01", "2024-01-31")]

---

Combining Conditions

Use & (AND), | (OR), ~ (NOT). Always wrap each condition in parentheses.

# AND — both must be true
high_completed = df[(df["status"] == "completed") & (df["total"] > 200)]

# OR — at least one must be true
pending_or_cancelled = df[(df["status"] == "pending") | (df["status"] == "cancelled")]

# NOT — negate a condition
not_cancelled = df[~(df["status"] == "cancelled")]

# Complex condition
target = df[
    (df["order_date"] >= "2024-01-01") &
    (df["total"] > 100) &
    (df["status"].isin(["completed", "pending"]))
]

Use &, |, ~ — not and, or, not

df[condition1 and condition2] raises a ValueError. NumPy/Pandas boolean arrays use & and |. Always parenthesise: (cond1) & (cond2).

---

.isin() — Filter by a List of Values

# Orders with specific statuses
active = df[df["status"].isin(["completed", "pending", "processing"])]

# Filter out certain values — combine with ~
not_done = df[~df["status"].isin(["completed", "refunded"])]

# Filter by a list of customer IDs
vip_ids = ["C001", "C003", "C012"]
vip_orders = df[df["customer_id"].isin(vip_ids)]

---

.query() — Readable Filter Syntax

.query() lets you write conditions as a string — closer to natural language. Great for complex multi-condition filters.

# Equivalent boolean index vs query
result = df[(df["status"] == "completed") & (df["total"] > 200)]
result = df.query("status == 'completed' and total > 200")

# Using external variables
min_total = 150
result = df.query("total > @min_total and status == 'completed'")

# Date filtering
result = df.query("order_date >= '2024-01-01' and total > 100")

For mid-level analysts

.query() is often more readable than chained boolean conditions, especially for 3+ conditions. It also handles column names with spaces if you wrap them in backticks: .query("order date>= '2024-01-01'").

---

String Filtering — .str Accessor

# Email domain
gmail_customers = df[df["email"].str.endswith("@gmail.com")]

# Product name contains a word
electronics = df[df["product"].str.contains("Pro", case=False)]

# Starts/ends with
bulk_orders = df[df["order_id"].str.startswith("B")]

# String length
long_names = df[df["product"].str.len() > 15]

# Case-insensitive exact match
completed = df[df["status"].str.lower() == "completed"]

# Remove whitespace before comparing
df["status"] = df["status"].str.strip()

---

Handling NaN in Filters

# Find rows with missing delivery date
undelivered = df[df["delivery_date"].isna()]

# Find rows where delivery date exists
delivered = df[df["delivery_date"].notna()]

# NaN in conditions — fill before filtering
df_filled = df.fillna({"discount_pct": 0})
big_discount = df_filled[df_filled["discount_pct"] > 15]

NaN comparisons always return False

df[df["delivery_date"] == None] returns zero rows. Always use .isna() or .notna() to check for missing values.

---

Practical Filtering Patterns

Pattern 1 — Top N rows after filtering

# Top 5 highest-value completed orders
top5 = (
    df[df["status"] == "completed"]
    .sort_values("total", ascending=False)
    .head(5)
)

Pattern 2 — Filter then select columns

# VIP customer order summary
vip_summary = df[df["segment"] == "VIP"][["order_id", "customer_id", "total", "order_date"]]

Pattern 3 — Filter with date range

# Q1 2024 completed orders
q1 = df.query("order_date >= '2024-01-01' and order_date <= '2024-03-31' and status == 'completed'")

Pattern 4 — Combine filters for an anomaly report

# Undelivered orders older than 14 days that are not cancelled
import pandas as pd

today = pd.Timestamp("2024-02-01")
overdue = df[
    df["delivery_date"].isna() &
    (df["status"] != "cancelled") &
    (today - df["order_date"]).dt.days > 14
]

---

Practice Exercises

Warm-up 1. Filter df to only completed orders. 2. Find all orders with total > 100. 3. Find all orders from customers in ["C001", "C003"] using .isin().

Main 4. Find all orders that are pending OR cancelled from January 2024. 5. Use .query() to find completed orders with total between £100 and £400. 6. Find all products whose name contains "Pro" (case-insensitive). 7. Find orders where delivery_date is missing AND status is not cancelled (overdue orders).

Stretch 8. Write a filter that finds "problem orders": completed status but delivery_date is NULL, placed more than 10 days ago. 9. Write a function smart_filter(df, **kwargs) that accepts keyword arguments like status="completed", min_total=100, max_total=500 and applies all non-None filters dynamically.

---

Interview Questions

[Beginner] How do you filter a Pandas DataFrame to show only rows where status == "completed"?

[Beginner] What is the difference between .isna() and == None for checking missing values?

[Mid-level] You need to filter rows where status is in ["completed", "pending"] AND total > 100. Write this using both boolean indexing and .query().

[Mid-level] Why must you wrap each condition in parentheses when using & in Pandas?

[Senior] A DataFrame has 50 million rows. Filtering with boolean indexing takes 8 seconds. How would you improve this?

---

← Pandas DataFrames · Next: GroupBy and Merge →