Mathew K AnalyticsLesson 9 · Pandas Full Course
Filter Pandas DataFrames with Conditions: Boolean Indexing Made Easy | Pandas Course #9
In this lesson you'll be able to: Build a True/False mask and use it to keep matching rows Combine conditions with &, | and ~ Match lists, ranges and text…
- CoursePandas Full Course
- Lesson9 of 65
- FormatJupyter notebook · 42 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 09: Conditional Selection and Boolean Indexing#
In this lesson you'll be able to:
- Build a True/False mask and use it to keep matching rows
- Combine conditions with &, | and ~
- Match lists, ranges and text with isin, between and str.contains
- Handle missing values inside conditions
- Update only the matching rows with loc Dataset: New York taxi trips, March 2019 (data/pandas/taxis.csv)
Warm-up: select by label and position#
Lesson 08 selected rows and columns with loc, iloc and nlargest.
import pandas as pd
taxis = pd.read_csv("data/pandas/taxis.csv")
print(taxis.shape)
taxis.loc[:2, ["pickup_zone", "dropoff_zone", "fare"]]
cols = ["pickup_zone", "dropoff_zone", "distance", "fare"]
taxis.nlargest(3, "fare")[cols]
Keep rows that pass one test#
Problem: select rows by what they contain, not where they sit.
no_tip = taxis["tip"] == 0
no_tip.head()
print(no_tip.sum())
print(no_tip.mean().round(3))
zero = taxis[no_tip]
zero[["payment", "fare", "tip"]].head()
zero["payment"].value_counts(dropna=False)
Quick check: how many trips were longer than 10 miles?
long = taxis["distance"] > 10
print(long.sum())
Common mistake: comparing a number column with text
taxis[taxis["passengers"] > "2"]
crowded = taxis[taxis["passengers"] > 2]
print(len(crowded))
print((taxis["fare"] > 0).all())
print((taxis["distance"] == 0).any())
print((taxis["distance"] == 0).sum())
Combine conditions with &, | and ~#
Problem: real questions have more than one rule.
big_fare = taxis["fare"] > 50
big_no_tip = big_fare & no_tip
print(big_no_tip.sum())
taxis[big_no_tip][["payment", "fare", "pickup_zone"]].head()
group_or_long = (taxis["passengers"] >= 4) | (taxis["distance"] > 15)
print(group_or_long.sum())
not_yellow = ~(taxis["color"] == "yellow")
print(not_yellow.sum())
taxis.loc[not_yellow, "color"].value_counts()
Common mistake: using Python's
andinstead of&
taxis[(taxis["fare"] > 50) and (taxis["tip"] == 0)]
fixed = taxis[(taxis["fare"] > 50) & (taxis["tip"] == 0)]
print(len(fixed))
Common mistake: forgetting the brackets around each test
taxis[taxis["fare"] > 50 & taxis["tip"] == 0]
Quick check: the ampersand runs before > and ==. Which part did pandas try to calculate first?
rule = (taxis["fare"] > 50) & (taxis["tip"] == 0)
print(rule.sum())
Your turn#
Of the yellow-cab rides over 20 miles, how many had no tip?
far = (taxis["color"] == "yellow") & (taxis["distance"] > 20)
print(far.sum())
print((far & no_tip).sum())
Match lists, ranges and text#
Problem: avoid long chains of == and | for common tests.
outer = ["Queens", "Brooklyn", "Bronx"]
from_outer = taxis["pickup_borough"].isin(outer)
print(from_outer.sum())
short = taxis["distance"].between(1, 2)
print(short.sum())
print(taxis.loc[short, "fare"].median())
zone = taxis["pickup_zone"]
airport_pick = zone.str.contains("Airport", na=False)
print(airport_pick.sum())
zone[airport_pick].value_counts()
dz = taxis["dropoff_zone"]
print(dz.str.contains("park", na=False).sum())
print(dz.str.contains("park", case=False, na=False).sum())
Common mistake: passing a single string to isin
taxis[taxis["payment"].isin("cash")]
cash = taxis[taxis["payment"].isin(["cash"])]
print(len(cash))
Your turn#
Count the green-cab rides from Brooklyn or the Bronx with a fare between 10 and 20.
green = taxis["color"] == "green"
place = taxis["pickup_borough"].isin(["Brooklyn", "Bronx"])
price = taxis["fare"].between(10, 20)
print((green & place & price).sum())
Handle missing values in conditions#
Problem: a missing value is never equal to anything.
missing_pay = taxis["payment"].isna()
print(missing_pay.sum())
taxis.loc[missing_pay, ["fare", "tip", "payment"]].head(3)
Quick check: how many rows does payment == None find?
print((taxis["payment"] == None).sum())
not_cash = taxis["payment"] != "cash"
print(not_cash.sum())
print((taxis["payment"] == "credit card").sum())
card_only = not_cash & taxis["payment"].notna()
print(card_only.sum())
pay = taxis["payment"].fillna("unknown")
pay.value_counts()
Your turn#
How many rides are missing the pickup zone, the dropoff zone, or both? And how many are missing both?
no_pick = taxis["pickup_zone"].isna()
no_drop = taxis["dropoff_zone"].isna()
print((no_pick | no_drop).sum())
print((no_pick & no_drop).sum())
Select columns and update rows with loc#
Problem: pick columns for the matching rows, or change just those rows.
rows = taxis["tip"] > 10
taxis.loc[rows, ["fare", "tip", "dropoff_zone"]].head()
taxis["tip_pct"] = (taxis["tip"] / taxis["fare"] * 100).round(1)
taxis["tip_known"] = True
taxis.loc[taxis["payment"] != "credit card", "tip_known"] = False
taxis["tip_known"].value_counts()
known = taxis[taxis["tip_known"]]
print(known["tip_pct"].median())
print(taxis["tip_pct"].median())
Quick check: among card rides, who tips a bigger percentage: green-cab or yellow-cab riders?
is_green = known["color"] == "green"
print(known.loc[is_green, "tip_pct"].median())
print(known.loc[~is_green, "tip_pct"].median())
Your turn#
Add a column long_trip that is True for trips over 10 miles, using loc. Then count it.
taxis["long_trip"] = False
taxis.loc[taxis["distance"] > 10, "long_trip"] = True
print(taxis["long_trip"].sum())
Mini-project: are airport runs worth it?#
Question: how do fares and tips compare for trips to or from an airport?
Plan: build an airport mask, compare fares, compare card tips, conclude.
pick = taxis["pickup_zone"].str.contains("Airport", na=False)
drop = taxis["dropoff_zone"].str.contains("Airport", na=False)
airport = pick | drop
print(airport.sum(), airport.mean().round(3))
cols = ["distance", "fare", "total"]
print(taxis.loc[airport, cols].mean().round(2))
print(taxis.loc[~airport, cols].mean().round(2))
print(taxis.loc[airport, "pickup_borough"].value_counts())
long = taxis["distance"] > 10
print(airport[long].mean().round(2))
card = taxis["tip_known"]
air_tip = taxis.loc[card & airport, ["tip", "tip_pct"]].median()
city_tip = taxis.loc[card & ~airport, ["tip", "tip_pct"]].median()
print(pd.DataFrame({"airport": air_tip, "city": city_tip}))
import matplotlib.pyplot as plt
avg = pd.Series({"Airport": taxis.loc[airport, "total"].mean(),
"City": taxis.loc[~airport, "total"].mean()})
ax = avg.plot(kind="bar", rot=0, figsize=(6, 4))
ax.set_title("Average total paid per taxi ride, March 2019")
ax.set_xlabel("Trip type")
ax.set_ylabel("Average total (dollars)")
plt.show()
ratio = avg["Airport"] / avg["City"]
print(f"Airport rides: {airport.sum()}, worth {ratio:.1f}x a city "
"ride on average")
Recap#
- A comparison makes a True/False mask;
df[mask]keeps the True rows - Combine masks with &, | and ~, with brackets round each test
- isin, between and str.contains replace long chains of tests
- NaN never equals anything: use isna() and notna()
df.loc[mask, cols]selects, anddf.loc[mask, col] = xupdates
Challenges#
- Find the share of card rides with no tip, in each pickup borough.
- List zero-distance rides that still charged over 20 dollars.
- Count green-cab rides whose dropoff zone contains 'Park'.
What's next#
Lesson 10: Filtering Rows and Columns using Query and isin Write the same filters as readable text with query().
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
22 minHands-On With Pandas: Creating and Exploring DataFrames
17 minMastering Conditional Selection & Boolean Indexing in Pandas for Data Analysis
18 min