Combining Conditions with &, | and ~
Python's and, or and not can't combine whole columns of True and False, so pandas uses & (and), | (or) and ~ (not), with parentheses around each comparison; isin, between and str.contains replace long chains of conditions, and query writes a filter as text.
Apprentice · Python · Updated
After this lesson you can
- Combine conditions with & (and), the or operator and ~ (not), with parentheses around each comparison
- Filter rows with isin, between and str.contains instead of long chains of conditions
Key takeaways
- Python's and, or and not stop with "The truth value of a Series is ambiguous". On columns, use & (read "and"), the bar for "or" and ~ (read "not").
- Put parentheses around each comparison, as in (df["price"] < 200) & (df["stock"] > 0). Without them, Python joins the wrong pieces first and pandas stops with an error.
- isin(["boots", "jackets"]) matches any value in a list, so it replaces a chain of "or" conditions.
- between(50, 100) keeps values from 50 to 100, both ends included, and str.contains("Ultra") keeps text that has "Ultra" in it.
Real questions almost never have only one condition. A camper wants a tent under $200 that's actually in stock. A teacher wants the students in grade 7 or grade 8 who scored below 70. A clinic wants every patient who is not up to date on a vaccine. Every one of those is two or three conditions joined together, and the way pandas joins them has a couple of traps worth knowing before they catch you.
The example is Trailhead Outfitters' catalog, products.csv: 300 outdoor gear products with their product name, category (tents, backpacks, boots, jackets, stoves or sleeping bags), price, stock, on_sale (True or False) and more. A comparison such as products["price"] < 200 gives a True or False mask, one answer per row, and products[mask] keeps the True rows (Filtering Rows with a Condition shows how).
Python's and doesn't work on columns
The camper's question in plain words is "category is tents and price is less than 200". Python's own and seems like the way to write it:
import pandas as pd
products = pd.read_csv("products.csv")
products[products["category"] == "tents" and products["price"] < 200]
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.item(), a.any() or a.all().
That's the last line of the error. Python's and asks one question about each side, "is this True?", and a column of 300 answers can't give one yes or no. pandas needs to combine the two masks row by row, like two checkboxes on a shopping site that must both be ticked.
& for and, with parentheses
The row-by-row "and" is &, read "and". Saving each mask with a name shows what it does:
tents = products["category"] == "tents"
under_200 = products["price"] < 200
print("tents:", tents.sum())
print("under $200:", under_200.sum())
print("tents under $200:", (tents & under_200).sum())
tents: 48
under $200: 174
tents under $200: 11
tents & under_200 is True only in rows where both are True: 11 of the 48 tents. Written in one line, each comparison goes in its own parentheses:
columns = ["product", "category", "price", "stock"]
products[(products["category"] == "tents") & (products["price"] < 200)][columns].head(3)
product category price stock
0 Mesa Glide Tent tents 182.99 18
3 Cascade Trek Tent tents 176.99 8
22 Basalt Pro Tent tents 115.00 0
The parentheses aren't decoration. Python works out & before == and <, so without them it tries to join "tents" and the price column first. products[products["category"] == "tents" & products["price"] < 200] stops with TypeError: Cannot perform 'rand_' with a dtyped [float64] array and scalar of type [bool]. When you see an error like that, check your parentheses.
| for or, ~ for not
|, the vertical bar, is read "or": True where either side is True. ~, the tilde, is read "not": it flips every True to False and every False to True.
boots_or_jackets = (products["category"] == "boots") | (products["category"] == "jackets")
in_stock = ~(products["stock"] == 0)
print("boots or jackets:", boots_or_jackets.sum())
print("in stock:", in_stock.sum())
print("not on sale:", (~products["on_sale"]).sum())
boots or jackets: 112
in stock: 274
not on sale: 228
The camper's full question, a tent under $200 that's in stock, is tents & under_200 & in_stock. Row 22 above, the Basalt Pro Tent with a stock of 0, drops out.
Shortcuts: isin, between and str.contains
Some conditions come up so often that pandas has a method for them:
isin([...])isTruewhen the value is any one of a list, so "boots or jackets" needs no|chain.between(50, 100)keeps values from 50 to 100, both ends included.str.contains("Ultra")keeps text that has "Ultra" anywhere in it.
print("boots or jackets:", products["category"].isin(["boots", "jackets"]).sum())
print("from $50 to $100:", products["price"].between(50, 100).sum())
print("Ultra products:", products["product"].str.contains("Ultra").sum())
boots or jackets: 112
from $50 to $100: 59
Ultra products: 48
They combine with & like any mask. Method calls don't need parentheses of their own, but a comparison still does. Here are gift ideas: stoves or jackets, $50 to $100, in stock:
gifts = products[
products["category"].isin(["stoves", "jackets"])
& products["price"].between(50, 100)
& (products["stock"] > 0)
]
gifts[columns].head(3)
product category price stock
7 Willow Trek Stove stoves 90.00 24
12 Ridge Trek Stove stoves 84.99 32
33 Basalt Lite Stove stoves 66.99 54
You may also meet query, which takes the whole filter as text in plain Python words: products.query("category == 'tents' and price < 200") keeps the same 11 tents. It's handy for short filters; the masks above work everywhere, so this course writes them.
Check yourself
2 questions to try on the lesson page.
So what
Joining conditions with &, | and ~, each comparison in its parentheses, turns a question with several parts into one mask, and isin and between keep that mask short enough to read aloud.