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