Find Nearest Value From Multiple Columns And Add To A New Column In Python

I have the following dataframe: import pandas as pd import numpy as np data = { 'index': [1, 2, 3, 4, 5], 'A': [11, 17, 5, 9, 10], 'B': [8, 6, 16, 17, 9], 'C': [10,

Solution 1:

Subtract "target" from the other columns, use idxmin to get the column of the minimum difference, followed by a lookup:

idx = df.drop(['index', 'target'], 1).sub(df.target, axis=0).abs().idxmin(1)
df['result'] = df.lookup(df.index, idx)
df
   index   A   B   C  target  result
0      1  11   8  10      12      11
1      2  17   6  17      13      17
2      3   5  16  12       8       5
3      4   9  17  13       6       9
4      5  10   9  15      12      10

General solution handling string columns and NaNs (along with your requirement of replacing NaN values in target with value in "v1"):

df2 = df.select_dtypes(include=[np.number])
idx = df2.drop(['index', 'target'], 1).sub(df2.target, axis=0).abs().idxmin(1)
df['result'] = df2.lookup(df2.index, idx.fillna('v1'))

---

You can also index into the underlying NumPy array by getting integer indices using df.columns.get_indexer.

# idx = df[['A', 'B', 'C']].sub(df.target, axis=0).abs().idxmin(1)
idx = df.drop(['index', 'target'], 1).sub(df.target, axis=0).abs().idxmin(1)
# df['result'] = df.values[np.arange(len(df)), df.columns.get_indexer(idx)]
df['result'] = df.values[df.index, df.columns.get_indexer(idx)]

df
   index   A   B   C  target  result
011181012111217617131723516128534917136945109151210

Solution 2:

You can use NumPy positional integer indexing with argmin:

col_lst = list('ABC')
col_indices = df[col_lst].sub(df['target'], axis=0).abs().values.argmin(1)
df['result'] = df[col_lst].values[np.arange(len(df.index)), col_indices]

Or you can lookupcolumn labels with idxmin:

col_labels = df[list('ABC')].sub(df['target'], axis=0).abs().idxmin(1)
df['result'] = df.lookup(df.index, col_labels)

print(df)

   index   A   B   C  target  result
0      1  11   8  10      12      11
1      2  17   6  17      13      17
2      3   5  16  12       8       5
3      4   9  17  13       6       9
4      5  10   9  15      12      10

The principle is the same, though for larger dataframes you may find NumPy more efficient:

# Python 3.7, NumPy 1.14.3, Pandas 0.23.0

def np_lookup(df):
    col_indices = df[list('ABC')].sub(df['target'], axis=0).abs().values.argmin(1)
    df['result'] = df[list('ABC')].values[np.arange(len(df.index)), col_indices]
    return df

def pd_lookup(df):
    col_labels = df[list('ABC')].sub(df['target'], axis=0).abs().idxmin(1)
    df['result'] = df.lookup(df.index, col_labels)
    return df

df = pd.concat([df]*10**4, ignore_index=True)

assert df.pipe(pd_lookup).equals(df.pipe(np_lookup))

%timeit df.pipe(np_lookup)  # 7.09 ms
%timeit df.pipe(pd_lookup)  # 67.8 ms