Python 如果 pandas 合并中缺少键，请使用备用键

发布于09月08日

我想合并两个数据帧df1和df2，条件是如果key2中右侧的值不存在于key1中的左侧，则使用Alternative_Key.有没有办法以一种好的方式这样做呢？

a = {'key1': ['a','b','c'], 'alternative_key':['f','g','h']}
df1 = pd.DataFrame(data=a)


b = {'key2':['a','b','h'], 'some_stuff': [1,2,3]}
df2 = pd.DataFrame(data=b)

df_final=df1.merge(df2, left_on='key1', right_on='key2', how='left')

DF_FINAL中的预期结果:

  key1 alternative_key  some_stuff
0    a               f           1
1    b               g           2
2    c               h           3

推荐答案

按所需的优先级顺序执行合并和combine_first:

(df1.merge(df2, left_on='key1', right_on='key2', how='left')
    .combine_first(df1.merge(df2, left_on='alternative_key',
                             right_on='key2', how='left'))
    .drop(columns='key2')
)

编写代码的其他方法(使用变量):

merge1 = df1.merge(df2, left_on='key1', right_on='key2', how='left')
merge2 = df1.merge(df2, left_on='alternative_key', right_on='key2', how='left')

out = merge1.combine_first(merge2).drop(columns='key2')

输出:

  key1 alternative_key  some_stuff
0    a               f         1.0
1    b               g         2.0
2    c               h         3.0

NB. the advantage of this method is that you can have as many other columns as you want. All will be merged in order.个

arbitrary number of alternative keys:

from functools import reduce

keys = ['key1', 'alternative_key']

out = (reduce(lambda a,b: a.combine_first(b),
              [df1.merge(df2, left_on=k, right_on='key2', how='left')
               for k in keys])
       .drop(columns='key2')
      )