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.wick...@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_gurbux...@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@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 ______________________________________________ R-help@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.
