Convert continuous time-series data into daily-hourly representation using R

Question

I have time-series data in xts representation as

 library(xts)
 xtime <-timeBasedSeq('2015-01-01/2015-01-30 23')
 df <- xts(rnorm(length(xtime),30,4),xtime)

Now I want to calculate co-orelation between different days, and hence I want to represent df in matrix form as:

To achieve this I used

p_mat= split(df,f="days",drop=FALSE,k=1)

Using this I get a list of days, but I am not able to arrange this list in matrix form. Also I used

p_mat<- df[.indexday(df) %in% c(1:30) & .indexhour(df) %in% c(1:24)]

With this I do not get any output. Also I tried to use rollapply(), but was not able to arrange it properly.

May I get help to form the matrix using xts/zoo objects.

Answer 1

Maybe you could use something like this:

#convert to a data.frame with an hour column and a day column
df2 <- data.frame(value = df, 
                  hour = format(index(df), '%H:%M:%S'), 
                  day  = format(index(df), '%Y:%m:%d'),
                  stringsAsFactors=FALSE)

#then use xtabs which ouputs a matrix in the format you need
tab <- xtabs(value ~ day + hour, df2)

Output:

            hour
day          00:00:00 01:00:00 02:00:00 03:00:00 04:00:00 05:00:00 06:00:00 07:00:00 08:00:00 09:00:00 10:00:00 11:00:00 12:00:00
  2015:01:01 28.15342 35.72913 27.39721 29.17048 28.42877 28.72003 28.88355 31.97675 29.29068 27.97617 35.37216 29.14168 29.28177
  2015:01:02 23.85420 28.79610 27.88688 27.39162 29.77241 22.34256 34.70633 23.34011 28.14588 25.53632 26.99672 38.34867 30.06958
  2015:01:03 37.47716 31.70040 29.04541 34.23393 33.54569 27.52303 38.82441 28.97989 24.30202 29.42240 30.83015 39.23191 30.42321
  2015:01:04 24.13100 32.08409 29.36498 35.85835 26.93567 28.27915 26.29556 29.29158 31.60805 27.07301 33.32149 25.16767 25.80806
  2015:01:05 32.16531 29.94640 32.04043 29.34250 31.68278 28.39901 24.51917 33.95135 36.07898 28.76504 24.98684 32.56897 29.82116
  2015:01:06 18.44432 27.43807 32.28203 29.76111 29.60729 32.24328 25.25417 34.38711 29.97862 32.82924 34.13643 30.89392 26.48517
  2015:01:07 34.58491 20.38762 32.29096 31.49890 28.29893 33.80405 28.44305 28.86268 33.42964 36.87851 31.08022 28.31126 25.24355
  2015:01:08 33.67921 31.59252 28.36989 35.29703 27.19507 27.67754 25.99571 27.32729 33.78074 31.73481 34.02064 28.43953 31.50548
  2015:01:09 28.46547 36.61658 36.04885 30.33186 32.26888 25.90181 31.29203 34.17445 30.39631 28.18345 27.37687 29.85631 34.27665
  2015:01:10 30.68196 26.54386 32.71692 28.69160 23.72367 28.53020 35.45774 28.66287 32.93100 33.78634 30.01759 28.59071 27.88122
  2015:01:11 32.70907 31.51985 29.22881 36.31157 32.38494 25.30569 29.37743 22.32436 29.21896 19.63069 35.25601 27.45783 28.28008
  2015:01:12 29.96676 30.51542 29.41650 29.34436 37.05421 33.05035 34.44572 26.30717 30.65737 34.61930 29.77391 21.48256 31.37938
  2015:01:13 33.46089 34.29776 37.58262 27.58801 28.43653 28.33511 28.49737 28.53348 28.81729 35.76728 27.20985 28.44733 32.61015
  2015:01:14 22.96213 32.27889 36.44939 23.45088 26.88173 27.43529 27.27547 21.86686 32.00385 23.87281 29.90001 32.37194 29.20722
  2015:01:15 28.30359 30.94721 20.62911 33.84679 27.58230 26.98849 23.77755 24.18443 30.22533 32.03748 21.60847 25.98255 32.14309
  2015:01:16 23.52449 29.56138 31.76356 35.40398 24.72556 31.45754 30.93400 34.77582 29.88836 28.57080 25.41274 27.93032 28.55150
  2015:01:17 25.56436 31.23027 25.57242 31.39061 26.50694 30.30921 28.81253 25.26703 30.04517 33.96640 36.37587 24.50915 29.00156
...and so on

Answer 2

Here's one way to do it using a helper function that will account for days that do not have 24 observations.

library(xts)
xtime <- timeBasedSeq('2015-01-01/2015-01-30 23')
set.seed(21)
df <- xts(rnorm(length(xtime),30,4), xtime)

tHourly <- function(x) {
  # initialize result matrix for all 24 hours
  dnames <- list(format(index(x[1]), "%Y-%m-%d"),
                 paste0("H", 0:23))
  res <- matrix(NA, 1, 24, dimnames = dnames)
  # transpose day's rows and set colnames
  tx <- t(x)
  colnames(tx) <- paste0("H", .indexhour(x))
  # update result object and return
  res[,colnames(tx)] <- tx
  res
}
# split on days, apply tHourly to each day, rbind results
p_mat <- split(df, f="days", drop=FALSE, k=1)
p_list <- lapply(p_mat, tHourly)
p_hmat <- do.call(rbind, p_list)