How to transform values according to matching values in another data frame in R?

Question

I have 2 data frames with a column that has the same type of values, as shown here:

a <- c(1, 2, 3, 4)
b <- c("John", "James", "James", "John")
df1 <- data.frame(a, b)
c <- c("John", "James")
d <- c("A", "B")
df2 <- data.frame(c, d)

In df1, I want to turn the John and James into A and B, using df2, which shows that John goes with A and James goes with B. Thus, df1 should look like this:

a   b
1   A           
2   B           
3   B           
4   A

I tried using the following code, but it doesn't work:

df1$b[df1$b == df2$c] <- df2$d

Answer 1

I have a solution with left_join() from dplyr. It joins the two data.frames by matching up the values in the df1$b and df2$c:

library(dplyr)

a <- c(1, 2, 3, 4)
b <- c("John", "James", "James", "John")
df1 <- data.frame(a, b)
c <- c("John", "James")
d <- c("A", "B")
df2 <- data.frame(c, d)

left_join(df1, df2, by = c("b" = "c"))

Output:

  a     b d
1 1  John A
2 2 James B
3 3 James B
4 4  John A

Answer 2

Using sqldf: We can perform join on column b from df1 and c from df2 as follow:

sqldf("select d from df1 join df2 on df1.b=df2.c")

Output:

  d
1 A
2 B
3 B
4 A

OR

In order to use merge please make sure that your join column should have same name:

merge(df1,df2,by = "b")
      b a d
1 James 2 B
2 James 3 B
3  John 1 A
4  John 4 A