2
$\begingroup$

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.

$\endgroup$
0
$\begingroup$

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.

$\endgroup$
0
$\begingroup$

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

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.