Add column as count of occurence for each column element in pandas-CodePudding

I have a dataframe such as :

COL1 COL2 
A    Human 
A    Human 
A    Human 
A    Snake 
B    Snake 
B    Snake 
B    Snake 
B    Human 
C    Baboon 
C    Baboon
D    Baboon
E    Human 
E    Human 
E    Bird

And I would like to count the number of different COL2 values and add their count into new columns and that for each COL1 elements.

And I should then get:

COL1 COL2   Count_human Count_Snake Count_Baboon Count_Bird
A    Human  3           1           0            0
A    Human  3           1           0            0
A    Human  3           1           0            0
A    Snake  3           1           0            0
B    Snake  1           4           0            0
B    Snake  1           4           0            0
B    Snake  1           4           0            0
B    Human  1           4           0            0
C    Human  1           0           1            1  
C    Baboon 1           0           1            1  
C    Bird   1           0           1            1

CodePudding user response：

Use crosstab with DataFrame.join:

df = df.join(pd.crosstab(df['COL1'], df['COL2']).add_prefix('Count_'), on='COL1')
print (df)
   COL1    COL2  Count_Baboon  Count_Bird  Count_Human  Count_Snake
0     A   Human             0           0            3            1
1     A   Human             0           0            3            1
2     A   Human             0           0            3            1
3     A   Snake             0           0            3            1
4     B   Snake             0           0            1            3
5     B   Snake             0           0            1            3
6     B   Snake             0           0            1            3
7     B   Human             0           0            1            3
8     C  Baboon             2           0            0            0
9     C  Baboon             2           0            0            0
10    D  Baboon             1           0            0            0
11    E   Human             0           1            2            0
12    E   Human             0           1            2            0
13    E    Bird             0           1            2            0