Use a dataframe as a key to fill another according to one column values in R Studio

Question

I am trying fill x1 x2 x3 x4 x5 into DF2 from DF1 according to DF1$x6. I have 800+ unique DF1$x6 observations. Thank you in advance

DF1:

    x1 x2 x3 x4 x5 x6 
    2  a  c  a  5  a1 
    o  5  y  7  I  a2 
    H  5  a  v  3  a3 
    0  8  I  a  3  a4

DF2

    x1 x2 x3 x4 x5 x6 
                   a4 
                   a2 
                   a4 
                   a1 
                   a1 
                   a4 
                   a3

To get a result below

DF2

    x1 x2 x3 x4 x5 x6 
    0  8  I  a  3  a4 
    o  5  y  7  I  a2
    0  8  I  a  3  a4 
    2  a  c  a  5  a1
    2  a  c  a  5  a1
    0  8  I  a  3  a4 
    H  5  a  v  3  a3

Could you clarify what your final desired result looks like? — Ben, May 27 '21 at 00:29
Maybe to replace the first five columns you could try: `df2[, 1:5] <- df1[match(df2$x6, df1$x6), 1:5]`? — Ben, May 27 '21 at 00:46
Read : [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — Ronak Shah, May 27 '21 at 01:25

score 1 · Accepted Answer · answered May 27 '21 at 01:03

This can be a merge/join operation:

merge(DF2[,"x6",drop=FALSE], DF1, by = "x6", all.x = TRUE)
#   x6 x1 x2 x3 x4 x5
# 1 a1  2  a  c  a  5
# 2 a1  2  a  c  a  5
# 3 a2  o  5  y  7  I
# 4 a3  H  5  a  v  3
# 5 a4  0  8  I  a  3
# 6 a4  0  8  I  a  3
# 7 a4  0  8  I  a  3

Or you can use match in a simplified join-like operaiton, as @Ben suggested in a comment:

DF1[match(DF2$x6, DF1$x6),]
#     x1 x2 x3 x4 x5 x6
# 4    0  8  I  a  3 a4
# 2    o  5  y  7  I a2
# 4.1  0  8  I  a  3 a4
# 1    2  a  c  a  5 a1
# 1.1  2  a  c  a  5 a1
# 4.2  0  8  I  a  3 a4
# 3    H  5  a  v  3 a3

Thank you. I was able to do merge(DF2, DF1, by x6) and drop the .x columns to get the desired results. — uxtonyxu, May 28 '21 at 02:51

Use a dataframe as a key to fill another according to one column values in R Studio

1 Answers1