How to drop duplicates but keep the rows if a particular other column is not null (Pandas)
you should sort values by the bank
column, with na_position='last'
(so the .drop_duplicates(..., keep='first')
will keep a value that is not na).
try this:
import pandas as pd
import numpy as np
df = pd.DataFrame({'firstname': ['foo Bar', 'Bar Bar', 'Foo Bar'],
'lastname': ['Foo Bar', 'Bar', 'Foo Bar'],
'email': ['Foo bar', 'Bar', 'Foo Bar'],
'bank': [np.nan, 'abc', 'xyz']})
uniq_indx = (df.sort_values(by="bank", na_position='last').dropna(subset=['firstname', 'lastname', 'email'])
.applymap(lambda s: s.lower() if type(s) == str else s)
.applymap(lambda x: x.replace(" ", "") if type(x) == str else x)
.drop_duplicates(subset=['firstname', 'lastname', 'email'], keep='first')).index
# save unique records
dfiban_uniq = df.loc[uniq_indx]
print(dfiban_uniq)
Output:
bank email firstname lastname
1 abc Bar Bar Bar Bar
2 xyz Foo Bar Foo Bar Foo Bar
(this is just your original code with .sort_values(by="bank", na_position='last')
at the beginning of uniq_indx = ...
)
Method 1: str.lower, sort & drop_duplicates
this works with many columns as well
subset = ['firstname', 'lastname']
df[subset] = df[subset].apply(lambda x: x.str.lower())
df.sort_values(subset + ['bank'], inplace=True)
df.drop_duplicates(subset, inplace=True)
firstname lastname email bank
1 bar bar bar Bar abc
2 foo bar foo bar Foo Bar xyz
Method 2: groupby, agg, first
does not generalize to many columns easily
df.groupby([df['firstname'].str.lower(), df['lastname'].str.lower()], sort=False)\
.agg({'email':'first','bank':'first'})\
.reset_index()
firstname lastname email bank
0 foo bar foo bar Foo bar xyz
1 bar bar bar Bar abc