Grouping by multiple columns in Pandas - Step-by-Step Execution

Concept Flow - Grouping by multiple columns

Start with DataFrame

↓

Select multiple columns to group by

↓

Apply groupby on these columns

↓

Aggregate or summarize each group

↓

Get grouped result with multi-level index or reset index

↓

Use or display grouped data

We start with a table, pick two or more columns to group by, then summarize data in each group.

Execution Sample

Pandas

import pandas as pd

data = {'City': ['NY', 'LA', 'NY', 'LA', 'NY'],
        'Year': [2020, 2020, 2021, 2021, 2020],
        'Sales': [100, 200, 150, 250, 130]}
df = pd.DataFrame(data)
grouped = df.groupby(['City', 'Year'])['Sales'].sum()

This code groups sales data by City and Year, then sums sales in each group.

Execution Table

Step	Action	Group Keys Created	Aggregation Result	Notes
1	Create DataFrame	-	DataFrame with 5 rows and 3 columns	Initial data loaded
2	Select columns 'City' and 'Year' for grouping	Unique pairs: ('LA',2020), ('LA',2021), ('NY',2020), ('NY',2021)	-	Groups identified by these pairs
3	Group data by ['City', 'Year']	Groups formed as above	-	Data grouped but not yet aggregated
4	Sum 'Sales' in each group	-	LA 2020: 200 LA 2021: 250 NY 2020: 230 NY 2021: 150	Sales summed per group
5	Result stored in 'grouped'	MultiIndex with City and Year	Aggregated sums shown	Ready for use or display
6	End	-	-	Grouping and aggregation complete

💡 All groups processed and sales summed; grouping by multiple columns done.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
df	Empty	DataFrame with 5 rows	Same DataFrame	Same DataFrame	Same DataFrame
grouped	Undefined	Undefined	Grouped object created	Aggregated Series with sums	Aggregated Series with sums

Key Moments - 3 Insights

Why does the grouped result have a MultiIndex?

Because grouping by multiple columns creates groups identified by combinations of those columns, pandas uses a MultiIndex to represent these pairs (see execution_table step 4).

What happens if we don't aggregate after grouping?

The groupby object holds groups but no summary; aggregation like sum() is needed to get meaningful results (see execution_table step 3 vs 4).

Can we reset the index after grouping?

Yes, calling reset_index() on the grouped result turns the MultiIndex back into columns for easier viewing or further processing.

Visual Quiz - 3 Questions

Look at the execution_table at step 4, what is the sum of Sales for City 'NY' in Year 2020?

A 100

B 230

C 150

D 130

💡 Hint

Check the Aggregation Result column in step 4 for 'NY 2020'.

At which step does the DataFrame get grouped but not yet aggregated?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Look for the step where groups are formed but no sum is calculated yet.

If we add another column 'Product' to group by, how would the number of groups change?

A Stay the same

B Decrease

C Increase

D Become zero

💡 Hint

Adding more columns to group by creates more specific groups (see concept_flow).

Concept Snapshot

Grouping by multiple columns in pandas:
- Use df.groupby(['col1', 'col2'])
- Groups are formed by unique pairs/triples...
- Aggregation like sum() summarizes each group
- Result has MultiIndex by default
- Use reset_index() to flatten the result

Full Transcript

We start with a DataFrame containing sales data by city and year. We choose to group by both 'City' and 'Year' columns. This creates groups identified by unique combinations of city and year. Then, we sum the sales within each group. The result is a DataFrame indexed by both city and year, showing total sales per group. This method helps us analyze data across multiple categories at once.

Practice

1. What does grouping by multiple columns in pandas allow you to do?

easy

A. Filter rows based on a single condition

B. Sort data alphabetically by one column

C. Analyze data by several categories at the same time

D. Change the data type of multiple columns

Solution

1. Step 1: Understand grouping concept

   Grouping by multiple columns means splitting data into groups based on combinations of those columns.

2. Step 2: Identify the purpose of grouping

   This helps analyze data by several categories at once, like sales by region and product.

3. Final Answer:

   Analyze data by several categories at the same time -> Option C

4. Quick Check:

   Grouping multiple columns = multi-category analysis [OK]

Hint: Grouping by list of columns splits data by combined categories [OK]

Common Mistakes:

• Thinking grouping changes data order
• Confusing grouping with filtering
• Assuming grouping changes data types

