Skip to content
Laviyx Learn

Try

Python for Data course menu

pandas groupby Explained: Group, Aggregate and Transform

Learn pandas groupby: split-apply-combine, aggregating with sum, mean and count, several statistics with agg and named aggregations, grouping by several columns, transform for group-level columns, and filter.

Intermediate12 min readUpdated
On this page

Grouping answers questions like "total sales per region" or "average salary per department". pandas uses the split-apply-combine idea:

  1. Split the rows into groups by the values of a column.
  2. Apply a calculation to each group.
  3. Combine the results into a new table.

It is the pandas version of SQL's GROUP BY.

The examples use this sales table:

Python
import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North", "East", "South"],
    "product": ["Pen", "Pen", "Book", "Pen", "Book", "Pen", "Book", "Book"],
    "units":   [10, 4, 2, 7, 5, 3, 6, 1],
    "revenue": [200, 80, 300, 140, 750, 60, 900, 150],
})
print(sales)
Output
  region product  units  revenue
0  North     Pen     10      200
1  South     Pen      4       80
2  North    Book      2      300
3   East     Pen      7      140
4  South    Book      5      750
5  North     Pen      3       60
6   East    Book      6      900
7  South    Book      1      150

One column, one statistic#

Python
import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North", "East", "South"],
    "product": ["Pen", "Pen", "Book", "Pen", "Book", "Pen", "Book", "Book"],
    "units":   [10, 4, 2, 7, 5, 3, 6, 1],
    "revenue": [200, 80, 300, 140, 750, 60, 900, 150],
})
print(sales.groupby("region")["revenue"].sum())
print(sales.groupby("region")["revenue"].mean().round(1))
print(sales.groupby("region").size())             # rows per group
Output
region
East     1040
North     560
South     980
Name: revenue, dtype: int64
region
East     520.0
North    186.7
South    326.7
Name: revenue, dtype: float64
region
East     2
North    3
South    3
dtype: int64

The result is a Series whose index is the group values, sorted alphabetically by default (sort=False keeps the order of first appearance).

Several statistics: agg#

Python
import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North", "East", "South"],
    "product": ["Pen", "Pen", "Book", "Pen", "Book", "Pen", "Book", "Book"],
    "units":   [10, 4, 2, 7, 5, 3, 6, 1],
    "revenue": [200, 80, 300, 140, 750, 60, 900, 150],
})
print(sales.groupby("region")["revenue"].agg(["sum", "mean", "max", "count"]))

summary = sales.groupby("region").agg(
    total_revenue=("revenue", "sum"),
    units_sold=("units", "sum"),
    biggest_sale=("revenue", "max"),
    orders=("revenue", "count"),
)
print(summary.sort_values("total_revenue", ascending=False))
Output
         sum        mean  max  count
region
East    1040  520.000000  900      2
North    560  186.666667  300      3
South    980  326.666667  750      3
        total_revenue  units_sold  biggest_sale  orders
region
East             1040          13           900       2
South             980          10           750       3
North             560          15           300       3

Named aggregation (new_name=("column", "function")) gives clear column names and lets each column have its own statistic. It is the most readable way to summarise data.

Grouping by several columns#

Python
import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North", "East", "South"],
    "product": ["Pen", "Pen", "Book", "Pen", "Book", "Pen", "Book", "Book"],
    "units":   [10, 4, 2, 7, 5, 3, 6, 1],
    "revenue": [200, 80, 300, 140, 750, 60, 900, 150],
})
by_both = sales.groupby(["region", "product"])["revenue"].sum()
print(by_both)
print(by_both.reset_index())                  # back to ordinary columns
print(by_both.unstack(fill_value=0))          # products as columns
Output
region  product
East    Book       900
        Pen        140
North   Book       300
        Pen        260
South   Book       900
        Pen         80
Name: revenue, dtype: int64
  region product  revenue
0   East    Book      900
1   East     Pen      140
2  North    Book      300
3  North     Pen      260
4  South    Book      900
5  South     Pen       80
product  Book  Pen
region
East      900  140
North     300  260
South     900   80

Grouping by two columns gives a two-level index (a MultiIndex). reset_index() turns it into normal columns, and unstack() spreads the inner level into columns, a quick way to build a cross table (see Pivot Tables).

