R dplyr: row-based conditions split/apply/combine

Question

This is the dplyr version of this question

I have the following data.table

initial.date <- as.POSIXct('2018-10-27 10:00:00',tz='GMT')
last.date <- as.POSIXct('2018-10-28 17:00:00',tz='GMT') 
    PriorityDateTime=seq.POSIXt(from=initial.date,to = last.date,by = '30 sec')
    TradePrice=seq(from=1, to=length(PriorityDateTime),by = 1)
    ndf<- data.frame(PriorityDateTime,TradePrice)
    ndf$InstrumentSymbol <- rep_len(x = c('asset1','asset2'),length.out = length(ndf$PriorityDateTime))
    ndf$id <- seq(1:length(x = ndf$InstrumentSymbol))
    ndf$datetime <- ymd_hms(ndf$PriorityDateTime)
    res <- ndf %>% data.table()

Looking like this:

    > res
         PriorityDateTime TradePrice InstrumentSymbol   id            datetime
   1: 2018-10-27 10:00:00          1           asset1    1 2018-10-27 10:00:00
   2: 2018-10-27 10:00:30          2           asset2    2 2018-10-27 10:00:30
   3: 2018-10-27 10:01:00          3           asset1    3 2018-10-27 10:01:00
   4: 2018-10-27 10:01:30          4           asset2    4 2018-10-27 10:01:30
   5: 2018-10-27 10:02:00          5           asset1    5 2018-10-27 10:02:00

Using dplyr what is the most elegant and fast way to:

Split: For each line define the other lines that have a datetime at most 60 secs in the past or future (time difference less than 60secs), and have the same InstrumentSymbol as this line's.
Apply: among these close lines, which one has the closest TradePrice to this line's TradePrice[i]: get the index in the original data.frame and the TradePrice of this other row
Combine: recombine the results as new columns into the original data.table for example as new columns index.minpricewithin60 and minpricewithin60

Example result:

> res
         PriorityDateTime TradePrice InstrumentSymbol   id            datetime minpricewithin60 index.minpricewithin60
   1: 2018-10-27 10:00:00          1           asset1    1 2018-10-27 10:00:00                2                      2
   2: 2018-10-27 10:00:30          2           asset2    2 2018-10-27 10:00:30                4                      4
   3: 2018-10-27 10:01:00          3           asset1    3 2018-10-27 10:01:00                1                      1
   4: 2018-10-27 10:01:30          4           asset2    4 2018-10-27 10:01:30                2                      2
   5: 2018-10-27 10:02:00          5           asset1    5 2018-10-27 10:02:00                3                      3

I guess my problem can be asked as "how to fix a row in dplyr in a similar way to apply(df,1, function(x) df$column-x["column"]) I have potential solutions using dplyr but so far all were quite slow.

Mankind_008 Mankind_008 · Accepted Answer · 2018-10-28T21:32:24

Solution using dplyr package and lapply function:

result_df <- do.call(rbind, lapply(1:nrow(res), function(row_id) {

             temp <-   res %>% filter(InstrumentSymbol == res$InstrumentSymbol[row_id]) %>% 
                       mutate(time_diff = abs(difftime(res$datetime[row_id], datetime, units = "secs")),
                              diff_price = abs(TradePrice - res$TradePrice[row_id])) %>% 
                       filter(id != res$id[row_id], time_diff <= 60) %>% 
                       filter(diff_price == min(diff_price)) %>% select(TradePrice, id) %>% 
                       rename(minpricewithin60 = TradePrice, index.minpricewithin60 = id)

             if(nrow(temp) == 0) temp[1,] <- c(NA, NA)

             return(bind_cols(res %>% slice(rep(row_id, nrow(temp))), temp))
                                                                  }))

head(result_df)

     PriorityDateTime TradePrice InstrumentSymbol id            datetime minpricewithin60 index.minpricewithin60
1 2018-10-27 10:00:00          1           asset1  1 2018-10-27 10:00:00                3                      3
2 2018-10-27 10:00:30          2           asset2  2 2018-10-27 10:00:30                4                      4
3 2018-10-27 10:01:00          3           asset1  3 2018-10-27 10:01:00                1                      1
4 2018-10-27 10:01:00          3           asset1  3 2018-10-27 10:01:00                5                      5
5 2018-10-27 10:01:30          4           asset2  4 2018-10-27 10:01:30                2                      2
6 2018-10-27 10:01:30          4           asset2  4 2018-10-27 10:01:30                6                      6

R dplyr: row-based conditions split/apply/combine

1 Answers