GroupBy with transform for normalization in Pandas - Mini Project: Build & Apply

GroupBy with transform for normalization

📖 Scenario: You work in a company that collects sales data from different stores. Each store has sales numbers for several products. You want to compare sales within each store by normalizing the sales numbers.

🎯 Goal: You will create a pandas DataFrame with sales data, then use groupby and transform to normalize sales within each store. Finally, you will print the normalized sales.

📋 What You'll Learn

Create a pandas DataFrame named sales_data with columns 'Store' and 'Sales' using exact values

Create a variable mean_sales that stores the mean sales per store using groupby and transform

Create a new column 'Normalized_Sales' in sales_data by subtracting mean_sales from 'Sales'

Print the sales_data DataFrame to show the normalized sales

💡 Why This Matters

🌍 Real World

Normalizing sales data within groups helps compare performance fairly across stores by removing store-level differences.

💼 Career

Data analysts and data scientists often use groupby and transform in pandas to prepare and normalize data for analysis and reporting.

1

Create the sales data DataFrame

Create a pandas DataFrame called sales_data with two columns: 'Store' and 'Sales'. Use these exact values: 'Store': ['A', 'A', 'B', 'B', 'B', 'C'], 'Sales': [100, 150, 200, 210, 190, 300].

Pandas

import pandas as pd
# Create the sales_data DataFrame with Store and Sales columns
# Your code here

Hint

Use pd.DataFrame with a dictionary containing the two columns and their values.

2

Calculate mean sales per store using groupby and transform

Create a variable called mean_sales that stores the mean sales for each store. Use sales_data.groupby('Store')['Sales'].transform('mean') to calculate it.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Store': ['A', 'A', 'B', 'B', 'B', 'C'],
    'Sales': [100, 150, 200, 210, 190, 300]
})

# Calculate mean sales per store using groupby and transform
# Your code here

Hint

Use groupby('Store')['Sales'].transform('mean') to get the mean sales for each row's store.

3

Create normalized sales column

Create a new column in sales_data called 'Normalized_Sales' by subtracting mean_sales from the 'Sales' column.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Store': ['A', 'A', 'B', 'B', 'B', 'C'],
    'Sales': [100, 150, 200, 210, 190, 300]
})
mean_sales = sales_data.groupby('Store')['Sales'].transform('mean')

# Create 'Normalized_Sales' column by subtracting mean_sales from Sales
# Your code here

Hint

Subtract the mean_sales Series from the 'Sales' column and assign it to a new column.

4

Print the normalized sales DataFrame

Print the sales_data DataFrame to display the original sales and the normalized sales values.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Store': ['A', 'A', 'B', 'B', 'B', 'C'],
    'Sales': [100, 150, 200, 210, 190, 300]
})
mean_sales = sales_data.groupby('Store')['Sales'].transform('mean')
sales_data['Normalized_Sales'] = sales_data['Sales'] - mean_sales

# Print the sales_data DataFrame
# Your code here

Hint

Use print(sales_data) to show the DataFrame with normalized sales.

Practice

1. What does the transform() function do when used after groupby() in pandas?

easy

A. It applies a function to each group and returns a result aligned with the original data.

B. It removes duplicate rows from each group.

C. It aggregates groups into a single summary value.

D. It sorts the groups in ascending order.

Solution

1. Step 1: Understand groupby and transform roles

   groupby() splits data into groups, and transform() applies a function to each group but keeps the original data shape.

2. Step 2: Identify transform output alignment

   The output of transform() matches the original data's index, allowing normalized or transformed values to be added back easily.

3. Final Answer:

   It applies a function to each group and returns a result aligned with the original data. -> Option A

4. Quick Check:

   transform() returns aligned group results [OK]

Hint: transform() keeps original data shape after group operation [OK]

Common Mistakes:

• Thinking transform aggregates to one value per group
• Confusing transform with apply or aggregate
• Assuming transform changes data order

2. Which of the following is the correct syntax to normalize a column 'score' within groups defined by 'team' using transform() in pandas?

easy

A. df['score_norm'] = df.transform('team')['score'].groupby(lambda x: (x - x.mean()) / x.std())

B. df['score_norm'] = df.groupby('team')['score'].apply(lambda x: (x - x.mean()) / x.std())

C. df['score_norm'] = df.groupby('team').transform('score', lambda x: (x - x.mean()) / x.std())

