Assign value to different columns based on lookup values in R

Question

I'm trying to assign values to different columns, separately for each row, based on lookup values. I'm working in R. Here's a minimal working example:

#Item scores
item1 <- c(NA, 1, NA, 4)
item2 <- c(NA, 2, NA, 3)
item3 <- c(NA, 3, NA, NA)
item57 <- c(NA, 4, 4, 1)

mydata <- data.frame(item1, item2, item3, item57)

#Lookup values based on item score
lookup <- data.frame(score = 1:4, value=c(6, 7, 8, 10))

I have many participants (i.e., rows) assessed with a score on each of many items (i.e., columns). I'd like to create variables in my data frame for the values that are tied to the item scores (based on the lookup table). Here's my desired output:

#Desired output (adding value that is tied to item score to the original data)
desiredOutput <- cbind(mydata,
                   value1 = c(NA, 6, NA, 10),
                   value2 = c(NA, 7, NA, 8),
                   value3 = c(NA, 8, NA, NA),
                   value57 = c(NA, 10, 10, 6))

I have a fairly large dataset and would like to stay away from loops, if possible. Also, we can skip rows with all NAs, if it's faster to process.

score 1 · Accepted Answer · answered Jun 19 '18 at 19:42

here's a tidyverse method. The basis of it is that you want to first gather the score columns and left_join the lookup table so that you have your values matched to scores. Then the rest is just manipulation to get back back to the desired output format. To do this, we need to create the column names that we want with gather and unite, and then finally spread back out. Note that you need rowid_to_column at the beginning so that spread will know what observations to place on what rows. If you want to exactly get your output column names, you can mix in some stringr.

item1 <- c(NA, 1, NA, 4)
item2 <- c(NA, 2, NA, 3)
item3 <- c(NA, 3, NA, NA)
item57 <- c(NA, 4, 4, 1)

mydata <- data.frame(item1, item2, item3, item57)

#Lookup values based on item score
lookup <- data.frame(score = 1:4, value=c(6, 7, 8, 10))

library(tidyverse)
mydata %>%
  rowid_to_column(var = "participant") %>%
  gather(items, score, starts_with("item")) %>%
  left_join(lookup) %>%
  gather(coltype, val, score:value) %>%
  unite(colname, coltype, items) %>%
  spread(colname, val)
#> Joining, by = "score"
#>   participant score_item1 score_item2 score_item3 score_item57 value_item1
#> 1           1          NA          NA          NA           NA          NA
#> 2           2           1           2           3            4           6
#> 3           3          NA          NA          NA            4          NA
#> 4           4           4           3          NA            1          10
#>   value_item2 value_item3 value_item57
#> 1          NA          NA           NA
#> 2           7           8           10
#> 3          NA          NA           10
#> 4           8          NA            6

Created on 2018-06-19 by the reprex package (v0.2.0).

Assign value to different columns based on lookup values in R

1 Answers1