Lesson 3 of 8 · 7 min · Working with tables in pandas
groupby and aggregation: split, apply, combine
What you will learn
- Summarise by category with
groupby - Use several aggregations at once with named aggregation
- Add a per-group value to every row with
transform - Group by date with
resampleanddt.to_period - Count unique values and rank within groups
In simple words
Think of sorting a stack of invoices into piles by customer, adding up each pile, and writing the totals on a list. That is groupby: split the rows into groups, calculate something for each group, and combine the answers into a new table. It is the pandas version of SQL's GROUP BY.
Words to know
- groupby: splitting a table into groups based on the values of one or more columns.
- Aggregation: reducing a group to one number, such as a sum or count.
- Transform: a group calculation whose result is repeated on every row of the group.
- Resample: grouping time-series data into regular time buckets, such as months.
The basic pattern
Using the orders DataFrame:
orders.groupby("status")["amount"].sum()
status
paid 3100
pending 900
refunded 1500
Name: amount, dtype: int64
Read it left to right: group the rows by status, take the amount column, sum within each group. Other aggregations: .count() (non-missing values), .size() (rows, including missing), .mean(), .min(), .max(), .median(), .nunique().
By default the group keys become the index of the result. For a normal table with a normal index, use as_index=False or reset_index():
orders.groupby("status", as_index=False)["amount"].sum()
Group by several columns for one row per combination:
orders.groupby(["customer_id", "status"], as_index=False)["amount"].sum()
Many aggregates at once: named aggregation
summary = orders.groupby("customer_id").agg(
orders=("order_id", "count"),
total=("amount", "sum"),
average=("amount", "mean"),
first_order=("order_date", "min"),
last_order=("order_date", "max"),
)
Each keyword is the output column name, and the tuple is (input column, function). Result:
orders total average first_order last_order
customer_id
1 3 2500 833.333333 2025-09-01 2025-10-20
2 1 1500 1500.000000 2025-09-20 2025-09-20
3 2 1500 750.000000 2025-10-02 2025-10-10
This is the standard way to build a customer summary. Use .reset_index() to turn the key back into a column. The functions can be strings ("sum"), NumPy-style callables or your own lambda.
Filtering before and after
Filter rows first with a mask when the condition is on rows (like SQL WHERE), and filter groups after with a normal filter on the result (like HAVING):
paid_totals = (orders[orders["status"] == "paid"]
.groupby("customer_id", as_index=False)["amount"].sum())
big = paid_totals[paid_totals["amount"] > 700]
Customer 1 (paid 2500) remains, customer 3 (paid 600) does not. Method chaining with brackets, as above, keeps a multi-step analysis readable.
transform: a group value on every row
agg shrinks the table to one row per group. transform keeps every row and repeats the group's result:
orders["customer_total"] = orders.groupby("customer_id")["amount"].transform("sum")
orders["share_of_customer"] = orders["amount"] / orders["customer_total"]
orders["rank_in_customer"] = orders.groupby("customer_id")["amount"].rank(ascending=False)
Use it for "percentage of group total", "difference from group average", and to flag the largest row per group. It is the pandas equivalent of a SQL window function with PARTITION BY. Related: groupby(...).cumsum() for running totals per group, groupby(...).shift(1) for the previous row's value in a group, and groupby(...).head(1) for the first row of each group.
Group by time
Group dates by month in two ways. With a period column:
orders["month"] = orders["order_date"].dt.to_period("M")
orders.groupby("month")["amount"].agg(["count", "sum"])
count sum
month
2025-09 3 3500
2025-10 3 2000
Or with resample, which needs the date as the index and fills gaps with empty periods:
orders.set_index("order_date").resample("ME")["amount"].sum()
"ME" means month end (pandas 2.2 and later; the old "M" alias was removed in pandas 3). Other frequencies: "W" weekly, "D" daily, "QE" quarter end, "YE" year end.
Value counts and crosstabs
orders["status"].value_counts(normalize=True) # shares: 0.667 paid, ...
pd.crosstab(orders["customer_id"], orders["status"])
crosstab counts combinations in a grid, and the pivot table lesson shows the general version.
Useful extras
groupby(...).apply(func)runs your own function per group. It is flexible but slower; tryaggortransformfirst.- Group keys with missing values are dropped by default; use
dropna=Falseto keep them. observed=Trueis advised when grouping by categorical columns, to avoid empty combinations.groupby("a")["b"].nunique()counts distinct values per group, such as products per customer.
Common mistakes
- Forgetting
reset_index()and wondering where the key column went. - Using
count()when you meantsize():countskips missing values. - Averaging an average across groups instead of recalculating from totals.
- Grouping by a column with missing values and losing those rows silently.
- Using
applyfor somethingaggortransformcan do, making it slow.
Try it yourself
- Compute total, count and average amount per status.
- Build a customer summary with named aggregation, then keep customers with at least two orders.
- Add a column with each order's share of the customer's total using
transform. - Compute monthly total and number of orders using
to_periodand again withresample. - Find the largest order per customer (hint:
idxmaxorsort_valuesthengroupby().head(1)).
Check yourself
What does df.groupby("a")["b"].sum() return?
A Series with the sum of b for each distinct value of a, indexed by a.
What is the difference between agg and transform?
agg returns one row per group. transform returns a result with the same number of rows as the input, repeating the group value.
What is the difference between count() and size()?
size() counts rows in each group; count() counts non-missing values in a column.
How do you group sales by month?
Convert the date with dt.to_period("M") and group by it, or set the date as the index and use resample("ME").
Interview questions
How would you find the top 3 customers by total spend?
orders.groupby("customer_id")["amount"].sum().nlargest(3).
How do you add each row's percentage of its group total?
Use transform: df["pct"] = df["amount"] / df.groupby("g")["amount"].transform("sum").
What is the pandas equivalent of SQL HAVING?
Aggregate with groupby, then filter the resulting table with a boolean condition on the aggregated column.
Summary
groupbysplits, applies a function and combines:groupby(key)[col].sum().- Use named aggregation for several measures, and
reset_index()for a flat table. transformrepeats group results on each row, replacing window functions.- Group dates with
to_periodorresample;MEis month end in recent pandas.
Going deeper
- Group by: split-apply-combine: the official guide.
- Time series and resampling: frequencies and
resample. - DataFrame.groupby reference: all parameters.
See how much of this you can explain
A free mock interview asks you new questions on Python for data analysis with pandas for Data Analyst and scores every answer, with what to fix.