How to aggregate by minute data for a week into hourly means?

How would you get hourly means for multiple data columns, for a daily period, and show results for twelve "Hosts" in the same graph? That is, I'd like to graph what a 24 hour period looks like, for a weeks worth of data. The eventual goal would be to compare two sets of this data, before and after samplings.

            dates         Host CPUIOWait CPUUser CPUSys
1 2011-02-11 23:55:12     db       0      14      8
2 2011-02-11 23:55:10     app1     0       6      1
3 2011-02-11 23:55:09     app2     0       4      1


I've been able to run xyplot(CPUUser ~ dates | Host) with good effect. However, rather than showing each date in the week, I'd like the X axis to be the hours of the day.

Trying to get this data into an xts object results in errors such as "order.by requires an appropriate time-based object"

Here is a str() of the data frame:

'data.frame':   19720 obs. of  5 variables:
$dates : POSIXct, format: "2011-02-11 23:55:12" "2011-02-11 23:55:10" ...$ Host     : Factor w/ 14 levels "app1","app2",..: 9 7 5 4 3 10 6 8 2 1 ...
$CPUIOWait: int 0 0 0 0 0 0 0 0 0 0 ...$ CPUUser  : int  14 6 4 4 3 10 4 3 4 4 ...
$CPUSys : int 8 1 1 1 1 3 1 1 1 1 ...  UPDATE: Just for future reference, I decided to go with a boxplot, to show both the median, and the 'outliers'. Essentially: Data$hour <- as.POSIXlt(dates)$hour # extract hour of the day boxplot(Data$CPUUser ~ Data$hour) # for a subset with one host or for all hosts xyplot(Data$CPUUser ~ Data$hour | Data$Host, panel=panel.bwplot, horizontal=FALSE)


Thanks

• I'm guessing you get those errors from xts() because the dates column is a factor. – Joshua Ulrich Feb 15 '11 at 21:26
• I'm really new to R ... I created the dates column from the strptime function. The original data is from read.csv. – Scott Hoffman Feb 15 '11 at 21:30
• Let's see str() of the data.frame. – Roman Luštrik Feb 16 '11 at 11:30
• @Roman Thanks for the str() function, I wasn't aware of that. So, getting rid of the Factor column, I can generate an xts object like this, x<-xts(d[,3:5],order.by=d[,1]). I was then able to apply to.hourly, which shortens the data from 19720 objects down to 480. I'm not sure if this will get me where I want, but I'm closer now, I think. – Scott Hoffman Feb 16 '11 at 14:22

Here is one approach using cut() to create the appropriate hourly factors and ddply() from the plyr library for calculating the means.

library(lattice)
library(plyr)

## Create a record and some random data for every 5 seconds
## over two days for two hosts.
dates <- seq(as.POSIXct("2011-01-01 00:00:00", tz = "GMT"),
as.POSIXct("2011-01-02 23:59:55", tz = "GMT"),
by = 5)
hosts <- c(rep("host1", length(dates)), rep("host2",
length(dates)))
x1    <- sample(0:20, 2*length(dates), replace = TRUE)
x2    <- rpois(2*length(dates), 2)
Data  <- data.frame(dates = dates, hosts = hosts, x1 = x1,
x2 = x2)

## Calculate the mean for every hour using cut() to define
## the factors and ddply() to calculate the means.
## getmeans() is applied for each unique combination of the
## hosts and hour factors.
getmeans  <- function(Df) c(x1 = mean(Df$x1), x2 = mean(Df$x2))
Data$hour <- cut(Data$dates, breaks = "hour")
Means <- ddply(Data, .(hosts, hour), getmeans)
Means$hour <- as.POSIXct(Means$hour, tz = "GMT")

## A plot for each host.
xyplot(x1 ~ hour | hosts, data = Means, type = "o",
scales = list(x = list(relation = "free", rot = 90)))

