Filtering, Grouping, and Aggregating
Narrow a DataFrame to the rows you care about and summarize it by category using boolean masks, groupby, and aggregation.
The Two Moves Behind Almost Every Analysis
Ask any data analyst what they spend most of their time doing, and after cleaning, the honest answer is some combination of two moves: narrow the data down to the rows that matter, and summarize it by category. In pandas those are filtering and groupby. Master these two and you can answer the large majority of questions anyone will ever ask you about a dataset.
We’re back to the clean corner_cafe_sales.csv from Module 3, Lesson 1 — twelve rows, three days, four items. Load it and add one more column you’ll use throughout this lesson:
import pandas as pd
sales = pd.read_csv("corner_cafe_sales.csv")
sales["revenue"] = sales["quantity"] * sales["price"]
sales["revenue"] = ... assigns a brand-new column, computed from two existing ones, in one line — no loop required. pandas applies the multiplication to every row at once, a pattern called vectorization that’s a large part of why pandas is fast: the loop still happens, but it happens inside pandas’ optimized internals instead of in your Python code.
Filtering With Boolean Masks
sales["quantity"] > 4 doesn’t filter anything by itself — it produces a Series of True/False, one per row, exactly like the comparisons from Module 1 but run across an entire column at once. That Series of booleans is a mask, and pandas lets you drop it straight inside the DataFrame’s square brackets to keep only the rows where it’s True:
big_orders = sales[sales["quantity"] > 4]
print(big_orders)
Combine multiple conditions with & (and) / | (or) — not Python’s and/or, which don’t work element-by-element on a Series — and parenthesize every individual condition:
cold_brew_big = sales[(sales["item"] == "Cold Brew") & (sales["quantity"] > 4)]
Skipping the parentheses is one of the most common pandas errors there is, and it’s worth understanding exactly why: Python evaluates & before ==, the opposite of the order you want, so sales["item"] == "Cold Brew" & sales["quantity"] > 4 doesn’t parse as two comparisons joined by & — pandas raises an error trying to make sense of it. Parenthesizing each condition sidesteps the whole issue.
Summarizing With groupby
Filtering answers “which rows match?” groupby answers a different kind of question: “what does each category look like, summarized?” It works in three conceptual steps — split the DataFrame into groups by a column’s values, apply an aggregation to each group separately, and combine the results back into one table:
by_item = sales.groupby("item").agg(
total_quantity=("quantity", "sum"),
total_revenue=("revenue", "sum"),
)
print(by_item)
.agg(total_quantity=("quantity", "sum"), total_revenue=("revenue", "sum")) builds named output columns from (source column, aggregation function) pairs — the result is one row per unique item value, with your two new summary columns attached. Run it against the twelve-row sales log and by_item should show Cold Brew with total_quantity 15 and total_revenue 60.00 — the single best-selling item in this dataset by both measures.
Sorting and Ranking
groupby gives you the summary; sort_values puts it in order:
by_item_sorted = by_item.sort_values("total_revenue", ascending=False)
print(by_item_sorted)
ascending=False puts the largest value first — the default is ascending (smallest first), so for a “top performer” question you’ll set this explicitly almost every time. .value_counts() is a related shortcut worth knowing: called directly on a column, it counts how often each unique value appears, sorted from most to least common — handy for a quick “how many rows per category” check without writing a full groupby:
sales["item"].value_counts()
Using corner_cafe_sales.csv from Module 3, Lesson 1:
import pandas as pd
sales = pd.read_csv("corner_cafe_sales.csv")
sales["revenue"] = sales["quantity"] * sales["price"]
by_item = sales.groupby("item").agg(
total_quantity=("quantity", "sum"),
total_revenue=("revenue", "sum"),
).sort_values("total_revenue", ascending=False)
print(by_item)
big_cold_brew = sales[(sales["item"] == "Cold Brew") & (sales["quantity"] > 4)]
print(f"\nCold Brew orders over 4 units:\n{big_cold_brew}")Confirm by_item ranks Cold Brew first at $60.00, followed by Latte at $40.50, Espresso at $38.50, and Muffin last at $19.50 — and that the filtered big_cold_brew result contains exactly two rows (the 6-unit and 5-unit Cold Brew orders).
This is the single most common groupby/filtering mistake in pandas, and it’s worth seeing the broken version once so you recognize the error message later.
# Wrong — Python evaluates & before ==, so this doesn't
# parse as two comparisons joined by "and"; pandas raises
# a TypeError trying to interpret it
sales[sales["item"] == "Latte" & sales["quantity"] > 3]# Right — every individual condition wrapped in parentheses
sales[(sales["item"] == "Latte") & (sales["quantity"] > 3)]If pandas ever raises an error pointing at a filter line that “looks correct,” check for missing parentheses around each comparison first — it’s the most likely cause, far more often than an actual logic mistake in the condition itself.
sales.groupby('item')['quantity'].sum() and sales.groupby('item').agg(total_quantity=('quantity', 'sum')) produce the same numbers. What's the practical difference between them?
Reveal Answer & Explanation
The first returns a Series (one column of sums, indexed by item); the second returns a DataFrame with a column explicitly named total_quantity — and .agg() lets you compute several differently-named summary columns from different source columns in one call, which the shorthand version can’t do.
sales.groupby("item")["quantity"].sum() is a convenient shortcut when you only need one summary statistic from one column — quick to type, but the result column just inherits the name quantity, which gets confusing once you have both a raw quantity column and a summed one in the same script. .agg(...) is more verbose but scales to real analysis: you can summarize quantity with sum and revenue with sum (or mean, or both) in a single call, with output names you choose yourself, like by_item above. Reach for the shortcut when exploring quickly in a REPL; reach for .agg() the moment the result is going into a report, a chart, or any code someone else will read.
Up Next
You can now group and summarize a dataset — the mechanical half of analysis. The next lesson uses these same tools to answer specific, real questions about the coffee shop’s sales and turn a summary table into a stated conclusion.