Lesson 3 of 8 · 7 min · Working with tables in pandas

groupby and aggregation: split, apply, combine

Covers: pandas 3.x. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

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 resample and dt.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; try agg or transform first.
  • Group keys with missing values are dropped by default; use dropna=False to keep them.
  • observed=True is 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 meant size(): count skips 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 apply for something agg or transform can do, making it slow.

Try it yourself

  1. Compute total, count and average amount per status.
  2. Build a customer summary with named aggregation, then keep customers with at least two orders.
  3. Add a column with each order's share of the customer's total using transform.
  4. Compute monthly total and number of orders using to_period and again with resample.
  5. Find the largest order per customer (hint: idxmax or sort_values then groupby().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

  • groupby splits, applies a function and combines: groupby(key)[col].sum().
  • Use named aggregation for several measures, and reset_index() for a flat table.
  • transform repeats group results on each row, replacing window functions.
  • Group dates with to_period or resample; ME is month end in recent pandas.

Going deeper

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.