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

Concept Flow - filter() for group-level filtering

Start with DataFrame

↓

Group data by key

↓

Apply filter function to each group

↓

Keep groups where filter returns True

↓

Combine filtered groups into new DataFrame

↓

Result: Filtered grouped data

We start with data, group it by a key, then apply a filter function to each group. Only groups passing the filter stay in the result.

Execution Sample

Data Analysis Python

import pandas as pd

df = pd.DataFrame({
    'Team': ['A', 'A', 'B', 'B', 'C'],
    'Points': [10, 15, 5, 7, 20]
})

filtered = df.groupby('Team').filter(lambda g: g['Points'].sum() > 20)
print(filtered)

This code groups data by 'Team' and keeps only teams with total Points over 20.

Execution Table

Step	Group	Group Points	Sum Points	Filter Condition (sum > 20)	Keep Group?
1	A	[10, 15]	25	25 > 20 is True	Yes
2	B	[5, 7]	12	12 > 20 is False	No
3	C	[20]	20	20 > 20 is False	No
4	Combine kept groups	-	-	-	Only group A kept

💡 All groups checked; only group A meets condition and is kept.

Variable Tracker

Variable	Start	After Group A	After Group B	After Group C	Final
filtered	empty	contains rows of Team A	no change	no change	DataFrame with Team A rows only

Key Moments - 2 Insights

Why does group C get removed even though it has 20 points?

Because the filter condition is sum > 20, not >= 20. Group C's sum is exactly 20, so it fails the condition (see execution_table row 3).

Does filter() keep individual rows or whole groups?

filter() keeps or removes entire groups based on the condition applied to the group as a whole, not individual rows (see execution_table rows 1-3).

Visual Quiz - 3 Questions

Look at the execution table, what is the sum of points for group B at step 2?

A 7

B 12

C 5

D 20

💡 Hint

Check the 'Sum Points' column in execution_table row 2.

At which step does the filter condition evaluate to False for group C?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Filter Condition' column for group C in execution_table.

If the filter condition changed to sum >= 20, which groups would be kept?

A Groups A and C

B Groups B and C

C Only group A

D All groups

💡 Hint

Compare sum points and condition in execution_table rows 1 and 3.

Concept Snapshot

filter() for group-level filtering:
- Use df.groupby('key').filter(func)
- func gets each group DataFrame
- Return True to keep group, False to drop
- Keeps whole groups, not rows
- Useful to filter groups by aggregate stats

Full Transcript

We start with a DataFrame and group it by a key column. Then, we apply a filter function to each group. This function checks a condition on the group, like if the sum of a column is greater than a value. Groups passing the condition are kept whole; others are removed. For example, grouping by 'Team' and filtering teams with total points over 20 keeps only those teams. The filter function returns True or False per group, not per row. This helps us keep or remove entire groups based on group-level statistics.

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