Aggregating millisecond time series to a second using xts library

Question

I need to aggregate 1sec time series from 100 millisecond time series. I use a function to.period() in R xts, having converted the data into xts format first. The error is: unsupported type.

x <- xts(data, order.by = as.POSIXct(data$Time, format = "%H:%M:%S.%ms"))
to.period(x, period = "seconds", k=1)
Error in to.period(x, period = "seconds", k = 1) : unsupported type

Same error when I try period = minutes and higher.

             Date           Time   Price     Amount
<NA> "2014/10/26" "17:30:12.500" "1.268"  "1000000"
<NA> "2014/10/26" "17:33:28.900" "1.268"  "1000000"
<NA> "2014/10/26" "17:33:52.600" "1.268"  "1000000"

Mac OS

Is this error because my data is in milliseconds format or is there any other reason? What can I do to fix the error?

Joshua Ulrich Joshua Ulrich · Accepted Answer · 2016-10-17T13:52:26

There are several issues with your code. I suggest you spend a few minutes reading the zoo and xts vignettes to better understand these two classes.

Assuming your data looks like:

data <- structure(list(Date = c("2014/10/26", "2014/10/26", "2014/10/26"),
  Time = c("17:30:12.500", "17:33:28.900", "17:33:52.600"), 
  Price = c(1.268, 1.268, 1.268), Amount = c(1000000L, 1000000L, 1000000L)),
  .Names = c("Date", "Time", "Price", "Amount"), class = "data.frame", 
  row.names = c(NA, -3L))

Your call to the xts constructor will not work.

x <- xts(data, order.by = as.POSIXct(data$Time, format = "%H:%M:%S.%ms"))

data$Time is just a time, not a datetime, so as.POSIXct will use today's date. But that doesn't work because you specified the format incorrectly ("%ms" isn't a valid format, see ?strptime), which causes all the resulting values to be NA.

Even after correcting those two issues, your code still will not work because data contains several types of data (character (factor?), double, integer) and xts objects are a matrix with an index attribute, and you can't mix types in a matrix. So you need to exclude the Date and Time columns from the coredata of the xts object. And you need to specify the format argument of as.POSIXct correctly.

x <- xts(data[,c("Price", "Amount")],
  order.by=as.POSIXct(paste(data$Date, data$Time), format="%Y/%m/%d %H:%M:%OS"))

Now to.period will work:

to.period(x, "seconds")
#                     x.Open x.High x.Low x.Close
# 2014-10-26 17:30:12  1.268  1.268 1.268   1.268
# 2014-10-26 17:33:28  1.268  1.268 1.268   1.268
# 2014-10-26 17:33:52  1.268  1.268 1.268   1.268

If you want to aggregate the Amount column as volume, you need to rename it before calling to.period.

colnames(x)[2] <- "Volume"
to.period(x, "seconds")
#                     x.Open x.High x.Low x.Close x.Volume
# 2014-10-26 17:30:12  1.268  1.268 1.268   1.268    1e+06
# 2014-10-26 17:33:28  1.268  1.268 1.268   1.268    1e+06
# 2014-10-26 17:33:52  1.268  1.268 1.268   1.268    1e+06

Aggregating millisecond time series to a second using xts library

1 Answers