Grouping Data in Pandas
Learn how to create groups using one or multiple columns, inspect groups, select grouped columns, and summarize records by category.
1. Learning Objectives
- Create groups using
groupby(). - Group records by one column or multiple columns.
- Understand how grouping keys determine group membership.
- Inspect group names and retrieve individual groups.
- Select columns before performing group-level calculations.
- Use
as_index,sort, anddropna. - Calculate totals, averages, and counts for groups.
- Understand common GroupBy mistakes and their solutions.
2. What Does Grouping Mean?
Grouping means organizing rows that share the same value in a chosen column. For example, a sales dataset may contain transactions from Pune, Mumbai, and Nashik. Grouping by city organizes transactions into three city-based groups.
Once the groups are created, you can calculate statistics for each group separately instead of calculating one statistic for the entire dataset.
Rows with the same city value belong to the same group, even when they are not adjacent in the original DataFrame.
3. Create a Sample DataFrame
We will use a sales dataset containing city, product, department, revenue, and units sold.
import pandas as pd
sales = pd.DataFrame({
"City": [
"Pune", "Mumbai", "Pune",
"Nashik", "Mumbai", "Pune",
"Nashik"
],
"Product": [
"Laptop", "Mouse", "Keyboard",
"Laptop", "Keyboard", "Mouse",
"Mouse"
],
"Department": [
"Technology", "Accessories", "Accessories",
"Technology", "Accessories", "Accessories",
"Accessories"
],
"Revenue": [50000, 12000, 18000, 45000, 20000, 10000, 8000],
"Units": [5, 12, 9, 4, 10, 10, 8]
})
print(sales)
| Index | City | Product | Department | Revenue | Units |
|---|---|---|---|---|---|
| 0 | Pune | Laptop | Technology | 50000 | 5 |
| 1 | Mumbai | Mouse | Accessories | 12000 | 12 |
| 2 | Pune | Keyboard | Accessories | 18000 | 9 |
| 3 | Nashik | Laptop | Technology | 45000 | 4 |
| 4 | Mumbai | Keyboard | Accessories | 20000 | 10 |
| 5 | Pune | Mouse | Accessories | 10000 | 10 |
| 6 | Nashik | Mouse | Accessories | 8000 | 8 |
4. Group by One Column
Pass the name of the column to groupby() to create groups
using that column.
grouped = sales.groupby("City")
print(type(grouped))
The expression creates a GroupBy object. It does not immediately print a grouped summary because the calculation has not yet been requested.
Calculate total revenue by city
result = sales.groupby("City")["Revenue"].sum()
print(result)
Expected output:
City Mumbai 32000 Nashik 53000 Pune 78000 Name: Revenue, dtype: int64
Calculate average revenue by 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
5. Group by Multiple Columns
Use a list of column names to group records by more than one key. Pandas creates a separate group for each unique combination of values.
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
For example, Pune has separate groups for Laptop, Keyboard, and Mouse. Mumbai has separate groups for Keyboard and Mouse.
City produces one
result per city. Grouping by City and Product
produces one result per city-product combination.
6. Keep Grouping Columns as Regular Columns
By default, grouping keys commonly become index labels in aggregation
results. Use as_index=False when you prefer a regular
tabular result.
result = sales.groupby(
"City",
as_index=False
)["Revenue"].sum()
print(result)
Expected output:
City Revenue 0 Mumbai 32000 1 Nashik 53000 2 Pune 78000
This is convenient when exporting results to CSV, creating charts, or passing summaries to other data-processing steps.
Multiple grouping keys with as_index=False
result = sales.groupby(
["City", "Product"],
as_index=False
)["Revenue"].sum()
print(result)
The result contains regular City, Product,
and Revenue columns instead of a MultiIndex.
7. Control the Order of Groups
The sort parameter controls whether grouping keys are
sorted in the output. By default, Pandas sorts group keys when
appropriate for the grouping dtype.
Sort group keys
result = sales.groupby(
"City",
sort=True
)["Revenue"].sum()
print(result)
Group keys appear in sorted order. For string city names, this normally means alphabetical order.
Keep groups in order of first appearance
result = sales.groupby(
"City",
sort=False
)["Revenue"].sum()
print(result)
Expected output:
City Pune 78000 Mumbai 32000 Nashik 53000 Name: Revenue, dtype: int64
Here, Pune appears first in the original dataset, followed by Mumbai
and Nashik. Setting sort=False avoids sorting the group
keys; it does not sort the rows within each group.
8. Inspect Group Names
The groups attribute provides a mapping from group keys
to the index labels of rows belonging to those groups.
grouped = sales.groupby("City")
print(grouped.groups)
The output is a dictionary-like mapping. Its values identify the original DataFrame index labels associated with each city.
For this dataset, the groups contain these row labels:
| Group key | Original row indices |
|---|---|
| Mumbai | 1, 4 |
| Nashik | 3, 6 |
| Pune | 0, 2, 5 |
Count groups
print(grouped.ngroups)
Expected output:
3
9. Retrieve a Specific Group
Use get_group() to retrieve the rows belonging to a
particular group key.
grouped = sales.groupby("City")
pune_sales = grouped.get_group("Pune")
print(pune_sales)
Expected output:
City Product Department Revenue Units 0 Pune Laptop Technology 50000 5 2 Pune Keyboard Accessories 18000 9 5 Pune Mouse Accessories 10000 10
The returned DataFrame contains only rows whose City value is Pune. Their original index labels are preserved.
KeyError.
10. Iterate Through Groups
You can loop through a GroupBy object. Each iteration provides the group key and the DataFrame containing that group's rows.
for city, group in sales.groupby("City"):
print("City:", city)
print(group[["Product", "Revenue"]])
print()
This is useful when you need to process each group separately, inspect records, or create a report for each category.
For straightforward totals or averages, built-in GroupBy aggregations are usually clearer and more efficient than writing a loop.
11. Select Columns Before Aggregating
Select the relevant column after grouping when you only need one statistic. This makes your intention explicit.
result = sales.groupby("Department")["Revenue"].sum()
print(result)
Expected output:
Department Accessories 68000 Technology 95000 Name: Revenue, dtype: int64
The expression groups rows by department and sums the Revenue values within each department.
Select several columns
result = sales.groupby("City")[["Revenue", "Units"]].sum()
print(result)
Expected output:
Revenue Units City Mumbai 32000 22 Nashik 53000 12 Pune 78000 24
12. Count Rows and Non-Missing Values
The size() and count() methods answer
different questions.
| Method | What it counts | Typical use |
|---|---|---|
size() |
Rows in each group. | Count transactions per city. |
count() |
Non-missing values in selected columns. | Count recorded revenue values per city. |
print(sales.groupby("City").size())
print(sales.groupby("City")["Revenue"].count())
Since the current Revenue column has no missing values, the two results contain the same counts. A dataset with missing Revenue values may produce different results.
13. Handle Missing Grouping Keys
A grouping column can contain missing values. By default,
groupby() generally excludes missing grouping keys.
Use dropna=False when you want missing keys to form
their own group.
data = pd.DataFrame({
"City": ["Pune", "Mumbai", None, "Pune", None],
"Revenue": [1000, 2000, 1500, 3000, 500]
})
print(data.groupby("City")["Revenue"].sum())
With the default behavior, only Pune and Mumbai appear in the result.
Include the missing-key group
result = data.groupby(
"City",
dropna=False
)["Revenue"].sum()
print(result)
Expected output:
City Mumbai 2000 Pune 4000 NaN 2000 Name: Revenue, dtype: int64
The missing-key group contains two rows with revenue values of 1500 and 500, giving a total of 2000.
14. Use GroupBy with Multiple Aggregations
Once grouping is understood, use agg() to produce
multiple summaries from each group.
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
Named aggregation makes the output easier to understand by giving each result column a descriptive name.
15. Common Grouping Parameters
| Parameter | Purpose | Example |
|---|---|---|
by |
Specifies the grouping key or keys. | groupby("City") |
as_index |
Controls whether group keys become the result index for applicable aggregations. | as_index=False |
sort |
Controls whether group keys are sorted. | sort=False |
dropna |
Controls whether missing group keys are excluded. | dropna=False |
observed |
Controls which categorical groups are returned for categorical grouping keys. | observed=True |
observed or other less common options.
16. Common Mistakes and Solutions
Mistake 1: Expecting groupby() to print a summary
Solution: Follow the grouping expression with an
operation such as sum(), mean(), or
agg().
Mistake 2: Grouping by the wrong key
Solution: Confirm whether the report needs one result per city, product, department, or combination of keys.
Mistake 3: Confusing size() and count()
Solution: Use size() for rows and
count() for non-missing values in a selected column.
Mistake 4: Ignoring missing grouping values
Solution: Consider dropna=False
when missing categories need to be included in the analysis.
Mistake 5: Assuming group order is the same as row order
Solution: Use sort to control the
order of group keys, and sort the aggregated result separately
when ranking the final statistics.
17. Revision Questions
- What is the purpose of the
groupby()method? - How do you group a DataFrame by two columns?
- What is the difference between a GroupBy object and an aggregated result?
- What does
as_index=Falsedo? - How can you retrieve all rows belonging to a specific group?
- What information is provided by the
groupsattribute? - How does
sort=Falseaffect group ordering? - What is the difference between
size()andcount()? - How can you include missing grouping keys?
18. Hands-On Exercises
Exercise 1: Group Sales by City
- Create a DataFrame with at least six sales records.
- Group the data by city.
- Calculate total revenue and transaction counts.
- Return the result with city as a regular column.
Exercise 2: Group by Department and Role
- Create an employee DataFrame with Department, Role, and Salary.
- Group by Department and Role.
- Calculate the average salary for each combination.
Exercise 3: Inspect Individual Groups
- Create a dataset containing records from Pune, Mumbai, and Nashik.
- Inspect the grouping keys and number of groups.
- Retrieve the Pune group using
get_group(). - Iterate over the groups and print each group's records.
Exercise 4: Missing Categories
- Add missing values to the grouping column.
- Compare the default grouping behavior with
dropna=False. - Explain how missing grouping keys affect the totals.
19. 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 argument keeps grouping keys as regular columns in applicable aggregation results?
- A.
sort=False - B.
as_index=False - C.
dropna=False
Answer: B. as_index=False
Question 3
Which method retrieves the rows for a specific group?
- A.
get_group() - B.
idxmax() - C.
nunique()
Answer: A. get_group()
Question 4
How can missing grouping keys be included?
- A.
dropna=True - B.
ignore_missing=True - C.
dropna=False
Answer: C. dropna=False
Summary
The Pandas groupby() method organizes rows into groups
based on one or more columns. After grouping, you can calculate
totals, averages, counts, minimums, maximums, and other statistics.
Use as_index=False for regular tabular output,
sort=False to avoid sorting group keys, and
dropna=False to include a group for missing grouping keys.
The groups attribute, ngroups, and
get_group() help you inspect and retrieve groups.
In the next lesson, you will study GroupBy aggregation in greater depth and learn to produce concise, multi-statistic summary reports.