How to temporarily convert pandas df to lower case for duplicates check? Then revert to normal afterwards

user_11222963 0
Viewed 135

I am dropping duplicates from a dataframe based on firstname, lastname, email and saving to a new df called df_unique.

I've converted everything to lower case and removed whitespace so the fields to increase the chances of a match. Note that index 0 and 2 are the same but won't be picked up without converting to lower. 

df = pd.DataFrame({'firstname':['foo Bar','Bar Bar','Foo Bar'],'lastname':['Foo Bar','Bar','Foo Bar'],'email':['Foo bar','Bar','Foo Bar']})

df

  firstname lastname    email
0   foo Bar  Foo Bar  Foo bar
1   Bar Bar      Bar      Bar
2   Foo Bar  Foo Bar  Foo Bar

#remove NAs
#convert to lower
#remove whitespace
#drop dupes


df_unique = (df.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']))


df_unique

  firstname lastname   email
0    foobar   foobar  foobar
1    barbar      bar     bar

However, the records are permanently lowercase now, and double names like Foo Bar are now foobar.

How can I do this without permanently changing the data?

I've tried using .copy() in the middle of all that but it just got too messy and broke things.

Is there a better way to do this?

edit:

With a bit of tweaking of @shaik moeeds answer below, I managed to get something working.

Here I get the index of unique records:

uniq_indx = (df.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'])).index

Then use this to index the DF and save the unique records:

df_unique = df.loc[uniq_indx]

I also wanted to keep the duplicate records, so used drop() and the index from above to remove the uniques:

df_duplicates = df.drop(uniq_indx)

This seems to work on checking the outputs, however if there are any mistakes please let me know.

pandas duplicates python
3 Answers

You can continue with your code, with small modification. Use the .index values of duplicates, to get original format.

Like this,

>>> df = pd.DataFrame({'firstname':['Foo Bar','Foo Bar','Bar Bar','Foo Bar'],
                       'lastname' :['Foo Bar','Foo Bar', 'Bar','Foo Bar'],
                       'email'    :['Foo Bar','Foo Bar', 'Bar','Foo Bar']})

>>> df.iloc[(df.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'])).index]

Output:

  firstname lastname    email
0   Foo Bar  Foo Bar  Foo Bar
2   Bar Bar      Bar      Bar
user_83537110

Look for the index values of your duplicates and drop the rows.

df.drop(
    index=(
        pd.concat(
            g for _, g in df.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)
            .groupby(["firstname", "lastname", "email"])
            if len(g) > 1
        )
    ).index
)

Output:

  firstname lastname email
1   Bar Bar      Bar   Bar
user_117286400

Try this:

df_unique = df[df.applymap(str).applymap(str.lower).applymap(str.strip).duplicated() == False]
user_71342860
Related