Pandas Grouping and Aggregation
Grouping splits your data into groups and calculates a summary for each one, like total sales per city.
The Example Data
Example
python
import pandas as pd
df = pd.DataFrame({
"city": ["Karachi", "Lahore", "Karachi", "Islamabad", "Karachi", "Lahore"],
"product": ["Cover", "Charger", "Earbuds", "Cover", "Charger", "Earbuds"],
"qty": [2, 1, 1, 3, 2, 1],
"amount": [900, 1200, 3500, 1350, 2400, 3500]
})
print(df)Output
city product qty amount 0 Karachi Cover 2 900 1 Lahore Charger 1 1200 2 Karachi Earbuds 1 3500 3 Islamabad Cover 3 1350 4 Karachi Charger 2 2400 5 Lahore Earbuds 1 3500
How groupby Works
Grouping happens in three steps, often called split, apply, combine:
- Split the rows into groups by a column (for example, by city)
- Apply a calculation to each group (for example, sum)
- Combine the results into a new table
Example
python
print(df.groupby("city")["amount"].sum())Output
city Islamabad 1350 Karachi 6800 Lahore 4700 Name: amount, dtype: int64
Common Aggregation Functions
| Function | What it gives per group |
|---|---|
sum() | Total |
mean() | Average |
count() | Number of rows |
min() / max() | Smallest / largest |
median() | Middle value |
nunique() | Number of different values |
Example
python
print(df.groupby("product")["qty"].mean())
print(df.groupby("city")["product"].count())Output
product Charger 1.5 Cover 2.5 Earbuds 1.0 Name: qty, dtype: float64 city Islamabad 1 Karachi 3 Lahore 2 Name: product, dtype: int64
Several Statistics at Once With agg
Example
python
summary = df.groupby("city")["amount"].agg(["sum", "mean", "count"])
print(summary)Output
sum mean count city Islamabad 1350 1350.000000 1 Karachi 6800 2266.666667 3 Lahore 4700 2350.000000 2
You can also choose a different calculation for each column, and give the results clear names:
Example
python
report = df.groupby("city").agg(
orders=("product", "count"),
items=("qty", "sum"),
revenue=("amount", "sum")
)
print(report.sort_values("revenue", ascending=False))Output
orders items revenue city Karachi 3 5 6800 Lahore 2 2 4700 Islamabad 1 3 1350
The result of groupby uses the group column as its index. Add .reset_index() at the end to turn it back into a normal column, which is handy before saving to CSV.
Grouping by Two Columns
Example
python
print(df.groupby(["city", "product"])["amount"].sum())Output
city product
Islamabad Cover 1350
Karachi Charger 2400
Cover 900
Earbuds 3500
Lahore Charger 1200
Earbuds 3500
Name: amount, dtype: int64Counting Values
For a quick count of each value in one column, value_counts() is shorter than groupby:
Example
python
print(df["city"].value_counts())Output
city Karachi 3 Lahore 2 Islamabad 1 Name: count, dtype: int64
Pivot Tables
A pivot table puts one column's values down the side and another's across the top, like in Excel:
Example
python
table = df.pivot_table(index="city", columns="product", values="amount", aggfunc="sum", fill_value=0)
print(table)Output
product Charger Cover Earbuds city Islamabad 0 1350 0 Karachi 2400 900 3500 Lahore 1200 0 3500
fill_value=0 puts 0 where a city has no sales of that product.
Exercise
Exercise
What does df.groupby("city")["amount"].sum() give?