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