R: apply a function row-wise to a table, passing values from a look-up table as arguments

Question

Here’s a row-wise iteration problem that I’ve been trying to solve with purrr::pmap, but no luck.

I start with a table of raw scores:

rawscore_table <- data.frame(rawscore = 10:14, SS1 = NA, SS2 = NA)

  rawscore SS1 SS2
1       10  NA  NA
2       11  NA  NA
3       12  NA  NA
4       13  NA  NA
5       14  NA  NA

There are two empty columns, SS1 and SS2, whose values I want to obtain by applying a function to each row:

SS1 = rawscore + x + y

SS2 = rawscore + x + y

The values of x and y are found in a lookup table:

lookup_table <- data.frame(SS = c('SS1', 'SS2'), x = 1:2, y = 3:4)

   SS x y
1 SS1 1 3
2 SS2 2 4

The solution I’m looking for will calculate the values of column rawscore_table$SS1 by finding the values of x and y in the SS1 row of lookup_table, and it will calculate the values of column rawscore_table$SS2 by finding the values of x and y in the SS2 row of lookup_table.

So the code has to refer to the name of the column in rawscore_table in order to pluck values from the corresponding row of lookup_table.

The desired output looks like this:

  rawscore   SS1   SS2
1       10    14    16
2       11    15    17
3       12    16    18
4       13    17    19
5       14    18    20

Thanks in advance for any help!

I struggled with similar logic in purrr - https://stackoverflow.com/questions/51978138/add-multiple-output-variables-using-purrr-and-a-predefined-function - and never really got something that seemed as clean as `Map` (base) or `pmap` (purrr) and matching left and right-hand-sides of a `<-` — thelatemail, May 16 '19 at 04:15

akrun · Answer 1 · 2019-05-16T02:49:15.713

An option would be to get the rowSums of the numeric columns of 'lookup_table', add (+) with the first column of 'rawscore_table', assign the output back to the columns except the first column

rawscore_table[-1] <- lapply(as.list(rowSums(lookup_table[-1])), `+`, 
           rawscore_table[,1])

Or replicate the rowSums output and add with the first column

rawscore_table[as.character(lookup_table$SS)] <- rawscore_table$rawscore + 
            rep(rowSums(lookup_table[-1]), each = nrow(rawscore_table))

Or using tidyverse

library(tidyverse)
lookup_table %>% 
    transmute(SS, xy = x + y) %>%
    deframe %>%
    as.list %>%
    imap_dfc(~ 
             rawscore_table  %>%
                 transmute(!! .y :=  .x + rawscore)) %>% 
    bind_cols(rawscore_table[1], .)
#   rawscore SS1 SS2
#1       10  14  16
#2       11  15  17
#3       12  16  18
#4       13  17  19
#5       14  18  20

score 1 · Accepted Answer · answered May 16 '19 at 05:03

1

Another option would be to join rawscore and lookup:

rawscore_table %>%
  gather(SS, val, -rawscore) %>%
  left_join(lookup_table, by = 'SS') %>%
  mutate(val = rawscore + x + y, x = NULL, y = NULL) %>%
  spread(SS, val)

#  rawscore SS1 SS2
#1       10  14  16
#2       11  15  17
#3       12  16  18
#4       13  17  19
#5       14  18  20

answered May 16 '19 at 05:03

utubun

4,400
1
14
17

Thanks to all for your input. The best solution for me is utubun's because it is based in tidyverse and uses functions I understand well. akrun's tidyverse solution is elegant, but I need to understand quotation better to feel confident using it. – DSH May 16 '19 at 19:55
Thanks @DSH. I think anyone can learn something new reading akrun's answers. – utubun May 16 '19 at 20:34

R: apply a function row-wise to a table, passing values from a look-up table as arguments

2 Answers2