Mathew K AnalyticsLesson 27 · Pandas Full Course
Pandas groupby Tutorial: Summarise Any Data with agg and transform | Pandas Course #27
In this lesson you'll be able to: Split a table into groups and summarise each one with groupby Count groups correctly, missing values included Build…
- CoursePandas Full Course
- Lesson27 of 65
- FormatJupyter notebook · 46 code cells
- Data1 dataset
What you'll learn
- Warm-up: one group at a time
- Split, apply, combine with groupby
- Count groups with size and count
- Several statistics at once with agg
- Group by two columns and reshape
- Keep every row with transform, drop groups with filter
- Sort and pick the top rows in each group
- Mini-project: did travelling with family help?
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- titanic.csv preview58.9 KB
📓 Full notebook
Download .ipynbPandas 27: Grouping Data with groupby() and Aggregations#
In this lesson you'll be able to:
- Split a table into groups and summarise each one with groupby
- Count groups correctly, missing values included
- Build summary tables with agg and named aggregation
- Group by two columns and reshape the result
- Keep every row with transform, and drop groups with filter Dataset: the Titanic passenger list (data/pandas/titanic.csv)
Warm-up: one group at a time#
Lesson 26 transformed and filtered data. Same skills, new dataset.
import pandas as pd
df = pd.read_csv("data/pandas/titanic.csv")
print(df.shape)
df[["Survived", "Pclass", "Sex", "Age", "Fare", "Embarked"]].head()
names = {1: "First", 2: "Second", 3: "Third"}
df["Class"] = df["Pclass"].map(names)
first = df[df["Class"] == "First"]
print(round(first["Survived"].mean(), 3))
Split, apply, combine with groupby#
Problem: compare one number across every group in a single line.
- Split the rows into groups by a key column
- Apply a calculation to each group
- Combine the answers into one result
df.groupby("Class")["Survived"].mean()
df.groupby("Class")["Survived"].sum()
by_sex = df.groupby("Sex")
print(by_sex.ngroups)
by_sex.size()
for name, group in by_sex:
avg_age = round(group["Age"].mean(), 1)
print(name, len(group), avg_age)
Quick check: which sex had the better survival rate, and by how much?
by_sex["Survived"].mean().round(3)
Common mistake: calling mean() on the whole grouped table
df.groupby("Class").mean()
cols = ["Survived", "Age", "Fare"]
df.groupby("Class")[cols].mean().round(2)
Count groups with size and count#
Problem: know how many rows each group has, and how many are missing.
df.groupby("Class")["Age"].agg(["size", "count"])
df.groupby("Class")["Ticket"].agg(["size", "nunique"])
df.groupby("Embarked").size()
Quick check: those ports add up to 889, but there are 891 passengers. Where did 2 go?
df.groupby("Embarked", dropna=False).size()
queens = df.groupby("Embarked").get_group("Q")
queens["Class"].value_counts()
ports = df.groupby("Class")["Embarked"]
ports.value_counts(normalize=True).round(2)
Several statistics at once with agg#
Problem: one tidy summary table instead of many separate lines.
fares = df.groupby("Class")["Fare"]
fares.agg(["mean", "median", "max"]).round(2)
df.groupby("Sex")["Age"].describe().round(1)
summary = df.groupby("Class").agg(
passengers=("PassengerId", "count"),
survival=("Survived", "mean"),
avg_age=("Age", "mean"),
avg_fare=("Fare", "mean"),
)
summary.round(2)
df.groupby("Class").agg({"Age": ["min", "max"], "Fare": "median"})
Common mistake: a column name with the wrong capital letter
df.groupby("Class").agg(avg_fare=("fare", "mean"))
df.groupby("Class").agg(avg_fare=("Fare", "mean")).round(2)
Your turn#
Find the youngest and oldest survivor in each class.
survivors = df[df["Survived"] == 1]
survivors.groupby("Class")["Age"].agg(["min", "max"])
Group by two columns and reshape#
Problem: see how class and sex combine.
rates = df.groupby(["Class", "Sex"])["Survived"].mean()
rates.round(3)
table = rates.unstack().round(3)
table
Common mistake: forgetting the list brackets around two keys
df.groupby("Class", "Sex").size()
df.groupby(["Class", "Sex"]).size()
Quick check: which class and sex had the lowest survival rate?
print(rates.idxmin())
print(round(rates.min(), 3))
import matplotlib.pyplot as plt
ax = table.plot(kind="bar", figsize=(7, 4), rot=0)
ax.set_title("Titanic survival rate by class and sex")
ax.set_xlabel("Passenger class")
ax.set_ylabel("Share who survived")
plt.show()
flat = df.groupby(["Class", "Sex"], as_index=False)["Survived"]
flat.mean().round(3)
Your turn#
Build a grid of survival rate by port (Embarked) and sex.
port_sex = df.groupby(["Embarked", "Sex"])["Survived"].mean()
port_sex.unstack().round(2)
Keep every row with transform, drop groups with filter#
Problem: compare each passenger with their own group.
grp_fare = df.groupby("Class")["Fare"]
df["class_fare"] = grp_fare.transform("mean")
df["above_avg"] = df["Fare"] > df["class_fare"]
df[["Class", "Fare", "class_fare", "above_avg"]].head().round(2)
df.groupby("Class")["above_avg"].mean().round(2)
Quick check: why not use plain mean() for that new column?
print(grp_fare.mean().shape)
print(grp_fare.transform("mean").shape)
class_median = df.groupby("Class")["Age"].transform("median")
df["Age"] = df["Age"].fillna(class_median)
print(df["Age"].isna().sum())
big = df.groupby("Embarked").filter(lambda g: len(g) > 100)
big["Embarked"].value_counts()
Your turn#
Keep only the classes whose survival rate is above 0.4.
def good_odds(g):
return g["Survived"].mean() > 0.4
kept = df.groupby("Class").filter(good_odds)
kept["Class"].value_counts()
Sort and pick the top rows in each group#
Problem: find the biggest or oldest inside every group, not just overall.
medians = df.groupby("Class")["Fare"].median().round(2)
medians.sort_values(ascending=False)
Common mistake: sorting the grouped object instead of the data
df.groupby("Class").sort_values("Fare")
by_fare = df.sort_values("Fare", ascending=False)
top2 = by_fare.groupby("Class").head(2)
top2[["Class", "Name", "Fare"]].round(2)
Your turn#
Show the oldest passenger in each class, with their name. Hint: idxmax.
oldest = df.groupby("Class")["Age"].idxmax()
df.loc[oldest, ["Class", "Name", "Age"]]
Mini-project: did travelling with family help?#
Family size = siblings or spouses + parents or children + yourself.
Plan: build the feature, group it, test hidden causes, conclude.
df["family_size"] = df["SibSp"] + df["Parch"] + 1
by_size = df.groupby("family_size")["Survived"]
by_size.agg(["size", "mean"]).round(3)
labels = ["Alone", "Small (2-4)", "Large (5+)"]
df["family"] = pd.cut(df["family_size"], bins=[0, 1, 4, 11],
labels=labels)
family = df.groupby("family", observed=True).agg(
passengers=("Survived", "size"),
survival=("Survived", "mean"),
)
family.round(3)
both = df.groupby(["Class", "family"], observed=True)["Survived"]
both.mean().unstack().round(2)
sex_fam = df.groupby(["Sex", "family"], observed=True)["Survived"]
sex_fam.agg(["size", "mean"]).round(2)
ax = family["survival"].plot(kind="bar", rot=0, figsize=(7, 4))
ax.set_title("Titanic survival rate by family size")
ax.set_xlabel("Travelling party")
ax.set_ylabel("Share who survived")
plt.show()
best = family["survival"].idxmax()
rate = family.loc[best, "survival"]
alone = family.loc["Alone", "survival"]
print(f"{best}: {rate:.0%} survived, alone: {alone:.0%}")
Recap#
- groupby splits rows, applies a calculation, and combines the results
- Select columns before mean(); text columns can't be averaged
- size counts rows, count skips gaps, dropna=False keeps missing keys
- agg with named pairs builds a clean summary table
- transform keeps every row; filter drops whole groups
Challenges#
- Average fare and survival rate by port of embarkation.
- Use agg to find the median fare and survival rate per family band.
- Use transform to flag passengers older than their class median.
What's next#
Lesson 28: Multi-level Grouping and Custom Aggregations Write your own aggregation functions and work with grouped indexes.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
26 minData Mining with Python: pandas & scikit-learn Crash Course
18 minAnalyse E-Commerce Orders with Python Pandas | Full Project Tutorial
Clean Stock Price Data in Python: Apple vs Tesla with Pandas | Data Analytics #11
36 min