[R-SIG-Finance] !SPAM: Re: aligning xts object to regularly spaced time clock

[Gabor Grothendieck]

On Mon, Jun 21, 2010 at 9:25 PM, Brian G. Peterson <brian at braverock.com> wrote:
> (to.period isn't the correct function for this)
>
> Typically, on tick data, I will use align.time to get everything on an even
> index, like seconds.  This will only align on a period where you have an
> observation, as you've noticed.


Here is an example using zoo and timeDate.  Most of this is just to
set up some test data and after that the last two lines do the real
work.


library(zoo)
library(timeSeries)

# create a time series object
##############################

Lines <- 'date time SYMBOL EX  PRICE   SIZE    COND CR   G127
2010-01-04 09:30:01 "GE"   "T" "15.21" "67099" ""   " 0" "0"
2010-01-04 09:30:03 "GE"   "Z" "15.22" "100"   "F"  " 0" "0"
2010-01-04 09:30:06 "GE"   "P" "15.23" "3950"  "F"  " 0" "0"
2010-01-04 09:30:07 "GE"   "T" "15.24" "1500"  "F"  " 0" "0"
2010-01-04 09:30:08 "GE"   "D" "15.23" "8000"  "F"  " 0" "0"
2010-01-04 09:30:09 "GE"   "P" "15.23" "1300"  "F"  " 0" "0"
2010-01-04 09:30:10 "GE"   "Z" "15.22" "11800" "F"  " 0" "0"
2010-01-04 09:30:11 "GE"   "D" "15.22" "5000"  "F"  " 0" "0"
2010-01-04 09:30:13 "GE"   "Z" "15.22" "1200"  "F"  " 0" "0"
2010-01-04 09:30:14 "GE"   "N" "15.21" "11900" "F"  " 0" "0" '

DF <- read.table(textConnection(Lines), header = TRUE, as.is = TRUE)
tser <- timeSeries(DF[3:9], paste(DF$date, DF$time))

##############################
# convert to zoo getting rid of junk; keep timeDate class on index
##############################

z <- zoo(tser[, c(3:4, 6:7)], time(tser))

##############################
# - create 1 sec grid, g
# - merge series with zero width series based on g
# - carry observations forward into NAs that were created via na.locf
# - pick off the grid points, [g]
##############################
g <- seq(start(z), end(z), by = "sec")
na.locf(merge(z, zoo(, g)))[g]

Output looks like this:

                    PRICE SIZE  CR G127
2010-01-04 09:30:01 15.21 67099 0  0
2010-01-04 09:30:02 15.21 67099 0  0
2010-01-04 09:30:03 15.22   100 0  0
2010-01-04 09:30:04 15.22   100 0  0
2010-01-04 09:30:05 15.22   100 0  0
2010-01-04 09:30:06 15.23  3950 0  0
2010-01-04 09:30:07 15.24  1500 0  0
2010-01-04 09:30:08 15.23  8000 0  0
2010-01-04 09:30:09 15.23  1300 0  0
2010-01-04 09:30:10 15.22 11800 0  0
2010-01-04 09:30:11 15.22  5000 0  0
2010-01-04 09:30:12 15.22  5000 0  0
2010-01-04 09:30:13 15.22  1200 0  0