Match and extract values from columns of two dataframes

Question

I want to match the values of two columns in two data frames and add a third column, it's better to see the data frames:

FromA <- c("a", "b", "d")
ToA <- c("a", "k", "h")
PAC<- c("red", "blue", "black")
df1 <- data.frame(FromA, ToA, PAC)

> df1
      FromA ToA PAC
  1     a   a   red
  2     b   k  blue
  3     d   h black

FromB<- c("a", "b", "f") 
ToB  <- c("a", "k", "h")   
df2<-   data.frame(FromB, ToB)

 > df2
       FromB ToB
     1    a  a
     2    b  k
     3    f  h

I want the second data frame to get the value of df1$PAC , if the value of the two first columns match. the final result:

 > DF2
       FromB ToB  PAC
     1    a  a    red
     2    b  k    blue
     3    f  h    NA

So, I used sapply()

  df2$PAC<- sapply(1:nrow(df2), function(i) 
            df1$PAC[df1$FromA==df2$FromB[i] & df1$ToA == df2$ToB[i] ])

The function works if the two columns match completely, otherwise, it gives me an error.

Could you help me to understand what I'm missing? I'd appreciate it.

*"if the value of the two first columns match"* But they don't match for row 3! So why do you merge `d` & `h` from `DF1` with `f` & `h` from `DF2`? Assuming this is a typo, this is a simple merge/left join. There are *a lot* of posts here on SO that show how to do that using a variety of different libraries. — Maurits Evers, Jun 19 '19 at 12:31
Possible duplicate of [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) — Maurits Evers, Jun 19 '19 at 12:34
@MauritsEvers Thanks for sharing this. I was looking for it I did not find it. — Zero, Jun 19 '19 at 12:41

score 1 · Accepted Answer · answered Jun 19 '19 at 12:32

1

You can simply use the merge function:

merge(df1, df2, by.x = c("FromA", "ToA"), by.y = c("FromB", "ToB"), all.y = T)

answered Jun 19 '19 at 12:32

jkd

1,327
14
29

Match and extract values from columns of two dataframes

1 Answers1