lesson 208 pandas Foundations · advanced

Filtering rows

A comparison on a column gives a True/False Series; putting it inside df[...] keeps the rows where it is True. isin, between and the .str methods build tests for lists, ranges and text.

You'll be able to

Filter rows with df[mask], where the mask is a comparison on a column
Combine conditions with &, | and ~, wrapping each comparison in parentheses
Build tests with isin, between and .str methods such as str.endswith

The idea

Filtering a DataFrame works like masking a NumPy array: orders["total"] > 50 is a Series of True/False, one per row, and orders[that_series] keeps the rows where it is True, with their original labels. Tests combine with & (and), | (or) and ~ (not); like in NumPy, each comparison needs its own parentheses.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Delivered web orders over $50

import pandas as pd

orders = pd.DataFrame([
    {"order_id": "O100282", "channel": "web", "status": "delivered", "total": 35.84},
    {"order_id": "O100376", "channel": "web", "status": "returned", "total": 91.47},
    {"order_id": "O101219", "channel": "web", "status": "delivered", "total": 246.6},
    {"order_id": "O101723", "channel": "phone", "status": "delivered", "total": 54.55},
    {"order_id": "O103199", "channel": "app", "status": "delivered", "total": 64.17},
])
big = orders["total"] > 50
print(big.tolist())
web_big = orders[(orders["channel"] == "web") & (orders["status"] == "delivered") & big]
print(web_big["order_id"].tolist())
not_web = orders[~(orders["channel"] == "web")]
print(not_web["order_id"].tolist(), not_web.index.tolist())

example-2.py · `and` between two Series

import pandas as pd

orders = pd.DataFrame([{"status": "returned", "total": 91.47}, {"status": "delivered", "total": 35.84}])
print(orders[orders["status"] == "returned" and orders["total"] > 50])

example-3.py · Lists, ranges and text tests

import pandas as pd

customers = pd.DataFrame([
    {"customer_id": "C00038", "email": "nora_garcia@fastmail.com", "state": "NY", "orders": 3},
    {"customer_id": "C00065", "email": "irishaddad32@gmail.com", "state": "CA", "orders": 1},
    {"customer_id": "C00071", "email": "arjun_mensah@gmail.com", "state": "TX", "orders": 6},
    {"customer_id": "C00077", "email": "jamal.walker62@icloud.com", "state": "TX", "orders": 2},
])
west_or_south = customers[customers["state"].isin(["CA", "TX"])]
print(west_or_south["customer_id"].tolist())
gmail = customers["email"].str.endswith("@gmail.com")
print(customers[gmail & customers["orders"].between(2, 6)]["customer_id"].tolist())

When to use it: every *which rows?* question. Mistakes to avoid: and/or instead of &/|; a missing parenthesis (orders["total"] > 50 & big compares against 50 & big); == on a list (use isin); and forgetting that between includes both ends.

Review

df[mask] keeps rows where a True/False Series is True; the kept rows keep their labels.
Join masks with &, |, ~, each comparison in parentheses.
isin for lists, between for inclusive ranges, .str methods for text columns.
Next: rows with missing values, and what each statistic does with them.

Practise this