Group Data with groupby

You've cleaned your data, handled missing values, and maybe even created some new features — but now you're staring at thousands of rows and thinking, "What's the actual story here?" Raw numbers overwhelm, not inform. The moment you need to compare sales by region, average score by student, or revenue per product category, you need a way to group data with groupby for insights — an operation that turns a messy spreadsheet of individual records into a clean, aggregated summary that answers your business questions in seconds.

The problem this lesson solves

Suppose you have a CSV with every order your online store has ever received — hundreds of thousands of rows, each one a single transaction. You're asked: "Which product category generates the most revenue?" Scanning row by row is impossible. Filtering one category at a time with a loop is slow and error-prone. What you need is a way to split your data into meaningful groups and apply an aggregation to each group automatically — that's exactly what pandas groupby() does.

Without groupby(), you'd write brittle loops:

import pandas as pd

# Painful manual approach
df = pd.read_csv('orders.csv')
categories = df['category'].unique()
revenue_by_cat = {}
for cat in categories:
    cat_df = df[df['category'] == cat]
    revenue_by_cat[cat] = cat_df['revenue'].sum()

It works, but it's slow, verbose, and falls apart the moment your data gets messy. The pain is real: you spend time writing plumbing code instead of discovering insights. This lesson ends that struggle.

By the end, you'll be able to group data with groupby for insights in a few lines, apply multiple aggregations at once, and handle real-world edge cases like missing values and multi-level groupings — all while keeping your code clean and your analyses reproducible.

Core concept / mental model

The pandas groupby() operation follows a classic pattern you'll encounter in database languages like SQL: split-apply-combine.

• Split: Break the DataFrame into groups based on one or more columns. Think of it like sorting a deck of cards into suits — each suit becomes its own mini-deck.
• Apply: Run a function on each mini-deck independently — sum, mean, count, or even a custom function.
• Combine: Stitch the results back together into a single DataFrame or Series, now indexed by the group labels.

It's a powerful mental model because it applies beyond pandas — the same idea powers GROUP BY in SQL and group_by() in R's dplyr.

Key terms to remember: - Group key: The column(s) you group by. E.g., 'region' or ['region', 'product_type']. - Aggregation: The function that reduces each group to a single value. Common ones: sum(), mean(), count(), min(), max(). - Grouped object: The intermediate object returned by groupby() — it's lazy and doesn't compute anything until you call an aggregation.

💡 Pro tip: groupby() alone doesn't return a DataFrame — it returns a DataFrameGroupBy object. You must follow it with an aggregation method to get actual output.

How it works step by step

Let's walk through using groupby() from scratch, step by step.

Step 1: Understand your DataFrame

Start with a clean DataFrame. For this lesson, we'll use sales data with columns like 'region', 'product_type', and 'revenue'.

Step 2: Choose your group key

Decide which column(s) define the groups. For a single key:

# Group by a single column
grouped = df.groupby('region')

For multiple keys (creating hierarchical grouping):

# Group by region AND product_type
grouped = df.groupby(['region', 'product_type'])

Step 3: Apply an aggregation

Now the magic happens. Call an aggregation method on the grouped object:

# Sum of revenue per region
region_revenue = df.groupby('region')['revenue'].sum()

This computes the total revenue for each region and returns a Series indexed by region.

Step 4: Multiple aggregations with agg()

When you need different functions on different columns, use .agg() with a dictionary:

summary = df.groupby('region').agg({
    'revenue': 'sum',
    'units_sold': 'mean',
    'order_id': 'count'
})

Step 5: Inspect and reset your index

The result has the group key as the index. If you want it as a regular column, call reset_index():

final_df = region_revenue.reset_index()

This makes the output easier to save to CSV or plot directly.

Hands-on walkthrough

Let's put it all together with a realistic dataset. We'll create a DataFrame of online store orders and answer three burning business questions.

Setup

import pandas as pd

