Pandas - Find unique values that show in every class group

user_7320521 0
Viewed 102

I have a python pandas table as follows:

class  |     id
_____________________
  A      12311894945
  A      12311760566
  A      12311433981
  A      12299990440       
  A      12291758744
  B      12311894945
  B      12311760566
  B      12311433981
  B      12299990440       
  B      12291758744
  C      12299990440       
  C      12291758744

I want to get the unique id values such that belong to every class group so that the expected result is as follows:

[12299990440, 12291758744]

I was thinking maybe a groupby class and some kind of intersection but I couldn't see how those two operations are done together in Pandas.

pandas python-3.x
4 Answers

Try:

uniques = df.groupby('id')['class'].nunique()

uniques[uniques.eq(df['class'].nunique()) ].index

Output:

Int64Index([12291758744, 12299990440], dtype='int64', name='id')
user_42384080

Using set intersection and reduce:

from functools import reduce

unique_by_group = df.groupby("class")["id"].apply(set)
reduce(lambda x, y: x & y, unique_by_group)

>>> {12291758744, 12299990440}
user_84711440

Just another alternative with pivotting:

s = pd.crosstab(df['id'],df['class']).astype(bool).all(1)
#s = df.pivot_table(index='id',columns='class',aggfunc=len).ge(1).all(1)
s[s].index
#Int64Index([12291758744, 12299990440], dtype='int64', name='id')
user_98406370

Here is another option:

uniques = df.groupby(['id','class']).size().unstack().fillna(0).astype(bool).all(axis=1)
uniques.loc[uniques].index.tolist()

or:

uniques = df.groupby('id')['class'].agg(set).eq(set(df['class']))
uniques.loc[uniques].index.tolist()
user_138021150
Related