Aggregation-based features in Data Analysis Python - Step-by-Step Execution

Concept Flow - Aggregation-based features

Start with raw data

↓

Group data by key(s)

↓

Apply aggregation functions

↓

Create new features from aggregated results

↓

Merge aggregated features back to original data

↓

Use enhanced data for analysis or modeling

We start with raw data, group it by one or more keys, apply aggregation functions like sum or mean, create new features from these results, and merge them back to enrich the original data.

Execution Sample

Data Analysis Python

import pandas as pd

data = pd.DataFrame({
    'user': ['A', 'A', 'B', 'B', 'B'],
    'score': [10, 20, 10, 30, 50]
})

agg = data.groupby('user')['score'].mean().reset_index(name='avg_score')
data = data.merge(agg, on='user')

This code calculates the average score per user and adds it as a new feature to the original data.

Execution Table

Step	Action	Data State	Result
1	Create initial DataFrame	[{'user':'A','score':10}, {'user':'A','score':20}, {'user':'B','score':10}, {'user':'B','score':30}, {'user':'B','score':50}]	DataFrame with 5 rows
2	Group by 'user'	Groups: 'A' -> [10,20], 'B' -> [10,30,50]	Two groups formed
3	Calculate mean score per user	'A': (10+20)/2=15, 'B': (10+30+50)/3=30	Aggregation result: [{'user':'A','avg_score':15}, {'user':'B','avg_score':30}]
4	Merge aggregated avg_score back to original data	Original data + avg_score per user	DataFrame with new column 'avg_score' added
5	Final data	[{'user':'A','score':10,'avg_score':15}, {'user':'A','score':20,'avg_score':15}, {'user':'B','score':10,'avg_score':30}, {'user':'B','score':30,'avg_score':30}, {'user':'B','score':50,'avg_score':30}]	Data enriched with aggregation-based feature

💡 All rows processed and aggregation feature merged successfully

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
data	Empty	Original DataFrame with 5 rows	Same as step 2	Merged with avg_score column	DataFrame with 'user', 'score', 'avg_score' columns
agg	Not defined	Groups created	Aggregation result with avg_score per user	Same as step 3	Aggregation DataFrame with 'user' and 'avg_score'

Key Moments - 3 Insights

Why do we need to reset_index() after aggregation?

After groupby and aggregation, the result has 'user' as index. reset_index() turns 'user' back into a column so we can merge it with the original data (see execution_table step 3 and 4).

What happens if we merge without specifying 'on' parameter?

Without 'on', merge tries to join on all common columns, which may cause errors or unexpected results. Specifying 'on="user"' ensures correct matching (see execution_table step 4).

Does aggregation change the original data?

No, aggregation creates a new summarized DataFrame. We merge it back to add new features without losing original rows (see execution_table steps 3 and 4).

Visual Quiz - 3 Questions

Look at the execution table, what is the average score for user 'B' after step 3?

A 20

B 40

C 30

D 50

💡 Hint

Check the aggregation result in execution_table row 3 under 'Result'

At which step is the new feature 'avg_score' added to the original data?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look for the merge action in execution_table rows

If we skip reset_index() after aggregation, what likely happens during merge?

A Merge fails or merges incorrectly because 'user' is index, not column

B Merge works fine without issues

C Aggregation results are lost

D Original data is overwritten

💡 Hint

Refer to key_moments about reset_index() and execution_table steps 3 and 4

Concept Snapshot

Aggregation-based features:
- Group data by key(s) using groupby()
- Apply aggregation functions (mean, sum, count, etc.)
- Use reset_index() to convert group keys to columns
- Merge aggregated results back to original data
- Result: new features summarizing grouped info

Full Transcript

Aggregation-based features help us summarize data by groups. We start with raw data, group it by one or more keys, then calculate summary statistics like averages. These summaries become new features that describe each group. We use pandas groupby() to group data, then apply aggregation functions like mean(). After aggregation, we reset the index to keep group keys as columns. Finally, we merge these new features back to the original data so each row has extra information. This process enriches data for better analysis or modeling.

Practice

1. What is the main purpose of aggregation-based features in data analysis?

easy

A. To summarize data by groups using functions like sum or mean

B. To visualize data using charts and graphs

C. To clean missing values from the dataset

D. To split data into training and testing sets

Solution

1. Step 1: Understand aggregation-based features

   Aggregation-based features summarize data by groups using functions like sum, mean, or count.

2. Step 2: Compare with other options

   Visualization, cleaning, and splitting data are different tasks not related to aggregation features.

3. Final Answer:

   To summarize data by groups using functions like sum or mean -> Option A

4. Quick Check:

   Aggregation = summary by groups [OK]

Hint: Aggregation means grouping and summarizing data [OK]

Common Mistakes:

