基于DataFrame分组筛选同时包含John和Susan的完整A组

Hey there! Let's work through this problem together. First, let's clarify your original DataFrame (I've formatted it properly for clarity):

import pandas as pd

data = {
    'A': ['2002-01-12', '2002-01-12', '2002-01-12', '2002-01-15', '2002-01-15', '2002-01-15',
          '2002-01-20', '2002-01-20', '2002-02-20', '2002-02-20', '2002-02-24', '2002-02-24',
          '2002-02-24', '2002-02-24'],
    'B': ['Sarah', 'John', 'Susan', 'Danny', 'Peter', 'John', 'John', 'Hung', 'John', 'Susan',
          'Rebel', 'Susan', 'Mark', 'Susan'],
    'C': [39, 17, 30, 12, 25, 25, 16, 10, 20, 40, 40, 15, 38, 30]
}

df = pd.DataFrame(data)

Your goal is to keep entire groups of rows grouped by column A, where each group contains both John and Susan (regardless of other members in the group).

What's wrong with your original code? #

The code you tried:

df.groupby('A').apply(lambda x: ((df.B == x.John) & (df.B == x.Susan)))

has a few key issues:

1. x is the DataFrame for each group—x.John isn't a valid way to check if "John" exists in the group; you're trying to reference a column named John, which doesn't exist.
2. Using df.B references the entire original DataFrame's B column, not just the current group's values.
3. Logically, df.B == "John" & df.B == "Susan" will always be False—a single value can't be both names at once.

Correct Solutions #

We can solve this in two straightforward ways:

Method 1: Identify valid groups first, then filter

First, we'll find all values of A where the group contains both John and Susan, then use those values to filter the original DataFrame:

# Step 1: Find which A groups have both John and Susan
valid_groups = df.groupby('A')['B'].apply(lambda group: {'John', 'Susan'}.issubset(group.unique()))
valid_dates = valid_groups[valid_groups].index

# Step 2: Filter the original DataFrame to keep only these groups
result = df[df['A'].isin(valid_dates)]
print(result)

Method 2: Use transform for a more concise approach

We can add a temporary column to mark if each row's group meets the condition, then filter:

# Add a flag to each row indicating if its group has both John and Susan
df['has_both'] = df.groupby('A')['B'].transform(lambda group: ('John' in group.values) and ('Susan' in group.values))

# Filter rows where the flag is True, then drop the temporary column
result = df[df['has_both']].drop('has_both', axis=1)
print(result)

Either method will give you the exact output you're looking for:

A      B   C
0  2002-01-12  Sarah  39
1  2002-01-12   John  17
2  2002-01-12  Susan  30
6  2002-01-20   John  16
7  2002-01-20    Hung  10
8  2002-02-20   John  20
9  2002-02-20  Susan  40

Quick Notes #

• Method 1 is more efficient for large datasets since it first narrows down the valid groups before filtering.
• Method 2 is more readable and intuitive, as it directly marks each row's eligibility.

内容的提问来源于stack exchange，提问作者Tie_24