Overview
Boolean masks, isin, and query: the WHERE clause of pandas.
On this page7 sections
The table we keep using
The order_status column has mixed values. Filtering keeps only matching rows.
| Input data (before filter) | ||
|---|---|---|
| O-101 | paid | 84.20 |
| O-102 | cancelled | 31.00 |
| O-103 | paid | 112.40 |
| O-104 | pending | 55.00 |
Filtering means selecting rows that meet specific conditions. In pandas, you create a 'boolean mask': a column of True and False values, one for each row. When you pass this mask to the DataFrame, it keeps the True rows and removes the False ones.
A boolean mask is created by comparing a column to a value. For example, df['status'] == 'paid' produces a Series of True (paid) and False (not paid) values with exactly the same length as the DataFrame. Passing that Series inside df[mask] or df.loc[mask] keeps only the matching rows.
This is the pandas equivalent of a SQL WHERE clause. Instead of writing WHERE status = 'paid', you write df[df['status'] == 'paid']. The logic is the same; the syntax is different.
What we need from it
In any data analysis job, you rarely need all the data at once. You might need only paid orders, only events from a specific country, or only transactions above a certain amount. Filtering lets you narrow down to exactly the rows you need before doing calculations.
Without proper filtering, you compute statistics on irrelevant data. An average order value that includes cancelled orders is misleading. A revenue total that counts refunded amounts is wrong. Filtering is how you define 'which rows matter for this question.'
Trace it step by step
Building a boolean mask
A comparison like df['order_status'] == 'paid' checks every value in the order_status column against the string 'paid'. The result is a Series of True/False values. You can preview how many rows match using value_counts.
Same table, next cut
Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.
paid = df_orders["order_status"].eq("paid")
print(paid.value_counts(dropna=False))
result = df_orders.loc[
paid,
["order_id", "order_status", "order_total"],
].head(12)Combining conditions with & and |
Python's and and or keywords do not work with pandas Series because a Series contains many values, not just one. Instead, use & for AND, | for OR, and ~ for NOT. Always wrap each condition in parentheses because of operator precedence rules.
mask = (
(df_orders["order_status"] == "paid")
& (df_orders["order_total"] > 50)
)
result = df_orders.loc[mask].copy()The center contains orders that are both paid AND over 50.
'and' is not '&'
Use (condition_a) & (condition_b) for pandas Series. The Python keyword 'and' tries to evaluate the whole Series as a single True/False, which pandas rejects with an error.
isin: filtering by a list of values
isin checks whether each value in a column is in a given list. It reads like SQL's IN clause and is much cleaner than chaining multiple equality comparisons with |.
To exclude values, negate with ~: ~df['status'].isin(['cancelled', 'refunded']) keeps everything except cancelled and refunded orders.
wanted = ["paid", "refunded"]
mask = df_orders["order_status"].isin(wanted)
result = df_orders.loc[
mask,
["order_id", "order_status", "order_total"],
]between and string filters
The between method checks if values fall within a numeric range (inclusive). String methods like .str.startswith() and .str.contains() test text patterns across an entire column. Pass na=False when missing values should fail the filter.
Pandas way vs SQL way: common filter patterns have direct SQL equivalents.
| Filter rule | Pandas expression | SQL equivalent |
|---|---|---|
| Exact match | df['status'] == 'paid' | WHERE status = 'paid' |
| Multiple values | df['status'].isin(['paid', 'refunded']) | WHERE status IN ('paid', 'refunded') |
| Numeric range | df['total'].between(20, 100) | WHERE total BETWEEN 20 AND 100 |
| Text starts with | df['code'].str.startswith('WH-', na=False) | WHERE code LIKE 'WH-%' |
| Negate | ~df['status'].isin(values) | WHERE status NOT IN (...) |
| Null check | df['total'].isna() | WHERE total IS NULL |
| Not null | df['total'].notna() | WHERE total IS NOT NULL |
SQL connection
Every pandas filter maps to a SQL WHERE clause. isin is IN, between is BETWEEN, .str.contains is LIKE, and ~ is NOT. If you can write the SQL, you can write the pandas.
Filter rows, then project columns
.loc puts the mask and the column list in one operation, avoiding chained indexing. This makes the shape of your output clear: which rows pass, and which columns survive.
Always keep the business key (like order_id) in your projection. Without it, you cannot verify duplicates or join back to the original data later.
Build criteria, combine masks, select rows, project columns, and publish.
Filtering does not sort
After filtering, rows keep their original order. If 'first 10 rows' means 'earliest 10 orders,' sort by the timestamp before using head(10).
- Build each condition from the DataFrame.
- Combine conditions with parenthesized & or | operators.
- Use .loc to select passing rows and required columns.
- Sort before positional slicing when order has business meaning.
- Assign the final DataFrame to result.
Copy-paste without reading the output
Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.
Common beginner questions
Why do I need parentheses around each condition?
Python evaluates & and | before == and >. Without parentheses, the expression is grouped incorrectly and you get an error. Always write (condition_a) & (condition_b).
Does filtering change the original DataFrame?
No. df.loc[mask] returns a new DataFrame. The original df is unchanged. If you want to modify the result, store it in a variable and optionally call .copy() to make it fully independent.
What happens to NaN values during filtering?
A comparison with NaN returns False. So rows with missing values in the filtered column are excluded unless you handle them explicitly with isna() or fillna().
What comes next
Now that you can select rows by condition, the next lesson covers transforming data: creating new columns with calculations, mapping values, and enriching your DataFrame without changing its grain (row count).
Practice
Filter df_orders to paid or refunded orders using isin. Keep order_id, order_status, and order_total for the full matching set. Do not call head in the final answer.
Assign the complete filtered DataFrame to result so the grid can validate every matching row.
Practicals · load into the editor
After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.