Replace values in some rows based on other dataframe mapping
<p>I have a table (<code>d.tab</code>) 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 (<code>d.lookup</code>).</p>

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

<pre><code>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)
</code></pre>

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

<p>I know I can do:</p>

<pre><code>merge(d.tab, d.lookup, by.x = c("question_id", "value"), by.y = c("question_id", "answer_id"), all.x = TRUE)
</code></pre>

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