Lesson 20 · Mastering Pandas

Master Filtering Rows and Columns in Pandas Using Query and isin for Data Analysis

Welcome! Today we will learn intermediate techniques for slicing and dicing data in pandas. We will use the restaurant Tips dataset to explore how to filter…

⬇ Download notebook Open in Colab ↗

CourseMastering Pandas
Lesson20 of 44
Video17 min
FormatJupyter notebook · 15 code cells

seaborn NumPy

Data

No separate download needed — the notebook creates or downloads everything it uses.

📓 Full notebook

Download .ipynb

Filtering Rows and Columns in Pandas: Query and isin#

Welcome! Today we will learn intermediate techniques for slicing and dicing data in pandas.

We will use the restaurant Tips dataset to explore how to filter rows and columns using the query() method and the isin() function.

Let us get started!

import warnings
warnings.filterwarnings("ignore")

# Data setup (Restaurant Tips Dataset)
import seaborn as sns
import numpy as np
np.random.seed(42)
df = sns.load_dataset('tips')
print(df.shape)
print(df.head(3))

(244, 7)
   total_bill   tip     sex smoker  day    time  size
0       16.99  1.01  Female     No  Sun  Dinner     2
1       10.34  1.66    Male     No  Sun  Dinner     3
2       21.01  3.50    Male     No  Sun  Dinner     3

Sneak peek at the columns#

Notice our dataset contains columns like: total_bill, tip, sex, smoker, day, time, and size.

We will practice filtering using these!

# Checking column names
print(df.columns.tolist())

['total_bill', 'tip', 'sex', 'smoker', 'day', 'time', 'size']

# Basic filtering using boolean indexing
high_tips = df[df['tip'] > 5]
print(high_tips.head(2))

    total_bill   tip   sex smoker  day    time  size
23       39.42  7.58  Male     No  Sat  Dinner     4
44       30.40  5.60  Male     No  Sun  Dinner     4

Using query() for readable filtering#

The query() method lets us filter with expressions closer to how we would write them in math or SQL.

Let us try it out!

# Filtering with query: tips greater than five
query_result = df.query('tip > 5')
print(query_result.head(2))

    total_bill   tip   sex smoker  day    time  size
23       39.42  7.58  Male     No  Sat  Dinner     4
44       30.40  5.60  Male     No  Sun  Dinner     4

# Filtering multiple conditions
dinner_high_tip = df.query('time == "Dinner" and tip > 5')
print(dinner_high_tip.head(2))

    total_bill   tip   sex smoker  day    time  size
23       39.42  7.58  Male     No  Sat  Dinner     4
44       30.40  5.60  Male     No  Sun  Dinner     4

Why use query?#

Query can be easier to read, especially with long conditions.

It avoids a lot of code clutter with brackets and ampersands (&).

# Filtering with variables in query (using @ syntax)
my_limit = 4.5
filtered = df.query('tip > @my_limit and smoker == "No"')
print(filtered.head(2))

    total_bill   tip     sex smoker  day    time  size
5        25.29  4.71    Male     No  Sun  Dinner     4
11       35.26  5.00  Female     No  Sun  Dinner     4

# Query for multiple values: Use in and not in
selected_days = ['Sat', 'Sun']
weekends = df.query('day in @selected_days')
print(weekends['day'].value_counts())

day
Sat     87
Sun     76
Thur     0
Fri      0
Name: count, dtype: int64

# The isin() function: filter with lists or sets
weekend_rows = df[df['day'].isin(['Sat', 'Sun'])]
print(weekend_rows.sample(3, random_state=42))

     total_bill   tip   sex smoker  day    time  size
208       24.27  2.03  Male    Yes  Sat  Dinner     2
173       31.85  3.18  Male    Yes  Sun  Dinner     2
189       23.10  4.00  Male    Yes  Sun  Dinner     3

# Negate with ~ : NOT in the list
not_weekend = df[~df['day'].isin(['Sat', 'Sun'])]
print(not_weekend['day'].unique())

['Thur', 'Fri']
Categories (4, object): ['Thur', 'Fri', 'Sat', 'Sun']

# Select a subset of columns by filtering column names
cols = ['total_bill', 'tip', 'day']
bills = df[cols]
print(bills.head())

   total_bill   tip  day
0       16.99  1.01  Sun
1       10.34  1.66  Sun
2       21.01  3.50  Sun
3       23.68  3.31  Sun
4       24.59  3.61  Sun

# Combining row and column filtering
lunch_bills = df.query('time == "Lunch"')[['total_bill', 'tip', 'sex']]
print(lunch_bills.head())

    total_bill   tip   sex
77       27.20  4.00  Male
78       22.76  3.00  Male
79       17.29  2.71  Male
80       19.44  3.00  Male
81       16.66  3.40  Male

Practice: Filter diners#

Try using query() to select all dinners (Dinner time) where the party size was three or more.

Practice makes perfect!

# Chaining: Filter + sort
big_tip_sorted = df.query('tip >= 5').sort_values('total_bill', ascending=False)
print(big_tip_sorted[['total_bill', 'tip']].head())

     total_bill    tip
170       50.81  10.00
212       48.33   9.00
59        48.27   6.73
156       48.17   5.00
197       43.11   5.00

# Find all unique values a column takes
print(df['day'].unique())

['Sun', 'Sat', 'Thur', 'Fri']
Categories (4, object): ['Thur', 'Fri', 'Sat', 'Sun']

# User input: filter by day (case-sensitive)
user_day = input("Type a day (Thur, Fri, Sat, Sun): ")
print(df[df['day'] == user_day].head())

    total_bill   tip     sex smoker  day    time  size
90       28.97  3.00    Male    Yes  Fri  Dinner     2
91       22.49  3.50    Male     No  Fri  Dinner     2
92        5.75  1.00  Female    Yes  Fri  Dinner     2
93       16.32  4.30  Female    Yes  Fri  Dinner     2
94       22.75  3.25  Female     No  Fri  Dinner     2

# Quick summary: group by day, mean tip
print(df.groupby('day')['tip'].mean().round(2))

day
Thur    2.77
Fri     2.73
Sat     2.99
Sun     3.26
Name: tip, dtype: float64

Recap: Filtering with query and isin#

We practiced filtering rows with readable, powerful tools. Query() allows us to write filter expressions like math or SQL. Isin() checks column values against lists or sets. Both make slicing our DataFrame easier.

Remember: practice and experimenting help you master filtering!

What's next?#

Ready to build more skills? Try combining filters with visualization or aggregation!

Watch our next video for advanced pandas tricks and follow along. Happy learning!

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.

☕ Buy me a coffee Other ways to help