Pandas intersection of groups

Question

Hi I'm trying to find the unique Player which show up in every Team.

df =

Team    Player    Number
A       Joe       8
A       Mike      10
A       Steve     11
B       Henry     9
B       Steve     19
B       Joe       4
C       Mike      18
C       Joe       6
C       Steve     18
C       Dan       1
C       Henry     3

and the result should be:

result =

Team    Player    Number
A       Joe       8
A       Steve     11
B       Joe       4
B       Steve     19
C       Joe       6
C       Steve     18

since Joe and Steve are the only Player in each Team

Answer 1

You can use a GroupBy.transform to get a count of unique teams that each player is a member of, and compare this to the overall count of unique teams. This will give you a Boolean array, which you can use to filter your DataFrame:

df = df[df.groupby('Player')['Team'].transform('nunique') == df['Team'].nunique()]

The resulting output:

  Team Player  Number
0    A    Joe       8
2    A  Steve      11
4    B  Steve      19
5    B    Joe       4
7    C    Joe       6
8    C  Steve      18