GroupBy Aggregation in Pandas
Learn how to calculate multiple statistics for groups, use named aggregation, summarize multiple columns, and create clean reports from grouped data.
1. Learning Objectives
- Understand aggregation after grouping.
- Calculate totals, averages, counts, minimums, and maximums by group.
- Apply several aggregation functions in one operation.
- Use named aggregation to create readable output columns.
- Aggregate different columns using different functions.
- Group data using multiple keys.
- Sort and format aggregated results.
- Handle missing values and interpret grouped summaries.
2. What Is GroupBy Aggregation?
GroupBy aggregation combines two operations. First, Pandas separates rows into groups using one or more keys. Then, it calculates a summary for each group.
For example, a sales dataset can be grouped by city and aggregated to calculate total revenue, average revenue, and the number of transactions in each city.
Grouping defines categories; aggregation calculates statistics for each category.
3. Prepare a Sample Dataset
We will use a sales dataset throughout this lesson.
import pandas as pd
sales = pd.DataFrame({
"City": [
"Pune", "Mumbai", "Pune",
"Nashik", "Mumbai", "Pune",
"Nashik"
],
"Product": [
"Laptop", "Mouse", "Keyboard",
"Laptop", "Keyboard", "Mouse",
"Mouse"
],
"Revenue": [50000, 12000, 18000, 45000, 20000, 10000, 8000],
"Units": [5, 12, 9, 4, 10, 10, 8]
})
print(sales)
| Index | City | Product | Revenue | Units |
|---|---|---|---|---|
| 0 | Pune | Laptop | 50000 | 5 |
| 1 | Mumbai | Mouse | 12000 | 12 |
| 2 | Pune | Keyboard | 18000 | 9 |
| 3 | Nashik | Laptop | 45000 | 4 |
| 4 | Mumbai | Keyboard | 20000 | 10 |
| 5 | Pune | Mouse | 10000 | 10 |
| 6 | Nashik | Mouse | 8000 | 8 |
4. Aggregate with a Single Function
The simplest grouped aggregation applies one function to one selected column.
Total revenue per city
result = sales.groupby("City")["Revenue"].sum()
print(result)
Expected output:
City Mumbai 32000 Nashik 53000 Pune 78000 Name: Revenue, dtype: int64
Average revenue per city
result = sales.groupby("City")["Revenue"].mean()
print(result)
Expected output:
City Mumbai 16000.0 Nashik 26500.0 Pune 26000.0 Name: Revenue, dtype: float64
Number of transactions per city
result = sales.groupby("City").size()
print(result)
Expected output:
City Mumbai 2 Nashik 2 Pune 3 dtype: int64
5. Apply Multiple Functions with agg()
The agg() method allows you to calculate multiple statistics
for the same column in one operation.
summary = sales.groupby("City")["Revenue"].agg(
["sum", "mean", "min", "max", "count"]
)
print(summary)
Expected output:
sum mean min max count City Mumbai 32000 16000.0 12000 20000 2 Nashik 53000 26500.0 8000 45000 2 Pune 78000 26000.0 10000 50000 3
Each row represents a city, and each summary column represents a different aggregation function.
agg() when a report requires
several statistics. It is clearer than repeatedly grouping the same
data for every statistic.
6. Aggregate Different Columns with Different Functions
You can assign different functions to different columns by passing a
dictionary to agg().
summary = sales.groupby("City").agg({
"Revenue": ["sum", "mean"],
"Units": ["sum", "max"]
})
print(summary)
The output contains a hierarchical column structure because each source column has multiple statistics.
Expected values:
| City | Revenue sum | Revenue mean | Units sum | Units max |
|---|---|---|---|---|
| Mumbai | 32000 | 16000 | 22 | 12 |
| Nashik | 53000 | 26500 | 12 | 8 |
| Pune | 78000 | 26000 | 24 | 10 |
This approach is useful when different business metrics require different statistical summaries.
7. Named Aggregation
Named aggregation lets you specify meaningful output column names. It is especially useful for creating clean reports and exporting summary tables.
summary = sales.groupby("City").agg(
TotalRevenue=("Revenue", "sum"),
AverageRevenue=("Revenue", "mean"),
MinimumRevenue=("Revenue", "min"),
MaximumRevenue=("Revenue", "max"),
TotalUnits=("Units", "sum"),
TransactionCount=("Product", "count")
)
print(summary)
Expected output:
TotalRevenue AverageRevenue MinimumRevenue MaximumRevenue TotalUnits TransactionCount City Mumbai 32000 16000.0 12000 20000 22 2 Nashik 53000 26500.0 8000 45000 12 2 Pune 78000 26000.0 10000 50000 24 3
Each named aggregation uses this pattern:
OutputColumn=("SourceColumn", "aggregation_function")
For example, TotalRevenue=("Revenue", "sum") creates
a column named TotalRevenue containing the sum of
Revenue values in each group.
8. Group by Multiple Columns
Grouping by more than one key creates a separate group for every unique combination of the selected columns.
summary = sales.groupby(
["City", "Product"],
as_index=False
).agg(
TotalRevenue=("Revenue", "sum"),
TotalUnits=("Units", "sum")
)
print(summary)
Expected output:
City Product TotalRevenue TotalUnits 0 Mumbai Keyboard 20000 10 1 Mumbai Mouse 12000 12 2 Nashik Laptop 45000 4 3 Nashik Mouse 8000 8 4 Pune Keyboard 18000 9 5 Pune Laptop 50000 5 6 Pune Mouse 10000 10
This report shows each city's performance for each product. It can help identify which products contribute the most revenue in different locations.
9. Use as_index=False for a Flat Result
By default, group keys commonly become index labels in aggregated
results. Use as_index=False to keep the group keys
as ordinary columns.
Default behavior
result = sales.groupby("City")["Revenue"].sum()
print(result.index)
The city names are stored in the result's index.
Keep City as a column
result = sales.groupby(
"City",
as_index=False
)["Revenue"].sum()
print(result.columns.tolist())
Expected output:
['City', 'Revenue']
10. Sort Aggregated Results
After calculating group statistics, sort the results to rank categories.
summary = (
sales.groupby("City", as_index=False)
.agg(TotalRevenue=("Revenue", "sum"))
.sort_values("TotalRevenue", ascending=False)
)
print(summary)
Expected output:
City TotalRevenue 2 Pune 78000 1 Nashik 53000 0 Mumbai 32000
The cities are now ranked by total revenue from highest to lowest.
11. Handling Missing Values
Numeric aggregation methods such as sum() and
mean() generally skip missing values by default.
Missing grouping keys are a separate concern.
data = pd.DataFrame({
"City": ["Pune", "Pune", "Mumbai", "Mumbai"],
"Revenue": [1000, None, 2000, 3000]
})
summary = data.groupby("City").agg(
RevenueTotal=("Revenue", "sum"),
RevenueAverage=("Revenue", "mean"),
RevenueCount=("Revenue", "count"),
RowCount=("Revenue", "size")
)
print(summary)
Expected output:
RevenueTotal RevenueAverage RevenueCount RowCount City Mumbai 5000.0 2500.0 2 2 Pune 1000.0 1000.0 1 2
Pune has two rows but only one recorded revenue value. Therefore, its row count is two and its non-missing revenue count is one.
12. Practical Example: Student Performance Report
Samadhan is preparing a report that compares average marks, highest marks, and the number of students in each class.
students = pd.DataFrame({
"Name": [
"Samadhan", "Pradnya", "Samiksha",
"Aarav", "Meera", "Rohan"
],
"Class": ["A", "A", "B", "B", "A", "B"],
"Marks": [92, 85, 78, 67, 88, 90],
"StudyHours": [4, 3, 2, 2, 5, 4]
})
summary = students.groupby("Class").agg(
AverageMarks=("Marks", "mean"),
HighestMarks=("Marks", "max"),
LowestMarks=("Marks", "min"),
AverageStudyHours=("StudyHours", "mean"),
StudentCount=("Name", "count")
)
print(summary)
Expected output:
AverageMarks HighestMarks LowestMarks AverageStudyHours StudentCount Class A 88.333333 92 85 4.000000 3 B 78.333333 90 67 2.666667 3
The report shows differences between classes across several metrics. These summaries describe the dataset; they do not establish that study hours caused a difference in marks.
13. Common Aggregation Functions
| Function | Purpose | Typical example |
|---|---|---|
sum |
Calculates a total. | Total revenue by city. |
mean |
Calculates an average. | Average marks by class. |
median |
Calculates the middle value. | Median salary by department. |
min |
Finds the smallest value. | Lowest price by category. |
max |
Finds the largest value. | Highest marks by class. |
count |
Counts non-missing values. | Recorded transactions by city. |
std |
Calculates standard deviation. | Variation in marks by class. |
nunique |
Counts distinct non-missing values. | Distinct products sold by city. |
14. Common Mistakes and Solutions
Mistake 1: Calling groupby() without an aggregation
Solution: Add a method such as sum(),
mean(), or agg() to calculate the summary.
Mistake 2: Forgetting to select the intended column
Solution: Use an expression such as
df.groupby("City")["Revenue"].sum() when only revenue
should be aggregated.
Mistake 3: Confusing count() with size()
Solution: Remember that count()
excludes missing values in the selected column, whereas
size() counts rows in each group.
Mistake 4: Producing hierarchical columns unintentionally
Solution: Use named aggregation when you want descriptive, flat column names.
Mistake 5: Forgetting to sort the final summary
Solution: Apply sort_values() to
the aggregated result when you need a ranking.
15. Revision Questions
- What does aggregation mean in GroupBy operations?
- How do you calculate total revenue by city?
- How can you apply several functions to one grouped column?
- What is named aggregation?
- How can you aggregate different columns using different functions?
- What is the benefit of
as_index=False? - How can you rank groups by their aggregated revenue?
- What is the difference between
count()andsize()? - How are missing values handled in grouped aggregation?
16. Hands-On Exercises
Exercise 1: Sales Summary
- Create a sales DataFrame with city, product, revenue, and quantity.
- Calculate total and average revenue by city.
- Find the highest and lowest transaction values per city.
Exercise 2: Multiple Aggregations
- Use
agg()to calculate sum, mean, minimum, and maximum. - Apply different aggregation functions to different columns.
- Display the result as a clean summary table.
Exercise 3: Named Aggregation
- Create output columns named TotalRevenue, AverageRevenue, and TotalUnits.
- Group by city and generate the report.
- Sort the report by TotalRevenue in descending order.
Exercise 4: Student Performance
- Create a dataset containing class, student name, and marks.
- Calculate the average, highest, and lowest marks by class.
- Count students in each class.
17. Quick Knowledge Check
Question 1
Which method applies several aggregation functions?
- A.
agg() - B.
rename() - C.
drop_duplicates()
Answer: A. agg()
Question 2
Which option is used for named aggregation?
- A.
Total=("Revenue", "sum") - B.
Total="Revenue" - C.
sum("Total", "Revenue")
Answer: A. Total=("Revenue", "sum")
Question 3
Which argument keeps group keys as regular columns in applicable aggregation results?
- A.
sort=False - B.
as_index=False - C.
dropna=False
Answer: B. as_index=False
Question 4
Which method counts rows in each group?
- A.
size() - B.
mean() - C.
idxmax()
Answer: A. size()
Summary
GroupBy aggregation allows you to calculate statistics for categories
rather than for an entire dataset at once. Use sum(),
mean(), min(), max(),
count(), and agg() to summarize groups.
Named aggregation creates readable output columns, while grouping by
multiple keys produces summaries for unique combinations of values.
Use as_index=False for flat results and
sort_values() to rank the final report.
In the next lesson, you will learn how to filter entire groups using conditions, rather than filtering individual rows.