Hi Naresh,
Perhaps the below is faster than your approach
library(dplyr)
library(tidyr)
merge(option.trades, stock.trades, by="timestamp", all=TRUE) |>
dplyr::arrange(timestamp) |>
dplyr::mutate(stock.timestamp =
as.POSIXct(ifelse(is.na(option.price), timestamp, NA))) |>
tidyr::fill(stock.price, stock.timestamp) |>
dplyr::filter(!is.na(option.price)) |>
dplyr::select(1,2,4,3) ->
myres2
identical(myres, myres2) ## TRUE
On Tue, Aug 8, 2023 at 5:32 AM 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.
______________________________________________
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.
