Merge and replace the values in a dataframe by the values from another dataframes in R

Question

I have two dataframs: DF1 and DF2, I want to replace the values from my DF1 using the values from my DF2.

DF1:

> DF1 <- data.frame(V1=c("AA23", "AA34", "AA54", "BB32", "CC43", "DD32"), V2=c("BB32", NA,"CC43", NA, "DD32", "EE22"), V3=c("CC43", "DD32", NA, NA, "AA54", "EE54"))
> DF1
    V1   V2   V3
1 AA23 BB32 CC43
2 AA34 <NA> DD32
3 AA54 CC43 <NA>
4 BB32 <NA> <NA>
5 CC43 DD32 AA54
6 DD32 EE22 EE54

DF2:

    > DF2 <- data.frame(col1=c("AA23", "AA34", "AA54", "BB32", "CC43", "DD32", "EE22", "EE54", "EE55"), col2=c("a22", "a23", "a25", "a27", "b11", "b13", "b15", "c11", "c13"))
> DF2
  col1 col2
1 AA23  a22
2 AA34  a23
3 AA54  a25
4 BB32  a27
5 CC43  b11
6 DD32  b13
7 EE22  b15
8 EE54  c11
9 EE55  c13

I would like to replace all values from all columns in my DF1 (keeping the NA) for the values in my DF2$col2.

This is my DF_final:

    > DF_final
   V1   V2   V3
1 a22  a27  b11
2 a23 <NA>  b13
3 a25  b11 <NA>
4 a27 <NA> <NA>
5 b11  b13  a25
6 b13  b15  c11

Please, can someone advise me? Thanks.

Answer 1

We can use a named vector and match

library(data.table)
setDT(DF1)[, lapply(.SD, function(x) 
     setNames(as.character(DF2$col2), DF2$col1)[as.character(x)])]
#   V1   V2   V3
#1: a22  a27  b11
#2: a23 <NA>  b13
#3: a25  b11 <NA>
#4: a27 <NA> <NA>
#5: b11  b13  a25
#6: b13  b15  c11

---

Or in base R

DF1[] <- lapply(DF1, function(x) 
     setNames(as.character(DF2$col2), DF2$col1)[as.character(x)])

Or convert to a matrix and do the match`

DF1[] <- setNames(as.character(DF2$col2), DF2$col1)[as.matrix(DF1)]

Answer 2

Here is another base R option using match + unlist:

DF1[]<-DF2$col2[match(unlist(DF1),DF2$col1)]

such that

> DF1
   V1   V2   V3
1 a22  a27  b11
2 a23 <NA>  b13
3 a25  b11 <NA>
4 a27 <NA> <NA>
5 b11  b13  a25
6 b13  b15  c11