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?
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