How to find closest elements to each other in two dataframes?-CodePudding

Given two dataframes:

df_1:

c1, c2
a, 0
b, 0
c, 2

df_2:

c3, c4
d, 2
e, 3
f, 7

How would you find the closest c2 row to the closest c4 row, and create a new dataframe which has these in corresponding rows next to each other?

Example output:

a, d (zero is closest to two)
b, d (second entry of zero counts as being closest to two)
c, d (this is the closest)

CodePudding user response：

Try apply with idxmin:

>>> df_1['c1']   ', '   df_1.apply(lambda x: df_2.set_index('c3')['c4'].sub(x['c2']).abs().idxmin(), axis=1)
0    a, d
1    b, d
2    c, d
dtype: object
>>>

CodePudding user response：

You could start by creating a new dataFrame which is a copy of your original dataframe containing c1, c2.

Then by using an apply function on the c2 column, you can use the following command to find the closest value and assign it to another column.

result_index = df['col_to_search'].sub(search_value).abs().idxmin()