<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/in-between-a-rock-and-a-conditional-join/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://www.mango-solutions.com/blog/in-between-a-rock-and-a-conditional-join"> Mango Solutions</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p> <img sizes="100vw" src="https://i2.wp.com/www.mango-solutions.com/blog/images/0/d/f/c/a/0dfca9618de0bd1bf5ba903d1f8e00f4a1f82f89-adnan-blog-header.png?w=456&ssl=1" data-recalc-dims="1"><br>
</p>
<h4><em>Adnan Fiaz</em></h4>
<p>Joining two datasets is a common action we perform in our analyses. Almost all languages have a solution for this task: R has the built-in <code>merge</code> function or the family of <code>join</code> functions in the <strong>dplyr</strong> package, SQL has the <code>JOIN</code> operation and Python has the <code>merge</code> function from the <strong>pandas</strong> package. And without a doubt these cover a variety of use cases but there’s always that one exception, that one use case that isn’t covered by the obvious way of doing things.</p>
<p>In my case this is to join two datasets based on a conditional statement. So instead of there being specific columns in both datasets that should be equal to each other I am looking to compare based on something else than equality (e.g. larger than). The following example should hopefully make things clearer.</p>
<pre><code>myData &lt;- data.frame(Record = seq(5), SomeValue=c(10, 8, 14, 6, 2))
myData</code></pre>
<pre><code>##   Record SomeValue
## 1      1        10
## 2      2         8
## 3      3        14
## 4      4         6
## 5      5         2</code></pre>
<p>The above dataset, <em>myData</em>, is the dataset to which I want to add values from the following dataset:</p>
<pre><code>linkTable &lt;- data.frame(ValueOfInterest = letters[1:3], LowerBound = c(1, 4, 10),
                        UpperBound = c(3, 5, 16))
linkTable</code></pre>
<pre><code>##   ValueOfInterest LowerBound UpperBound
## 1               a          1          3
## 2               b          4          5
## 3               c         10         16</code></pre>
<p>This second dataset, <em>linkTable</em>, is the dataset containing the information to be added to <em>myData</em>. You may notice the two dataset have no columns in common. That is because I want to join the data based on the condition that <em>SomeValue</em> is between <em>LowerBound</em> and <em>UpperBound</em>. This may seem like an artificial (and perhaps trivial) example but just imagine <em>SomeValue</em> to be a date or zip code. Then imagine the <em>LowerBound</em> and <em>UpperBound</em> to be bounds on a specific time period or geographical region respectively.</p>
<p>In Mango’s R <a rel="nofollow" href="https://www.mango-solutions.com/data-science/r-training/courses.html" target="_blank">training courses</a> one of the most important lessons we teach our participants is that the answer is just as important as <strong>how</strong> you obtain the answer. So i’ll try to convey that here too instead of just giving you the answer.</p>
<div>
<h4>Helping you help yourself</h4>
<p>So the first step in finding the answer is to explore R’s comprehensive help system and documentation. Since we’re talking about joins its only natural to look at the documentation of the <code>merge</code> function or the <code>join</code> functions from the <strong>dplyr</strong> package. Unfortunately both only have the option to supply columns that are compared to each other based on equality. However the documentation for the <code>merge</code> functions does mention that when no columns are given the function performs a Cartesian product. That’s just a seriously cool way of saying every row from <em>myData</em> is joined with every row from <em>linkTable</em>. It might not solve the task but it does give me the following idea:</p>
<pre><code># Attempt #1: Do a cartesian product and then filter the relevant rows
merge(myData, linkTable) %>% 
  filter(SomeValue >= LowerBound, SomeValue &lt;= UpperBound) %>% 
  select(-LowerBound, -UpperBound)</code></pre>
<pre><code>##   Record SomeValue ValueOfInterest
## 1      5         2               a
## 2      1        10               c
## 3      3        14               c</code></pre>
<p>You can do the above in <strong>dplyr</strong> as well but I’ll leave that as an exercise. The more important question is: what is wrong with the above answer? You may notice that we’re missing records 2 and 4. That’s because these didn’t satisfy the filtering condition. If we wanted to add them back in we would have to do another join. Something that you won’t notice with these small example datasets is that a Cartesian product is an expensive operation, combining all the records of two datasets can result in an explosion of values.</p>
</div>
<div>
<h4>(Sometimes) a SQL is better than the original</h4>
<p>When neither of the built-in functions or functions from packages you know solve the problem, the next step is to expand the search. You can directly resort to your favourite search engine (which will inevitably redirect you to Stack Overflow) but it helps to first narrow the search by thinking about any possible clues. For me that clue was that joins are an important part of SQL so I searched for a <a rel="nofollow" href="https://duckduckgo.com/?q=sql+between+join" target="_blank">SQL solution</a> that <a rel="nofollow" href="https://duckduckgo.com/?q=sql+in+r" target="_blank">works in R</a>.</p>
<p>The above search directed me to the excellent <strong>sqldf</strong> package. This package allows you to write SQL queries and execute them using data.frames instead of tables in a database. I can thus write a SQL JOIN query with a BETWEEN clause and apply it to my two tables.</p>
<pre><code>library(sqldf)
# Attempt #2: Execute a SQL query
sqldf('SELECT Record, SomeValue, ValueOfInterest 
      FROM myData 
      LEFT JOIN linkTable ON SomeValue BETWEEN LowerBound and UpperBound')</code></pre>
<pre><code>##   Record SomeValue ValueOfInterest
## 1      1        10               c
## 2      2         8            </code></pre></div>