Replace Dataframe Values Using Index from Another Dataframe Column in R

Question

This is something that I come across fairly often and this is the solution that I typically land on. I'm wondering if anyone has suggestions on a less verbose way to accomplish this task. Here I create an example dataframe that contains three columns. One of these columns is a parameter code rather than a parameter name.

Second I create an example of a reference dataframe that contains unique parameter codes and the associated parameter names.

My solution has been to use a 'for' loop to match the parameter name from real_param_names with the associated parameter codes in dat. I have tried to use match() and replace but haven't quite found a way that those work. None of the examples I've come across in old questions have quite hit the mark either but would be happily referred to one that does. Thank in advance.

dat <- data.frame(site = c(1,1,2,2,3,3,4,4),
                  param_code = c('a','b','c','d','a','b','c','d'),
                  param_name = NA)
dat

real_param_names <- data.frame(param_code = c('a','b','c','d'),
                               param_name = c('gold', 'silver', 'mercury', 'lead'))
real_param_names

for (i in unique(dat$param_code)) {
  dat$param_name[dat$param_code==i] <- real_param_names$param_name[real_param_names$param_code==i]
}
dat

This is a duplicate (and often-asked) question; while you didn't necessarily know to search for merge/join in your research, I think that your best takeaway from this is to try to understand the merge mechanics, and use them instead of a slower and often problematic `for` loop manual process. I'm closing the question, but you are still free to accept it if you choose. If I am completely off the mark, please ping me again and we can discuss re-opening the question. Good luck! — r2evans, May 28 '21 at 14:59
I agree, it was a matter of knowing what to search for and was my hope that someone would point me, and possibly others who were lost, in the right direction. Cheers. — dandrews, May 28 '21 at 15:03

score 1 · Answer 1 · answered May 28 '21 at 14:55

This is a merge/join operation:

First, let's get rid of the unnnecessary dat$param_name, since it'll be brought over in the merge:

dat$param_name <- NULL
dat
#   site param_code
# 1    1          a
# 2    1          b
# 3    2          c
# 4    2          d
# 5    3          a
# 6    3          b
# 7    4          c
# 8    4          d

Now the merge:

merge(dat, real_param_names, by = "param_code", all.x = TRUE)
#   param_code site param_name
# 1          a    1       gold
# 2          a    3       gold
# 3          b    1     silver
# 4          b    3     silver
# 5          c    2    mercury
# 6          c    4    mercury
# 7          d    2       lead
# 8          d    4       lead

Some good links for the concepts of joins/merges: How to join (merge) data frames (inner, outer, left, right), https://stackoverflow.com/a/6188334/3358272

Replace Dataframe Values Using Index from Another Dataframe Column in R

1 Answers1