Pandas：删除重复项，并在另一列中添加约束

Question

  Title URL Price Address Rental_Type
0 House URL $600  Auburn  Apartment
1 House URL $600  Auburn  Apartment
2 House URL $900  NY      Apartment
3 Room! URL $1018 NaN     Office
4 Room! URL $910  NaN     Office

我正在尝试删除Title下的重复项。但我只想删除具有Rental_Type == 'Office'的行。我还有第二个限制。我想删除带有Rental_Type == 'Apartment'的行，但是我希望在这个场景中保留第一个副本。所以在这种情况下，第3行和第4行将被删除，然后第0/1行中只有第1行。在

Answer 1

我会逐步建立一个你想放弃的事件列表。在

offices = df['Rental_Type'] == 'Office'
apts = df['Rental_Type'] == 'Apartment'

dup_offices = df[offices].duplicated('Title', keep=False)
dup_apts = df[apts].duplicated('Title', keep='first')

to_drop = pd.Index(dup_apts[dup_apts].index.tolist() + \
                   dup_offices[dup_offices].index.tolist())

df = df.drop(to_drop)

Answer 2

可以使用以下方式删除具有约束的重复项：

  #drop all duplicate with Rental_Type=='Office'
  df1 = df[(df.Rental_Type=='Office')].drop_duplicates(['Title'], keep=False)

  #Capture the duplicate row with Rental_Type=='Apartment'
  df2 = df[(df.Rental_Type=='Apartment')].duplicated(['Title'], keep = 'last')
  df3=df[(df.Rental_Type=='Apartment')][df2.values][1:]

  #Put them together
  df_final = pd.concat([df1,df3])


In [1]: df_final
Out[1]:
    Title   URL Price   Address Rental_Type
1   House   URL 600     Auburn  Apartment