pandas filter value counts with more than one answer

user_253594 0
Viewed 1145

Say I have this dataframe:

df = pd.DataFrame([
    ('max', 'park', True),
    ('max', 'home', False),
    ('max', 'somewhere', True),
    ('spot', 'home', False),
    ('spot', 'park', False),
    ('bell', 'home', True),
    ('bell', 'park', True),
], columns='name,location,seen'.split(','))
df

df

How do I find everyone who has been seen in one place, but not another? An alternative way to phrase this question is how do I filter a MultiIndex series with more than one value in a given index.

df.groupby('name').seen.value_counts() 

name  seen 
bell  True     2
max   True     2
      False    1
spot  False    2
Name: seen, dtype: int64

I want to filter to see just max in this case. E.g. I would like this:

enter image description here

pandas
1 Answers

EDIT: @unutbu provides two elegant solutions in the comments:

# Option 1: groupby.transform
df.loc[df.groupby(['name'])['seen'].transform('nunique') > 1]

# Option 2: groupby.filter
df.groupby(['name']).filter(lambda x: x['seen'].nunique() > 1)

[Old answer follows]

Here's one way that uses a boolean mask to select names with two unique seen values:

mask = df.groupby('name').seen.nunique().eq(2)
names = mask[mask].index
df[df['name'].isin(names)]

  name   location   seen
0  max       park   True
1  max       home  False
2  max  somewhere   True
user_80087760
Related