Groupby one column and split the count based on another column

Question

I have a dataframe like so:

ID    Category
1        A
1        B
1        A
1        B
2        A
2        C
2        C
2        A
2        D
3        E

How can I groupby the ID column and perform a count of each category?

I.E.

ID    A   B   C   D  E
1     2   2   0   0  0
2     2   0   2   1  0
3     0   0   0   0  1

I have tried df.groupby('ID').count() & df.groupby('ID')['Category']

`pd.crosstab(df.ID,df.Category,df.Category,aggfunc='count').fillna(0)` — Umar.H, Mar 19 '20 at 14:31

score 3 · Answer 1 · answered Mar 19 '20 at 14:31

This can be done with either groupby:

df.groupby('ID')['Category'].value_counts().unstack(fill_value=0)

or crosstab (shorter code but a bit slower)

pd.crosstab(df.ID, df.Category)

Output:

Category  A  B  C  D  E
ID                     
1         2  2  0  0  0
2         2  0  2  1  0
3         0  0  0  0  1

1 Answers1