GroupBy Overview in Pandas
Learn how to divide a dataset into groups, calculate summaries for each group, and combine the results into meaningful reports.
1. Learning Objectives
- Understand the purpose of grouping data.
- Use
groupby()to group rows by a column. - Calculate group totals, averages, counts, minimums, and maximums.
- Group data using multiple columns.
- Apply multiple aggregation functions to each group.
- Understand the split-apply-combine workflow.
- Use GroupBy to answer practical business and student-performance questions.
2. What Is GroupBy?
GroupBy is a technique for organizing rows into categories and performing calculations separately for each category.
For example, a sales dataset might contain transactions from several cities. Instead of calculating one total for the entire dataset, we can calculate a separate total for Pune, Mumbai, and Nashik.
Questions GroupBy can answer
- What is the total revenue for each city?
- What is the average salary in each department?
- How many students belong to each class?
- Which product category has the highest average price?
- How many transactions occurred in each region?
GroupBy splits rows into categories, applies calculations, and combines the results.
3. The Split-Apply-Combine Process
GroupBy follows three important steps.
| Step | Meaning | Example |
|---|---|---|
| Split | Divide the rows into groups according to one or more keys. | Create a group for each city. |
| Apply | Perform a calculation or transformation on each group. | Calculate revenue totals. |
| Combine | Collect the group results into a final output. | Return a table of city totals. |
4. Create a Sample Dataset
We will use a sales dataset with cities, products, and revenue.
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 |
5. Group Data Using One Column
Use groupby() to group records by a column. The following
expression creates a GroupBy object grouped by city.
grouped = sales.groupby("City")
print(type(grouped))
The result is a GroupBy object. It represents the groups but does not automatically display a summary table.
groupby() alone does not
calculate a total or average. Follow it with an operation such as
sum(), mean(), size(), or
agg().
6. Calculate Total Revenue by City
To find the total revenue for each city, group by City
and sum the Revenue column.
city_revenue = sales.groupby("City")["Revenue"].sum()
print(city_revenue)
Expected output:
City Mumbai 32000 Nashik 53000 Pune 78000 Name: Revenue, dtype: int64
The calculation adds all revenue values belonging to the same city.
Return the result as a DataFrame
city_revenue = (
sales.groupby("City", as_index=False)["Revenue"]
.sum()
)
print(city_revenue)
Expected output:
City Revenue 0 Mumbai 32000 1 Nashik 53000 2 Pune 78000
The as_index=False argument keeps the grouping key as a
regular column instead of using it as the result index.
7. Calculate Average Revenue by City
Replace sum() with mean() to calculate the
average revenue per transaction in each city.
average_revenue = sales.groupby("City")["Revenue"].mean()
print(average_revenue)
Expected output:
City Mumbai 16000.0 Nashik 26500.0 Pune 26000.0 Name: Revenue, dtype: float64
Pune's average is 78,000 divided by three transactions, which equals 26,000. Nashik's average is 53,000 divided by two transactions, which equals 26,500.
8. Count Records in Each Group
There are two common ways to count grouped records. Use
size() to count rows in each group, or
count() to count non-missing values in a selected column.
Count rows with size()
transaction_count = sales.groupby("City").size()
print(transaction_count)
Expected output:
City Mumbai 2 Nashik 2 Pune 3 dtype: int64
Count non-missing revenue values
revenue_count = sales.groupby("City")["Revenue"].count()
print(revenue_count)
Because the current dataset contains no missing revenue values, both examples return the same counts. They can differ when the selected column contains missing data.
9. Find the Minimum and Maximum by Group
You can calculate the smallest and largest revenue values for each city.
city_minimum = sales.groupby("City")["Revenue"].min()
city_maximum = sales.groupby("City")["Revenue"].max()
print("Minimum revenue:")
print(city_minimum)
print("Maximum revenue:")
print(city_maximum)
Expected output:
Minimum revenue: City Mumbai 12000 Nashik 8000 Pune 10000 Name: Revenue, dtype: int64 Maximum revenue: City Mumbai 20000 Nashik 45000 Pune 50000 Name: Revenue, dtype: int64
10. Group by Multiple Columns
Grouping by multiple columns creates a separate group for each unique combination of grouping values.
For example, grouping by both City and Product
calculates results separately for each city-product combination.
result = (
sales.groupby(["City", "Product"])["Revenue"]
.sum()
)
print(result)
Expected output:
City Product
Mumbai Keyboard 20000
Mouse 12000
Nashik Laptop 45000
Mouse 8000
Pune Keyboard 18000
Laptop 50000
Mouse 10000
Name: Revenue, dtype: int64
The output uses a MultiIndex because two columns are used as grouping keys. Each combination has its own result.
Keep both grouping columns in the result
result = (
sales.groupby(
["City", "Product"],
as_index=False
)["Revenue"]
.sum()
)
print(result)
This returns a regular DataFrame with City,
Product, and Revenue columns.
11. Apply Multiple Aggregations
Use agg() to calculate several statistics for each group
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
This creates a compact report showing total revenue, average revenue, minimum revenue, maximum revenue, and the number of non-missing revenue values for each city.
Aggregate multiple numeric columns
summary = sales.groupby("City").agg({
"Revenue": ["sum", "mean"],
"Units": ["sum", "max"]
})
print(summary)
The output has hierarchical column labels because each original column has more than one requested statistic. This format is useful for multi-metric reporting.
12. GroupBy with a Named Aggregation
Named aggregation lets you assign clear names to output columns. This is particularly useful when preparing a report for another application or exporting a clean summary.
summary = sales.groupby("City").agg(
TotalRevenue=("Revenue", "sum"),
AverageRevenue=("Revenue", "mean"),
TotalUnits=("Units", "sum"),
TransactionCount=("Product", "count")
)
print(summary)
Expected output:
TotalRevenue AverageRevenue TotalUnits TransactionCount City Mumbai 32000 16000.0 22 2 Nashik 53000 26500.0 12 2 Pune 78000 26000.0 24 3
Each named output column uses a tuple containing the source column and aggregation function. This produces readable column names without hierarchical output columns.
13. GroupBy and Missing Values
Missing values in a grouping key are generally excluded from groups
by default. Use dropna=False if you need a separate group
for missing grouping keys.
data = pd.DataFrame({
"City": ["Pune", "Mumbai", None, "Pune"],
"Revenue": [1000, 2000, 1500, 3000]
})
print(data.groupby("City")["Revenue"].sum())
By default, the missing city does not appear as a separate group.
Include missing grouping keys
result = data.groupby(
"City",
dropna=False
)["Revenue"].sum()
print(result)
The result includes a missing-key group representing the record whose city is not available. This can be useful for identifying incomplete records rather than silently excluding them from a report.
dropna in groupby() controls missing grouping
keys. Missing values in the aggregated column are handled according
to the selected aggregation function.
14. Sort Grouped Results
After calculating group summaries, you can sort the results to identify the highest or lowest categories.
summary = (
sales.groupby("City")["Revenue"]
.sum()
.sort_values(ascending=False)
)
print(summary)
Expected output:
City Pune 78000 Nashik 53000 Mumbai 32000 Name: Revenue, dtype: int64
Pune has the highest total revenue in this dataset, followed by Nashik and Mumbai.
15. Practical Example: Student Performance by Class
Pradnya is preparing a student report and wants to compare average marks and total study hours across classes.
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"),
TotalStudyHours=("StudyHours", "sum"),
StudentCount=("Name", "count")
)
print(summary)
Expected output:
AverageMarks HighestMarks TotalStudyHours StudentCount Class A 88.333333 92 12 3 B 78.333333 90 8 3
Class A has a higher average mark and more total study hours in this example. These are descriptive results; they do not by themselves establish that study hours caused the difference in marks.
16. Important GroupBy Methods
| Method | Purpose | Example |
|---|---|---|
sum() |
Calculates group totals. | sales.groupby("City")["Revenue"].sum() |
mean() |
Calculates group averages. | sales.groupby("City")["Revenue"].mean() |
count() |
Counts non-missing values per group. | sales.groupby("City")["Revenue"].count() |
size() |
Counts rows per group. | sales.groupby("City").size() |
min() |
Finds the minimum within each group. | sales.groupby("City")["Revenue"].min() |
max() |
Finds the maximum within each group. | sales.groupby("City")["Revenue"].max() |
agg() |
Applies one or more aggregation functions. | sales.groupby("City").agg(...) |
transform() |
Returns results aligned to the original rows. | sales.groupby("City")["Revenue"].transform("sum") |
filter() |
Keeps or removes entire groups based on a condition. | grouped.filter(...) |
The next lessons explore these operations in more detail, including aggregation, filtering groups, and transformations.
17. Common Mistakes
Mistake 1: Calling groupby() without an operation
groupby() creates a grouped object. Add an operation
such as sum() or agg() to calculate a result.
Mistake 2: Confusing size() and count()
size() counts rows in each group. count()
counts non-missing values in the selected column.
Mistake 3: Grouping by the wrong columns
Check whether you need a group per city, per product, or per city-product combination.
Mistake 4: Expecting all columns in the result
Aggregation returns the grouping keys and the selected summaries, not necessarily every original column.
Mistake 5: Ignoring missing grouping keys
If missing categories matter to your analysis, consider
dropna=False and inspect the resulting missing-key group.
18. Revision Questions
- What is the purpose of
groupby()? - Explain the split-apply-combine process.
- How do you calculate total revenue for each city?
- What is the difference between
size()andcount()? - How do you group data by two columns?
- Why would you use
agg()aftergroupby()? - What does
as_index=Falsedo? - How can you include missing grouping keys?
- How do you sort a grouped revenue summary from highest to lowest?
19. Hands-On Exercises
Exercise 1: Sales by City
Create a sales DataFrame with city, product, and revenue columns.
- Calculate total revenue for each city.
- Calculate average revenue for each city.
- Sort cities by total revenue in descending order.
Exercise 2: Department Salary Report
Create an employee DataFrame containing department, name, and salary.
- Calculate the average salary by department.
- Find the highest salary in each department.
- Count employees in each department.
Exercise 3: Group by Multiple Columns
Group a sales dataset by both city and product. Calculate total revenue for every combination.
Exercise 4: Multiple Aggregations
Use agg() to calculate total revenue, average revenue,
minimum revenue, and maximum revenue for each city.
Exercise 5: Missing Grouping Keys
Add a row with a missing city. Compare the grouped output with
the default behavior and with dropna=False.
20. Quick Knowledge Check
Question 1
Which method creates groups based on column values?
- A.
groupby() - B.
sort_values() - C.
reset_index()
Answer: A. groupby()
Question 2
Which method counts rows in each group?
- A.
mean() - B.
size() - C.
idxmax()
Answer: B. size()
Question 3
Which method is suitable for several statistics per group?
- A.
agg() - B.
rename() - C.
drop_duplicates()
Answer: A. agg()
Question 4
What does grouping by City and Product create?
- A. One group for the entire DataFrame
- B. A group for each unique city-product combination
- C. A group for every numeric column
Answer: B. A group for each unique city-product combination
Summary
GroupBy is a powerful Pandas technique for summarizing data by categories. It follows the split-apply-combine process: divide the rows into groups, perform calculations, and combine the results.
Use groupby() with functions such as sum(),
mean(), min(), max(),
size(), count(), and agg().
Grouping by multiple columns creates a separate group for every unique
combination of the selected keys.
In the next lesson, you will explore GroupBy aggregation in greater depth, including named aggregations and building clean summary reports.