Filtering, Sorting & Grouping

A DataFrame you can only look at is a spreadsheet. A DataFrame you can query is a superpower. This lesson covers the operations that answer real questions: "which orders were over $50?", "what's the average rating per genre?", "who are our top five customers?" — all without a single loop.

We'll use one small cereal-style dataset throughout, so each cell is self-contained.

Boolean masks: filtering rows

The pattern is the one you met with NumPy: build a Series of True/False, then use it to index the DataFrame:

Python — runs in your browser

Run

import pandas as pd df = pd.DataFrame({ "name": ["Corn Flakes", "Choco Blast", "Fiber One", "Honey Pops", "Bran Crunch", "Sugar Bombs", "Oat Rings", "Wheat Bites"], "mfr": ["K", "N", "K", "G", "P", "G", "K", "N"], "calories":[100, 130, 60, 110, 90, 150, 105, 95], "sugars": [2, 12, 0, 11, 5, 15, 6, 3], "rating": [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6], }) high_rated = df[df["rating"] > 50] print(high_rated) print() print(df[df["mfr"] == "K"]) # equality works too

Read df[df["rating"] > 50] inside-out: the inner expression makes a boolean mask, the outer brackets keep only the True rows.

Combining conditions: the parentheses gotcha

Multiple conditions use & (and), | (or), and ~ (not) — not Python's and/or/not. And each condition must be wrapped in parentheses, because & binds tighter than >:

Python — runs in your browser

Run

import pandas as pd df = pd.DataFrame({ "name": ["Corn Flakes", "Choco Blast", "Fiber One", "Honey Pops", "Bran Crunch", "Sugar Bombs", "Oat Rings", "Wheat Bites"], "mfr": ["K", "N", "K", "G", "P", "G", "K", "N"], "calories":[100, 130, 60, 110, 90, 150, 105, 95], "sugars": [2, 12, 0, 11, 5, 15, 6, 3], "rating": [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6], }) # healthy AND tasty: low sugar and high rating print(df[(df["sugars"] < 5) & (df["rating"] > 40)]) print() # either very low calorie OR top-rated print(df[(df["calories"] < 90) | (df["rating"] > 55)]) print() # NOT made by Kellogg's ("K") print(df[~(df["mfr"] == "K")])

isin, between, and string filters

Three shortcuts save you from long chains of |:

Python — runs in your browser

Run

import pandas as pd df = pd.DataFrame({ "name": ["Corn Flakes", "Choco Blast", "Fiber One", "Honey Pops", "Bran Crunch", "Sugar Bombs", "Oat Rings", "Wheat Bites"], "mfr": ["K", "N", "K", "G", "P", "G", "K", "N"], "calories":[100, 130, 60, 110, 90, 150, 105, 95], "sugars": [2, 12, 0, 11, 5, 15, 6, 3], "rating": [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6], }) # membership: mfr is K or N (instead of two == chained with |) print(df[df["mfr"].isin(["K", "N"])]) print() # range: calories from 90 to 110 inclusive print(df[df["calories"].between(90, 110)]) print() # string matching via the .str accessor print(df[df["name"].str.contains("Cr")]) print(df[df["name"].str.startswith("C")])

The .str accessor exposes most Python string methods (contains, startswith, lower, split, …) applied to every value at once — vectorized, like everything else.

loc vs iloc

Both select rows and columns, but by different coordinates:

• .loc[rows, cols] — by label (index values, column names); slices are inclusive
• .iloc[rows, cols] — by integer position; slices exclude the end, like Python lists

Python — runs in your browser

Run

import pandas as pd df = pd.DataFrame({ "mfr": ["K", "N", "K", "G"], "calories":[100, 130, 60, 110], "rating": [45.9, 22.4, 68.2, 31.1], }, index=["Corn Flakes", "Choco Blast", "Fiber One", "Honey Pops"]) # by label — note the slice INCLUDES "Fiber One" print(df.loc["Choco Blast":"Fiber One", ["calories", "rating"]]) print() # by position — rows 0-1, columns 0-1 (end excluded) print(df.iloc[0:2, 0:2]) print() # loc also accepts a boolean mask plus a column selection: print(df.loc[df["rating"] > 40, "calories"])

