Non-equi join of dates using data table

Question

I have a data table of edits:

library(data.table)

edits <- data.table(proposal=c('A','A','A'),
           editField=c('probability','probability','probability'),
           startDate=as.POSIXct(c('2017-04-14 00:00:00','2019-09-06 12:12:00','2018-10-10 15:47:00')),
           endDate=as.POSIXct(c('2019-09-06 12:12:00','2018-10-10 15:47:00','9999-12-31 05:00:00')),
           value=c(.1,.3,.1))

   proposal   editField           startDate             endDate value
1:        A probability 2017-04-14 00:00:00 2019-09-06 12:12:00   0.1
2:        A probability 2019-09-06 12:12:00 2018-10-10 15:47:00   0.3
3:        A probability 2018-10-10 15:47:00 9999-12-31 05:00:00   0.1

That I would like to join to a data table of events:

events <-     data.table(proposal='A',
                  editDate=as.POSIXct(c('2017-04-14 00:00:00','2019-09-06 12:12:00','2019-09-06 12:12:00','2019-09-06 12:12:00','2018-07-04 15:33:59','2018-07-27 08:01:00','2018-10-10 15:47:00','2018-10-10 15:47:00','2018-10-10 15:47:00','2018-11-26 11:10:00','2019-02-05 13:06:59')),
                  editField=c('Created','stage','probability','estOrder','estOrder','estOrder','stage','probability','estOrder','estOrder','estOrder'))

    proposal            editDate   editField
 1:        A 2017-04-14 00:00:00     Created
 2:        A 2019-09-06 12:12:00       stage
 3:        A 2019-09-06 12:12:00 probability
 4:        A 2019-09-06 12:12:00    estOrder
 5:        A 2018-07-04 15:33:59    estOrder
 6:        A 2018-07-27 08:01:00    estOrder
 7:        A 2018-10-10 15:47:00       stage
 8:        A 2018-10-10 15:47:00 probability
 9:        A 2018-10-10 15:47:00    estOrder
10:        A 2018-11-26 11:10:00    estOrder
11:        A 2019-02-05 13:06:59    estOrder

To get an output that looks like this, where the value specifies the value of the probability at the time the edit took place:

desired.join <- cbind(events, value=c(.1,.3,.3,.3,.3,.3,.3,.1,.1,.1,.1))
    proposal            editDate   editField value
 1:        A 2017-04-14 00:00:00     Created   0.1
 2:        A 2019-09-06 12:12:00       stage   0.3
 3:        A 2019-09-06 12:12:00 probability   0.3
 4:        A 2019-09-06 12:12:00    estOrder   0.3
 5:        A 2018-07-04 15:33:59    estOrder   0.3
 6:        A 2018-07-27 08:01:00    estOrder   0.3
 7:        A 2018-10-10 15:47:00       stage   0.3
 8:        A 2018-10-10 15:47:00 probability   0.1
 9:        A 2018-10-10 15:47:00    estOrder   0.1
10:        A 2018-11-26 11:10:00    estOrder   0.1
11:        A 2019-02-05 13:06:59    estOrder   0.1

This is what I have so far to try to join the two:

edits[editField=='probability'][events, on=.(proposal, startDate<=editDate, endDate>editDate)]

However when I attempt this, I get an error message reading,"Error in vecseq(f__, len__, if (allow.cartesian || notjoin || !anyDuplicated(f__, : Join results in 16 rows; more than 14 = nrow(x)+nrow(i). Check for duplicate key values in i each of which join to the same group in x over and over again. If that's ok, try by=.EACHI to run j for each group to avoid the large allocation. If you are sure you wish to proceed, rerun with allow.cartesian=TRUE. Otherwise, please search for this error message in the FAQ, Wiki, Stack Overflow and data.table issue tracker for advice."

Have a look at [this](https://stackoverflow.com/questions/21560500/data-table-merge-based-on-date-ranges), in particular the edit (Version 3) on the main answer around the `foverlaps` function. I've used it for joining date ranges. Easy to use and quick. https://www.rdocumentation.org/packages/data.table/versions/1.12.6/topics/foverlaps — Jonny Phelps, Nov 27 '19 at 16:31
the error is saying that there is a one to many matches. be sure and clear of what you are doing and then you can add `allow.cartesian=TRUE` in the join to fix this error. — chinsoon12, Nov 28 '19 at 00:24

greg5678 · Accepted Answer · 2019-12-02T14:45:54.330

It looks like you are trying to join edits and events so that a probability value from the edits data table is associated with the correct observation from the events data table.

It looks like the error is ocuring because the time intervals used to create the edits data table are not mutually exclusive. When I modify the time intervals to what I think you intended, then your code gives the result that you were looking for.

library(data.table)

edits <- data.table(proposal=c('A','A','A'),
    editField=c('probability','probability','probability'),
    startDate=as.POSIXct(c('2017-04-14 00:00:00','2018-10-10 15:47:00','2019-09-06 12:12:00')),
    endDate=as.POSIXct(c('2018-10-10 15:47:00','2019-09-06 12:12:00','9999-12-31 05:00:00')),
    value=c(.1,.3,.1))

events <- data.table(proposal='A',
    editDate=as.POSIXct(c('2017-04-14 00:00:00','2019-09-06 12:12:00','2019-09-06 12:12:00','2019-09-06 12:12:00','2018-07-04 15:33:59','2018-07-27 08:01:00','2018-10-10 15:47:00','2018-10-10 15:47:00','2018-10-10 15:47:00','2018-11-26 11:10:00','2019-02-05 13:06:59')),
    editField=c('Created','stage','probability','estOrder','estOrder','estOrder','stage','probability','estOrder','estOrder','estOrder'))

edits[editField=='probability'][events, on=.(proposal, startDate<=editDate, endDate>editDate)]

or you can do the join with out chaining it

  edits[events, on=.(proposal, startDate<=editDate, endDate>editDate)]

or you could do as Jonny Phelps suggested and use foverlaps, but this also requires mutually exclusive time intervals in the edits data table

events[,startDate:= editDate]

setkey(events, startDate, editDate)

setkey(edits, startDate, endDate)

foverlaps(events, edits, type="any", mult="first")

Non-equi join of dates using data table

1 Answers1

Linked