"lag" in irregular time series
I have a data.frame that shows the current price and bid price of a stock and my signal at that time.
time bid_price ask_price signal
10:10:01.000500 50.02 50.05 50.03
10:10:01.000855 50.02 50.03 50.05
10:10:01.000856 50.02 50.03 50.06
at 10: 10: 01.000856 and I have a signal at 50.06, I cannot use it. I can only use a signal 50 microseconds ago
So I need this data.frame result
at 10: 10: 01.000856, 50 microseconds ago, the time is 10: 01: 01.000806, so a useful signal that the time is 50.03
time bid_price ask_price signal signal_50microseconds_ago
10:10:01.000500 50.02 50.05 50.03 NA
10:10:01.000855 50.02 50.04 50.05 50.03
10:10:01.000856 50.02 50.04 50.06 50.03
Is there an R / python solution that generates a data.frame result? For example, say we first load the data.frame into an object xts
, then we could have
xts_obj$signal_50microseconds_ago <- get_time_lag_wish_this_function_exists(xts_obj$signal,lag=0.000050)
Note. I don't think I can just use xts.lag
1, because I end up going back 50.05 down rather than 50.03
time bid_price ask_price signal signal_from_lag1
10:10:01.000500 50.02 50.05 50.03 NA
10:10:01.000855 50.02 50.04 50.05 50.03
10:10:01.000856 50.02 50.04 50.06 50.05
source to share
This is the approach I would take to align the values with the last previous observation. It only uses the merge function xts
and na.locf()
to fill the merged time values forward:
d <- read.table(stringsAsFactors=F, header=T, text="
time bid_price ask_price signal
10:10:01.000500 50.02 50.05 50.03
10:10:01.000855 50.02 50.03 50.05
10:10:01.000856 50.02 50.03 50.06
")
t <- as.POSIXct(paste0("2015-05-28 ", d$time))
#format(t, "%Y-%m-%d %H:%M:%OS9")
library(xts)
d_xts <- xts(d[,-1], order.by=t)
## Lag the signal by 50 microseconds:
signal_lag <- xts(d[,"signal"], order.by=t+0.000050)
merge_xts <- merge(d_xts, signal_lag)
## Carry last lagged value forward:
merge_xts$signal_lag <- na.locf(merge_xts$signal_lag)
## Finally subset back to only original rows:
merge_xts <- merge_xts[ !is.na(merge_xts$signal) ]
Result merge_xts
:
> merge_xts
bid_price ask_price
2015-05-28 10:10:01 50.02 50.05
2015-05-28 10:10:01 50.02 50.03
2015-05-28 10:10:01 50.02 50.03
signal signal_lag
2015-05-28 10:10:01 50.03 NA
2015-05-28 10:10:01 50.05 50.03
2015-05-28 10:10:01 50.06 50.03
source to share