That last pattern — df.loc[mask, column] — is also the safe way to modify a filtered subset (plain chained indexing like df[mask][col] = ... triggers the infamous SettingWithCopyWarning).

Sorting

sort_values orders rows by one or more columns:

Python — runs in your browser

Run

import pandas as pd df = pd.DataFrame({ "name": ["Corn Flakes", "Choco Blast", "Fiber One", "Honey Pops", "Bran Crunch", "Sugar Bombs", "Oat Rings", "Wheat Bites"], "mfr": ["K", "N", "K", "G", "P", "G", "K", "N"], "calories":[100, 130, 60, 110, 90, 150, 105, 95], "rating": [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6], }) # top 3 by rating print(df.sort_values("rating", ascending=False).head(3)) print() # two keys: by manufacturer, then rating within each print(df.sort_values(["mfr", "rating"], ascending=[True, False]))

groupby: split, apply, combine

groupby is the single most important pandas operation. It splits the table into groups, applies an aggregation to each, and combines the results into a new table:

Python — runs in your browser

Run

import pandas as pd df = pd.DataFrame({ "mfr": ["K", "N", "K", "G", "P", "G", "K", "N"], "type": ["cold", "cold", "cold", "cold", "hot", "cold", "hot", "cold"], "calories":[100, 130, 60, 110, 90, 150, 105, 95], "sugars": [2, 12, 0, 11, 5, 15, 6, 3], "rating": [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6], }) # average rating per manufacturer print(df.groupby("mfr")["rating"].mean().round(1)) print() # several statistics at once with .agg print(df.groupby("mfr").agg( n=("rating", "size"), avg_rating=("rating", "mean"), max_sugar=("sugars", "max"), ).round(1))

The named-aggregation form — new_name=("column", "function") — keeps the output tidy and self-documenting.

pivot_table: groupby in two dimensions

When you want groups along both axes (rows and columns), reach for pivot_table:

Python — runs in your browser

Run

import pandas as pd df = pd.DataFrame({ "mfr": ["K", "N", "K", "G", "P", "G", "K", "N"], "type": ["cold", "cold", "cold", "cold", "hot", "cold", "hot", "cold"], "calories":[100, 130, 60, 110, 90, 150, 105, 95], "rating": [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6], }) table = df.pivot_table( values="rating", index="mfr", # rows columns="type", # columns aggfunc="mean", ) print(table.round(1))

Each cell is the mean rating for one (manufacturer, type) combination — the same result as groupby(["mfr", "type"]), just reshaped into a grid that's much easier to scan (and to feed into a heatmap, as you'll see in the seaborn lesson).

Check your understanding

5 questions · free

1. Q1.Why does df[df.sugars < 5 & df.rating > 40] raise an error?

   AYou can't combine two conditions in pandasB& binds tighter than the comparisons, so each condition needs parenthesesCsugars and rating have different dtypesDYou must use .query() for multiple conditions

2. Q2.Which expression keeps rows where mfr is either "K" or "N"?

   Adf[df.mfr == "K" or df.mfr == "N"]Bdf[df.mfr.isin(["K", "N"])]Cdf[df.mfr in ["K", "N"]]Ddf.isin(df.mfr, "K", "N")

3. Q3.What's the key difference between .loc and .iloc slicing?

   A.loc is fasterB.loc slices by label and includes the endpoint; .iloc slices by position and excludes itC.iloc works only on numeric columnsD.loc can't select columns

4. Q4.df.groupby("mfr")["rating"].mean() implements which pattern?

   Amap-reduce-shuffleBsplit-apply-combineCsort-merge-joinDfilter-project-scan

5. Q5.When is pivot_table more convenient than a plain groupby?

   AWhen you have missing valuesBWhen you want groups spread across both rows and columns as a gridCWhen the data has more than 1000 rowsDWhen you need to sort the result

Exercise: Query an orders table

Build an orders DataFrame with columns city (3 cities, 10 rows), payment ("card", "cash", "e-wallet"), and amount. Then answer: (1) which orders exceed 100? (2) which are card orders over 100 (two conditions)? (3) for each city, how many orders, total, and average amount (one groupby().agg())? (4) build a pivot_table of total amount by city × payment method.

You can now slice, dice, and summarize tables — next we make the numbers visible with Matplotlib.