D. df['score_norm'] = df.groupby('team')['score'].transform(lambda x: (x - x.mean()) / x.std())

Solution

1. Step 1: Check groupby and transform usage

   The correct syntax groups by 'team', selects 'score', then uses transform with a lambda for normalization.

2. Step 2: Validate lambda function and assignment

   The lambda subtracts the group mean and divides by group std, which is correct for normalization. The result is assigned to a new column.

3. Final Answer:

   df['score_norm'] = df.groupby('team')['score'].transform(lambda x: (x - x.mean()) / x.std()) -> Option D

4. Quick Check:

   Correct syntax uses groupby + transform + lambda [OK]

Hint: Use groupby('col')['col2'].transform(lambda) for normalization [OK]

Common Mistakes:

• Using apply instead of transform (changes output shape)
• Wrong order of method calls
• Incorrect lambda function syntax

3. Given the DataFrame:

import pandas as pd
df = pd.DataFrame({'team': ['A', 'A', 'B', 'B'], 'score': [10, 20, 30, 40]})
df['norm_score'] = df.groupby('team')['score'].transform(lambda x: (x - x.min()) / (x.max() - x.min()))
print(df['norm_score'].tolist())

What is the output list?

medium

A. [0.0, 1.0, 0.0, 1.0]

B. [0.5, 1.0, 0.5, 1.0]

C. [10, 20, 30, 40]

D. [0.0, 0.5, 0.0, 0.5]

Solution

1. Step 1: Calculate min-max normalization per group

   For team 'A': scores are 10 and 20. Normalized: (10-10)/(20-10)=0.0, (20-10)/(20-10)=1.0. For team 'B': scores 30 and 40 normalized similarly to 0.0 and 1.0.

2. Step 2: List normalized values in original order

   Values are [0.0, 1.0, 0.0, 1.0] matching the original row order.

3. Final Answer:

   [0.0, 1.0, 0.0, 1.0] -> Option A

4. Quick Check:

   Min-max normalization per group = [0.0,1.0,0.0,1.0] [OK]

Hint: Min-max normalize each group separately with transform [OK]

Common Mistakes:

• Normalizing across entire DataFrame, not per group
• Confusing min-max formula
• Mixing apply and transform outputs

4. Consider this code snippet:

df['norm'] = df.groupby('group')['value'].transform(lambda x: (x - x.mean()) / x.std())

It raises a ZeroDivisionError. What is the most likely cause?

medium

A. The groupby column 'group' does not exist in df.

B. The column 'value' contains strings instead of numbers.

C. One or more groups have all identical values causing std() to be zero.

D. transform() cannot be used with lambda functions.

Solution

1. Step 1: Understand ZeroDivisionError in normalization

   Normalization divides by standard deviation. If std is zero, division by zero error occurs.

2. Step 2: Identify cause in group data

   If a group has identical values, std() is zero, causing the error during transform.

3. Final Answer:

   One or more groups have all identical values causing std() to be zero. -> Option C

4. Quick Check:

   Zero std in group causes division error [OK]

Hint: Check for groups with zero std before normalizing [OK]

Common Mistakes:

• Assuming transform can't use lambda
• Blaming missing columns without error
• Ignoring data type issues

5. You have a DataFrame with sales data:

import pandas as pd
df = pd.DataFrame({
  'region': ['East', 'East', 'West', 'West', 'West'],
  'sales': [200, 300, 400, 400, 400]
})

You want to add a column 'sales_norm' that normalizes sales within each region using z-score normalization. However, some regions have zero standard deviation. How can you safely compute 'sales_norm' without errors and keep zero for those groups?

hard

A. Use apply instead of transform to avoid errors.

B. Use transform with a lambda that checks std and returns zero if std is zero.

C. Drop groups with zero std before normalization.

D. Replace sales with mean before normalization.

Solution

1. Step 1: Identify problem with zero std groups

   Groups with identical sales have std=0, causing division by zero in z-score.

2. Step 2: Use conditional lambda in transform

   Write a lambda that checks if std is zero; if yes, return zeros, else perform normalization.

3. Final Answer:

   Use transform with a lambda that checks std and returns zero if std is zero. -> Option B

4. Quick Check:

   Conditional transform avoids zero division [OK]

Hint: Check std inside lambda; return zeros if std=0 [OK]

Common Mistakes:

• Using apply which changes output shape
• Dropping data instead of handling zero std
• Ignoring zero std causing errors