How can I merge two dataframes if two cols have to be the same?

Question

I have two data frames. For example the df1 looks like:

Name Month Number       
1.H  1      8
2.H  2      7  
3.H  3      6 
4.A  1      9
5.A  2      10
6.A  3      11

And df2 looks like:

Name Month index      
1.H    1      3
2.H    2      2  
3.H    3      1
4.A    1      3 
5.A    2      5
6.A    3      9

And I want to merge it to the following df:

  Name Month Number index
  1.H  1    8       3
  2.H  2    7       2
  3.H  3    6       1
  4.A  1    9       3
  5.A  2    10      5
  6.A  3    11      9

How can I merge the two df's to this df?

I have already tried the merge function by.x and by.y but that only allows merging by one column, but I want also the second column.

Who told you that `by.x` and `by.y` allows to merge only by one column? Where is that written? The documentation doesn't say so. Did you even try doing it by several columns? — David Arenburg, May 28 '16 at 19:39

score 1 · Answer 1 · answered May 28 '16 at 14:48

1

You can merge on more than one column at a time:

merge(df1, df2, by = c('Name', 'Month'))

In fact, that should be the default, as the default value of by is intersect(names(df1), names(df2)).

answered May 28 '16 at 14:48

David_B

926
5
7

score 0 · Answer 2 · answered May 28 '16 at 19:34

There are a lot of different ways to do this. The other two answers give base ways to do it. Here are two other ways with packages.

You can also use the sqldf package:

sqldf("select a.*, b.index from df1 as a join df2 as b on a.name = b.name and a.month = b.month")

You can use the dplyr package:

inner_join(df1, df2, by = c("name", "month"))

How can I merge two dataframes if two cols have to be the same?

2 Answers2