如何在保留非空特定列的情况下删除重复行（Pandas）

Question

我有许多重复的记录，其中一些有银行账户。 我想保留带有银行账户的记录。

基本上像这样：

if there are two Tommy Joes:
     keep the one with a bank account

我已经尝试使用以下代码进行去重，但它仍然保留了没有银行账户的重复项。

df = pd.DataFrame({'firstname':['foo Bar','Bar Bar','Foo Bar','jim','john','mary','jim'],
                   'lastname':['Foo Bar','Bar','Foo Bar','ryan','con','sullivan','Ryan'],
                   'email':['Foo bar','Bar','Foo Bar','jim@com','john@com','mary@com','Jim@com'],
                   'bank':[np.nan,'abc','xyz',np.nan,'tge','vbc','dfg']})


df


  firstname  lastname     email bank
0   foo Bar   Foo Bar   Foo bar  NaN  
1   Bar Bar       Bar       Bar  abc
2   Foo Bar   Foo Bar   Foo Bar  xyz
3       jim      ryan   jim@com  NaN
4      john       con  john@com  tge
5      mary  sullivan  mary@com  vbc
6       jim      Ryan   Jim@com  dfg



# get the index of unique values, based on firstname, lastname, email
# convert to lower and remove white space first

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'], keep='first')).index


# save unique records
dfiban_uniq = df.loc[uniq_indx]

dfiban_uniq



  firstname  lastname     email bank
0   foo Bar   Foo Bar   Foo bar  NaN # should not be here
1   Bar Bar       Bar       Bar  abc
3       jim      ryan   jim@com  NaN # should not be here
4      john       con  john@com  tge
5      mary  sullivan  mary@com  vbc


# I wanted these duplicates to appear in the result:

  firstname  lastname     email bank
2   Foo Bar   Foo Bar   Foo Bar  xyz  
6       jim      Ryan   Jim@com  dfg

你可以看到索引0和3被保留了下来。这些有银行账户的客户的版本被删除了。我的期望结果是相反的，删除那些没有银行账户的重复记录。

我考虑过先按银行账户排序，但是我的数据非常多，我不确定如何“感性检查”它是否有效。

任何帮助都将不胜感激。

这里有一些类似的问题，但所有这些问题似乎都有可以排序的值，例如年龄等。这些哈希银行账户号码非常混乱。

编辑：

尝试在我的真实数据集上使用答案的一些结果。

@Erfan的方法先对子集+银行进行排序

去重后剩下58594条记录：

subset = ['firstname', 'lastname']

df[subset] = df[subset].apply(lambda x: x.str.lower())
df[subset] = df[subset].apply(lambda x: x.replace(" ", ""))
df.sort_values(subset + ['bank'], inplace=True)
df.drop_duplicates(subset, inplace=True)

print(df.shape[0])

58594 

@Adam.Er8 根据银行进行排序值的答案。去重后仍剩下59170条记录：

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

df.loc[uniq_indx].shape[0]

59170

不确定为什么会有差异，但两者足够相似。

- SCool

---

为什么不使用keep='last'而不是keep='first'？last有银行。 - MEdwin

在我的玩具示例中，最后一个是银行。我不知道它们在真实数据集中的顺序。 - SCool

@SCool，你能否扩展你的输入样本并提供更多记录和扩展的预期结果（以涵盖潜在的边缘情况）吗？ - RomanPerekhrest

我刚刚添加了更多的数据。不确定需要多少额外的记录。 - SCool

Answer 1

你应该按照 bank 列对值进行排序，使用 na_position='last'（这样 .drop_duplicates(..., keep='first') 将会保留不是缺失值的值）。

尝试以下代码：

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)

输出：

  bank    email firstname lastname
1  abc      Bar   Bar Bar      Bar
2  xyz  Foo Bar   Foo Bar  Foo Bar

（这只是您原始代码的翻译，其中.sort_values(by ="bank"，na_position ='last')位于uniq_indx = ...开头）

- Adam.Er8

---

谢谢，我已经将你的答案与另一个答案进行了比较。我已经编辑了我的原始问题。 - SCool

Answer 2

方法一：str.lower，sort和drop_duplicates

这种方法同样适用于许多列

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

方法二：groupby、agg、first

不容易适用于多列数据

---

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

- Erfan

---

谢谢，这比我的更优雅，但我在真实数据集中有58列。这就是为什么我使用了drop_duplicates和subset=['firstname'， 'lastname'， 'email']参数的原因。 - SCool

添加了另一个方法 @SCool - Erfan

我刚刚编辑了我的原始问题，以发布真实数据集的结果。您为什么要从子集中删除“电子邮件”？另外，按subset + ['bank']排序与仅按['bank']排序有什么区别？ - SCool

Answer 3

你可以在drop_duplicates之前按银行账户进行排序，以便将带有NaN的重复项放在最后： "最初的回答"

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)
.sort_values(by='bank')  # here we sort values by bank column
.drop_duplicates(subset=['firstname', 'lastname', 'email'], keep='first')).index

- Nick Skywalker

Answer 4

在删除重复项之前，按降序对值进行排序。这将确保NAN不会出现在顶部。

- Srikant Jayaraman