How to indicate count of values in categorical column in Pandas, Python?-CodePudding

I have the following Pandas DataFrame:

ID CAT
1  A
1  B
1  A
2  A
2  B
2  A
1  B
1  A

I'd like to have a table that indicates the number of occurance per CAT values for each ID in different columns like this:

ID CAT_A_NUM CAT_B_NUM
1  3         2
2  2         1

I tried in many ways, like this one with pivot table, but unsuccessfully:

df.pivot_table(values='CAT', index='ID', columns='CAT', aggfunc='count')

CodePudding user response：

you can use crosstab():

df=pd.DataFrame(data={'ID':[1,1,1,2,2,2,1,1],'CAT':['A','B','A','A','B','A','B','A']})
final = pd.crosstab(df['ID'], df['CAT'])
final.columns=['CAT_A_NUM','CAT_B_NUM']
final

ID  CAT_A_NUM   CAT_B_NUM
1   3           2
2   2           1

CodePudding user response：

Probably you can use groupby unstack

df.groupby(["ID","CAT"]).size().unstack()

which gives