Why advanced grouping matters in Pandas - See It in Action

Why advanced grouping matters

📖 Scenario: Imagine you work at a small online store. You have sales data for different products sold in different months. You want to understand how much money each product made in each month. This helps you see which products are popular and when.

🎯 Goal: You will create a small sales data table, set up a grouping configuration, group the data by product and month, and then calculate the total sales for each group. Finally, you will display the grouped sales totals.

📋 What You'll Learn

Create a pandas DataFrame called sales_data with columns product, month, and sales using the exact values provided.

Create a list called group_columns containing the column names 'product' and 'month'.

Use the groupby method on sales_data with group_columns and calculate the sum of sales for each group, storing the result in grouped_sales.

Print the grouped_sales DataFrame to show total sales by product and month.

💡 Why This Matters

🌍 Real World

Grouping data by multiple columns is common in business to analyze sales, customer behavior, or inventory by categories and time periods.

💼 Career

Data analysts and scientists use grouping to summarize and report data insights that help companies make decisions.

1

Create the sales data table

Create a pandas DataFrame called sales_data with these exact columns and values: product with ['Pen', 'Pen', 'Notebook', 'Notebook', 'Eraser', 'Eraser'], month with ['Jan', 'Feb', 'Jan', 'Feb', 'Jan', 'Feb'], and sales with [100, 150, 200, 250, 50, 60].

Pandas

import pandas as pd
# Create the sales_data DataFrame with the exact data
# Your code here

Hint

Use pd.DataFrame with a dictionary where keys are column names and values are lists of data.

2

Set up grouping columns

Create a list called group_columns containing the strings 'product' and 'month' to specify how to group the data.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'product': ['Pen', 'Pen', 'Notebook', 'Notebook', 'Eraser', 'Eraser'],
    'month': ['Jan', 'Feb', 'Jan', 'Feb', 'Jan', 'Feb'],
    'sales': [100, 150, 200, 250, 50, 60]
})
# Create the group_columns list with 'product' and 'month'
# Your code here

Hint

Make a list with the two column names as strings.

3

Group and sum sales

Use the groupby method on sales_data with group_columns and calculate the sum of the sales column for each group. Store the result in a variable called grouped_sales.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'product': ['Pen', 'Pen', 'Notebook', 'Notebook', 'Eraser', 'Eraser'],
    'month': ['Jan', 'Feb', 'Jan', 'Feb', 'Jan', 'Feb'],
    'sales': [100, 150, 200, 250, 50, 60]
})
group_columns = ['product', 'month']
# Group sales_data by group_columns and sum the sales
# Your code here

Hint

Use sales_data.groupby(group_columns)['sales'].sum() to get total sales per group.

4

Display the grouped sales totals

Print the grouped_sales DataFrame to show total sales for each product in each month.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'product': ['Pen', 'Pen', 'Notebook', 'Notebook', 'Eraser', 'Eraser'],
    'month': ['Jan', 'Feb', 'Jan', 'Feb', 'Jan', 'Feb'],
    'sales': [100, 150, 200, 250, 50, 60]
})
group_columns = ['product', 'month']
grouped_sales = sales_data.groupby(group_columns)['sales'].sum().reset_index()
# Print the grouped_sales DataFrame
# Your code here

Hint

Use print(grouped_sales) to show the result.

Practice

1.

What is the main benefit of using advanced grouping in pandas?

easy

A. It helps summarize data by multiple categories at once.

B. It makes the data disappear.

C. It deletes duplicate rows automatically.

D. It sorts data alphabetically.

Solution

1. Step 1: Understand grouping basics

   Grouping in pandas organizes data by categories to summarize it.

2. Step 2: Recognize advanced grouping advantage

   Advanced grouping uses multiple columns to get deeper insights from data.

3. Final Answer:

   It helps summarize data by multiple categories at once. -> Option A

4. Quick Check:

   Advanced grouping = summarize by multiple categories [OK]

Hint: Advanced grouping means grouping by more than one column [OK]

Common Mistakes:

• Thinking grouping deletes data
• Confusing grouping with sorting
• Assuming grouping removes duplicates

2.

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

easy

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

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

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

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

Solution

1. Step 1: Recall pandas groupby syntax

   The groupby method takes a list of column names inside square brackets.

2. Step 2: Check each option

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

3. Final Answer:

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

4. Quick Check:

   Correct groupby syntax uses list of columns [OK]

Hint: Use list inside groupby: df.groupby(['col1', 'col2']) [OK]

Common Mistakes:

• Using group instead of groupby
• Passing columns as separate arguments
• Using bitwise & inside list

3.

Given the DataFrame df:

   Team  Points  Assists
