Filtering & Sorting
Most questions about data start with "which rows…?": which sales were in Nairobi, which orders were above KES 3,000, which product sold the most. Filtering keeps the rows that matter, and sorting puts them in a useful order.
The example data
import pandas as pd
sales = pd.DataFrame({
"product": ["Maize flour", "Sugar", "Cooking oil", "Rice", "Sugar", "Rice"],
"county": ["Nairobi", "Kisumu", "Nairobi", "Nakuru", "Nairobi", "Kisumu"],
"units": [30, 12, 8, 15, 20, 25],
"price": [180, 160, 350, 220, 160, 220],
})
sales["revenue"] = sales["units"] * sales["price"]
Filtering with a Boolean mask
A comparison on a column gives a Series of True/False values called a mask. Putting the mask inside sales[...] keeps the rows where it is True, exactly like Boolean indexing in NumPy.
in_nairobi = sales["county"] == "Nairobi" print(sales[in_nairobi])
The original row labels (0, 2, 4) are kept, so you can always trace a row back to the full table.
Combining conditions
Use & for "and", | for "or" and ~ for "not", with brackets around each condition.
big_and_pricey = sales[(sales["units"] >= 15) & (sales["price"] > 200)] print(big_and_pricey)
Using and/or instead of &/| raises ValueError: The truth value of a Series is ambiguous. Forgetting the brackets causes errors too, because & is evaluated before >=. And remember that == compares while = assigns.
Handy filtering methods
# isin: match any value in a list mask = sales["product"].isin(["Sugar", "Rice"]) print(sales.loc[mask, ["product", "county", "revenue"]]) # between: a range, inclusive at both ends by default print(sales.loc[sales["revenue"].between(3000, 5000), "product"])
df.loc[mask, columns] filters rows and picks columns in one step. Other useful tools:
sales["product"].str.contains("oil", case=False)matches text that contains a word.sales["county"].str.startswith("Na")matches the start of the text.sales[sales["units"].notna()]keeps rows where a value is not missing.sales[~mask]keeps everything the mask did not match.
The query() method
query() lets you write the condition as a string, which can be easier to read when there are several parts.
print(sales.query("county == 'Kisumu' and units > 20"))
Inside a query string you can use and/or, and you refer to columns by name. To use a Python variable, prefix it with @, for example sales.query("units > @minimum").
Sorting
sort_values() orders rows by one or more columns. It returns a new DataFrame; the original is not changed.
by_revenue = sales.sort_values("revenue", ascending=False)
print(by_revenue[["product", "county", "revenue"]])
Sorting by several columns
Pass a list of columns, and a matching list for ascending. Here counties go A to Z, and within each county the highest revenue comes first.
ordered = sales.sort_values(["county", "revenue"], ascending=[True, False]) print(ordered[["county", "product", "revenue"]])
Top and bottom rows
print(sales.nlargest(2, "revenue")["product"].tolist()) print(sales.nsmallest(1, "units")["product"].tolist())
nlargest() and nsmallest() are shortcuts for "sort, then take the first n". Use sort_index() to put rows back in index order, and reset_index(drop=True) to renumber them 0, 1, 2… after filtering or sorting.
Calling sales.sort_values("revenue") on its own line and expecting sales to be sorted. The method returns a sorted copy, so assign it: sales = sales.sort_values("revenue"). Missing values (NaN) are placed last by default whichever direction you sort; change that with na_position="first".
Key points
- Filter with a Boolean mask:
df[df["col"] > value]. - Combine conditions with
&,|and~, with brackets around each one. isin(),between(),.str.contains()andquery()make common filters easier.sort_values()sorts by one or more columns and returns a new DataFrame.
Tip: use the ← and → keys to move between pages.
