SyntaxPilot
Courses/Data Analytics with Python/Lesson 15

Data Analytics with Python · Lesson 15 of 90 · 12 min

pandas 5: filtering rows

boolean indexingmultiple conditions& (and)or operator (pipe)~ (not)parentheses ruleisinbetweenquery()str.contains filterfiltering on null values

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"] > 350 is a boolean mask: a Series of True/False with the same index as the table (dtype bool). Think of a stencil: holes (True) where paint goes through.
  • week[mask] keeps exactly the True rows. 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=False ignores capitals.
  • The pattern is a regular expression by default (episode 21), so "chai|tea" means "chai or tea". Pass regex=False to search for the literal text.
  • In pandas 3, a missing item in a str column simply counts as "no match". In an object column (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"): True where the value is missing (isnull() is the same thing);
  • notna(): True where 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
  1. Rows needing a fix: anything missing in either column (|).
  2. Tea-type orders that have an amount: a regex "or" inside str.contains, combined with notna().
  3. 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

  1. What is boolean indexing? Selecting rows with a boolean Series aligned to the index: df[mask] keeps rows where the mask is True.
  2. Why can't you use and/or between pandas conditions? They need a single truth value; a Series has many, so pandas raises "The truth value of a Series is ambiguous". Use &, |, ~.
  3. Why are parentheses required around each condition? &/| bind tighter than comparison operators, so without brackets Python evaluates 300 & df["col"] first.
  4. isin vs multiple == with |? Same result; isin is shorter and faster for many values.
  5. Is between inclusive? Yes, both ends by default; change it with inclusive="neither", "left" or "right".
  6. When would you use query()? For readable conditions on many columns, or when building filters from variables with @var.
  7. How do you filter rows with missing values? df[df["col"].isna()]; for "any column missing", df[df.isna().any(axis=1)]. Never == None or == np.nan.
  8. Why is np.nan == np.nan False? NaN represents an unknown value; by the floating-point standard it compares unequal to everything, including itself.
  9. How do you filter text case-insensitively? df["col"].str.contains("x", case=False); add na=False for columns that may hold missing values.
  10. How do you change values only on rows that match a condition? df.loc[mask, "col"] = value in 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), never and/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, @var for variables).
  • Text: str.contains("x", case=False); the pattern is a regex ("a|b" = a or b); na=False for object columns with missing values.
  • Missing values: isna() / notna(). == None silently 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.