Count duplicates in column and add them to new col Pandas

user_14914490 0
Viewed 5108

im trying to count duplicates and then drop.cuplicates to get dataframe without duplicates but heve extra col in which we have number that represents how many duplicates there were

from

       id  letter
0  665639  AAAAAA
1  665639  AAAAAA
2  665639  AAAAAA
3  665639  AAAAAA
5  702090  BBBBBB
6  702092  CCCCCC

to

       id  letter   count
0  665639  AAAAAA     4
1  665639  AAAAAA     4
2  665639  AAAAAA     4
3  665639  AAAAAA     4
5  702090  BBBBBB     1
6  702092  CCCCCC     1

and after

df.drop.duplicates(subset=['id'] ,inplace=True)

       id  letter   count
0  665639  AAAAAA     4
5  702090  BBBBBB     1
6  702092  CCCCCC     1

my approach was:

cols = ['id','letter']
df['count'] = df.groupby(cols)['id'].transform('size')

but when im doing this i get dataframe that has only 1's in count

pandas python
3 Answers

I would first create the count of duplicates

df['Count'] = 1
df.groupby(['id','letter']).Count.count().reset_index()

And then drop the duplicates

df.drop_duplicates()
user_71424010 
transform operator recombines data after aggregation. Hence it returns all rows. Hence for all rows try:

df['count']=df.groupby('id')['letter'].transform('count')#all rows ;

direct use of a method in groupby summarises the columns which it is applied to. Since groupby returns a series, .to_frame() converts it to dataframe.If name is given the resulting series is renamed thus. For summarised outcome try:

df.groupby('id')['letter'].value_counts().to_frame('count')#summarised
user_89869750

Try this:

df = pd.DataFrame(
        {'A': [665639, 665639, 665639, 665639, 702090, 702092],
         'B': ['AAAAAA', 'AAAAAA', 'AAAAAA', 'AAAAAA', 'BBBBBB', 'CCCCCC']})
df.value_counts().reset_index(name='counts')

If you only need repeated values:

df.value_counts().reset_index(name='counts').query('counts > 1')

user_184453520
Related