How to map values in one dataframe to those in another dataframe, in R

Question

I have a dataframe that has a column that contains a code.

my_food <- data.frame( name = c("food1","food2","food3"), ID_US = c(1001,1002,1001))

   name ID_US
1 food1  1001
2 food2  1002
3 food3  1001

And I have another data frame that has a column with the same code and a corresponding name.

my_list <- data.frame( ID_US = c(1001,1002), common_name = c("Rice, cooked","Lentils, cooked"))

  ID_US     common_name
1  1001    Rice, cooked
2  1002 Lentils, cooked

I want to lookup the ID for each row in the first dataframe and create a new column with the corresponding name from the second dataframe. So basically I want to transform the first dataframe to...

   name ID_US     common_name
1 food1  1001    Rice, cooked
2 food2  1002 Lentils, cooked
3 food3  1001    Rice, cooked

So I tried mapvalues...

my_food$long_name <- mapvalues(my_food$ID_US,from=my_list$ID_US,to=my_list$common_name)

But that gives me

   name ID_US common_name
1 food1  1001           2
2 food2  1002           1
3 food3  1001           2

So it's giving me the index, not the name.

score 2 · Answer 1 · answered Dec 15 '20 at 14:50

Try this:

library(dplyr)
#Code
new <- my_food %>% left_join(my_list)

Output:

   name ID_US     common_name
1 food1  1001    Rice, cooked
2 food2  1002 Lentils, cooked
3 food3  1001    Rice, cooked

Or base-R merge():

#Code2
new <- merge(my_food,my_list)

Output:

  ID_US  name     common_name
1  1001 food1    Rice, cooked
2  1001 food3    Rice, cooked
3  1002 food2 Lentils, cooked

How to map values in one dataframe to those in another dataframe, in R

1 Answers1