🔢 Week 2 – NumPy & Pandas · Day 13
Day 13: GroupBy, Aggregation & Pivot Tables
⏱ 2 hours📶 Intermediate🎯 100% self-study
Day 13 / 60
✅ Today you will learn
- Split → apply → combine
- groupby with sum, mean, count, nunique
- Named aggregations with agg()
- transform for “share of group”
- pivot_table and crosstab like Excel
🏢 In the real world
Every Monday a regional manager asks for “revenue, orders and average bill by region and category, plus each city’s share of its region”. With groupby and pivot_table this weekly report takes 10 lines instead of an afternoon in Excel.
Split → apply → combine
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
print(sales.groupby("region")["revenue"].sum().round())
print(sales.groupby("category")["rating"].mean().round(2).sort_values())region
East 1299826.0
North 1600660.0
South 2081689.0
West 1897608.0
Name: revenue, dtype: float64
category
Grocery 4.04
Fashion 4.05
Home & Kitchen 4.06
Electronics 4.08
Books & Stationery 4.10
Name: rating, dtype: float64
Several statistics at once
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
report = sales.groupby("region").agg(
orders=("order_id", "count"),
cities=("city", "nunique"),
revenue=("revenue", "sum"),
avg_bill=("revenue", "mean"),
avg_rating=("rating", "mean"),
).round(1).sort_values("revenue", ascending=False)
print(report) orders cities revenue avg_bill avg_rating
region
South 654 3 2081689.0 3183.0 4.1
West 519 3 1897607.7 3656.3 4.1
North 508 3 1600659.8 3150.9 4.0
East 319 2 1299826.2 4074.7 4.0
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
two = sales.groupby(["region", "category"])["revenue"].sum().round()
print(two.head(7))
print(two.unstack().fillna(0).astype(int)) # long -> wideregion category
East Books & Stationery 42697.0
Electronics 853186.0
Fashion 212273.0
Grocery 118959.0
Home & Kitchen 72711.0
North Books & Stationery 91710.0
Electronics 747832.0
Name: revenue, dtype: float64
category Books & Stationery Electronics Fashion Grocery Home & Kitchen
region
East 42697 853186 212273 118959 72711
North 91710 747832 467318 215108 78692
South 79562 1151046 457677 237789 155616
West 96012 1087347 377749 218082 118417
transform – keep every row
agg returns one row per group. transform returns a value for every original row, so you can compare each row with its group.
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
city = sales.groupby(["region", "city"], as_index=False)["revenue"].sum()
city["share_of_region_%"] = (city["revenue"] / city.groupby("region")["revenue"].transform("sum") * 100).round(1)
print(city.sort_values(["region", "share_of_region_%"], ascending=[True, False]).to_string(index=False))region city revenue share_of_region_%
East Kolkata 839885.10 64.6
East Patna 459941.15 35.4
North Delhi 945592.45 59.1
North Lucknow 394912.00 24.7
North Jaipur 260155.35 16.3
South Bengaluru 964019.65 46.3
South Hyderabad 572190.15 27.5
South Chennai 545479.20 26.2
West Mumbai 1043230.20 55.0
West Ahmedabad 450611.90 23.7
West Pune 403765.60 21.3
Python
import pandas as pd
st = pd.read_csv("students_performance.csv")
st["school_avg"] = st.groupby("school_type")["maths"].transform("mean").round(1)
st["above_school_avg"] = st["maths"] > st["school_avg"]
print(st[["student_id", "school_type", "maths", "school_avg", "above_school_avg"]].head()) student_id school_type maths school_avg above_school_avg
0 S1001 Government 65.0 64.9 True
1 S1002 Private 50.0 65.0 False
2 S1003 Private 50.0 65.0 False
3 S1004 Private 68.0 65.0 True
4 S1005 Government 67.0 64.9 True
pivot_table – Excel pivot in one line
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
sales["month"] = pd.to_datetime(sales["order_date"]).dt.month
pv = pd.pivot_table(sales, index="category", columns="region", values="revenue",
aggfunc="sum", margins=True, margins_name="Total").round(-2)
print((pv / 1000).round(1)) # in ₹ thousandregion East North South West Total
category
Books & Stationery 42.7 91.7 79.6 96.0 310.0
Electronics 853.2 747.8 1151.0 1087.3 3839.4
Fashion 212.3 467.3 457.7 377.7 1515.0
Grocery 119.0 215.1 237.8 218.1 789.9
Home & Kitchen 72.7 78.7 155.6 118.4 425.4
Total 1299.8 1600.7 2081.7 1897.6 6879.8
Python
import pandas as pd
import matplotlib.pyplot as plt
sales = pd.read_csv("sales_data.csv")
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
pv = pd.pivot_table(sales, index="category", columns="region", values="revenue", aggfunc="sum") / 100000
pv.plot(kind="bar", figsize=(9, 4.6))
plt.title("Revenue by category and region (₹ lakh)")
plt.ylabel("₹ lakh"); plt.xticks(rotation=0)
plt.show()
crosstab – counts and percentages
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
print(pd.crosstab(sales["region"], sales["payment_mode"]))
print((pd.crosstab(sales["region"], sales["payment_mode"], normalize="index") * 100).round(1))payment_mode Card Cash on Delivery Net Banking UPI
region
East 75 60 22 162
North 122 101 42 243
South 163 127 56 308
West 125 103 43 248
payment_mode Card Cash on Delivery Net Banking UPI
region
East 23.5 18.8 6.9 50.8
North 24.0 19.9 8.3 47.8
South 24.9 19.4 8.6 47.1
West 24.1 19.8 8.3 47.8
💡 Insight from the crosstab
Look at the Cash-on-Delivery share by region. A region with high COD has more returns and cash-handling cost – the business might offer a UPI cashback there.
Real example: Monday sales report
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv", parse_dates=["order_date"])
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
sales["quarter"] = "Q" + sales["order_date"].dt.quarter.astype(str)
q = sales.groupby("quarter").agg(orders=("order_id", "count"), revenue=("revenue", "sum"))
q["growth_%"] = (q["revenue"].pct_change() * 100).round(1)
q["revenue"] = (q["revenue"] / 1e5).round(2)
print(q.rename(columns={"revenue": "revenue_lakh"}))
top = (sales.groupby(["quarter", "product"])["revenue"].sum().reset_index()
.sort_values("revenue", ascending=False).groupby("quarter").head(1))
print(top.sort_values("quarter").to_string(index=False)) orders revenue_lakh growth_%
quarter
Q1 463 18.16 NaN
Q2 406 13.41 -26.2
Q3 442 13.81 3.0
Q4 689 23.41 69.5
quarter product revenue
Q1 Laptop 455852.50
Q2 Laptop 318206.05
Q3 Mobile Phone 363518.70
Q4 Mobile Phone 701427.70
🏅 Job skills unlocked today
✔ Summarise data with groupby + agg✔ Compare rows with their group via transform✔ Build Excel-style pivot tables✔ Create percentage crosstabs
Practice tasks
🏋️ Practice – do it yourself
Task 1: Average house price per city and furnishing type as a pivot table (houses dataset).
Show solution
Python
import pandas as pd
h = pd.read_csv("house_prices.csv")
print(pd.pivot_table(h, index="city", columns="furnishing", values="price_lakh", aggfunc="mean").round(1))furnishing Furnished Semi-Furnished Unfurnished
city
Bengaluru 70.7 65.9 66.7
Hyderabad 61.3 57.9 51.4
Jaipur 51.4 40.5 43.7
Lucknow 41.2 39.9 35.6
Pune 64.4 63.9 62.5
Task 2: Churn rate (%) per contract type and internet service (telecom data).
Show solution
Python
import pandas as pd
t = pd.read_csv("telecom_churn.csv")
t["churned"] = (t["churn"] == "Yes").astype(int)
print((t.pivot_table(index="contract", columns="internet_service", values="churned", aggfunc="mean") * 100).round(1))internet_service DSL Fiber
contract
Monthly 37.8 58.6
One year 9.6 19.7
Two year 4.4 14.6
Task 3: For each school type, show the % of students with an average of 75+ and the average study hours of 75+ vs others.
Show solution
Python
import pandas as pd
st = pd.read_csv("students_performance.csv")
st["top"] = st["average"] >= 75
print((st.groupby("school_type")["top"].mean() * 100).round(1))
print(st.groupby(["school_type", "top"])["study_hours"].mean().round(2).unstack())school_type
Government 21.1
Private 23.7
Name: top, dtype: float64
top False True
school_type
Government 2.48 4.28
Private 2.34 4.20
Quick quiz
🧠 Quick Quiz – test yourself
1. groupby("city")["sales"].sum() returns…
One value per group.
2. Which keeps the original number of rows?
transform broadcasts the group result back to every row.
3. margins=True in pivot_table adds…
An “All/Total” row and column.
4. crosstab(..., normalize="index") gives…
normalize="columns" would make each column sum to 1.
5. unstack() turns…
The inner index level becomes columns.
Interview corner
💼 Interview Corner
What is the difference between agg, apply and transform in groupby?
agg returns one row per group (reductions like sum, mean). transform returns a result aligned with the original rows (e.g. group mean on every row). apply is the most flexible – any function per group – but the slowest.
How is pivot_table different from pivot?
pivot only reshapes and fails if an index/column pair appears twice. pivot_table aggregates duplicates with aggfunc (mean by default) and can add totals with margins.
Summary
- groupby = split → apply → combine.
- agg with named outputs builds clean report tables.
- transform keeps every row – perfect for shares and comparisons.
- pivot_table and crosstab reproduce Excel pivots in one line.
➡️ Next
Day 14: combine tables with merge, join and concat – like SQL joins inside pandas.
Frequently Asked Questions
Why does my groupby result have the key as index?
That is the default. Use as_index=False or .reset_index() to get it back as a normal column.
Can I group by a computed value like month?
Yes – group by a Series: df.groupby(df["date"].dt.month) or create a month column first.
