How to perform a fuzzy join with fuzzyjoin::difference_* in R
<p>I'm working with two different datasets that I want to merge based on a threshold. Let's say the two dataframes look like this:</p>

<pre><code>library(dplyr)
library(fuzzyjoin)
library(lubridate)

df1 = data_frame(Item=1:5, 
                 DateTime=c("2015-01-01 11:12:14", "2015-01-02 09:15:23", 
                            "2015-01-02 15:46:11", "2015-04-19 22:11:33", 
                            "2015-06-10 07:00:00"), 
                 Count=c(1, 6, 11, 15, 9), 
                 Name="Sterling", 
                 Friend=c("Pam", "Cyril", "Cheryl", "Mallory", "Lana"))
df1$DateTime = ymd_hms(df1$DateTime)

df2 = data_frame(Item=21:25, 
                 DateTime=c("2015-01-01 11:12:15", "2015-01-02 19:15:23", 
                            "2015-01-02 15:46:11", "2015-05-19 22:11:33", 
                            "2015-06-10 07:00:02"), 
                 Count=c(3, 7, 11, 15, 8), 
                 Name="Sterling", 
                 Friend=c("Pam", "Kreger", "Woodhouse", "Gillete", "Lana"))
df2$DateTime = ymd_hms(df2$DateTime)
</code></pre>

<p>What I would like now, is to be able to left join <code>df2</code> with <code>df1</code> based on a fuzzy match of <code>DateTime</code> and <code>Count</code> being within two seconds of their respective values, while all other values except <code>Item</code> are identical. I thought I could get there with the following:</p>

<pre><code>df1 %&gt;%
  difference_left_join(df2, by=c("DateTime", "Count"), max_dist=2)
</code></pre>

<p>But that gives me the following output:</p>

<pre><code> # A tibble: 8 × 10
  Item.x          DateTime.x Count.x   Name.x Friend.x Item.y          DateTime.y Count.y   Name.y  Friend.y
   &lt;int&gt;              &lt;dttm&gt;   &lt;dbl&gt;    &lt;chr&gt;    &lt;chr&gt;  &lt;int&gt;              &lt;dttm&gt;   &lt;dbl&gt;    &lt;chr&gt;     &lt;chr&gt;
1      1 2015-01-01 11:12:14       1 Sterling      Pam     21 2015-01-01 11:12:15       3 Sterling       Pam
2      1 2015-01-01 11:12:14       1 Sterling      Pam     21 2015-01-01 11:12:15       3 Sterling       Pam
3      2 2015-01-02 09:15:23       6 Sterling    Cyril     NA                &lt;NA&gt;      NA     &lt;NA&gt;      &lt;NA&gt;
4      3 2015-01-02 15:46:11      11 Sterling   Cheryl     23 2015-01-02 15:46:11      11 Sterling Woodhouse
5      3 2015-01-02 15:46:11      11 Sterling   Cheryl     23 2015-01-02 15:46:11      11 Sterling Woodhouse
6      4 2015-04-19 22:11:33      15 Sterling  Mallory     NA                &lt;NA&gt;      NA     &lt;NA&gt;      &lt;NA&gt;
7      5 2015-06-10 07:00:00       9 Sterling     Lana     25 2015-06-10 07:00:02       8 Sterling      Lana
8      5 2015-06-10 07:00:00       9 Sterling     Lana     25 2015-06-10 07:00:02       8 Sterling      Lana
</code></pre>

<p>This is close, except that row 3 should not have merged given that the names are different (and I would have expected row 2 to merge given the thresholds, even though I don't want it to). </p>

<p>How do I end up with the following dataframe? Note that the second row and third row from <code>df2</code> was not merged despite <code>DateTime</code> and <code>Count</code> meeting the threshold limit. This is because the other columns (except <code>Item</code>) were not identical.</p>

<pre><code>desired_output
#   Item            DateTime Count     Name  Friend
# 1    3 2015-01-02 15:46:11    11 Sterling  Cheryl
# 2    2 2015-01-02 09:15:23     6 Sterling   Cyril
# 3    5 2015-06-10 07:00:00     9 Sterling    Lana
# 4   25 2015-06-10 07:00:02     8 Sterling    Lana
# 5    4 2015-04-19 22:11:33    15 Sterling Mallory
# 6    1 2015-01-01 11:12:14     1 Sterling     Pam
# 7   21 2015-01-01 11:12:15     3 Sterling     Pam
</code></pre>