Pandas Hwo To Groupby Create Other Columns By Counting Values Of Existing Columns

I got to know how to do this in R( How to make new columns by counting up an existing column), but I'd like also to know how it works in python as well. When the original table is

Solution 1:

Use melt with GroupBy.size and unstack:

df = (df.melt('userID')
        .groupby(['userID','variable','value'])
        .size()
        .unstack([1,2], fill_value=0))
#python 3.6+
df.columns = [f'{a}_{b}'for a, b in df.columns]
#python bellow#df.columns = ['{}_{}'.format(a,b) for a, b in df.columns]df = df.reset_index()
print (df)
RangeIndex(start=0, stop=7, step=1)
  userID  cat1_f  cat1_m  cat1_u  cat2_1  cat2_3  cat2_2
0      a       2       1       1       2       2       0
1      b       0       2       1       1       0       2

Alternative with crosstab:

df = df.melt('userID')
df = pd.crosstab(df['userID'], [df['variable'], df['value']])
df.columns = [f'{a}_{b}' for a, b in df.columns]
df = df.reset_index()