Replace duplicate value in pandas column by other column value

user_9177379 0
Viewed 52

I'm using python 3.x

I have a pandas dataframe df, it looks like

Sl   Name  Name2
1    abc   abc e
2    pqr   lmn o
3    abc   abc e
4    abc   krs
5    cde   rmn

I would like to replace duplicate value in column name, starting from 2nd occurrence. So my final data frame will look like

   Sl   Name  Name2
    1    abc   abc
    2    pqr   lmn o
    3    abc e abc e
    4    krs   krs
    5    cde   rmn

Can you suggest me how to do this?

python
3 Answers

You can use np.where for this:

import pandas as pd
import numpy as np

df['Name'] = np.where(df['Name'].duplicated(),df['Name2'], df['Name'])

print(df)

   Sl   Name  Name2
0   1    abc  abc e
1   2    pqr  lmn o
2   3  abc e  abc e
3   4    krs    krs
4   5    cde    rmn
user_184706920

You need to add a count of occurrences of duplicate & then use ouroboros1 solution with one more condition in np.where to check if count is more than 1

import numpy as np

df['counts'] = df.groupby('Name', sort=False).cumcount() + 1
df

This will give output

sl  Name    Name2   counts
0   1   abc abc e   1
1   2   pqr lmn o   1
2   3   abc abc e   2
3   4   krs krs     1
4   5   cde rmn     1

Then apply where clause with counts added in where clause

import numpy as np
df['Name'] = np.where((df['Name'].duplicated()) & (df['counts']>1),df['Name2'], df['Name'])

print(df[['sl','Name','Name2']])This will give you the required output

print(df)
   sl   Name    Name2
0   1    abc    abc e
1   2    pqr    lmn o
2   3    abc e  abc e
3   4    krs    krs
4   5    cde    rmn
user_98053620

A longer-form solution that is easy to understand, though I haven't tested its performance, is to treat the name groups for "first" and "others" separately:

import pandas as pd
import numpy as np

df = pd.DataFrame(
    {
        'Name':  ('abc',   'pqr',   'abc',   'abc', 'cde'),
        'Name2': ('abc e', 'lmn o', 'abc e', 'krs', 'rmn'),
    },
    index=pd.Index(np.arange(1, 6), name='Sl'),
)

grouped = df.assign(Sl=df.index).groupby('Name').Sl
counts = grouped.count()
dupe_names = counts[counts > 1].index

# First row in each non-singleton group
first_sl = grouped.first()[dupe_names]
df.loc[first_sl, 'Name2'] = df.loc[first_sl, 'Name']

# Other rows in each non-singleton group
others = (
    (~df.index.isin(first_sl)) &
    (df.Name.isin(dupe_names))
)
df.loc[others, 'Name'] = df.loc[others, 'Name2']

print(df)
user_3137680
Related