0  A     10      5
1  A     15      7
2  B     10      8
3  B     20      6
4  C     5       9

What is the output of df.groupby('Team').agg({'Points':'sum', 'Assists':'mean'})?

medium

A. Team A: Points=10, Assists=5; B: Points=10, Assists=8; C: Points=5, Assists=9

B. Team A: Points=15, Assists=7; B: Points=20, Assists=6; C: Points=5, Assists=9

C. Team A: Points=25, Assists=6.0; B: Points=30, Assists=7.0; C: Points=5, Assists=9.0

D. Team A: Points=25, Assists=12; B: Points=30, Assists=14; C: Points=5, Assists=9

Solution

1. Step 1: Sum Points by Team

   Team A: 10+15=25, Team B: 10+20=30, Team C: 5

2. Step 2: Calculate mean Assists by Team

   Team A: (5+7)/2=6.0, Team B: (8+6)/2=7.0, Team C: 9

3. Final Answer:

   Team A: Points=25, Assists=6.0; B: Points=30, Assists=7.0; C: Points=5, Assists=9.0 -> Option C

4. Quick Check:

   Sum and mean aggregation = Team A: Points=25, Assists=6.0; B: Points=30, Assists=7.0; C: Points=5, Assists=9.0 [OK]

Hint: Sum adds values, mean averages them per group [OK]

Common Mistakes:

• Adding means instead of averaging
• Mixing sum and mean results
• Ignoring grouping effect

4.

What is wrong with this code snippet?

df.groupby('Category').agg({'Sales': 'sum', 'Profit'})

Choose the best explanation.

medium

A. The agg method requires a list, not a dictionary.

B. The groupby method cannot take a string argument.

C. The DataFrame must be sorted before grouping.

D. The aggregation dictionary is missing a function for 'Profit'.

Solution

1. Step 1: Check agg dictionary format

   The agg method expects a dictionary with column names as keys and aggregation functions as values.

2. Step 2: Identify error in dictionary

   Here, 'Profit' is given without a function, causing a syntax error.

3. Final Answer:

   The aggregation dictionary is missing a function for 'Profit'. -> Option D

4. Quick Check:

   agg dict must have column:function pairs [OK]

Hint: agg needs column:function pairs, not just column names [OK]

Common Mistakes:

• Passing columns without functions in agg
• Thinking groupby needs sorted data
• Using wrong argument types in agg

5.

You have a sales DataFrame df with columns 'Region', 'Product', and 'Revenue'. You want to find the total revenue per region and the average revenue per product within each region. Which pandas code correctly achieves this advanced grouping?

hard

A. df.groupby(['Region', 'Product']).agg(TotalRevenue=('Revenue', 'sum')).groupby('Region').agg(TotalRevenue=('TotalRevenue', 'sum'), AvgRevenuePerProduct=('TotalRevenue', 'mean'))

B. df.groupby('Region').agg({'Revenue': 'sum', 'Product': 'mean'})

C. df.groupby('Region').agg(TotalRevenue=('Revenue', 'sum'), AvgRevenuePerProduct=('Revenue', 'mean'))

D. df.groupby(['Region', 'Product']).agg({'Revenue': ['sum', 'mean']})

Solution

1. Step 1: Calculate total revenue per region and product

   Grouping by both 'Region' and 'Product' and summing 'Revenue' gives total revenue per product in each region.

2. Step 2: Calculate average revenue per product within each region

   Then grouping by 'Region' again and averaging the summed revenues gives average revenue per product per region.

3. Step 3: Check options

   df.groupby(['Region', 'Product']).agg(TotalRevenue=('Revenue', 'sum')).groupby('Region').agg(TotalRevenue=('TotalRevenue', 'sum'), AvgRevenuePerProduct=('TotalRevenue', 'mean')) correctly chains these two groupbys to get both metrics separately.

4. Final Answer:

   df.groupby(['Region', 'Product']).agg(TotalRevenue=('Revenue', 'sum')).groupby('Region').agg(TotalRevenue=('TotalRevenue', 'sum'), AvgRevenuePerProduct=('TotalRevenue', 'mean')) -> Option A

5. Quick Check:

   Chained groupbys for multi-level aggregation = df.groupby(['Region', 'Product']).agg(TotalRevenue=('Revenue', 'sum')).groupby('Region').agg(TotalRevenue=('TotalRevenue', 'sum'), AvgRevenuePerProduct=('TotalRevenue', 'mean')) [OK]

Hint: Chain groupbys to get totals then averages per group [OK]

Common Mistakes:

• Trying to get sum and mean in one agg without grouping levels
• Using mean on non-numeric columns
• Not chaining groupbys for nested aggregation