GroupBy & Aggregation

"Total revenue per city." "Average marks per school type." "Number of rainy days per month." Questions that end in per something are answered with groupby, probably the most used tool in data analysis. If you know SQL, it is pandas' version of GROUP BY.

In this lesson you will group by one and several columns, calculate several statistics at once with clear names, and use transform() and filter() for more advanced tasks such as each order's share of its city's total.

Split, apply, combine

A groupby works in three steps:

1. Split the rows into groups by the values of a key column (for example, one group per category).
2. Apply a calculation to each group (sum, mean, count...).
3. Combine the results into a new Series or DataFrame, one row per group.

import pandas as pd

sales = pd.read_csv("sales.csv").drop_duplicates()
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"] / 100)

by_cat = sales.groupby("category")["revenue"].sum()
print(by_cat.round())
print(type(by_cat).__name__, by_cat.index.name)

print(sales.groupby("category")["revenue"].mean().round(1).sort_values(ascending=False))

category
Books             111569.0
Electronics       502862.0
Fashion           529526.0
Groceries         178462.0
Home & Kitchen    450092.0
Sports            134330.0
Name: revenue, dtype: float64
Series category
category
Home & Kitchen    2103.2
Fashion           1961.2
Electronics       1676.2
Sports            1110.2
Books              996.2
Groceries          959.5
Name: revenue, dtype: float64

Read sales.groupby("category")["revenue"].sum() as "group the sales by category, take the revenue column, and sum it in each group". The result is a Series whose index is the category names, sorted alphabetically. The second result changes the story: Home & Kitchen has the highest average order value, even though Fashion has the highest total.

size, count and common aggregations

import pandas as pd

weather = pd.read_csv("weather.csv")
weather["rainy"] = weather["rainfall_mm"] > 2.5

g = weather.groupby("city")
print(g.size())
print(g["rainfall_mm"].count())
print(g["rainy"].sum().sort_values(ascending=False))
print(g["temp_max"].agg(["min", "median", "max"]))

city
Chennai    365
Delhi      365
Jaipur     365
Kochi      365
Mumbai     365
dtype: int64
city
Chennai    364
Delhi      362
Jaipur     363
Kochi      362
Mumbai     362
Name: rainfall_mm, dtype: int64
city
Kochi      138
Mumbai      81
Chennai     80
Delhi       69
Jaipur      40
Name: rainy, dtype: int64
          min  median   max
city                       
Chennai  24.2   32.85  39.9
Delhi    16.3   32.20  42.5
Jaipur   19.3   32.70  99.9
Kochi    25.5   30.20  99.9
Mumbai   24.6   31.90  37.1

size() gives 365 for every city, but count() shows how many rainfall readings exist, which is less where values are missing. Summing a boolean column counts the True values: here, the number of days with more than 2.5 mm of rain, a common threshold for a "rainy day". Kochi has by far the most, and dry Jaipur the fewest. The max column still shows the two 99.9 °C sensor errors; real work would remove them first, as in Finding & Handling Outliers.

Named aggregation

agg() can calculate different statistics on different columns at once. Named aggregation lets you name each result column as you create it, using new_name=("column", "function"). This gives tidy, report-ready tables.

import pandas as pd

sales = pd.read_csv("sales.csv").drop_duplicates()
sales["city"] = sales["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"] / 100)

report = (
    sales.groupby("city")
    .agg(
        orders=("order_id", "count"),
        items=("quantity", "sum"),
        revenue=("revenue", "sum"),
        avg_order=("revenue", "mean"),
        top_discount=("discount", "max"),
    )
    .sort_values("revenue", ascending=False)
    .round(0)
)
print(report)

           orders  items   revenue  avg_order  top_discount
city                                                       
Mumbai        184  298.0  317139.0     1724.0            20
Delhi         188  318.0  294885.0     1569.0            20
Pune          105  276.0  217645.0     2073.0            20
Bengaluru     154  227.0  210063.0     1382.0            20
Hyderabad     124  182.0  204527.0     1676.0            20
Chennai       123  190.0  178588.0     1452.0            20
Kolkata        96  146.0  148900.0     1551.0            20
Lucknow        87  136.0  127243.0     1480.0            20
Jaipur         74  107.0  113568.0     1535.0            20
Kochi          74  101.0   94283.0     1292.0            20

Each keyword argument creates one column. Cleaning the city names first matters: without it, DELHI and Hyderabad would appear as separate rows with tiny totals. Mumbai and Delhi lead on revenue mainly because they have the most orders. Pune's average order looks unusually high: that is the 120-copy exam guide order you met in Finding & Handling Outliers, still uncorrected here. One bad row can move a whole group's average.

Grouping by several columns

Pass a list of keys to group by combinations. The result has a MultiIndex, an index with several levels. as_index=False (or reset_index()) turns the keys back into ordinary columns, and unstack() spreads the inner level into columns.

import pandas as pd

students = pd.read_csv("students.csv", na_values=["AB"]).drop_duplicates()
students["school_type"] = (students["school_type"].str.strip().str.lower().str.rstrip(".")
                           .map({"government": "Government", "govt": "Government", "private": "Private"}))

