Merge two dataframes based on common two columns & if values in third column are nearest to each other

Question

I am stuck in merging two data-sets the simple complexity of which goes well-beyond my fluency in R. I tried to learn from here and here but could not solve my problem. I am trying to merge the following two data frames:

df1

No    County       Route      Number
1     Anderson       SR009       6150
2     Anderson       SR061       5880
3     Bedford        SR016       9500
4     Bedford        SR130       320
5       .
6       .
7       .
8       .

df2

No.  County        Route     Number1    abc      def
1    Clay          02264     4500        50       789
2     Dickson       01544     5870       45       33
3     Anderson      01421     981        70       65
4     Anderson      SR009     10000      56       56
5     Anderson      SR009     6145       32       53
6     Bedford       SR016     7500       23       32
7     Anderson      SR061     4400       12       24
8     Anderson      SR061     5875       87       26
9     Anderson      SR061     15000      45       45
10     Bedford       SR016     22000     71       75
11     Bedford       SR016     9450      145      615
12     Bedford       SR130     900       7854     76
13     Bedford       SR130     310       124      25
14     Anderson      SR061     5865      312      123
       .
       .
       .

First, the "county" and "Route" columns in df1 and df2 should be compared, and if they exactly match, then specific row of df2$Number1 should be selected whose value is NEAREST to df1$Number, and as such all unique df2 columns should be added to df1

Here is the pseudo-code of what i am trying to achieve:

if(df1$County == Anderson & df2$County == Anderson) && if(df1$Route == SR009 & df2$Route == SR009) 
then select specific row from df2$Number1 whose value is nearest to the df1$Number value, 
and add all subsequent columns of df2 to corresponding row in df1

An example:

Based on "County" and "Route" columns, row No. 1 in df1 matches with row 4 and 5 in df2. Now out of the two df2 rows that match with first row in df1, i want to select that specific row in df2 whose "Number1" value is closest to the "Number" value in df1 i.e. 6150. Said this, i want to select row 5 in df2 because "Number1" value is 6145 which is closest to 6150, and add all subsequent columns from df2 to df1...

The final output would look like this:

No      County           Route       Number     Number1     abc    def  .  .    
1       Anderson         SR009       6150       6145        32     53   .  .
2       Anderson         SR061       5880       5875        87     26   .  .
3       Bedford          SR016       9500       9450        145    615  .  .
4       Bedford          SR139       320        310         124    25   .  .
.          .
.          .

I highly appreciate any help on this. Sorry for the long post.

Jacob H Jacob H · Accepted Answer · 2016-10-15T00:27:48

Your question is a bit confusing. That withstanding, from your desired output I think the following dplyr approach will work for you.

library(dplyr)

d1%>%
  full_join(d2, by = c("County", "Route")) %>%
  group_by(County, Route) %>%
  mutate(myDiff = abs(Number - Number1)) %>%
  slice(which.min(myDiff))

Merge two dataframes based on common two columns & if values in third column are nearest to each other

2 Answers