-1

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.

zx8754
  • 52,746
  • 12
  • 114
  • 209
user6394631
  • 7
  • 1

2 Answers2

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)).

David_B
  • 926
  • 5
  • 7
0

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"))
MKG
  • 56
  • 4