Get the count value of the column in the dataframe and set remaining rows as 1

user_14368172 0
Viewed 55

I have a dataframe with occ empty:

ID occ
a
a
b
a
b
c

Now I want to make another column that will count the number of occurence and only show the count in first row and rest should remain "1":

expected result

ID occ
a  3
a  1
b  2
a  1
b  1
c  1

In here 'a' is 3 times, 'b' is 2 times and 'c' is 1 times. All other rows of a and b are to be shown 1.

I got the count by :

df['ID'].value_counts()

but it throws an error when I try to put it in a dataframe using:

df['occ']=df['Value'].value_counts()

TypeError: unhashable type: 'list'

dataframe count python
1 Answers

While creating occ column, assign value 1 as initial value and then you can use pd.DataFrame.duplicated passing parameter keep='first' to create the masking for first occurrence of the values, and assign the counts:

df['occ']=1
df.loc[~df.duplicated(keep='first'), 'occ'] = df['ID'].value_counts().values

OUTPUT:

  ID  occ
0  a    3
1  a    1
2  b    2
3  a    1
4  b    1
5  c    1

PS: It may fail in scenario when first occurance of values in ID column are not in sorted form, you may want to sort ID column first in that scenario using df.sort_values(by=['ID'], inplace=True, ignore_index=True), or you can assign the counts selectively comparing the values in ID column

user_91363480
Related