键列具有重复的值。我正在尝试合并数据帧

Question

我想将CustomerMail上的DF1与DF2合并，这在两者中都很常见，但具有重复值。 DF1

 customer_Email  Fraud
 name_0          False
 name_1          True
 name_2          True
 name_3          True
 name_4          False
 name_1          False        

DF2

customer_Email  ID
 name_0           0
 name_1           1
 name_2           2
 name_3           3
 name_4           4
 name_1           5

请注意，DF1和DF2仅用于示例目的

customerEmail在两个数据帧中都不是唯一的

因此，当我使用pd.merge(DF1, DF2, on='customerEmail', how=left)连接这两个表时，当相同的customerEmail重复时，它会用上面行中的随机值填充目标列Fraud

当customerEmail中存在重复项时，我希望我的Fraud列具有空值

customer_email在两个数据帧中都不是唯一的

电流输出：

customer_Email ID     Fraud
 name_0        0      False
 name_1        1      True
 name_2        2      True
 name_3        3      True   
 name_4        4      False
 name_1        5      True
 name_2        0      True
 name_1        1      True
 name_3        2      True

预期产出：

customer_Email ID     Fraud
 name_0        0      False
 name_1        1      True
 name_2        2      True
 name_3        3      True   
 name_4        4      False
 name_1        5      N/A
 name_2        0      N/A
 name_1        1      N/A
 name_3        2      N/A

Answer 1

import pandas as pd

df1 = pd.read_csv('1.csv')
df2 = pd.read_csv('2.csv')

out = pd.merge(df1, df2, on='customer_Email', how='left')
out.loc[~out['customer_Email'].isin(df2.drop_duplicates(subset='customer_Email', keep=False)['customer_Email'].tolist()), 'Fraud'] = None
out

给出：

    customer_Email  Fraud   ID
0   name_0  0.0 0
1   name_1  NaN 1
2   name_1  NaN 5
3   name_2  1.0 2
4   name_3  1.0 3
5   name_4  0.0 4
6   name_1  NaN 1
7   name_1  NaN 5

Answer 2

“当CustomerMail中存在重复项时，希望我的欺诈列具有空值。”

因此，在预期的输出中，您忘记在customerEmail中添加name_4 ，因为它也是重复的

 df1 = pd.DataFrame({
    'customerEmail':['name0','name1','name2','name3','name4','name1'],
    'Fraud':[False,True,True,True,False,False]
}
                  )
df2 = pd.DataFrame({
    'customerEmail': ['name0', 'name1', 'name2', 'name3', 'name4', 'name1'],
    'ID':[0,1,2,3,4,5]
})


df3=pd.merge(df1, df2, on='customerEmail', how='left')

#here you need to know which customers are duplicated, to fill for them rows in column Fraud
df_duplicates = df3.drop_duplicates(subset=['customerEmail'],keep='last')
print(df_duplicates)
  customerEmail  Fraud  ID
0         name0  False   0
3         name2   True   2
4         name3   True   3
5         name4  False   4
7         name1  False   5
#now for those duplicates fill cells in column Fraud using iloc and np.nan
df_duplicates.loc[:,'Fraud'] = np.nan
print(df_duplicates)
  customerEmail  Fraud  ID
0         name0    NaN   0
3         name2    NaN   2
4         name3    NaN   3
5         name4    NaN   4
7         name1    NaN   5
#so now you have two df's , one df_duplicates with Nans duplicates values above,
#and main df3 with original merged values

#now you need to add those df's using concat , (add column to column )
#but you dont need values with same customerEmail that you used for df_duplicated, so you can delete them using drop_duplicates
result = pd.concat([df3,df_duplicates]).drop_duplicates(subset=['customerEmail','Fraud'])
#after concat True and False values has been coverted to 1.0 and 0 , for we need to change the type to False and True again
result.Fraud = result.Fraud.astype('boolean')
print(result)
  customerEmail  Fraud  ID
0         name0  False   0
1         name1   True   1
3         name2   True   2
4         name3   True   3
5         name4  False   4
6         name1  False   1
0         name0   <NA>   0
3         name2   <NA>   2
4         name3   <NA>   3
5         name4   <NA>   4
7         name1   <NA>   5

Answer 3

下面呢？（假设customer_email在df2中是唯一的）：

df3 = pd.merge(df1, df2, on=['customer_Email'], how="left")
df3["count"] = df3.groupby("customer_Email").cumcount()
df3.loc[df3["count"]>0,"Fraud"] = "N/A"
df3[["customer_Email","Fraud","ID"]]

输出：

    customer_Email  Fraud   ID
0   name_0          False   0
1   name_1          True    1
2   name_1          N/A     5
3   name_2          True    2
4   name_3          True    3
5   name_4          False   4
6   name_1          N/A     1  
7   name_1          N/A     5