1

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

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

chemy:

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

Lithy:

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

When I tried to merge, the resulting table was missing data rows – looks like it only merged for matches between coreID and location, rather than merging based on range in FROM and TO.

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

Veerendra Gadekar
  • 4,452
  • 19
  • 24
user3196261
  • 11
  • 2
  • Please show the code you used for merging. – Stibu Feb 12 '16 at 14:52
  • See: http://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right – h3rm4n Feb 12 '16 at 15:45
  • FROM-TO in lithy describe a range for which a given lith is present. Several rows of data in "chemy" fall within the range described by FROM TO in "lithy". I want to merge them, but across the range- not just for the instances they match (which is rare). – user3196261 Feb 15 '16 at 19:11

1 Answers1

1

You could try this using foverlaps from data.table

library(data.table)
setDT(chemy)
setDT(lithy)
setkey(lithy, coreID, location, FROM, TO)

out = foverlaps(chemy, lithy, type="within",nomatch=0L)
req = setnames(out, gsub('\\<i.', 'chemy.', colnames(out)))

#>req
#   coreID location  FROM  TO      Lith1 Lith2 Lith3 chemy.FROM chemy.TO    S
#1:   12SW        1  52.5 350 peridotite  fine black       52.5     56.5 0.50
#2:   12SW        1  52.5 350 peridotite  fine black       56.7     65.0 0.30
#3:   13NW        1   3.0  50  saprolite                   10.0     30.0 0.60
#4:   13NW        1   3.0  50  saprolite                   32.0     35.0 0.40
#5:   13NW        1   3.0  50  saprolite                   36.0     43.0 0.20
#6:    13S        4   1.0 150    diorite                    1.0      2.0 0.60
#7:    13S        4   1.0 150    diorite                    5.0     25.0 0.50
#8:    13S        4   1.0 150    diorite                   26.0    150.0 0.10
#9:    13S        4 151.0 300 peridotite  fine black      151.0    155.0 0.05
Veerendra Gadekar
  • 4,452
  • 19
  • 24