Match id in multiple groups in python

user_4257964 0
Viewed 129

I have a dataframe that looks like below

Group ID
1 AAA
1 BBB
1 CCC
2 AAA
2 DDD
2 CCC
3 AAA
3 GGG
3 TTT

Here i want to find the number of ids that are present in "group 1 only", "group 1 and 2", "group 1, 2 and 3".

I want the final table to look like below

Group Count
1 3
2 2
3 1

This is just an example table but i have 10 groups and millions of rows of data like this and i need an efficient way to calculate the same.

pandas-groupby pandas python
1 Answers

Try with crosstab then cumsum

pd.crosstab(df.Group,df.ID).cumsum().eq([1,2,3],axis=0).sum(1).reset_index(name='count')
Out[70]: 
   Group  count
0      1      3
1      2      2
2      3      1
user_79645270
Related