On Mon, Apr 4, 2011 at 8:49 AM, Den Alpin <den.al...@gmail.com> wrote:
> I retrieve for a few hundred times a group of time series (10-15 ts
> with 10000 values each), on every group I do some calculation, graphs
> etc. I wonder if there is a faster method than what presented below to
> get an appropriate timeseries object.
>
> Making a query with RODBC for every group I get a data frame like this:
>
>> X
> ID DATE VALUE
> 14 3 2000-01-01 00:00:03 0.5726334
> 4 1 2000-01-01 00:00:03 0.8830174
> 1 1 2000-01-01 00:00:00 0.2875775
> 15 3 2000-01-01 00:00:04 0.1029247
> 11 3 2000-01-01 00:00:00 0.9568333
> 9 2 2000-01-01 00:00:03 0.5514350
> 7 2 2000-01-01 00:00:01 0.5281055
> 6 2 2000-01-01 00:00:00 0.0455565
> 12 3 2000-01-01 00:00:01 0.4533342
> 8 2 2000-01-01 00:00:02 0.8924190
> 3 1 2000-01-01 00:00:02 0.4089769
> 13 3 2000-01-01 00:00:02 0.6775706
>
> And I want to get a timeSeries object or xts object like this:
>
> 1 2 3
> 2000-01-01 00:00:00 0.2875775 0.0455565 0.9568333
> 2000-01-01 00:00:01 NA 0.5281055 0.4533342
> 2000-01-01 00:00:02 0.4089769 0.8924190 0.6775706
> 2000-01-01 00:00:03 0.8830174 0.5514350 0.5726334
> 2000-01-01 00:00:04 NA NA 0.1029247
>
>
> Input data can be sorted or unsorted (the most complicated case is in
> the example, unsorted and missing data) in the sense that I can sort
> in query if I can take an advantage from this.
>
> Some considerations:
> - Xts is generally faster than timeSeries
> - both accept a matrix so if I can create a matrix like the one
> represented above and an array of characters representing dates faster
> than what possible with xts:::cbind, for examole,I will have a faster
> implementation (package data.table ?).
> - create timeseries objects in multithread and then merge (package plyr ?)
> - faster merge algorithms?
>
> Below some code to generate the test case above:
>
>
> set.seed(123)
> N <- 5 # number of observations
> K <- 3 # number of timeseries ID
>
> X <- data.frame(
> ID = rep(1:K, each = N),
> DATE = as.character(rep(as.POSIXct("2000-01-01", tz = "GMT")+ 0:(N-1), K)),
> VALUE = runif(N*K), stringsAsFactors = FALSE)
>
> X <- X[sample(1:(N*K), N*K),] # sample observations to get random
> order (optional)
> X <- X[-(sample(1:nrow(X), floor(nrow(X)*0.2))),] # 20% missing
>
> head(X, 15)
>
> # use explicitly environments to avoid '<<-'
> buildTimeSeriesFromDataFrame <- function(x, env)
> {
> {
> if(exists("xx", envir = env)) # if exist variable xx in env cbind
> assign("xx",
> cbind(get("xx", env), timeSeries(x$VALUE, x$DATE,
> format = '%Y-%m-%d %H:%M:%S',
> zone = 'GMT', units = as.character(x$ID[1]))),
> envir = env)
> else # create xx in env
> assign("xx",
> timeSeries(x$VALUE, x$DATE, format = '%Y-%m-%d %H:%M:%S',
> zone = 'GMT', units = as.character(x$ID[1])),
> envir = env)
>
> return(TRUE)
> }
> }
>
> # use package plyr, faster than 'by' function
> tsDaply <- function(...)
> {
> library(plyr)
> e1 <- new.env(parent = baseenv()) #create a new env
> res <- daply(X, "ID", buildTimeSeriesFromDataFrame,
> env = e1)
> return(get("xx", e1)) # return xx from env
> }
>
> ##replicate 100 times
> #Time03 <- replicate(100,
> # system.time(tsDaply(X, X$ID))[[1]])
> #median(Time03)
>
> # result
> tsDaply(X, X$ID)
>
Haven't checked how fast it is but using read.zoo its just one line of
code to produce the required matrix:
# set up input data frame, DF
Lines <- "ID,DATE,VALUE
3,2000-01-01 00:00:03,0.5726334
1,2000-01-01 00:00:03,0.8830174
1,2000-01-01 00:00:00,0.2875775
3,2000-01-01 00:00:04,0.1029247
3,2000-01-01 00:00:00,0.9568333
2,2000-01-01 00:00:03,0.5514350
2,2000-01-01 00:00:01,0.5281055
2,2000-01-01 00:00:00,0.0455565
3,2000-01-01 00:00:01,0.4533342
2,2000-01-01 00:00:02,0.8924190
1,2000-01-01 00:00:02,0.4089769
3,2000-01-01 00:00:02,0.6775706"
DF <- read.table(textConnection(Lines), header = TRUE, sep = ",")
# create zoo matrix
library(zoo)
z <- read.zoo(DF, split = 1, index = 2, tz = "")
The last line gives:
> z
1 2 3
2000-01-01 00:00:00 0.2875775 0.0455565 0.9568333
2000-01-01 00:00:01 NA 0.5281055 0.4533342
2000-01-01 00:00:02 0.4089769 0.8924190 0.6775706
2000-01-01 00:00:03 0.8830174 0.5514350 0.5726334
2000-01-01 00:00:04 NA NA 0.1029247
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
______________________________________________
R-help@r-project.org mailing list
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.
