Non-equi join of data table operation

Question

I'd like to add columns to data table 1 that are operations on data table 2, joining by a variable and where dates from data table 2 are <= the dates from data table 1. I'm looking for a solution that isn't too computationally expensive (I have about 20k rows).

Data table 1 - I have a dataset of proposals, their owners, and their editDates:

proposal_df <- structure(list(proposal = c(41, 62, 169, 72), owner = c("Adam", 
"Adam", "Alan", "Alan"), totalAtEdit = c(-27, 1000, 151, 1137
), editDate = structure(c(1556014200, 1560762240, 1563966600, 
1540832280), class = c("POSIXct", "POSIXt"), tzone = "UTC")), class = "data.table", row.names = c(NA, 
-4L))

  proposal owner totalAtEdit            editDate
1       41  Adam         -27 2019-04-23 10:10:00
2       62  Adam        1000 2019-06-17 09:04:00
3      169  Alan         151 2019-07-24 11:10:00
4       72  Alan        1137 2018-10-29 16:58:00

Data table 2 - I have a log of proposals and the date at which they were won or lost (outcome == 1 or 0):

proposal_log <- structure(list(proposal = c(9, 48, 43, 39, 45, 73, 111, 179, 
115, 146), outcome = c(0, 1, 1, 1, 0, 0, 0, 0, 0, 0), owner = c("Adam", 
"Adam", "Adam", "Adam", "Adam", "Alan", "Alan", "Alan", "Alan", 
"Alan"), totalAtEdit = c(2, 2, 4, 566, 100, 1264, 5000, 75, 493, 
18), editDate = structure(c(1557487860, 1561368780, 1561393140, 
1546446240, 1549463520, 1546614180, 1547196960, 1579603560, 1566925200, 
1536751800), class = c("POSIXct", "POSIXt"), tzone = "UTC")), class = "data.table", row.names = 
c(NA, 
-10L))

   proposal outcome owner totalAtEdit            editDate
1         9       0  Adam           2 2019-05-10 11:31:00
2        48       1  Adam           2 2019-06-24 09:33:00
3        43       1  Adam           4 2019-06-24 16:19:00
4        39       1  Adam         566 2019-01-02 16:24:00
5        45       0  Adam         100 2019-02-06 14:32:00
6        73       0  Alan        1264 2019-01-04 15:03:00
7       111       0  Alan        5000 2019-01-11 08:56:00
8       179       0  Alan          75 2020-01-21 10:46:00
9       115       0  Alan         493 2019-08-27 17:00:00
10      146       0  Alan          18 2018-09-12 11:30:00

I want to add several columns to proposal_df that are operations on proposal_log, joining by owner and where proposal_log$editDate <= proposal_df$editDate:

countWon - number of proposals where outcome == 1
countLost - number of proposals where outcome == 0
wonValueMean - totalAtEdit mean of proposals where outcome == 1
pctWon - % of proposals where outcome == 1

Output would look like this:

  proposal owner totalAtEdit            editDate countWon countLost wonValueMean    pctWon
1       41  Adam         -27 2019-04-23 10:10:00        1         1          566 0.5000000
2       62  Adam        1000 2019-06-17 09:04:00        1         2          566 0.3333333
3      169  Alan         151 2019-07-24 11:10:00        0         3          NaN 0.0000000
4       72  Alan        1137 2018-10-29 16:58:00        0         1          NaN 0.0000000

Thanks!

What does the `outcome` variable in your desired output mean? — Edward, Mar 22 '20 at 06:28
Whoops I forgot those were there. The ```outcome``` and ```manager``` variables are irrelevant to the question, so I removed them. — Slash, Mar 22 '20 at 14:41

score 2 · Answer 1 · answered Mar 22 '20 at 05:47

There may be a more elegant solution, but this gives the desired output in 4 steps.

First, set the tables to data tables in order to perform the non-equi join.

library(data.table)

setDT(proposal_df)
setDT(proposal_log)

Step 1: Non-equi join for same owner and proposal_log$editDate <= proposal_df$editDate.

Proposals <- proposal_log[proposal_df, on = .(owner, editDate <= editDate)]

This returns the proposals in proposal_log that meet the conditions. The proposal and totalAtEdit variables from the smaller table are added to the result, prefixed by i..

   proposal outcome owner totalAtEdit            editDate i.proposal i.totalAtEdit
1:       39       1  Adam         566 2019-04-23 10:10:00         41           -27
2:       45       0  Adam         100 2019-04-23 10:10:00         41           -27
3:        9       0  Adam           2 2019-06-17 09:04:00         62          1000
4:       39       1  Adam         566 2019-06-17 09:04:00         62          1000
5:       45       0  Adam         100 2019-06-17 09:04:00         62          1000
6:       73       0  Alan        1264 2019-07-24 11:10:00        169           151
7:      111       0  Alan        5000 2019-07-24 11:10:00        169           151
8:      146       0  Alan          18 2019-07-24 11:10:00        169           151
9:      146       0  Alan          18 2018-10-29 16:58:00         72          1137

Step 2: Reshape this into wide format to count (fun=length) the number of outcomes for each i.proposal and then calculate the proportion of outcomes that were won (outcome=1).

Outcomes <- dcast(Proposals, i.proposal ~ outcome, fun=length)[
  , pctWon := `1`/(`0`+`1`)]

Step 3: Calculate the mean value of totalAtEdit for outcomes that were won (outcome==1) for each proposal and inner join with the Outcomes on the proposal ID.

Means <- Proposals[outcome==1, .(m_total = mean(totalAtEdit)), by=i.proposal]
Outcomes[Means, on=.(i.proposal), wonValueMean := m_total]

Step 4: Join this with the proposal_df table.

proposal_df[Outcomes, on=c(proposal = "i.proposal")]

   proposal owner totalAtEdit            editDate 0 1    pctWon wonValueMean
1:       41  Adam         -27 2019-04-23 10:10:00 1 1 0.5000000          566
2:       62  Adam        1000 2019-06-17 09:04:00 2 1 0.3333333          566
3:       72  Alan        1137 2018-10-29 16:58:00 1 0 0.0000000           NA
4:      169  Alan         151 2019-07-24 11:10:00 3 0 0.0000000           NA

score 1 · Accepted Answer · answered Mar 23 '20 at 01:10

Another option is to use by=.EACHI:

library(data.table)
setDT(proposal_df)
setDT(proposal_log)
proposal_df[, c("countWon","countLost","wonValueMean","pctWon") := 
    proposal_log[.SD, on=.(owner, editDate<=editDate), by=.EACHI, {
        cw <- sum(outcome==1L)
        .(cw, sum(outcome==0L), mean(x.totalAtEdit[outcome==1L]), cw/.N)
    }][, (1L:2L) := NULL]
]

Non-equi join of data table operation

2 Answers2