Complexe non-equi merge in R
I'm trying to do a complexe non-equi join between two tables. I got inspired by a presentation in the last useR2016 (https://channel9.msdn.com/events/useR-international-R-User-conference/useR2016/Efficient-in-memory-non-equi-joins-using-datatable) which made me believe it would be a suitable task for data.table. My table 1 looks like:
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
It's a list of trees with their dbh. My second table (below) gives a generic table that gives for each tree species a range of dbh to classify the size class or the 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 the join of the two tables by species ("sp" field) and within the range of dhb stated by "DBH_MIN" and "DBH_MAX". That would make my table looks 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've tried something like:
dt1[dt2, on=.(sp=sp, dbh>=dbh_min, dbh<=dbh_max)]
which gives too many rows...
Thanks for your help
Solution 1:
So I was very close. I had 2 problems, first a bad installation of the data.table package (Data table error could not find function ".") caused an obscure error.
After having fixed that, I got closer an found that :
dt1[dt2, on=.(sp=sp, dbh>=dbh_min, dbh<=dbh_max), nomatch=0]
gave me what I wanted with a bad dbh column. Inverting the command with:
dt2[dt1, on=.(sp=sp, dbh_min<=dbh, dbh_max>=dbh)]
fixed the problem with only one useless extra column.
Solution 2:
For "between" joins like this one, one could also use data.table::foverlaps, which joins two data.table's on ranges that overlap, instead of using non-equi joins.
Taking the same example, the following code would produce the desired outcome.
# 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