Replace values in some rows based on other dataframe mapping

Question

I have a table (d.tab) with question–answer pairs from a survey. Some of these are single-choice answers, some are multiple-choice. I want to look up the text value of the single-choice answer from its numerical value. For that, I have a lookup table (d.lookup).

I tried to merge these, but it's a bit ugly, since I now have to filter out all the rows where value != answer_id. Is there a prettier way of doing this, possibly using plyr or dplyr or tidyr?

tab = '
question_id question_type   subject value
1   single-choice   1   1
2   multiple-choice 1   2
3   single-choice   1   2
1   single-choice   2   2
2   multiple-choice 2   3,4
3   single-choice   2   2
'

lookup = '
question_id answer_id   answer_text
1   1   female
1   2   male
3   1   no
3   2   yes
'

d.tab = read.table(text = tab, header = TRUE)
d.lookup = read.table(text = lookup, header = TRUE)

merge(d.tab, d.lookup, by = "question_id", all.x = TRUE)

I don't want to do anything with multiple-choice rows, but simply update the original dataframe to replace value with the actual text from d.tab's answer_text if the answer_ids match the value.

I know I can do:

merge(d.tab, d.lookup, by.x = c("question_id", "value"), by.y = c("question_id", "answer_id"), all.x = TRUE)

But this gives me a new column answer_text with the original value still there, which I don't need.

Answer 1

You have the correct call of merge() in your question. All that is left is that you filter for the rows with single-choice answers and select all the columns except value. Using dplyr, this can be done as follows:

library(dplyr)
filter(d.tab, question_type == "single-choice") %>%
  mutate(value = as.numeric(as.character(value))) %>%
  merge(d.lookup, by.x = c("question_id", "value"),
        by.y = c("question_id", "answer_id")) %>%
  select(-value)

The second line contains the explicit conversion of the factor variable value to numeric. This is important, because conversion of factors to numeric can lead to weird results. I'll add a few lines regarding this topic below.

Note that dplyr also comes with its own functions to replace merge. In case your table is large, you will notice that these are more efficient. Using left_join from dplyr the solution reads:

library(dplyr)
filter(d.tab, question_type == "single-choice") %>%
  mutate(value = as.numeric(as.character(value))) %>%
  left_join(d.lookup,
            by = c("question_id" = "question_id",
                   "value" = "answer_id")) %>%
  select(-value)

So here comes the comment regarding factors that I promised. The issue with factors is that they are actually integers, where each integer value has a label associated with it. When you naively convert the factors to numeric with as.numeric(), you will get the integer that is associated with the label. You will almost certainly run into this problem with your data and here is why.

I create a factor variable that mimicks your data:

values <- factor(c("1", "2", "3,4", "3", "4"))

Now I throw away the third value ("3,4") and convert to numeric:

as.numeric(values[-3])
## [1] 1 2 3 5

This is probably not what you expected. The reason is that the numbers 1 to 5 were associated to the five levels that we defined above. If you want to get the numbers that match the labels, you need to first convert to character:

as.numeric(as.character(values[-3]))
## [1] 1 2 3 4

So, even though merge() does the conversion of the factors to numeric somewhere, I wouldn't rely on it doing it in the way you want. Therefore, you should do the conversion explicitly.

Answer 2

An alternative solution with data.table:

library(data.table)

# converting to datatables & setting the 'answer_id' to character
setDT(d.tab)
setDT(d.lookup)[, answer_id := as.character(answer_id)]

# join 'd.tab' with 'd.lookup' and update 'value' by reference
d.tab[d.lookup, value := answer_text, on = c("question_id", "value"="answer_id")]

which gives:

   question_id   question_type subject  value
1:           1   single-choice       1 female
2:           2 multiple-choice       1      2
3:           3   single-choice       1    yes
4:           1   single-choice       2   male
5:           2 multiple-choice       2    3,4
6:           3   single-choice       2    yes

---

As already mentioned by @Stibu, it is probably better to split rows with multiple values. An example with the cSplit function from the splitstackshape package:

library(splitstackshape)
cSplit(d.tab, "value", sep=",", 
       direction="long", 
       type.convert = FALSE)[d.lookup, 
                             value := answer_text, 
                             on = c("question_id", "value"="answer_id")]

# or everything in 'data.table'
d.tab[, lapply(.SD, function(x) unlist(tstrsplit(x, ','))), setdiff(names(d.tab),"value")
      ][d.lookup, value := answer_text, on = c("question_id", "value"="answer_id")][]

which both give:

   question_id   question_type subject  value
1:           1   single-choice       1 female
2:           2 multiple-choice       1      2
3:           3   single-choice       1    yes
4:           1   single-choice       2   male
5:           2 multiple-choice       2      3
6:           2 multiple-choice       2      4
7:           3   single-choice       2    yes