Merging two dataframes based on overlapping ranges
<p>I want to merge two data frames of differing lengths based on information within one column. The data in columns 3 and 4 (<code>FROM</code> and <code>TO</code>) describe length of core for a given lithology in the data frame <code>Lithy</code>.</p>

<p>The core was analyzed with respect to <code>S</code> at smaller size fragments in data frame <code>chemy</code>. The <code>FROM</code> and <code>TO</code> for <code>chemy</code> sit inside the range described by <code>Lithy</code>.  </p>

<p><code>chemy</code>:</p>

<pre><code>coreID location  FROM    TO    S
1    12SW        1  52.5  56.5 0.50
2    12SW        1  56.7  65.0 0.30
3    12SW        1  66.0 402.0 0.20
4    13NW        1  10.0  30.0 0.60
5    13NW        1  32.0  35.0 0.40
6    13NW        1  36.0  43.0 0.20
7     13S        4   1.0   2.0 0.60
8     13S        4   5.0  25.0 0.50
9     13S        4  26.0 150.0 0.10
10    13S        4 151.0 155.0 0.05
</code></pre>

<p><code>Lithy</code>:</p>

<pre><code>coreID location  FROM  TO      Lith1  Lith2 Lith3 
1   12SW        1  52.5 350 peridotite   fine black
2   12SW        1 350.0 420 peridotite coarse green
3   13NW        1   3.0  50  saprolite             
4   13NW        1  51.0 400    granite             
5    13S        4   1.0 150    diorite             
6    13S        4 151.0 300 peridotite   fine black
</code></pre>

<p>When I tried to merge, the resulting table was missing data rows &ndash; looks like it only merged for matches between <code>coreID</code> and <code>location</code>, rather than merging based on range in <code>FROM</code> and <code>TO</code>.</p>

<p>The code I used to merge is :
    together = merge(chemy, lithy)
The outcome I would like to to have the lithology columns added to the chemy data frame, so that lithology is identified in appropriate rows (applied across range described by FROM and TO). </p>