python - Pandas - 合并两个具有部分匹配的 DataFrame

Question

有了下图所示的数据框，我想在 ['A','B','C'] 上合并和 ['X','Y','Z']首先然后逐渐寻找少一列的匹配项，即 ['A','B']和 ['X','Y']然后 ['A']和 ['X']不复制结果的行，在下面的示例中 a,y,y,v3自 a,d,d 起被排除在外已经匹配。

到目前为止，我的代码在所有 3 列上都匹配:

df1 = pd.DataFrame({"A":['a','b','c'],"B":['d','e','f'],"C":['d','e','f']})
df2 = pd.DataFrame({"X":['a','b','a','c'],"Y":['d','e','y','z'],"Z":['d','x','y','z'],"V":['v1','v2','v3','v4']})

merged = pd.merge(df1,df2,left_on=['A','B','C'],right_on=['X','Y','Z'], how='left')
merged = merged.drop_duplicates(['A','B','C'])
merged.head()

我怎样才能实现我的目标？
更新:预期输出

Answer 1

一个想法有多个 merge与 DataFrame.drop_duplicates 一起循环第二个 DataFrame什么应该避免最终 DataFrame 中的重复行:

from functools import reduce

dfs = []
L = [['A', 'B', 'C'], ['X', 'Y', 'Z']]

for i in range(len(L[0]), 0, -1):
    df22 = df2.drop_duplicates(L[1][:i])
    df = pd.merge(df1,df22,left_on=L[0][:i],right_on=L[1][:i], how='left')
    dfs.append(df)

df = reduce(lambda l,r: pd.DataFrame.fillna(l,r), dfs)
print (df)
   A  B  C  X  Y  Z   V
0  a  d  d  a  d  d  v1
1  b  e  e  b  e  x  v2
2  c  f  f  c  z  z  v4

像这样工作:

merged1 = pd.merge(df1,df2.drop_duplicates(['X','Y','Z']),left_on=['A','B','C'],right_on=['X','Y','Z'], how='left')
merged2 = pd.merge(df1,df2.drop_duplicates(['X','Y']),left_on=['A','B'],right_on=['X','Y'], how='left')
merged3 = pd.merge(df1,df2.drop_duplicates('X'),left_on=['A'],right_on=['X'], how='left')

df = merged1.fillna(merged2).fillna(merged3)
print (df)
   A  B  C  X  Y  Z   V
0  a  d  d  a  d  d  v1
1  b  e  e  b  e  x  v2
2  c  f  f  c  z  z  v4