# Sample order data
data = {
    'order_id': [101, 102, 103, 104, 105, 106],
    'region': ['North', 'North', 'South', 'South', 'West', 'West'],
    'product_type': ['Electronics', 'Books', 'Electronics', 'Clothing', 'Books', 'Electronics'],
    'revenue': [1500, 25, 900, 120, 45, 2500],
    'units_sold': [2, 3, 1, 6, 2, 4]
}

df = pd.DataFrame(data)
print(df)

Expected output:

   order_id region product_type  revenue  units_sold
0       101  North   Electronics     1500           2
1       102  North         Books       25           3
2       103  South   Electronics      900           1
3       104  South      Clothing      120           6
4       105   West         Books       45           2
5       106   West   Electronics     2500           4

Question 1: Total revenue per region

region_revenue = df.groupby('region')['revenue'].sum()
print(region_revenue)

Expected output:

region
North    1525
South    1020
West     2545
Name: revenue, dtype: int64

Question 2: Average units sold per product type

avg_units = df.groupby('product_type')['units_sold'].mean()
print(avg_units)

Expected output:

product_type
Books         2.5
Clothing      6.0
Electronics   2.333333
Name: units_sold, dtype: float64

Question 3: Multi-level summary with agg()

summary = df.groupby(['region', 'product_type']).agg({
    'revenue': 'sum',
    'units_sold': 'mean',
    'order_id': 'count'
})
print(summary)

Expected output:

                      revenue  units_sold  order_id
region product_type                                
North  Books               25    3.000000         1
       Electronics       1500    2.000000         1
South  Clothing           120    6.000000         1
       Electronics        900    1.000000         1
West   Books               45    2.000000         1
       Electronics       2500    4.000000         1

This single call gives you a rich, multi-dimensional view of your data in one shot.

💡 Pro tip: Use .agg() with a list of functions to apply multiple aggregations to the same column: df.groupby('region')['revenue'].agg(['sum', 'mean', 'count']).

Compare options / when to choose what

There are several ways to group data beyond groupby(). Here's a quick comparison:

Choosing guide: - Use groupby() as your default: it's the most flexible and readable. - Use pivot_table() when you want a matrix with groups both on rows and columns — great for cross-tabulations. - Use resample() only when you need to aggregate by time frequencies like daily, monthly, or yearly.

Troubleshooting & edge cases

Group key with missing values

By default, groupby() drops rows where the group key is NaN. If you want to include them, use dropna=False:

df.groupby('region', dropna=False)['revenue'].sum()

Selecting columns after grouping

When you do df.groupby('region')['revenue'], you get a SeriesGroupBy. If you try to call .sum() on the full grouped object without selecting columns, you'll sum all numeric columns, which is often not what you want:

# Sums revenue AND units_sold — probably not intended
df.groupby('region').sum()

Always select the column(s) you need or use .agg() with explicit mappings.

Using .size() vs .count()

.size() counts all rows in each group, including those with NaN values. .count() counts only non-NaN entries in the selected column. Choose based on the meaning of your analysis.

Performance tips

For large DataFrames, groupby() is optimized in C, but avoid iterating over groups with .apply() unless necessary. Better to use built-in aggregations or agg() with named functions.

Wrong output: numeric columns summing unexpectedly

If you call .sum() on a grouped object with both revenue and units_sold, you'll get sums for both. If you need only one, select it first. Always validate the output columns.

What you learned & what's next

You can now group data with groupby for insights: you understand the split-apply-combine mental model, can apply single and multiple aggregations, know how to compare groupby() with pivot_table() and resample(), and can troubleshoot common pitfalls like missing keys and accidental column sums.

These skills are the backbone of exploratory data analysis. Up next in the track, you'll learn how to visualize these grouped summaries with Matplotlib and Seaborn — turning your grouped insights into persuasive charts that tell a story. You'll take region_revenue and plot a bar chart in under ten lines of code. That's the natural next step in your data analysis journey.