CODEWITHSUNDEEP
r
Digsby
Digsby

Reputation: 151

Printing out a list of values from a column in one table for every row with an overlapping interval in another table

I'd like to be able to compare values in two columns in one table "r" to values in two columns in another table "f", and print the value in a third column from "f" for every row in "r" where "r" values overlap "f" values.

Table r:

> rn=c("i","ii","iii","iv","v","vi","vii","viii")
> rs=c(1,21,3,23,6,28,7,26)
> re=c(10,30,17,42,18,43,11,31)
> r=data.frame(rn,rs,re);r

    rn rs re
1    i  1 10
2   ii 21 30
3  iii  3 17
4   iv 23 42
5    v  6 18
6   vi 28 43
7  vii  7 11
8 viii 26 31

Table f:

> fn=c("a","b")
> fs=c(5,25)
> fe=c(15,40)
> f=data.frame(fn,fs,fe)
> f$fl=f$fe-f$fs;f

  fn fs fe fl
1  a  5 15 10
2  b 25 40 15

Ultimately, my desired output would be an additional column in the "r" table that listed the corresponding f$fl value in the "f" table if the r$rs to r$re interval overlaps the f$fs to f$fe interval.

Desired output:

     rn rs re flen
1    i  1 10   10
2   ii 21 30   15
3  iii  3 17   10
4   iv 23 42   15
5    v  6 18   10
6   vi 28 43   15
7  vii  7 11   10
8 viii 26 31   15

What I've tried:

> setDT(r)[f, flen := fl, on = .(re >= fs, re <= fe)][,flen := nafill(flen, 'locf')];r
     rn rs re flen
1:    i  1 10   10
2:   ii 21 30   15
3:  iii  3 17   10
4:   iv 23 42   10
5:    v  6 18   10
6:   vi 28 43   10
7:  vii  7 11   10
8: viii 26 31   15

This doesn't quite work

Upvotes: 1

Views: 393

Answers (1)

akrun
akrun

Reputation: 886948

Perhaps, we can do a non-equi join from data.table

library(data.table)
setDT(r)[f, flen := fl, on = .(re >= fs, re <= fe)]
r[f, flen := fl, on = .(rs >= fs, rs <= fe)]
r[f, flen := fl, on = .(rs <= fs, re >= fe)]

-output

r
#     rn rs re flen
#1:    i  1 10   10
#2:   ii 21 30   15
#3:  iii  3 17   10
#4:   iv 23 42   15
#5:    v  6 18   10
#6:   vi 28 43   15
#7:  vii  7 11   10
#8: viii 26 31   15

Upvotes: 1

Related Questions