Adding a column to df that contains count of a value of a different column in the df?

Question

I have a df below as:

Day 
Morning
Day
Night 
Night
Day
Morning
Day
Day

There are other columns in the this df and does not only contain the above column

when I run code below as:

df.groupby('day').count()

it outputs a df with the count of times each value for day column occurs in the other columns in the df ( its the same count number for each value in the day column)

How can I create a new column that lists those same counts for each value of the Day column?

Expected output:

 Day     New_Col
Morning    2
Day        4
Night      2
Night      2
Day        4
Morning    2
Day        4
Day        4

Thanks!

score 1 · Accepted Answer · answered Oct 23 '20 at 16:28

1

Use transform() if you want to map back:

df['New_Col'] = df.groupby('Day')['Day'].transform('count')

Or you can use map, and also, value_counts():

df['New_Col'] = df['Day'].map(df['Day'].value_counts())

Output:

       Day  New_Col
0  Morning        2
1      Day        4
2    Night        2
3    Night        2
4      Day        4
5  Morning        2
6      Day        4
7      Day        4

answered Oct 23 '20 at 16:28

Quang Hoang

146,074
10
56
74

Thank you very much, how come when I tried df.groupby('day').value_counts() it didnt work? @Quang Hoang – Chris90 Oct 23 '20 at 16:32
@Chris90 not `groupby().value_counts()` just `df['Day'].value_counts()`. – Quang Hoang Oct 23 '20 at 16:33
right sorry, and the best way to add a column is to map that back to the df correct? – Chris90 Oct 23 '20 at 16:36
1

@Chris90 either way is fine. – Quang Hoang Oct 23 '20 at 16:39

score 1 · Answer 2 · answered Oct 23 '20 at 16:31

1

Use df.groupby.transform('size'):

df['New_Col'] = df.groupby('Day')['Day'].transform('size')

answered Oct 23 '20 at 16:31

Mayank Porwal

33,470
8
37
58

Adding a column to df that contains count of a value of a different column in the df?

2 Answers2