These are the notes for Data Analytics with Python, episode 15. In episode 14 we selected rows by label (loc) and by position (iloc). Most real questions, though, are about a condition: only the rainy days, only orders above ₹500, only rows with missing data. Keeping only the rows that match a condition is called filtering. Here we invent it from scratch: boolean masks and boolean indexing, combining conditions with &, | and ~, the parentheses rule, the shortcuts isin, between and query(), text filters with str.contains, and filtering missing values with isna / notna. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5, pyarrow 26.0.0); outputs are unedited.
Most examples use Ramesh's week of chai sales (episode 12). The first line only hides pandas' long internal traceback so errors are short:
import sys; sys.tracebacklimit = 0
import pandas as pd
week = pd.DataFrame({
"day": ["Mon", "Tue", "Wed", "Thu",
"Fri", "Sat", "Sun"],
"cups": [320, 280, 410, 300, 350, 390, 450],
"price": [10, 10, 12, 10, 10, 12, 12],
"weather": ["sunny", "rain", "cold", "sunny",
"sunny", "cold", "rain"],
})
The problem: "show me only the big days"
Ramesh asks: on which days did I sell more than 350 cups? With a for loop and if (episode 6):
big = []
for i in range(len(week)):
if week["cups"][i] > 350:
big.append(week["day"][i])
print(big)
['Wed', 'Sat', 'Sun']
Correct, but five lines for one simple question, and it only gives a list of days, not the rows. Worse, it is slow on real data. Two lakh (200,000) random orders, loop vs the one-liner we're about to invent, timed with Python's time module:
import pandas as pd, numpy as np, time
rng = np.random.default_rng(1)
orders = pd.DataFrame({"amt": rng.integers(10, 1000, 200_000)})
t = time.time()
big = []
for i in range(len(orders)):
if orders["amt"][i] > 900:
big.append(i)
print(len(big), "big orders, loop:", round(time.time() - t, 2), "s")
t = time.time()
big2 = orders[orders["amt"] > 900]
print(len(big2), "big orders, mask:", round(time.time() - t, 4), "s")
20083 big orders, loop: 2.46 s
20083 big orders, mask: 0.0016 s
(Exact times vary from machine to machine and run to run; the gap of more than a thousand times is what matters.) A Python loop handles one value at a time. pandas works on the whole column at once in fast compiled code: vectorised, like NumPy in episode 8.
Boolean masks
A boolean is a value that is only True or False (episode 4). Comparing a whole column with a value gives one boolean per row:
mask = week["cups"] > 350
print(mask)
print(week[mask])
0 False
1 False
2 True
3 False
4 False
5 True
6 True
Name: cups, dtype: bool
day cups price weather
2 Wed 410 12 cold
5 Sat 390 12 cold
6 Sun 450 12 rain
week["cups"] > 350is a boolean mask: a Series ofTrue/Falsewith the same index as the table (dtypebool). Think of a stencil: holes (True) where paint goes through.week[mask]keeps exactly theTruerows. Selecting rows with a mask is boolean indexing. The kept rows keep their original labels (2, 5, 6).- Usually written in one go:
week[week["cups"] > 350].
Comparison operators that build masks: >, >=, <, <=, == (equal), != (not equal).
Useful trick: True counts as 1 and False as 0, so mask.sum() counts matching rows and mask.mean() gives the fraction that match.
Two conditions: &, |, ~
The obvious way uses Python's and:
print(week[week["cups"] > 300 and week["weather"] == "sunny"])
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
Python's and, or and not need one True or False on each side. Each side here is a whole column of them. Should seven booleans count as True if any is true? If all are? That is ambiguous, so pandas refuses to guess.
pandas uses operators that work element by element (row by row) instead:
| Meaning | Python (single values) | pandas (whole columns) | Name |
|---|---|---|---|
| and | and |
& |
ampersand |
| or | or |
\| |
pipe / vertical bar |
| not | not |
~ |
tilde (flips True ↔ False) |
print(week[(week["cups"] > 300) & (week["weather"] == "sunny")])
print(week[(week["weather"] == "rain") | (week["cups"] > 400)])
print(week[~(week["weather"] == "sunny")])
day cups price weather
0 Mon 320 10 sunny
4 Fri 350 10 sunny
day cups price weather
1 Tue 280 10 rain
2 Wed 410 12 cold
6 Sun 450 12 rain
day cups price weather
1 Tue 280 10 rain
2 Wed 410 12 cold
5 Sat 390 12 cold
6 Sun 450 12 rain
The parentheses rule
Every operator has a precedence: a priority deciding what Python works out first (like × before + in maths). &, | and ~ have higher precedence than >, == and the other comparisons. So
print(week[week["cups"] > 300 & week["weather"] == "sunny"])
is read as week["cups"] > (300 & week["weather"]) == "sunny", which asks pandas to and the number 300 with a text column:
pyarrow.lib.ArrowNotImplementedError: Function 'and_kleene' has no kernel matching input types (int64, large_string)
The above exception was the direct cause of the following exception:
TypeError: operation 'rand_' not supported for dtype 'str' with object of type <class 'int'>
(pandas 3 stores text with the pyarrow library, so the first line comes from pyarrow; rand_ is pandas' internal name for & with the number on the left.) With number columns on both sides (for example week["cups"] > 300 & week["price"] == 10) you get the "truth value of a Series is ambiguous" ValueError instead: Python reads a > b == c as a chained comparison, (a > b) and (b == c), which hides an and. Either way, the cure is the same.
Rule: wrap every condition in its own round brackets: (cond1) & (cond2).
Shortcuts: isin, between, query()
print(week[week["day"].isin(["Sat", "Sun"])])
print(week[week["cups"].between(300, 350)])
print(week.query("cups > 300 and weather == 'sunny'"))
day cups price weather
5 Sat 390 12 cold
6 Sun 450 12 rain
day cups price weather
0 Mon 320 10 sunny
3 Thu 300 10 sunny
4 Fri 350 10 sunny
day cups price weather
0 Mon 320 10 sunny
4 Fri 350 10 sunny
| Shortcut | Meaning | Same as |
|---|---|---|
s.isin([a, b, c]) |
value is in the list (a guest list) | (s == a) \| (s == b) \| (s == c) |
s.between(lo, hi) |
lo <= value <= hi; both ends included by default (inclusive="both") |
(s >= lo) & (s <= hi) |
df.query("...") |
the condition written as a string | the &/\|/~ version |
Inside query():
- columns are named directly (no
df[...]); - you may write
and,or,not(or&,|,~); - text values need their own quotes:
weather == 'sunny'; - a Python variable is referred to with
@:query("maths > @limit").
~s.isin([...]) means "not in the list".
Text filters: str.contains
Cashiers type names differently: Masala Chai, Ginger chai. == "chai" matches neither, so we search inside the text. The .str accessor is a dot-name that unlocks text methods for a whole column (an accessor groups related methods behind one name). str.contains("x") gives True where the text contains x.
import sys; sys.tracebacklimit = 0
import pandas as pd
orders = pd.DataFrame({
"item": ["Masala Chai", "Coffee", "Ginger chai",
"Samosa", None, "Iced Tea"],
"amt": [20, 40, None, 45, 30, 60],
})
print(orders[orders["item"].str.contains("chai")])
print(orders[orders["item"].str.contains("chai", case=False)])
item amt
2 Ginger chai NaN
item amt
0 Masala Chai 20.0
2 Ginger chai NaN
- Matching is case-sensitive:
Chai≠chai.case=Falseignores capitals. - The pattern is a regular expression by default (episode 21), so
"chai|tea"means "chai or tea". Passregex=Falseto search for the literal text. - In pandas 3, a missing item in a
strcolumn simply counts as "no match". In anobjectcolumn (mixed values), a missing value gives NA in the mask, and boolean indexing then fails:
s = pd.Series(["Masala Chai", None, 5], dtype=object)
print(s[s.str.contains("chai", case=False)])
ValueError: Cannot mask with non-boolean array containing NA / NaN values
Fix: s.str.contains("chai", case=False, na=False) treats missing as no match:
0 Masala Chai
dtype: object
Filtering missing values: isna / notna, never == None
print(orders[orders["amt"] == None])
print(float("nan") == float("nan"))
Empty DataFrame
Columns: [item, amt]
Index: []
False
No error, and no rows, although Ginger chai's amount is missing. A missing value (NaN) is not equal to anything, not even itself: "unknown" can't be compared. So == can never find it. Use:
isna()("is not available"):Truewhere the value is missing (isnull()is the same thing);notna():Truewhere the value is present (notnull()is the same).
print(orders[orders["amt"].isna()])
print(orders[orders["amt"].notna() & orders["item"].notna()])
item amt
2 Ginger chai NaN
item amt
0 Masala Chai 20.0
1 Coffee 40.0
3 Samosa 45.0
5 Iced Tea 60.0
Missing values get a whole episode (19); for now: find them with isna, never with ==.
Masks inside loc
A mask works in loc's row slot, so you can filter rows and pick columns at once, and also change the matching rows in one step (episode 14's rule):
orders.loc[orders["amt"] >= 40, "item"] # filter + pick a column
marks.loc[marks["maths"].isna(), "maths"] = 0 # set values on matching rows
Mini project: a cleanup list for the accountant
missing = orders["amt"].isna() | orders["item"].isna()
print(orders[missing])
tea = orders["item"].str.contains("chai|tea", case=False)
print(orders[tea & orders["amt"].notna()])
print(orders.loc[orders["amt"] >= 40, "item"])
item amt
2 Ginger chai NaN
4 NaN 30.0
item amt
0 Masala Chai 20.0
5 Iced Tea 60.0
1 Coffee
3 Samosa
5 Iced Tea
Name: item, dtype: str
- Rows needing a fix: anything missing in either column (
|). - Tea-type orders that have an amount: a regex "or" inside
str.contains, combined withnotna(). - Items of orders worth ₹40 or more:
loc[mask, "item"].
Masks can be saved in variables with meaningful names (missing, tea) and combined later. That makes long filters readable.
Common mistakes
| Code | Real error / symptom | Why | Fix |
|---|---|---|---|
week[c1 and c2] |
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). |
and/or/not need one True/False |
week[(c1) & (c2)] |
week[week["cups"] > 300 & week["weather"] == "sunny"] |
TypeError: operation 'rand_' not supported for dtype 'str' with object of type <class 'int'> |
& runs before > and == |
Brackets around each condition |
orders[orders["amt"] == None] |
(no error: Empty DataFrame) |
NaN is never equal to anything | orders["amt"].isna() |
str.contains("chai") |
(no error: misses Masala Chai) |
Case-sensitive | case=False |
str.contains on an object column with None |
ValueError: Cannot mask with non-boolean array containing NA / NaN values |
Missing gives NA in the mask | na=False |
between(300, 350) expecting 300 excluded |
(no error: 300 and 350 kept) | Both ends included by default | inclusive="neither" / "left" / "right" |
| Loops over rows to filter | (no error: very slow) | One value at a time | A mask: df[df["col"] > x] |
Practice
All practice questions use this table:
import pandas as pd
marks = pd.DataFrame({
"name": ["Asha", "Ravi", "Meena", "Kabir", "Zoya", "Dev"],
"maths": [81, 67, 92, 74, 88, None],
"city": ["Pune", "Agra", "Pune", "Delhi", "Agra", "pune"],
})
1. Students with maths 80 or more.
Solution
print(marks[marks["maths"] >= 80])
name maths city
0 Asha 81.0 Pune
2 Meena 92.0 Pune
4 Zoya 88.0 Agra
Dev's missing mark is not >= 80, so he's left out (comparisons with NaN are always False).
2. Students from Agra or with maths above 90. Then students from neither Pune nor Agra (exact spelling).
Solution
print(marks[(marks["city"] == "Agra") | (marks["maths"] > 90)])
print(marks[~marks["city"].isin(["Pune", "Agra"])])
name maths city
1 Ravi 67.0 Agra
2 Meena 92.0 Pune
4 Zoya 88.0 Agra
name maths city
3 Kabir 74.0 Delhi
5 Dev NaN pune
Dev's city is a small-letter pune, so isin doesn't match it: data cleaning (episode 21) would standardise it.
3. Maths between 70 and 85; then, with query, maths below 80 and city not Delhi.
Solution
print(marks[marks["maths"].between(70, 85)])
print(marks.query("maths < 80 and city != 'Delhi'"))
name maths city
0 Asha 81.0 Pune
3 Kabir 74.0 Delhi
name maths city
1 Ravi 67.0 Agra
4. Everyone from Pune whatever the capitals; who has no maths mark; how many have one.
Solution
print(marks[marks["city"].str.contains("pune", case=False)])
print(marks[marks["maths"].isna()])
print(len(marks[marks["maths"].notna()]))
name maths city
0 Asha 81.0 Pune
2 Meena 92.0 Pune
5 Dev NaN pune
name maths city
5 Dev NaN pune
5
5. Use a Python variable limit = 85 inside query. Then set missing maths marks to 0 in one step.
Solution
limit = 85
print(marks.query("maths > @limit"))
marks.loc[marks["maths"].isna(), "maths"] = 0
print(marks)
name maths city
2 Meena 92.0 Pune
4 Zoya 88.0 Agra
name maths city
0 Asha 81.0 Pune
1 Ravi 67.0 Agra
2 Meena 92.0 Pune
3 Kabir 74.0 Delhi
4 Zoya 88.0 Agra
5 Dev 0.0 pune
6. How many students scored above 80, and what fraction is that?
Solution
mask = marks["maths"] > 80
print(mask.sum(), mask.mean())
3 0.5
True counts as 1: 3 of 6 rows.
Quick quiz
# cups: 320 280 410 300 350 390 450
print(len(week[(week["cups"] >= 300)
& ~(week["cups"] > 400)]))
A. 4 B. 3 C. 5 D. 2
Answer
A. At least 300 and not above 400: 320, 300, 350, 390 → 4 rows. 280 is too small; 410 and 450 are flipped out by ~.
Interview questions
- What is boolean indexing? Selecting rows with a boolean Series aligned to the index:
df[mask]keeps rows where the mask is True. - Why can't you use
and/orbetween pandas conditions? They need a single truth value; a Series has many, so pandas raises "The truth value of a Series is ambiguous". Use&,|,~. - Why are parentheses required around each condition?
&/|bind tighter than comparison operators, so without brackets Python evaluates300 & df["col"]first. isinvs multiple==with|? Same result;isinis shorter and faster for many values.- Is
betweeninclusive? Yes, both ends by default; change it withinclusive="neither","left"or"right". - When would you use
query()? For readable conditions on many columns, or when building filters from variables with@var. - How do you filter rows with missing values?
df[df["col"].isna()]; for "any column missing",df[df.isna().any(axis=1)]. Never== Noneor== np.nan. - Why is
np.nan == np.nanFalse? NaN represents an unknown value; by the floating-point standard it compares unequal to everything, including itself. - How do you filter text case-insensitively?
df["col"].str.contains("x", case=False); addna=Falsefor columns that may hold missing values. - How do you change values only on rows that match a condition?
df.loc[mask, "col"] = valuein a single step.
Glossary
| Term | Meaning | Example |
|---|---|---|
| Filtering | Keeping only the rows that match a condition | only rainy days |
| Condition | An expression that is True or False | cups > 350 |
| Boolean | A value that is only True or False | True |
| Boolean mask | A Series of True/False, one per row | week["cups"] > 350 |
| Boolean indexing | Selecting rows with a mask | week[mask] |
| Vectorised | Working on a whole column at once in fast code, not row by row | df["a"] > 5 |
| Comparison operators | >, >=, <, <=, ==, != |
price != 10 |
& (ampersand) |
Element-wise and | (c1) & (c2) |
\| (pipe) |
Element-wise or | (c1) \| (c2) |
~ (tilde) |
Element-wise not: flips True and False | ~(c1) |
| Element-wise | Applied to each pair of values, row by row | & on two masks |
| Ambiguous truth value | A whole Series can't be one True/False | the and ValueError |
| Precedence | The order operators are worked out | & before > |
| Parentheses rule | Wrap every condition in ( ) |
(a > 1) & (b < 2) |
isin |
True where the value is in a list | day.isin(["Sat", "Sun"]) |
between |
True where lo <= value <= hi |
cups.between(300, 350) |
| Inclusive | The end values count as inside the range | 300 and 350 kept |
query() |
Filter with the condition written as a string | df.query("cups > 300") |
@variable |
Use a Python variable inside query |
query("maths > @limit") |
| Accessor | A dot-name that groups related methods | .str, .dt |
.str.contains |
True where the text contains a pattern | item.str.contains("chai") |
| Case-sensitive | Capital and small letters count as different | Chai ≠ chai |
case=False |
Ignore capitals when matching | contains("chai", case=False) |
| Regular expression (regex) | A text pattern language (episode 21); \| means or |
"chai\|tea" |
na=False |
Treat missing text as "no match" in str.contains |
for object columns |
| NaN / missing value | A value that isn't there; equal to nothing, not even itself | float("nan") == float("nan") → False |
isna / isnull |
True where the value is missing | amt.isna() |
notna / notnull |
True where the value is present | amt.notna() |
loc[mask, col] |
Filter rows and pick a column (or set values) in one step | df.loc[df["amt"] >= 40, "item"] |
Summary
- Don't loop over rows to filter: a condition on a column gives a boolean mask in one vectorised step, and
df[mask]keeps the True rows (boolean indexing). - Combine conditions with
&(and),|(or),~(not), neverand/or/not, and wrap each condition in brackets because&binds tighter than comparisons. - Shortcuts:
isin(value in a list),between(inclusive range),query("...")(readable string,@varfor variables). - Text:
str.contains("x", case=False); the pattern is a regex ("a|b"= a or b);na=Falsefor object columns with missing values. - Missing values:
isna()/notna().== Nonesilently finds nothing, because NaN equals nothing. - A mask also works in
loc[mask, "col"], to pick columns or to change matching rows in one step. - Next episode: pandas 6: sorting, ranking and creating columns.