Remove/Drop duplicates EXCEPT where row contains certain string

user_11633491 0
Viewed 557

I need to drop all duplicates using Pandas, except for the ones where the cell contains a certain string.

Given that DF is:

NAME     ID        
Joe      110
Joe      123
Joe     PENDING
Mary    PENDING
Mary     110
Justin   123

I need to keep rows where 'ID' is PENDING, and at the same time drop the rest of the duplicates.

Desired output looks like this:

NAME     ID        
Joe      110
Joe      123
Joe     PENDING
Mary    PENDING
pandas duplicates python-3.x python
3 Answers

You could use duplicated:

import pandas as pd

data = [['Joe', 110],
        ['Joe', 123],
        ['Joe', 'PENDING'],
        ['Mary', 'PENDING'],
        ['Mary', 110],
        ['Justin', 123]]

df = pd.DataFrame(data=data, columns=['NAME', 'ID'])

print(df[~df.duplicated('ID') | (df['ID'] == 'PENDING')])

As an alternative you could do:

print(df[df.ID.duplicated(keep='last') | df.ID.eq('PENDING')])

Output

   NAME       ID
0   Joe      110
1   Joe      123
2   Joe  PENDING
3  Mary  PENDING
user_40015920

Use Series.duplicated with inverting by ~ and chain by | for bitwise OR with compare ID for PENDING for mask for filter by boolean indexing:

df = df[~df['ID'].duplicated() | df['ID'].eq('PENDING')]
print (df)
   NAME       ID
0   Joe      110
1   Joe      123
2   Joe  PENDING
3  Mary  PENDING
user_29010020

Just add another column in case you wanted to keep both Name and ID unique:

import pandas as pd

data = [['Joe', 110],
        ['Joe', 123],
        ['Joe', 'PENDING'],
        ['Mary', 'PENDING'],
        ['Mary', 'PENDING'],
        ['es', 110],
        ['Joe', 110],
        ['Joe', 123]]

df = pd.DataFrame(data=data, columns=['NAME', 'ID'])

df = df[~df.duplicated(['ID', 'NAME']) | (df['ID'] == 'PENDING')]

print(df)
user_120770690
Related