How do I find the count of a particular column, based on another column(date) using pandas?

Question

I have a dataframe with 3 columns, such as SoldDate,Model and TotalSoldCount. How do I create a new column, 'CountSoldbyMonth' which will give the count of each of the many models sold monthly?

Date        Model  TotalSoldCount
Jan 19        A          4
Jan 19        A          4
Jan 19        A          4
Jan 19        B          6
Jan 19        C          2
Jan 19        C          2
Feb 19        A          4
Feb 19        B          6
Feb 19        B          6
Feb 19        B          6
Mar 19        B          6
Mar 19        B          6

The new df should look like this.

Date      Model     TotalSoldCount     CountSoldbyMonth
Jan 19     A               4                    3
Jan 19     A               4                    3
Jan 19     A               4                    3
Jan 19     B               6                    1
Jan 19     C               2                    2
Jan 19     C               2                    2
Feb 19     A               4                    1
Feb 19     B               6                    3
Feb 19     B               6                    3
Feb 19     B               6                    3
Mar 19     B               6                    2
Mar 19     B               6                    2

I tried doing df['CountSoldbyMonth'] = df.groupby(['date','model']).totalsoldcount.transform('sum') but it is generating a different value.

Answer 1

Try this:

df['CountSoldbyMonth'] = df.groupby(['Date','Model']).transform('count')

You don't need to select the third column as you only need the counts of groupby items.

Answer 2

data['CountSoldbyMonth']= data.groupby(['Date','Model']).TotalSoldCount.transform('count') is working perfectly.