Calculate the minimum difference among the three column and give the corresponding value

Question

Suppose I have data as follows,

data

required_value          Value1          Value2        Value3
     0.5                  .1              0.3           0.4
     1.0                   1              0.7           0.2
     1.5                  .37             0.3           0.7
     2.0                  1.25            0.9           1.9

I want to find which of the three columns(Value1, Value2, Value3) are closest to the required value and create a new column and have the value of the closest one here.

My sample output is,

data

required_value          Value1          Value2        Value3       output
     0.5                  .1              0.3           0.4         0.4
     1.0                   1              0.7           0.2         1.0 
     1.5                  .37             0.3           0.7         0.7
     2.0                  1.25            0.9           1.9         1.9

The following is my trying,

max.col(-abs(sweep(data[-1], 1, data$required_value)),"first")

this gives me the column which is closest. But I want to find the corresponding value.

Can anybody help me in doing this?

Thanks

Answer 1

A on-liner that is not very pretty:

apply(data, 1, function(x) x[-1][which.min(abs(x[-1] - x[1]))])
# 0.4 1.0 0.7 1.9

Answer 2

From the column id to the final solution it is only one step missing. You can use subsetting by a matrix as described in Hadley Wickhams Advaned R: http://adv-r.had.co.nz/Subsetting.html. Build a subsetting matrix by using your already detected colums and for the rows you can just use a running number.

df <- read.table(text = "required_value          Value1          Value2        Value3
                             0.5                  0.1              0.3           0.4
                             1.0                   1              0.7           0.2
                             1.5                  0.37             0.3           0.7
                             2.0                  1.25            0.9           1.9", header = TRUE)

min_id     <- max.col(-abs(sweep(df[-1], 1, df$required_value)),"first")
df$min_div <- df[cbind(1:4, min_id + 1)]

     df
#    required_value Value1 Value2 Value3 min_div
# 1            0.5   0.10    0.3    0.4     0.4
# 2            1.0   1.00    0.7    0.2     1.0
# 3            1.5   0.37    0.3    0.7     0.7
# 4            2.0   1.25    0.9    1.9     1.9