The disequilibrium complexity in R

Question

I am trying to make a complex unequal join between two tables. I was inspired by the last presentation of R2016 ( https://channel9.msdn.com/events/useR-international-R-User-conference/useR2016/Efficient-in-memory-non-equi-joins-using-datatable ) which made me believe that this would be a suitable task for data.table. My table 1 looks like this:

library(data.table) sp <- c("SAB","SAB","SAB","SAB","EPN","EPN","BOP","BOP","BOP","BOP","BOP","PET","PET","PET") dbh <- c(10,12,16,22,12,16,10,12,14,20,26,12,16,18) dt1 <- data.table(sp,dbh) dt1 sp dbh 1: SAB 10 2: SAB 12 3: SAB 16 4: SAB 22 5: EPN 12 6: EPN 16 7: BOP 10 8: BOP 12 9: BOP 14 10: BOP 20 11: BOP 26 12: PET 12 13: PET 16 14: PET 18 

This is a list of trees with their dbh. My second table (below) gives a general table that gives a dbh range for each type of tree to classify a size class or tree:

 gr_sp <- c("RES","RES","RES","RES","RES","RES", "DEC", "DEC", "DEC", "DEC", "DEC", "DEC") sp <- c("SAB","SAB", "SAB", "EPN", "EPN", "EPN", "BOP", "BOP", "BOP", "PET", "PET", "PET") dbh_min <- c(10, 16, 22, 10, 14, 20, 10, 18, 24, 10, 20, 26) dbh_max <- c(14, 20, 30, 12, 18, 30, 16, 22, 30, 18, 24, 30) dhb_clas <- c("s", "m", "l", "s", "m", "l", "s", "m", "l", "s", "m", "l") dt2 <- data.table(gr_sp, sp, dbh_min, dbh_max, dhb_clas) dt2 gr_sp sp dbh_min dbh_max dhb_clas 1: RES SAB 10 14 s 2: RES SAB 16 20 m 3: RES SAB 22 30 l 4: RES EPN 10 12 s 5: RES EPN 14 18 m 6: RES EPN 20 30 l 7: DEC BOP 10 16 s 8: DEC BOP 18 22 m 9: DEC BOP 24 30 l 10: DEC PET 10 18 s 11: DEC PET 20 24 m 12: DEC PET 26 30 l 

I want my final table to be a union of two tables by type (sp field) and within the dhb range specified in "DBH_MIN" and "DBH_MAX". This would make my table look like:

 data.table(dt1, gr_sp = c("RES","RES","RES","RES","RES","RES","DEC","DEC","DEC","DEC","DEC","DEC","DEC","DEC"), dhb_clas = c("s","s","m","l","s","m","s","s","s","m","l","s","s","s")) sp dbh gr_sp dhb_clas 1: SAB 10 RES s 2: SAB 12 RES s 3: SAB 16 RES m 4: SAB 22 RES l 5: EPN 12 RES s 6: EPN 16 RES m 7: BOP 10 DEC s 8: BOP 12 DEC s 9: BOP 14 DEC s 10: BOP 20 DEC m 11: BOP 26 DEC l 12: PET 12 DEC s 13: PET 16 DEC s 14: PET 18 DEC s 

I tried something like:

 dt1[dt2, on=.(sp=sp, dbh>=dbh_min, dbh<=dbh_max)] 

which gives too many lines ...

thank you for your help

Answer 1

So, I was very close. I had 2 problems, at first the poor installation of the data.table package ( Error in the data table could not find the "." Function ) caused an unclear error.

After I fixed this, I came closer, finding that:

 dt1[dt2, on=.(sp=sp, dbh>=dbh_min, dbh<=dbh_max), nomatch=0] 

gave me what i wanted with a bad dbh column. Invert a command using:

 dt2[dt1, on=.(sp=sp, dbh_min<=dbh, dbh_max>=dbh)] 

only one useless extra column fixed.

Answer 2

For "intermediate" joins like this, you can also use data.table::foverlaps , which joins two data.table in ranges that overlap, instead of using unequal joins.

Taking the same example, the following code will give the desired result.

 # foverlap tests the overlap of two ranges. Create a second column, # dbh2, as the end point of the range. dt1[, dbh2 := dbh] # foverlap requires the second argument to be keyed setkey(dt1, sp, dbh, dbh2) # find rows where dbh falls between dbh_min and dbh_max, and drop unnecessary # columns afterwards foverlaps(dt2, dt1, by.x = c("sp", "dbh_min", "dbh_max"), by.y = key(dt1), nomatch = 0)[ , -c("dbh2", "dbh_min", "dbh_max") ] # sp dbh gr_sp dhb_clas # 1: SAB 10 RES s # 2: SAB 12 RES s # 3: SAB 16 RES m # 4: SAB 22 RES l # 5: EPN 12 RES s # 6: EPN 16 RES m # 7: BOP 10 DEC s # 8: BOP 12 DEC s # 9: BOP 14 DEC s # 10: BOP 20 DEC m # 11: BOP 26 DEC l # 12: PET 12 DEC s # 13: PET 16 DEC s # 14: PET 18 DEC s