filter() for group-level filtering in Data Analysis Python

Introduction

We use filter() to keep or remove whole groups of data based on a rule. This helps us focus on groups that matter.

You want to keep only groups with more than 5 sales records.

You want to analyze customers who bought more than 3 different products.

You want to remove groups where the average score is below 70.

You want to keep only months where total revenue passed a target.

You want to filter groups of students who attended more than 80% of classes.

Syntax

Data Analysis Python

grouped_data.filter(function)

The function takes a group (a small table) and returns True or False.

If True, the whole group stays; if False, the group is removed.

Examples

Keep groups where the number of rows is more than 3.

Data Analysis Python

df.groupby('Category').filter(lambda x: len(x) > 3)

Keep teams with average score above 70.

Data Analysis Python

df.groupby('Team').filter(lambda x: x['Score'].mean() > 70)

Keep months where total sales are more than 1000.

Data Analysis Python

df.groupby('Month').filter(lambda x: x['Sales'].sum() > 1000)

Sample Program

This code creates a small table with teams and scores. It keeps only teams whose average score is above 70.

Data Analysis Python

import pandas as pd

data = {'Team': ['A', 'A', 'B', 'B', 'B', 'C', 'C'],
        'Score': [80, 85, 60, 70, 65, 90, 95]}
df = pd.DataFrame(data)

# Group by 'Team' and keep only teams with average score > 70
filtered_df = df.groupby('Team').filter(lambda x: x['Score'].mean() > 70)

print(filtered_df)

Important Notes

The filter() function works on groups, not individual rows.

Make sure your function returns a single True or False for each group.

Use filter() when you want to keep or drop entire groups, not just rows.

Summary

filter() helps keep or remove whole groups based on a rule.

It works by applying a function to each group and checking if it returns True or False.

Use it to focus your data on groups that meet your conditions.

Practice

1. What does the filter() function do when used with groupby in pandas?

easy

A. Filters individual rows inside groups without removing groups

B. Keeps or removes entire groups based on a condition function

C. Sorts groups by their size

D. Aggregates data by summing values in each group

Solution

1. Step 1: Understand filter() with groupby

   The filter() function applies a condition to each group and keeps or removes the whole group based on True or False.

2. Step 2: Differentiate from other functions

   Unlike aggregation or sorting, filter() does not change group content but decides if the group stays or goes.

3. Final Answer:

   Keeps or removes entire groups based on a condition function -> Option B

4. Quick Check:

   filter() = group-level keep/remove [OK]

Hint: filter() keeps or drops whole groups by condition [OK]

Common Mistakes:

• Thinking filter() works on individual rows only
• Confusing filter() with aggregation functions
• Assuming filter() sorts groups

2. Which of the following is the correct syntax to filter groups in a pandas DataFrame df grouped by column 'Category' where the group size is greater than 3?

easy

A. df.groupby('Category').filter(lambda x: len(x) > 3)

B. df.filter(groupby='Category', condition=lambda x: len(x) > 3)

C. df.groupby('Category').apply(lambda x: len(x) > 3)

D. df.groupby('Category').select(lambda x: len(x) > 3)

Solution

1. Step 1: Recall filter() syntax with groupby

   The correct syntax is df.groupby('Category').filter(function) where the function returns True or False per group.

2. Step 2: Check each option

   df.groupby('Category').filter(lambda x: len(x) > 3) matches the correct syntax. Options A, C, and D use invalid methods or parameters.

3. Final Answer:

   df.groupby('Category').filter(lambda x: len(x) > 3) -> Option A

4. Quick Check:

   Correct syntax uses groupby().filter() [OK]

Hint: Use groupby().filter(lambda x: condition) syntax [OK]

Common Mistakes:

• Using filter() directly on DataFrame without groupby
• Using apply() instead of filter() for filtering groups
• Passing wrong parameters to filter()

3. Given the following code, what will be the output?

import pandas as pd
data = {'Team': ['A', 'A', 'B', 'B', 'B', 'C'], 'Points': [10, 15, 5, 7, 8, 20]}
df = pd.DataFrame(data)
filtered = df.groupby('Team').filter(lambda x: x['Points'].mean() > 10)
print(filtered)

medium

A. Rows where Team is 'A' or 'C' only

B. Rows where Team is 'B' only

C. All rows from all teams

D. Empty DataFrame

Solution

1. Step 1: Calculate mean Points per Team

   Team A mean = (10+15)/2 = 12.5, Team B mean = (5+7+8)/3 = 6.67, Team C mean = 20

2. Step 2: Apply filter condition

   Keep groups with mean Points > 10, so Teams A and C remain, Team B is removed.

3. Final Answer:

   Rows where Team is 'A' or 'C' only -> Option A

4. Quick Check:

   Mean > 10 keeps A and C [OK]

Hint: Calculate group mean, keep groups meeting condition [OK]

Common Mistakes:

• Forgetting to calculate mean per group
• Assuming filter keeps rows, not groups
• Confusing group means with total sums

4. Identify the error in this code snippet that tries to filter groups with sum of 'Sales' greater than 100:

df.groupby('Region').filter(lambda x: x['Sales'].sum > 100)

medium

A. Lambda function should return False instead of True

B. filter() cannot be used with groupby

C. Missing parentheses after sum method call

D. Wrong column name 'Sales'

Solution

1. Step 1: Check the lambda function syntax

   The code uses x['Sales'].sum which is a method, but it lacks parentheses to call it.

2. Step 2: Correct the method call

   It should be x['Sales'].sum() to get the sum value for comparison.

3. Final Answer:

   Missing parentheses after sum method call -> Option C

4. Quick Check:

   sum() needs parentheses to execute [OK]

Hint: Remember to call sum() with parentheses [OK]

Common Mistakes:

• Using sum without parentheses
• Thinking filter() is invalid with groupby
• Assuming wrong column name causes error

5. You have a sales DataFrame df with columns 'Store', 'Month', and 'Revenue'. You want to keep only stores where the total revenue in any month exceeds 5000. Which code correctly filters these stores using filter()?

hard

A. df.groupby('Store').filter(lambda g: g['Revenue'].mean() > 5000)

B. df.groupby('Store').filter(lambda g: g['Revenue'].sum() > 5000)

C. df.groupby('Month').filter(lambda g: g['Revenue'].max() > 5000)

D. df.groupby('Store').filter(lambda g: g['Revenue'].max() > 5000)

Solution

1. Step 1: Understand the condition

   We want stores where revenue in any month exceeds 5000, so check max revenue per store.

2. Step 2: Analyze each option

   df.groupby('Store').filter(lambda g: g['Revenue'].max() > 5000) uses max() per store, matching the condition. df.groupby('Store').filter(lambda g: g['Revenue'].sum() > 5000) sums all revenue, which is different. df.groupby('Month').filter(lambda g: g['Revenue'].max() > 5000) groups by Month, not Store. df.groupby('Store').filter(lambda g: g['Revenue'].mean() > 5000) uses mean, which is not the condition.

3. Final Answer:

   df.groupby('Store').filter(lambda g: g['Revenue'].max() > 5000) -> Option D

4. Quick Check:

   max() per store matches condition [OK]

Hint: Use max() to check if any month exceeds threshold [OK]

Common Mistakes:

• Using sum() instead of max() for condition
• Grouping by wrong column
• Using mean() which may not meet condition