• Thanks for this ... I think I might need to reword the question though, or ask a new one. Looking at this question stats.stackexchange.com/questions/980/…, I now think getting the means is not exactly what I'm after. – Scott Hoffman Feb 17 '11 at 18:18
• @JVM Can you explain how the getmeans function works, and why you didn't just use the mean or colMeans functions? – Scott Hoffman Feb 18 '11 at 19:14
• The ddply() function cuts the original dataset into subsets defined by hosts and hour. It then passes these to getmeans() as a data.frame. For your task, using colMeans() would probably work just fine, but you would probably need to first remove the columns you don't need. The nice thing about using ddply() this way is that you can calculate any arbitrary stat for which you might be interested; e.g., sd(), range(), etc. – Jason Morgan Feb 18 '11 at 19:39

Aggregation also works without using zoo (with random data from 2 variables for 3 days and 4 hosts like from JWM). I assume that you have data from all hosts for each hour.

nHosts <- 4  # number of hosts
dates  <- seq(as.POSIXct("2011-01-01 00:00:00"),
as.POSIXct("2011-01-03 23:59:30"), by=30)
hosts  <- factor(sample(1:nHosts, length(dates), replace=TRUE),
labels=paste("host", 1:nHosts, sep=""))
x1     <- sample(0:20, length(dates), replace=TRUE)  # data from 1st variable
x2     <- rpois(length(dates), 2)                    # data from 2nd variable
Data   <- data.frame(dates=dates, hosts=hosts, x1=x1, x2=x2)


I'm not entirely sure if you want to average just within each hour, or within each hour over all days. I'll do both.

Data$hFac <- droplevels(cut(Data$dates, breaks="hour"))
Data$hour <- as.POSIXlt(dates)$hour  # extract hour of the day

# average both variables over days within each hour and host
# formula notation was introduced in R 2.12.0 I think
res1 <- aggregate(cbind(x1, x2) ~ hour + hosts, data=Data, FUN=mean)
# only average both variables within each hour and host
res2 <- aggregate(cbind(x1, x2) ~ hFac + hosts, data=Data, FUN=mean)


The result looks like this:

> head(res1)
hour hosts        x1       x2
1    0 host1  9.578431 2.049020
2    1 host1 10.200000 2.200000
3    2 host1 10.423077 2.153846
4    3 host1 10.241758 1.879121
5    4 host1  8.574713 2.011494
6    5 host1  9.670588 2.070588

hFac hosts        x1       x2
1 2011-01-01 00:00:00 host1  9.192308 2.307692
2 2011-01-01 01:00:00 host1 10.677419 2.064516
3 2011-01-01 02:00:00 host1 11.041667 1.875000
4 2011-01-01 03:00:00 host1 10.448276 1.965517
5 2011-01-01 04:00:00 host1  8.555556 2.074074
6 2011-01-01 05:00:00 host1  8.809524 2.095238


I'm also not entirely sure about the type of graph you want. Here's the bare-bones version of a graph for just the first variable with separate data lines for each host.

# using the data that is averaged over days as well
res1L <- split(subset(res1, select="x1"), res1$hosts) mat1 <- do.call(cbind, res1L) colnames(mat1) <- levels(hosts) rownames(mat1) <- 0:23 matplot(mat1, main="x1 per hour, avg. over days", xaxt="n", type="o", pch=16, lty=1) axis(side=1, at=seq(0, 23, by=2)) legend(x="topleft", legend=colnames(mat1), col=1:nHosts, lty=1)  The same graph for the data that is only averaged within each hour. res2L <- split(subset(res2, select="x1"), res2$hosts)
mat2  <- do.call(cbind, res2L)
colnames(mat2) <- levels(hosts)
rownames(mat2) <- levels(Data\$hFac)
matplot(mat2, main="x1 per hour", type="o", pch=16, lty=1)
legend(x="topleft", legend=colnames(mat2), col=1:nHosts, lty=1)

• Nice response, plenty in there that I'm not familiar with, so I need to try it out. Still, looking at my data with your methods, I'm thinking I need to show the high points in my data as well. Thanks – Scott Hoffman Feb 19 '11 at 22:16

You might checkout the aggregate.zoo function from the package zoo: http://cran.r-project.org/web/packages/zoo/zoo.pdf

Charlie

• Can you help me understand why I'm getting NAs when running this? – Scott Hoffman Feb 18 '11 at 16:01
• Hi Scott, I haven't actually used the aggregate.zoo function, though I have used the zoo package. Did you make sure that your object was a zoo object first? The documentation that I pointed to should help you there. – Charlie Feb 18 '11 at 17:58