merge dataframes on multiple columns ignoring order

user_6296473 0
Viewed 118

I have the following dataframes:

df1=pd.DataFrame({'fr':[1,2,3],'to':[4,5,6],'R':[0.1,0.2,0.3]})
df2=pd.DataFrame({'fr':[1,5,3],'to':[4,2,6],'X':[0.4,0.5,0.6]})

I would like to merge these two dataframes on fr and to, ignoring the order of fr and to, i.e., (2,5) is the same as (5,2). The desired output is:

dfO=pd.DataFrame({'fr':[1,2,3],'to':[4,5,6],'R':[0.1,0.2,0.3],'X':[0.4,0.5,0.6]})

or

dfO=pd.DataFrame({'fr':[1,5,3],'to':[4,2,6],'R':[0.1,0.2,0.3],'X':[0.4,0.5,0.6]})

I can do the following:

pd.merge(df1,df2,on=['fr','to'],how='left')

However, as expected, the X value of the second row is NaN.

Thank you for your help.

pandas merge python-3.x
2 Answers

You need do numpy sort first

df1[['fr','to']] = np.sort(df1[['fr','to']].values,1)
df2[['fr','to']] = np.sort(df2[['fr','to']].values,1)
out = df1.merge(df2,how='left')
out
Out[44]: 
   fr  to    R    X
0   1   4  0.1  0.4
1   2   5  0.2  0.5
2   3   6  0.3  0.6
user_79645270

You can create a temp field and then join on it

df1['tmp'] = df1.apply(lambda x: ','.join(sorted([str(x.fr), str(x.to)])), axis=1)
df2['tmp'] = df2.apply(lambda x: ','.join(sorted([str(x.fr), str(x.to)])), axis=1)

This will give the result that you expect

pd.merge(df1,df2[['tmp',  'X']],on=['tmp'], how='left').drop(columns=['tmp'])
user_59795430
Related