Mathew K AnalyticsLesson 10 · Pandas Full Course
Pandas query() and isin(): Write Filters That Read Like English | Pandas Course #10
In this lesson you'll be able to: Write row filters as readable sentences with query Use Python variables inside a query with @ Match many values at once…
- CoursePandas Full Course
- Lesson10 of 65
- FormatJupyter notebook · 48 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
📓 Full notebook
Download .ipynbPandas 10: Filtering Rows and Columns using Query and isin#
In this lesson you'll be able to:
- Write row filters as readable sentences with query
- Use Python variables inside a query with @
- Match many values at once with isin, and flip it with ~
- Pick columns by name, pattern or type with filter
- Combine row and column filters to answer a real question Dataset: restaurant tips (data/pandas/tips.csv)
Warm-up: boolean masks from lesson 9#
Lesson 9 filtered rows with masks, & and |. Same tool, tips data.
import pandas as pd
df = pd.read_csv("data/pandas/tips.csv")
print(df.shape)
df.head()
mask = (df["time"] == "Dinner") & (df["tip"] > 5)
print(mask.sum())
df[mask].head(3)
Write filters as sentences with query#
Problem: masks get cluttered with brackets, df and &.
big_tips = df.query("tip > 5")
print(len(big_tips))
big_tips.head(3)
dinner_big = df.query("time == 'Dinner' and tip > 5")
print(len(dinner_big))
print(dinner_big.equals(df[mask]))
df.query("size >= 5 or total_bill > 45")[["total_bill", "size"]]
df.query("15 <= total_bill <= 20").shape
Common mistake: one equals sign inside query
df.query("size = 4")
df.query("size == 4").shape
Quick check: how many lunch bills came to more than $20?
lunch_20 = df.query("time == 'Lunch' and total_bill > 20")
print(len(lunch_20))
Your turn#
Find Saturday bills for parties of 3 or more that tipped under $3.
sat = df.query("day == 'Sat' and size >= 3 and tip < 3")
sat[["total_bill", "tip", "size"]]
Use Python variables inside query with @#
Problem: thresholds change, so don't hard-code them.
limit = 4.5
generous = df.query("tip > @limit and smoker == 'No'")
print(len(generous))
avg_tip = df["tip"].mean()
print(round(avg_tip, 2))
above = df.query("tip > @avg_tip")
print(len(above), len(df))
Common mistake: forgetting the @
df.query("tip > avg_tip")
df.query("tip > @avg_tip").shape
rate = df.query("tip / total_bill > 0.25")
rate[["total_bill", "tip", "size"]]
per_head = df.query("tip > size * 1.5")
print(len(per_head))
per_head["size"].value_counts()
Your turn#
Store 0.10 in a variable, then find Sunday bills that tipped less than that share.
low = 0.10
stingy = df.query("day == 'Sun' and tip / total_bill < @low")
stingy[["total_bill", "tip", "size"]]
Match many values at once with isin#
Problem: "day is Sat or Sun or ..." gets long fast.
print(df["day"].unique())
weekend = df["day"].isin(["Sat", "Sun"])
print(weekend.sum())
df[weekend]["day"].value_counts()
weekdays = df[~weekend]
weekdays["day"].value_counts()
Common mistake: comparing a column to a list with ==
df[df["day"] == ["Sat", "Sun"]]
df[df["day"].isin(["Sat", "Sun"])].shape
Common mistake: passing a single string to isin
df[df["day"].isin("Sat")]
df[df["day"].isin(["Sat"])].shape
quiet = df["day"].value_counts().index[-2:]
print(list(quiet))
print(df["day"].isin(quiet).sum())
party = df["size"].isin([5, 6])
df[party][["total_bill", "tip", "size", "day"]]
Quick check: what share of bills were weekend dinners?
wk_dinner = weekend & (df["time"] == "Dinner")
print(round(wk_dinner.mean(), 3))
Use in, not in and backticks inside query#
Problem: bring isin and odd column names into query strings.
df.query("day in ['Sat', 'Sun']").shape
slow_days = ["Thur", "Fri"]
rest = df.query("day not in @slow_days and size > 2")
rest["day"].value_counts()
menu = df.rename(columns={"total_bill": "total bill"})
print(menu.columns.tolist())
Common mistake: a column name with a space inside query
menu.query("total bill > 45")
menu.query("`total bill` > 45")
Your turn#
With query: Fri or Sat bills, a party size other than 2, and a tip of at least $4.
busy = df.query("day in ['Fri', 'Sat'] and size != 2 "
"and tip >= 4")
busy[["day", "size", "tip", "total_bill"]]
Filter columns by name, pattern or type#
Problem: keep just the columns you need, without typing them all.
df.filter(items=["day", "time", "tip"]).head(3)
df.filter(like="ti").head(3)
df.filter(regex="^t").head(3)
nums = df.select_dtypes(include="number")
print(nums.columns.tolist())
lunch = df.query("time == 'Lunch'")
lunch[["total_bill", "tip", "sex"]].head()
fri_lunch = df["day"].isin(["Fri"]) & (df["time"] == "Lunch")
df.loc[fri_lunch, ["total_bill", "tip", "size"]]
top = df.query("tip >= 5").sort_values("total_bill",
ascending=False)
top[["total_bill", "tip", "day"]].head()
Quick check: which columns have an underscore in their name?
df.filter(like="_").columns.tolist()
Your turn#
Keep only the columns whose names end with the letter e.
df.filter(regex="e$").head(3)
Mini-project: do bigger parties tip a smaller share?#
Plan: add tip %, filter small and large parties, compare, check.
df["tip_pct"] = (df["tip"] / df["total_bill"] * 100).round(1)
small = df[df["size"].isin([1, 2])]
large = df.query("size >= 4")
print(len(small), len(large))
print(round(small["tip_pct"].mean(), 1))
print(round(large["tip_pct"].mean(), 1))
print(small["tip_pct"].median(), large["tip_pct"].median())
wk = "day in ['Sat', 'Sun'] and time == 'Dinner'"
s_wk = small.query(wk)["tip_pct"].median()
l_wk = large.query(wk)["tip_pct"].median()
print(s_wk, l_wk)
s_gen = len(small.query("tip_pct >= 20"))
l_gen = len(large.query("tip_pct >= 20"))
print(s_gen, round(s_gen / len(small), 3))
print(l_gen, round(l_gen / len(large), 3))
import matplotlib.pyplot as plt
meds = df.groupby("size")["tip_pct"].median()
ax = meds.plot(kind="bar", rot=0, figsize=(7, 4))
ax.set_title("Median tip by party size")
ax.set_xlabel("Party size")
ax.set_ylabel("Tip, % of bill")
plt.show()
Recap#
- query writes row filters as readable strings: and, or, ranges
- @ brings Python variables into a query
- isin matches a list of values; ~ flips it
- in, not in and backticks work inside query strings
- filter and select_dtypes choose columns by name, pattern or type
Challenges#
- Find dinners where a female smoker paid over $30, with query.
- Keep Thur and Fri lunches with isin, then their bill and tip.
- Store the median bill in a variable and query bills above it.
What's next#
Lesson 11: Setting, Resetting, and Sorting Indexes Turn a column into row labels, and put them in order.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
26 minData Mining with Python: pandas & scikit-learn Crash Course
17 minMaster Filtering Rows and Columns in Pandas Using Query and isin for Data Analysis
59 minPython Debugging for Beginners: Read Errors, Fix Bugs, Write Tests (Beginner Python Course, Part 5)
57 min