How do I combine two data-frames based on two columns?

Question

I know I can use the plyr and its friends to combine dataframes, and merge as well, but so far I don't know how to merge two dataframes with multiple columns based on 2 columns?

Answer 1

You can use the merge function to merge two data frames by common columns or row names, or do other versions of database join operations.

For example:

x <- data.frame(k1=c(NA,NA,3,4,5), k2=c(1,NA,NA,4,5), data=1:5)

> x

  k1 k2 data

1 NA  1    1

2 NA NA    2

3  3 NA    3

4  4  4    4

5  5  5    5

> y <- data.frame(k1=c(NA,2,NA,4,5), k2=c(NA,NA,3,4,5), data=1:5)

> y

  k1 k2 data

1 NA NA    1

2  2 NA    2

3 NA  3    3

4  4  4    4

5  5  5    5

> merge(x, y, by=c("k1","k2"))

  k1 k2 data.x data.y

1  4  4      4      4

2  5  5      5      5

3 NA NA      2      1