create a new column which is a value_counts of another column in python

Question

I have a pandas datafram df that contains a column say x, and I would like to create another column out of x which is a value_count of each item in x.

Here is my approach

x_counts= []

for item in df['x']:
    item_count = len(df[df['x']==item])
    x_counts.append(item_count)
    
df['x_count'] = x_counts

This works but this is far inefficient. I am looking for a more efficient way to handle this. Your approach and recommendations are highly appreciated

score 1 · Answer 1 · answered Oct 01 '20 at 06:28

1

It sounds like you are looking for groupby function that you are trying to get the count of items in x There are many other function driven methods but they may differ in different versions. I suppose that you are looking to join the same elements and find their sum

df.loc[:,'x_count']=1 # This will make a new column of x_count to each row with value 1 in it 
aggregate_functions={"x_count":"sum"}
df=df.groupby(["x"],as_index=False,sort=False).aggregate(aggregate_functions) # as_index and sort functions will allow you to choose x separately otherwise it would conside the x column as index column

Hope it heps.

answered Oct 01 '20 at 06:28

Syed Bilal Ali

124
1
10

Why `sum` with `1` column? In my opinion overcomplicated here – jezrael Oct 01 '20 at 06:32
Check dupe for better solution, second answer. – jezrael Oct 01 '20 at 06:33
It is also wrong, OP need `transform` – jezrael Oct 01 '20 at 06:34
Yes I know that, I have tried searching over S/O when I was going through that issue nothing helped so I had to comeup with this, it's not recommended but can be helpful for someone new to build an understanding for groupby function – Syed Bilal Ali Oct 01 '20 at 06:35
groupby function dos not fill in the exact number of rows. its still doing value_counts and therefor not filling the rows – JA-pythonista Oct 01 '20 at 06:39

create a new column which is a value_counts of another column in python

1 Answers1