Find overlap of multiple ranges in data.table

Question

I would like to find the overlapping part of multiple ranges which are given rowise in a data.table object.

An example would be:

t <- data.table(a = c(3,4,5), b = c(13,12,19))

So we have the ranges:

3 - 13, 4 - 12, 5 - 19

Hence the overlapping range would be:

5 - 12

In case of an additional range 19 - 22 the overlap should return NA - NA or 0 - 0 since there is no overlap.

I found solutions for similar problems like spatstat.utils:: intersect.ranges(). However this works only on two vectors and is hard to implement in a data.table

    DT[,.(o.l = function()[1], o.r = function()[2], by=.()]

manner which I would really like to do if possible,..

As output for this example I would like to have:

t <- data.table(a = c(3,4,5), b = c(13,12,19), o.l = c(5,5,5), o.r = c(12,12,12))

I have used foverlaps to join two data.tables, but would not know how to apply it in this context now — yasel, Jul 12 '19 at 13:58
`data.table(a = c(3,4,5), b = c(13,12,19))[, .(max(a), min(b))]` — pogibas, Jul 12 '19 at 14:12
thank you, that would of course work for that example. I adapted the question a bit to account for that case too — yasel, Jul 12 '19 at 14:19

score 2 · Accepted Answer · answered Jul 12 '19 at 14:21

Here's a one-line example:

library(data.table)

dt = data.table(a = c(3,4,5), b = c(13,12,19))

dt[, c("o.l", "o.r") := as.list(range(Reduce(intersect, mapply(seq, a, b, 1))))]

dt
#    a  b o.l o.r
# 1: 3 13   5  12
# 2: 4 12   5  12
# 3: 5 19   5  12

Where the core of the problem is

dt = data.table(a = c(3,4,5), b = c(13,12,19))
dt[, Reduce(intersect, mapply(seq, a, b, 1))]
# [1]  5  6  7  8  9 10 11 12

chinsoon12 · Answer 2 · 2019-07-15T01:50:27.063

Borrowing idea from David Aurenburg answer in How to flatten / merge overlapping time periods, here is another possible approach:

DT[, g := c(0L, cumsum(shift(a, -1L) >= cummax(b))[-.N])][, 
    c("ol", "or") := .(max(a), min(b)), g]

data:

DT <- data.table(a = c(3,4,5,19,20,24), b = c(13,12,19,22,23,25))

output:

    a  b g ol or
1:  3 13 0  5 12
2:  4 12 0  5 12
3:  5 19 0  5 12
4: 19 22 1 20 22
5: 20 23 1 20 22
6: 24 25 2 24 25

Find overlap of multiple ranges in data.table

2 Answers2