I am looking to assign a new column to a Pandas by finding a key value which corresponds to the minimum difference between a value of a column (x) and any value of another column (y). Example:

key x y 0 1 6 2 1 3 9 4 2 5 7 7 3 7 2 10

row 0 has an x value of 6. This is closest to the y value of row 2 which is 7. Thus the corresponding value that I want to get for this row would be 5, the key of row 2. Expected result:

key x y result 0 1 6 2 5 1 3 9 4 7 2 5 7 7 5 3 7 2 10 1

I’ve thought about using

import np as numpy import pandas as pd result = [] for x in df["x"]: diffs = abs(df.y - x) min_diff = diffs.index(min(diffs)) result.append(min_diff) df["result"] = result

But this requires creating temporary lists and requires running through the second column multiple times. Is there a pythonic workaround to this?

## Answer

You can try this, no temporaries

df['result'] = df.apply(lambda row: df['key'][(df['y']-row['x']).abs().argmin()], axis=1)

output

key x y result 0 1 6 2 5 1 3 9 4 7 2 5 7 7 5 3 7 2 10 1