2. Which of the following is the correct syntax to group a DataFrame df by columns 'A' and 'B'?

easy

A. df.groupby(['A', 'B'])

B. df.groupby('A', 'B')

C. df.group_by(['A', 'B'])

D. df.groupby(['A' & 'B'])

Solution

1. Step 1: Recall pandas groupby syntax

   To group by multiple columns, pass a list of column names inside groupby().

2. Step 2: Check each option

   df.groupby(['A', 'B']) uses a list ['A', 'B'], which is correct syntax. Others have syntax errors or wrong method names.

3. Final Answer:

   df.groupby(['A', 'B']) -> Option A

4. Quick Check:

   List of columns inside groupby() = correct syntax [OK]

Hint: Use a list of column names inside groupby() [OK]

Common Mistakes:

• Passing columns as separate arguments instead of a list
• Using wrong method name like group_by
• Using bitwise operators inside list

3. Given the DataFrame df below:

   City  Product  Sales
0  NY    A        10
1  NY    B        20
2  LA    A        15
3  LA    B        25
4  NY    A        5

What is the output of df.groupby(['City', 'Product']).sum()?

medium

A. A DataFrame with total sales for each City and Product combination

B. A DataFrame with average sales for each City

C. A Series with sales summed only by City

D. An error because sum() cannot be used after groupby

Solution

1. Step 1: Group by City and Product

   The data is grouped by both City and Product columns, creating groups like (NY, A), (NY, B), (LA, A), (LA, B).

2. Step 2: Apply sum() aggregation

   Sum sales within each group: (NY, A) = 10+5=15, (NY, B) = 20, (LA, A) = 15, (LA, B) = 25.

3. Final Answer:

   A DataFrame with total sales for each City and Product combination -> Option A

4. Quick Check:

   Group by multiple columns + sum() = grouped sums [OK]

Hint: Sum after groupby sums values per group combination [OK]

Common Mistakes:

• Expecting average instead of sum
• Grouping by only one column
• Thinking sum() causes error

4. What is wrong with this code snippet?

df.groupby('City', 'Product').mean()

medium

A. groupby() requires column names as integers

B. mean() cannot be used after groupby

C. The columns 'City' and 'Product' do not exist

D. groupby() expects a single argument, so multiple columns must be passed as a list

Solution

1. Step 1: Check groupby argument format

   groupby() accepts one argument for columns; multiple columns must be inside a list.

2. Step 2: Identify error cause

   Passing 'City', 'Product' as separate arguments causes a TypeError.

3. Final Answer:

   groupby() expects a single argument, so multiple columns must be passed as a list -> Option D

4. Quick Check:

   Multiple columns in groupby() = list argument [OK]

Hint: Pass multiple columns as a list inside groupby() [OK]

Common Mistakes:

• Passing multiple columns as separate arguments
• Thinking mean() is invalid after groupby
• Assuming column names must be integers

5. You have a sales DataFrame df with columns Region, Category, and Revenue. You want to find the average revenue per region and category, but only for categories where the total revenue exceeds 1000. Which code correctly does this?

hard

A. df.groupby(['Region', 'Category']).Revenue.mean()[df.groupby(['Region', 'Category']).Revenue.mean() > 1000]

B. df.groupby(['Region', 'Category']).filter(lambda x: x['Revenue'].sum() > 1000).groupby(['Region', 'Category']).Revenue.mean()

C. df.groupby(['Region', 'Category']).Revenue.mean().filter(lambda x: x > 1000)

D. df.groupby(['Region', 'Category']).Revenue.sum().mean() > 1000

Solution

1. Step 1: Filter groups by total revenue

   Use groupby with filter and a lambda to keep only groups where sum of Revenue > 1000.

2. Step 2: Calculate average revenue per filtered group

   After filtering, group again by Region and Category and calculate mean Revenue.

3. Final Answer:

   df.groupby(['Region', 'Category']).filter(lambda x: x['Revenue'].sum() > 1000).groupby(['Region', 'Category']).Revenue.mean() -> Option B

4. Quick Check:

   Filter groups by sum, then compute mean [OK]

Hint: Use filter() on groupby to keep groups before aggregation [OK]

Common Mistakes:

• Trying to filter after mean instead of before
• Using boolean indexing on Series with mismatched index
• Calculating mean of sums instead of per group