transform: keep every row#

agg returns one row per group. transform returns a value for every original row, which lets you compare each row with its group:

Python
import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North", "East", "South"],
    "revenue": [200, 80, 300, 140, 750, 60, 900, 150],
})
sales["region_total"] = sales.groupby("region")["revenue"].transform("sum")
sales["share_of_region"] = (sales["revenue"] / sales["region_total"] * 100).round(1)
sales["rank_in_region"] = sales.groupby("region")["revenue"].rank(ascending=False).astype(int)
print(sales)
Output
  region  revenue  region_total  share_of_region  rank_in_region
0  North      200           560             35.7               2
1  South       80           980              8.2               3
2  North      300           560             53.6               1
3   East      140          1040             13.5               2
4  South      750           980             76.5               1
5  North       60           560             10.7               3
6   East      900          1040             86.5               1
7  South      150           980             15.3               2

This is like a SQL window function: group-level values without collapsing the rows.

filter: keep whole groups#

filter keeps or drops entire groups based on a condition about the group:

Python
import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North", "East", "South"],
    "revenue": [200, 80, 300, 140, 750, 60, 900, 150],
})
big_regions = sales.groupby("region").filter(lambda g: g["revenue"].sum() >= 900)
print(big_regions)
Output
  region  revenue
1  South       80
3   East      140
4  South      750
6   East      900
7  South      150

North's total is 560, so all its rows are dropped. This is like SQL's HAVING, but it returns the original rows.

Common mistakes#

Practice#

Practice exercise

Using the sales table, print for each product: the total units, the total revenue, and the average revenue per unit (revenue ÷ units, rounded to 2 decimals). Then add a column to the original rows showing each sale's revenue minus its product's average revenue (rounded to 1 decimal).

Show hint

Named aggregation for the totals, then divide the two columns. For the second part, transform("mean") grouped by product.

Show solution
Python
import pandas as pd

sales = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North", "East", "South"],
    "product": ["Pen", "Pen", "Book", "Pen", "Book", "Pen", "Book", "Book"],
    "units":   [10, 4, 2, 7, 5, 3, 6, 1],
    "revenue": [200, 80, 300, 140, 750, 60, 900, 150],
})
per_product = sales.groupby("product").agg(units=("units", "sum"), revenue=("revenue", "sum"))
per_product["revenue_per_unit"] = (per_product["revenue"] / per_product["units"]).round(2)
print(per_product)

sales["vs_product_avg"] = (sales["revenue"] - sales.groupby("product")["revenue"].transform("mean")).round(1)
print(sales[["product", "revenue", "vs_product_avg"]])
Output
         units  revenue  revenue_per_unit
product
Book        14     2100             150.0
Pen         24      480              20.0
  product  revenue  vs_product_avg
0     Pen      200            80.0
1     Pen       80           -40.0
2    Book      300          -225.0
3     Pen      140            20.0
4    Book      750           225.0
5     Pen       60           -60.0
6    Book      900           375.0
7    Book      150          -375.0

Key takeaway#

Next: Pivot Tables and Crosstabs.

Finished this lesson?

Related lessons

Python for Datapandas Pivot Tables and Crosstabs (Like Excel Pivot Tables)Build Excel-style pivot tables in pandas with pivot_table: rows, columns, values and aggregation functions, totals with margins, filling gaps, counting with crosstab, percentages, and reshaping with pivot and melt.Intermediate8 min readPython for Datapandas merge Explained: Inner, Left, Right and Outer JoinsCombine pandas DataFrames with merge: inner, left, right and outer joins, joining on columns with different names, several key columns, finding unmatched rows with indicator, and avoiding duplicated rows from one-to-many joins.Intermediate9 min readSQLSQL GROUP BY Explained Simply (With Examples)Learn how SQL GROUP BY turns many rows into summary rows. Count, sum, and average per group, group by multiple columns, filter groups with HAVING, and avoid common errors.Beginner8 min readSQLSQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX and MoreA complete guide to SQL aggregate functions: how COUNT, SUM, AVG, MIN and MAX work together, how they treat NULLs and empty results, string aggregation, and the rules for combining aggregates with GROUP BY.Intermediate8 min read