[R] Merge with closest (not equal) time stamps

[Naresh Gurbuxani]

Thanks for your suggestion. 

I have just returned from a vacation and started catching up on my emails. 

Rolling join is an elegant and most suitable solution for my tasks.  I invested some time in learning data.table package.  The vignette on secondary indices and auto indexing refers to another vignette on joins and rolling joins.  But the vignette on joins is missing.  There is already some discussion on the internet regarding this missing vignette.  

Here is one more request for this joins vignette, even if in a draft form.  

Naresh  

> On Aug 9, 2023, at 8:39 AM, Hadley Wickham <h.wickham using gmail.com> wrote:
> 
> It sounds like you might want a rolling join, e.g.
> https://dplyr.tidyverse.org/reference/join_by.html#rolling-joins.
> 
> (And data.table has similar functionality which inspired dplyr)
> 
> Hadley
> 
> On Mon, Aug 7, 2023 at 9:32 PM Naresh Gurbuxani
> <naresh_gurbuxani using hotmail.com> wrote:
>> 
>> 
>> I have two dataframes, each with a column for timestamp.  I want to
>> merge the two dataframes such that each row from first dataframe
>> is matched with the row in the second dataframe with most recent but
>> preceding timestamp. Here is an example.
>> 
>> option.trades <- data.frame(timestamp = as.POSIXct(c("2023-08-07 10:23:22", "2023-08-07 10:25:33", "2023-08-07 10:28:41")), option.price = c(2.5, 2.7, 1.8))
>> 
>> stock.trades <- data.frame(timestamp = as.POSIXct(c("2023-08-07 10:23:21", "2023-08-07 10:23:34", "2023-08-07 10:24:57", "2023-08-07 10:28:37", "2023-08-07 10:29:01")), stock.price = c(102.2, 102.9, 103.1, 101.8, 101.7))
>> 
>> stock.trades <- stock.trades[order(stock.trades$timestamp),]
>> 
>> library(plyr)
>> mystock.prices <- ldply(option.trades$timestamp, function(tstamp) tail(subset(stock.trades, timestamp <= tstamp), 1))
>> names(mystock.prices)[1] <- "stock.timestamp"
>> myres <- cbind(option.trades, mystock.prices)
>> 
>> This method works. But for large dataframes, it is very slow.  Is there
>> a way to speed up the merge?
>> 
>> Thanks,
>> Naresh
>> 
>> ______________________________________________
>> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
> 
> 
> 
> -- 
> http://hadley.nz