• Confusing aggregation with visualization
• Thinking aggregation cleans data
• Mixing aggregation with data splitting

2. Which of the following is the correct syntax to calculate the mean of column 'sales' grouped by 'region' in pandas?

easy

A. df.mean('sales').groupby('region')

B. df['sales'].groupby('region').mean()

C. df.groupby('region')['sales'].mean()

D. df.groupby('sales')['region'].mean()

Solution

1. Step 1: Identify correct groupby syntax

   The correct syntax groups by 'region' then selects 'sales' column and applies mean: df.groupby('region')['sales'].mean()

2. Step 2: Check incorrect options

   df.mean('sales').groupby('region') reverses order, df['sales'].groupby('region').mean() tries to group on a column series, df.groupby('sales')['region'].mean() groups by 'sales' instead of 'region'.

3. Final Answer:

   df.groupby('region')['sales'].mean() -> Option C

4. Quick Check:

   Group by region, then mean sales [OK]

Hint: Use df.groupby('col')['col2'].agg_func() [OK]

Common Mistakes:

• Calling mean before groupby
• Grouping on wrong column
• Applying groupby on a Series instead of DataFrame

3. Given the DataFrame:

import pandas as pd
data = {'team': ['A', 'A', 'B', 'B', 'B'], 'points': [10, 15, 10, 5, 20]}
df = pd.DataFrame(data)
result = df.groupby('team').agg({'points': 'sum'})
print(result)

What is the output?

medium

A. team\nA 25\nB 10

B. team\nA 10\nB 20

C. team\nA 15\nB 10

D. team\nA 25\nB 35

Solution

1. Step 1: Group data by 'team'

   Teams are 'A' and 'B'. 'A' has points 10 and 15, 'B' has 10, 5, and 20.

2. Step 2: Sum points per team

   Sum for 'A' = 10 + 15 = 25; Sum for 'B' = 10 + 5 + 20 = 35.

3. Final Answer:

   team A 25 B 35 -> Option D

4. Quick Check:

   Sum points by team = 25 and 35 [OK]

Hint: Sum points after groupby to get totals per team [OK]

Common Mistakes:

• Adding only first value per group
• Confusing sum with mean
• Misreading group labels

4. What is wrong with this code snippet to get the average 'score' per 'class' in pandas?

df.groupby('class').score.agg('average')

medium

A. The groupby column 'class' is misspelled

B. The aggregation function name 'average' is invalid; should be 'mean'

C. The code should use double brackets: df.groupby(['class'])

D. The code is correct and will run without error

Solution

1. Step 1: Check aggregation function name

   'average' is not a valid pandas aggregation function; the correct function for average is 'mean'.

2. Step 2: Verify other parts

   Grouping by 'class' and accessing 'score' column is correct syntax; double brackets are optional here.

3. Final Answer:

   The aggregation function name 'average' is invalid; should be 'mean' -> Option B

4. Quick Check:

   Use 'mean' not 'average' for average calculation [OK]

Hint: Use 'mean' for average in agg() [OK]

Common Mistakes:

• Using 'average' instead of 'mean'
• Thinking double brackets are mandatory
• Assuming code runs without error

5. You have a sales DataFrame with columns 'store', 'product', and 'revenue'. You want to create a new feature showing the average revenue per store and product combined. Which pandas code correctly creates this aggregation-based feature and adds it as a new column 'avg_rev' to the original DataFrame?

hard

A. df['avg_rev'] = df.groupby(['store', 'product'])['revenue'].transform('mean')

B. df['avg_rev'] = df.groupby('store')['revenue'].mean()

C. df['avg_rev'] = df.groupby('product')['revenue'].sum()

D. df['avg_rev'] = df.groupby(['store', 'product']).agg({'revenue': 'mean'})

Solution

1. Step 1: Understand the goal

   We want average revenue per store and product, and add it as a new column matching original rows.

2. Step 2: Use groupby with transform

   Using groupby on ['store', 'product'] and transform('mean') returns a Series aligned with original DataFrame, perfect for new column.

3. Step 3: Check other options

   df['avg_rev'] = df.groupby('store')['revenue'].mean() groups only by store, not product; df['avg_rev'] = df.groupby('product')['revenue'].sum() sums revenue, not average; df['avg_rev'] = df.groupby(['store', 'product']).agg({'revenue': 'mean'}) returns a DataFrame, not a Series aligned for assignment.

4. Final Answer:

   df['avg_rev'] = df.groupby(['store', 'product'])['revenue'].transform('mean') -> Option A

5. Quick Check:

   Use transform to add group average as new column [OK]

Hint: Use transform for group stats as new column [OK]

Common Mistakes:

• Using agg() which returns DataFrame, not Series
• Grouping by only one column instead of both
• Using sum instead of mean