isin, between and query
Overview
Three ways to say a condition that reads like the question.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Test membership of a set with
isin. - Test a range with
between, and know which ends it includes. - Write a condition as a sentence with
query. - Pick the form that reads closest to the question being asked.
Why This Matters
All three of these do things you could already do with masks. They matter because a filter you can read is a filter you can check, and a chain of six |s is not one. Choosing the clearest form is a correctness decision, not a style one.
Step 1: isin for a set of values
Testing against several values with a chain of == and | works and reads badly. isin takes the list.
The readable form
And it does not get longer as the list grows, which matters when the list comes from somewhere else.
import pandas as pd
df = pd.DataFrame({"subject": ["maths", "art", "computing"]})
print(df[df["subject"].isin(["maths", "computing"])])Negating it
"Everything except these" is ~ in front of the whole thing.
import pandas as pd
df = pd.DataFrame({"subject": ["maths", "art", "computing"]})
print(df[~df["subject"].isin(["art"])])Mini Practice #1: A list of subjects
Try It YourselfSelect the rows in maths or computing twice — once with | and once with isin — and compare which you would rather read.
Step 2: between for a range
between is inclusive at both ends by default, which is usually what a question means by "between 60 and 80".
Both ends included
One call instead of two comparisons and a pair of brackets.
import pandas as pd
df = pd.DataFrame({"score": [55, 60, 70, 80, 85]})
print(df[df["score"].between(60, 80)])When you meant something else
Pass inclusive to change it. Saying so explicitly is better than writing two comparisons and hoping the reader checks them.
import pandas as pd
df = pd.DataFrame({"score": [55, 60, 70, 80, 85]})
print(df[df["score"].between(60, 80, inclusive="neither")])Step 3: query for a condition that reads as a sentence
Inside query the string is parsed by pandas, so the ordinary words work and the brackets go away.
Plain and, plain or
Column names are bare, and a Python variable is reachable with an @ in front of it.
import pandas as pd
df = pd.DataFrame({"score": [92, 88, 60], "city": ["L", "Y", "L"]})
print(df.query("score > 80 and city == 'L'"))
mark = 85
print(df.query("score > @mark"))query is not faster in any way that matters at this size. The argument for it is that the filter reads like the question, which is what makes it checkable.
Mini Practice #2: The same filter, three ways
Try It YourselfSelect scores from 60 to 80 inclusive with a pair of comparisons, with between, and with query.
Step 4: When to Use It
Membership in a list
isin replaces a chain of == with | when the allowed values are a set.
df[df["team"].isin(["red", "blue"])]Ranges and readable conditions
between for inclusive ranges; query when the condition reads better as a sentence.
When Not to Use It
Do not reach for query when a column name has spaces or the condition uses outside variables you have not marked with @. A plain mask is clearer there.
End-of-Lesson Exercises
Exercise 1: Count the rows in a set of subjects
Write in_subjects(df, subjects) returning the number of rows whose subject is one of subjects.
df["subject"].isin(subjects) is the mask. Its sum is the count, and int() makes it a plain number.
Exercise 2: Names in a band
Write band_names(df, low, high) returning the names whose score is between low and high inclusive, in the order they appear. Say the range once rather than as two comparisons.
df["score"].between(low, high) is inclusive at both ends by default. Mask the table with it, then take the name column.