Counting number of times each value occurs and groupby ID pandas-CodePudding

I have an excel file with data like:

I'm trying for output:

ID	a_count	b_count
1	2	1
2	1	1

Using pandas dataframes to store the columns and groupby function. But not achieving the desired result. I appreciate any help.

CodePudding user response：

Try this:

(df.groupby('ID')['Type'].value_counts()
.unstack()
.rename('{}_count'.format,axis=1)
.reset_index()
.rename_axis(None,axis=1))

CodePudding user response：

You need a crosstab:

(pd
 .crosstab(df['ID'], df['Type'])
 .add_suffix('_count')
 .reset_index().rename_axis(None, axis=1)
)

output:

   ID  a_count  b_count
0   1        2        1
1   2        1        1