avg = students.groupby(["school_type", "tuition"])["maths"].mean().round(1)
print(avg)
print(avg.unstack())

flat = students.groupby(["school_type", "tuition"], as_index=False).agg(students=("student_id", "count"))
print(flat)

school_type  tuition
Government   No         55.5
             Yes        56.7
Private      No         60.9
             Yes        59.9
Name: maths, dtype: float64
tuition        No   Yes
school_type            
Government   55.5  56.7
Private      60.9  59.9
  school_type tuition  students
0  Government      No       167
1  Government     Yes        74
2     Private      No       107
3     Private     Yes       132

The unstacked table is easy to read: private school students average about 4 to 5 marks more in maths than government school students, while tuition makes almost no difference (about one mark up in government schools and one mark down in private ones). A table like this shows a pattern but does not prove a cause: families who pay for tuition may differ in other ways too. You will test differences like these properly in Hypothesis Testing Basics.

transform(): group results on every row

agg() returns one row per group. transform() returns a result for every original row, with the group's value repeated. That makes it perfect for comparing each row with its group, for example an order's share of its category, or a student's marks compared with their city's average.

import pandas as pd

sales = pd.read_csv("sales.csv").drop_duplicates()
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"] / 100)

cat_total = sales.groupby("category")["revenue"].transform("sum")
sales["share_pct"] = (sales["revenue"] / cat_total * 100).round(2)

cat_avg = sales.groupby("category")["revenue"].transform("mean")
sales["vs_avg"] = (sales["revenue"] - cat_avg).round(0)

print(sales[["category", "product", "revenue", "share_pct", "vs_avg"]].head(5))
print(sales.groupby("category")["share_pct"].sum().round(1))

      category            product  revenue  share_pct  vs_avg
0       Sports           Yoga Mat   699.00       0.52  -411.0
1       Sports        Cricket Bat  1519.05       1.13   409.0
2       Sports           Yoga Mat   699.00       0.52  -411.0
3  Electronics  Bluetooth Earbuds  1499.00       0.30  -177.0
4      Fashion            T-Shirt   399.00       0.08 -1562.0
category
Books             100.0
Electronics       100.4
Fashion           100.1
Groceries         100.0
Home & Kitchen    100.0
Sports            100.0
Name: share_pct, dtype: float64

The shares in each category add up to 100%, apart from small differences because each share was rounded to two decimals, which is a good check that the calculation is right. vs_avg shows how far each order is above or below a typical order in its own category: the ₹1,519 cricket bat order is about ₹409 above a typical Sports order, while the ₹399 T-shirt is ₹1,562 below a typical Fashion order.

filter(): keep whole groups

filter() keeps or drops entire groups based on a condition about the group. The condition is a function that receives each group as a DataFrame and returns True or False.

import pandas as pd

sales = pd.read_csv("sales.csv").drop_duplicates()

popular = sales.groupby("product").filter(lambda g: len(g) >= 60)
print(popular["product"].value_counts())
print(len(sales), "->", len(popular), "rows")

product
Silk Saree    69
Name: count, dtype: int64
1209 -> 69 rows

Only products with at least 60 orders survive, with all their rows. Here that is just one product, the silk saree, so 69 of the 1,209 rows remain. A lambda is a small one-line function; here it receives each product's rows as g.

Common mistakes

• Grouping dirty keys. Delhi, DELHI and Delhi become three groups. Clean the key column first.
• Confusing size() and count(). size() counts rows; count() counts non-missing values per column.
• Averaging averages. The mean of city averages is not the overall average when cities have different numbers of orders. Compute from the raw rows.
• Calling mean() on text columns. In pandas 3.0, df.groupby("city").mean() raises a TypeError if any column is text. Select the numeric columns first.

Key points

• df.groupby("key")["col"].agg_function() splits rows into groups, applies a calculation and combines the results.
• size() counts rows, count() counts non-missing values; summing a boolean column counts True values.
• Named aggregation, agg(new=("col", "func")), builds clear summary tables.
• Group by a list of keys for combinations; use unstack(), reset_index() or as_index=False to reshape.
• transform() returns group results aligned to every row; filter() keeps or drops whole groups.

import pandas as pd

emp = pd.read_csv("employees.csv").drop_duplicates()
emp["department"] = emp["department"].str.strip().str.title().replace({"Engg": "Engineering", "Hr": "HR"})
emp["left_flag"] = emp["left"] == "Yes"

table = (
    emp.groupby("department")
    .agg(staff=("emp_id", "count"), median_salary=("salary", "median"),
         avg_rating=("rating", "mean"), left_pct=("left_flag", "mean"))
    .sort_values("median_salary", ascending=False)
)
table["avg_rating"] = table["avg_rating"].round(2)
table["left_pct"] = (table["left_pct"] * 100).round(1)
print(table)

              staff  median_salary  avg_rating  left_pct
department                                              
Data Science     38      1119500.0        3.32      13.2
Engineering      97      1011000.0        3.30      16.5
HR               25       807000.0        3.60       4.0
Sales            60       774000.0        3.35      16.7
Finance          29       748000.0        3.17      27.6
Support          51       534000.0        3.24      13.7