rxlookup

XLOOKUP using R

I am trying to achieve XLOOKUP function from excel in R (as in attached picture). So far no luck with it. Any suggestion on this can be done in R Below is the code and example dataframe

dataF <- structure(list(
       A_1 = c(3.548393, 4.211518, 4.712385,3.895335,3.016505, 2.728305), 
       A_2 = c(3.879862, 4.061333, 4.950454, 4.242747,3.216443, 2.530415), 
       A_3 = c(6.543898, 8.928949, 9.418975, 8.372533,5.68308, 5.278409), 
       A_4 = c(3.493369, 4.108747, 4.477213, 3.256992,2.991532, 3.015297), 
       A_5 = c(3.548393, 4.211518, 4.712385, 3.895335,3.016505, 2.728305), 
       A_6 = c(7.428255, 8.272851, 9.662839, 8.138082,6.232948, 5.258721), 
       A_7 = c(10.09229, 13.14047, 14.13136, 12.26787,8.699586, 8.006714), 
       A_8 = c(13.64068, 17.35199, 18.84375, 16.1632,11.71609, 10.73502), 
       A_9 = c(17.46552, 21.31055, 23.55903, 19.76761,14.90756, 13.55243), 
       A_10 = c(21.01392, 25.52207, 28.27141, 23.66294,17.92407, 16.28073), 
       A_19 = c(2L, 2L, 2L, 2L, 2L, 3L), 
       A_20 = c(2L,2L, 1L, 1L, 2L, 2L), 
       A_21 = c(2L, 0L, 0L, 0L, 2L, 0L), 
       SearchVal = c(2.5,2, 2, 2, 2.5, 3)),row.names = c(NA, -6L),class = "data.frame")

df_lookupVal <- tribble(~threshold,0,1,2,2.5,3,3.5)

dataF %>% mutate(New_Val = with(df_lookupVal, approx(df_lookupVal$threshold, 
                  dataF[,A_5:A_10], dataF[,SearchVal]))$y)

Expected output

> outputDF_with_newVariable$New_Val
[1] 13.64068 13.14047 14.13136 12.26787 11.71609 13.55243

XLOOKUP(SearchVal,df_lookupVal$threshold,dataF$A_5:A_10) - is there any easy way to realize this?

I tried it with approx but got some errors.

Solution

  • So you are trying to match up the values of SearchVal to the threshold column of df_lookupVal. The easiest way to do that in R is using match(). For example

    match(dataF$SearchVal, df_lookupVal$threshold)
# [1] 4 3 3 3 4 5

    That gives you the column that you want to extract. Because you are doing non-vectorized operations, when using dplyr you would have to use rowwise() here to extract values from different columns form each row. But that would look like

    dataF %>% 
  rowwise() %>% 
  mutate(New_Val = c_across(A_5:A_10)[match(SearchVal, df_lookupVal$threshold)])

    or with base R, you can create those values with

    idx <- match(dataF$SearchVal, df_lookupVal$threshold)
dataF$New_Val <- dataF[, 5:10][cbind(seq_along(idx), idx)]