Masks, isin, between and query - and the parenthesis rule that catches everyone once.
Overview
A mask is a Series
Filtering in pandas does not work like filtering a Python list. There is no loop, no if-statement, no list comprehension. Instead, a comparison produces a boolean Series, and that Series is handed to the frame to select rows.
That two-step process is worth understanding on its own terms. The comparison step and the selection step are separate operations. You can store the mask in a variable, inspect it, combine it with other masks, count how many rows it matches, and reuse it. A mask is data, not a control-flow instruction.
Combining masks uses bitwise operators, not the Python keywords. The and-keyword does not work on Series. You write the ampersand instead. The same applies to or and not. Every combined mask needs parentheses around each part, because the bitwise operators bind tighter than comparison operators. Leaving out the parentheses gives a TypeError or, worse, a wrong result.
This pattern repeats everywhere in pandas. Once you are comfortable with masks, group-by filters, conditional assignment, and flagging all use the same idea. A boolean Series aligned to the data by index.
The module also covers isin for membership checks, between for range checks, and query for string-based filtering. Each is a convenience wrapper around the mask pattern. Knowing the underlying pattern makes the wrappers obvious rather than mysterious.
Filtering is the step you will do most often. Almost every analysis starts by narrowing the data to the subset that matters. Getting this right means every downstream result is computed on the right rows.
df["age"] >= 25 doesn't return rows. It returns a boolean Series, one value per row, carrying the same index as the frame.
You then hand that mask to .loc, and pandas keeps the rows where it's True.
Because the mask is an plain Series, all you know applies to it. mask.sum() counts matches. True is 1. mask.mean() gives the proportion. ~mask inverts it. It can be stored in a var, named, and reused.
Naming masks is worth doing when a filter has a few parts:
adult = df["age"] >= 18
local = df["city"] == "pune"
recent = df["days"] < 30
df.loc[adult & local & recent]
Each name docs a rule, and each can be counted apart when the result is unexpectedly empty. Which is how you find out which clause is doing the damage.
Worth knowing
A comparison produces a boolean Series; pass it to .loc. mask.sum() counts matches for free.
Parenthesise every condition and use & | ~. and raises, and missing parentheses parse wrongly because & binds tighter than >.
isin and between read as the question you are asking; between is inclusive on both ends by default.
Missing values fail every comparison, so a condition and its negation can both drop the same row.
query("age > 20 and score > 7") takes a string, needs no parentheses, and uses @name to reference Python variables.
Filtering returns a new frame and keeps the original labels — it never modifies the original.
Filtering Rows
Masks, isin, between and query, and the parenthesis rule that catches all.
A comparison gives a boolean Series
Which you then hand to loc. The mask keeps the original index.
example_01.pypandas
import pandas as pd
df = pd.DataFrame({
"name": ["ana", "raj", "kim", "sam"],
"age": [31, 25, 40, 19],
})
mask = df["age"] >= 25
print("the mask itself:")
print(mask)
print()
print("used to select:")
print(df.loc[mask])
print()
print("count matches for free, because True is 1:")
print(" matched:", int(mask.sum()), "of", len(mask))
Output
Parentheses are not optional
& binds tighter than >, so the obvious spelling parses wrongly.
example_02.pypandas
import pandas as pd
df = pd.DataFrame({"age": [19, 25, 31, 40]})
good = df.loc[(df["age"] > 20) & (df["age"] < 35)]
print("with parentheses:", list(good["age"]))
try:
df.loc[df["age"] > 20 & df["age"] < 35]
except Exception as e:
print("without them :", type(e).__name__)
print(" ", str(e)[:70])
print()
try:
df.loc[(df["age"] > 20) and (df["age"] < 35)]
except ValueError as e:
print("using `and` : ValueError")
print(" ", str(e)[:66])
print()
print("Use & | ~ , never and/or/not, and parenthesise every condition.")
Output
isin and between
Clearer than a chain of comparisons, and they read as the question you are asking.
example_03.pypandas
import pandas as pd
df = pd.DataFrame({
"city": ["pune", "delhi", "goa", "pune"],
"age": [31, 25, 40, 19],
})
print("isin:")
print(df.loc[df["city"].isin(["pune", "goa"])])
print()
print("between is inclusive on both sides by default:")
print(list(df.loc[df["age"].between(25, 31), "age"]))
print(" exclusive:", list(df.loc[df["age"].between(25, 31, inclusive="neither"), "age"]))
print()
print("negate with ~ :")
print(list(df.loc[~df["city"].isin(["pune"]), "city"]))
Output
df["city"].isin(["pune", "goa"]) is clearer and faster than (df["city"] == "pune") | (df["city"] == "goa"). It scales to a list of any length. Including one found at runtime.
df["age"].between(25, 31) is inclusive of both ends by default. The inclusive arg takes "both", "left", "right" or "neither". This Is worth passing by name when the boundary matters. The default isn't what all assumes.
~ negates either of them.
Missing values fail every test
So a condition and its opposite can both drop the same row.
example_04.pypandas
import pandas as pd
import numpy as np
s = pd.Series([1.0, np.nan, 5.0])
print("s > 2 :", list(s > 2))
print("s <= 2:", list(s <= 2))
print(" the NaN row is False in BOTH - it vanishes from either half.")
print()
print("counts:", int((s > 2).sum()), "+", int((s <= 2).sum()), "= 2, not 3")
print()
print("Handle it explicitly when it matters:")
print(" isna :", list(s.isna()))
print(" fillna first:", list(s.fillna(0) > 2))
Output
query, when the expression is long
A string, so column names need no quoting - and no parentheses either.
example_05.pypandas
import pandas as pd
df = pd.DataFrame({
"city": ["pune", "delhi", "goa", "pune"],
"age": [31, 25, 40, 19],
"score": [8.5, 9.1, 7.2, 6.0],
})
print(df.query("age > 20 and score > 7"))
print()
print("no parentheses needed, and `and` works here because it is")
print("pandas' own parser rather than Python's operators.")
print()
print("reference a Python variable with @:")
cutoff = 30
print(list(df.query("age > @cutoff")["city"]))
print()
print("in / not in work too:")
print(list(df.query("city in ['pune', 'goa']")["city"]))
Output
Filtering returns a new frame
Which is why assigning into the result is the mistake the next module is about.
example_06.pypandas
import pandas as pd
df = pd.DataFrame({"name": ["ana", "raj"], "age": [31, 25]})
sub = df.loc[df["age"] > 30]
print("the filtered frame:")
print(sub)
print()
print("its index kept the original labels:", list(sub.index))
print()
sub2 = df.loc[df["age"] > 30].reset_index(drop=True)
print("reset_index(drop=True) renumbers:", list(sub2.index))
print()
print("Filtering never modifies the original:")
print(" original still has", len(df), "rows")
Output
The parenthesis rule
This is the single most common syntax error in pandas.
df["age"] > 20 & df["age"] < 35 doesn't mean what it looks like. In Python, & binds tighter than >. This parses as df["age"] > (20 & df["age"]) < 35. The result is an error or, worse, a thing plausible.
each rule needs its own parentheses: (df["age"] > 20) & (df["age"] < 35).
The related mistake is and instead of &. Python's and needs a single truth value and a Series has many. It raises "The truth value of a Series is ambiguous". That message always means the same thing: use &, and add parentheses.
The three ops are & (and), | (or), ~ (not). There's no way to make Python's precedence rules cooperate here. Parenthesising turns a reflex.
Missing values fail everything
each compare involving NaN is False. That includes >, <, == and !=.
The result is exact and easy to miss: df[df["x"] > 2] and df[df["x"] <= 2] do not split the frame. Rows where x is missing are absent from both. The two counts don't add up to the row count.
This is often the right default. An unknown value truly doesn't meet a rule. But it means "all else" isn't the same as "the negation of this rule" when missing data is possible.
When missing rows should be handled rather than dropped, say so by name with isna(), or fill before filtering.
query
df.query("age > 20 and score > 7") takes the rule as a string.
Inside the string, column names are bare identifiers, and/or/not work most times. No parentheses are needed for precedence. That makes long rules much easier to read than the op form.
@name links a Python var: df.query("age > @cutoff").
in and not in work against lists.
The costs: column names with spaces need backticks, there's no editor autocompletion or type checking inside a string. A typo turns a runtime error rather than a thing a linter catches. query also has a small parsing cost per call. This Is irrelevant once and noticeable in a loop.
Use it when a rule is long enough that the op form is hard to read. Use ops else.
Filtering copies
df.loc[mask] returns a new frame. The first is untouched. The result keeps the first index labels rather than renumbering.
Both facts matter downstream. The surviving labels mean spot-based code breaks. reset_index(drop=True) is the fix when you want a clean 0..n-1.
That the result is new. And namely that it may be a copy rather than a view of the first — is what makes assigning into a filtered frame unreliable. That is the subject of the next module. It's the single largest source of confusion in pandas.
A summary
Build masks with checks, combine with & | ~, and parenthesise all.
Prefer isin and between where they fit. They say what you mean.
note NaN fails each test. So A rule and its negation aren't a split.
Reach for query when the formula is long, and for ops when it's short.
And know that the result is a new object whose index came along for the ride.
Filtering on more than one column
Rules on other columns combine just like rules on one:
For a var number of rules. Built from user input, or a config — combine them programmatically:
from functools import reduce
mask = reduce(lambda a, b: a & b, conditions)
np.logical_and.reduce(rules) does the same thing. Either is better than building a query string. This Loses type checking and invites injection if any part comes from outside.
Filtering by index rather than data
df.loc[["a", "c"]] selects by label and raises if a label is missing.
df.reindex(["a", "c", "zz"]) selects by label and fills missing ones with NaN instead. The gap matters when you're conforming one frame to another's labels and lack is expected.
df[df.index.isin(wanted)] filters by membership without needing each label to exist.
df.index.str.startswith("2024") works when the labels are strings, since an Index has the .str accessor too.
Filtering with a lookup from another frame
The common task of "keep rows whose key appears in this other table" has two forms.
df[df["id"].isin(other["id"])] is the direct one, and it's often what you want. It can't change the row count upward and needs no thought about join semantics.
df.merge(other[["id"]], on="id") does the same thing as an inner join. can times rows if other has copy ids. isin can't.
For "keep rows whose key does not appear", ~isin is the whole answer. Here The join equal needs an outer join with an indicator and a filter. This is one of the places isin is clearly better than a merge.
nlargest, sample and head as filters
Not each subset comes from a rule.
df.nlargest(10, "sales") — the top ten by a column, without sorting all.
df.sample(n=100, random_state=0) — a random subset, reproducible with the seed. frac=0.1 takes a proportion.
df.head(1000) — the first thousand. This Is only real if the order means a thing.
df.drop_duplicates(subset=["id"]) — one row per key.
Performance
Filtering builds a boolean mask the length of the frame, then copies the matching rows. Both cost time scaled to the data.
Three things that help on large frames:
Combine rules before selecting.df[(a) & (b)] allocates one result. df[a][b] allocates two.
Filter before finding, not after. each step downstream then touches fewer rows. Often the largest structural win on hand.
Use a group dtype for the columns you filter on again and again. compare then runs on int codes rather than strings.
query has a small parsing cost per call. This Is irrelevant once and measurable in a loop. It can also use numexpr for very large frames. This At times makes it faster than the op form rather than slower.
The mistakes, collected
Missing parentheses.(a > 1) & (b < 2), always.
**and instead of &.** The "truth value is ambiguous" error always means this.
**Losing na=False** on a .str predicate. This Raises as soon as the data has a gap.
given a rule and its negation split the frame. They don't, when values can be missing.
Chained set on a filtered frame.df[mask]["col"] = x. The subject of its own module. The most costly mistake here.
Losing the index came along.reset_index(drop=True) when downstream code thinks positionally.
Filters that read well
A filter is a statement about the data, and it's worth writing it as one.
Three gains beyond readability: each mask can be counted apart when the result is unexpectedly empty. The masks can be reused for a complementary pick. And the mixing line reads as the sentence it shows.
For a filter that appears in a few places, a small function returning the mask keeps the meaning in one place:
df.loc[active(df)] then means the same thing all over. Changing the meaning changes it all over.
Debugging an empty result
When a filter returns nothing, the cause is nearly always one of five things. They can be checked in about a minute.
A type mismatch.df["id"] == 1 against a string column matches nothing. Check df["id"].dtype.
Whitespace or case.df["city"] == "Pune" against "pune ". Check df["city"].value_counts().head(20).
Missing values. They fail each compare. So A rule can exclude more than you think.
**An & that should be |.** Rules that can't be true at once.
A stale var. In a notebook, df isn't what the cell above assumed.
Counting each rule apart — is_adult.sum(), in_scope.sum() — finds which clause is in charge in one step. This Is far quicker than thinking about the mix.
Filtering and the copy warning
sub = df[mask] makes a new frame whose link to df is on purpose unspecified.
Anything you then assign into sub may or may not affect df. This is the single most common route into the copy warning. It's worth deciding at the point of filtering rather than at the point of set:
sub = df[mask].copy() if sub is a split dataset.
df.loc[mask, "col"] = ... if you meant to change df.
Adding .copy() to a filter you intend to modify costs seven characters and drops the question fully.
A closing note on Filtering Rows
Filtering is the most-used step in pandas and has two persistent gotchas, both syntactic and both cheap to avoid.
Parenthesise each rule, because & binds tighter than the compare ops and the unparenthesised version parses into a thing else fully. And use &, |, ~ rather than and, or, not. This Need a single truth value that a Series can't provide.
The semantic gotcha is missing values. They fail each compare. A rule and its negation don't split the frame. Rows quietly appear in neither half.
Beyond that, the advice is about readability: name your masks when there's more than one. Each can be counted apart when the result is unexpectedly empty. Debugging a filter that returns nothing is nearly always a matter of finding which clause is in charge. Named masks turn that into one line rather than a process of elimination.
One more thing
df.query accepts engine="python" for expressions the default parser can't handle. inplace=True which, as elsewhere, is best avoided.
Questions people ask about Filtering Rows
How do I filter rows with more than one condition? Combine boolean masks with & for and, | for or, and wrap each condition in parentheses, for example df[(df.a > 1) & (df.b == 'x')].
Why do I get an error when I use 'and' instead of '&'? Python's and tries to turn a whole Series into one True or False, which is ambiguous. & works element by element.
How do I keep rows whose value is in a list? Use df[df['col'].isin(['a', 'b'])]. Negate it with ~ to keep rows not in the list.
Why does my filter drop rows with missing values? Any comparison with NaN is False, so those rows never pass the mask. Add | df['col'].isna() if you want to keep them.
When is query() better than a boolean mask? query('a > 1 and b == 2') reads more cleanly for long conditions and chains well. Masks are better when the logic is built from variables or functions.
Does filtering change the original DataFrame? No. It returns a new frame with the original index labels, so you may want reset_index(drop=True) afterwards.
Filtering Rows, in summary
checks give boolean Series. Combine them with &, | and ~, and parenthesise each one.
isin and between read as the question being asked. query earns its place when the formula is long.
Missing values fail each test. So A rule and its negation don't split the frame. And .copy() the result if you intend to modify it. This Decides the copy question at the point it arises rather than at the point it bites.
Check yourself
0 of 4
Answer without scrolling back up.
Why does `df[df['age'] > 20 & df['age'] < 35]` fail?
Python's precedence rules cannot be made to cooperate here, so parenthesising every condition becomes a reflex.
Do `df[df['x'] > 2]` and `df[df['x'] <= 2]` together cover every row?
Every comparison with NaN is False, so 'everything else' is not the same as 'the negation of this condition' when missing data is possible.
What does `between(25, 31)` include by default?
The `inclusive` argument takes 'both', 'left', 'right' or 'neither' - worth passing explicitly whenever the boundary matters.
What does `@` mean inside a `query` string?
df.query('age > @cutoff') uses the local variable cutoff. Inside the string, bare identifiers are column names.
Cheat sheet
Filtering Rows
Filter pandas rows with boolean masks, isin, between and query. Learn the parenthesis rule for combining conditions that trips every beginner.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.