5

I am interested in calculating averages over specific time periods in a time series data set.

Given a time series like this:

dtm=as.POSIXct("2007-03-27 05:00", tz="GMT")+3600*(1:240)
Count<-c(1:240)
DF<-data.frame(dtm,Count)

In the past I have been able to calculate daily averages with 

DF$Day<-cut(DF$dtm,breaks="day")
Day_Avg<-aggregate(DF$Count~Day,DF,mean)

But now I am trying to cut up the day into specific time periods and I'm not sure how to set my "breaks".

As opposed to a daily average from 0:00:24:00, How for example could I get a Noon to Noon average?

Or more fancy, how could I set up a Noon to Noon average excluding the night times of 7PM to 6AM (or conversely only including the daylight hours of 6AM- 7PM).

Vinterwoo
  • 3,843
  • 6
  • 36
  • 55

3 Answers3

5

Let me quickly repeat your code.

dtm <- as.POSIXct("2007-03-27 05:00", tz="GMT")+3600*(1:240)
Count <- c(1:240)
DF<-data.frame(dtm,Count)

DF$Day<-cut(DF$dtm,breaks="day")
Day_Avg<-aggregate(DF$Count~Day,DF,mean)

If you offset each time by 12 hours in the function call, you can still use cut with breaks on "day". I will save the day that the noon to noon starts on, so I will subtract 12 hours.

# Get twelve hours in seconds
timeOffset <- 60*60*12
# Subtract the offset to get the start day of the noon to noon
DF$Noon_Start_Day <- cut((DF$dtm - timeOffset), breaks="day")
# Get the mean
NtN_Avg <- aggregate(DF$Count ~ Noon_Start_Day, DF, mean)

One way to exclude certain hours is to convert the dates to POSIXlt. Then you can access hour among other things.

# Indicate which times are good (use whatever boolean test is needed here)
goodTimes <- !(as.POSIXlt(DF$dtm)$hour >= 19) & !(as.POSIXlt(DF$dtm)$hour <= 6)
new_NtN_Avg <- aggregate(Count ~ Noon_Start_Day, data=subset(DF, goodTimes), mean)

I found some help at this question on stackoverflow: r-calculate-means-for-subset-of-a-group

Community
  • 1
  • 1
James Pringle
  • 1,079
  • 6
  • 15
5

xts is perfect package for timeseries analysis

library(xts)

originalTZ <- Sys.getenv("TZ")

Sys.setenv(TZ = "GMT")

data.xts <- as.xts(1:240, as.POSIXct("2007-03-27 05:00", tz = "GMT") + 3600 * (1:240))

head(data.xts)
##                     [,1]
## 2007-03-27 06:00:00    1
## 2007-03-27 07:00:00    2
## 2007-03-27 08:00:00    3
## 2007-03-27 09:00:00    4
## 2007-03-27 10:00:00    5
## 2007-03-27 11:00:00    6


# You can filter data using ISO-style subsetting
data.xts.filterd <- data.xts["T06:00/T19:00"]

# You can use builtin functions to apply any function FUN on daily data.
apply.daily(data.xts.filtered, mean)
##                      [,1]
## 2007-03-27 18:00:00   7.5
## 2007-03-28 18:00:00  31.5
## 2007-03-29 18:00:00  55.5
## 2007-03-30 18:00:00  79.5
## 2007-03-31 18:00:00 103.5
## 2007-04-01 18:00:00 127.5
## 2007-04-02 18:00:00 151.5
## 2007-04-03 18:00:00 175.5
## 2007-04-04 18:00:00 199.5
## 2007-04-05 18:00:00 223.5


# OR

# now let's say you want to find noon to noon average.

period.apply(data.xts, c(0, which(.indexhour(data.xts) == 11)), FUN = mean)
##                      [,1]
## 2007-03-27 11:00:00   3.5
## 2007-03-28 11:00:00  18.5
## 2007-03-29 11:00:00  42.5
## 2007-03-30 11:00:00  66.5
## 2007-03-31 11:00:00  90.5
## 2007-04-01 11:00:00 114.5
## 2007-04-02 11:00:00 138.5
## 2007-04-03 11:00:00 162.5
## 2007-04-04 11:00:00 186.5
## 2007-04-05 11:00:00 210.5


# now if you want to exclude time from 7 PM to 6 AM
data.xts.filtered <- data.xts[!data.xts %in% data.xts["T20:00/T05:00"]]

head(data.xts.filtered, 20)
##                     [,1]
## 2007-03-27 06:00:00    1
## 2007-03-27 07:00:00    2
## 2007-03-27 08:00:00    3
## 2007-03-27 09:00:00    4
## 2007-03-27 10:00:00    5
## 2007-03-27 11:00:00    6
## 2007-03-27 12:00:00    7
## 2007-03-27 13:00:00    8
## 2007-03-27 14:00:00    9
## 2007-03-27 15:00:00   10
## 2007-03-27 16:00:00   11
## 2007-03-27 17:00:00   12
## 2007-03-27 18:00:00   13
## 2007-03-27 19:00:00   14
## 2007-03-28 06:00:00   25
## 2007-03-28 07:00:00   26
## 2007-03-28 08:00:00   27
## 2007-03-28 09:00:00   28
## 2007-03-28 10:00:00   29
## 2007-03-28 11:00:00   30


period.apply(data.xts.filtered, c(0, which(.indexhour(data.xts.filtered) == 11)), FUN = mean)
##                          [,1]
## 2007-03-27 11:00:00   3.50000
## 2007-03-28 11:00:00  17.78571
## 2007-03-29 11:00:00  41.78571
## 2007-03-30 11:00:00  65.78571
## 2007-03-31 11:00:00  89.78571
## 2007-04-01 11:00:00 113.78571
## 2007-04-02 11:00:00 137.78571
## 2007-04-03 11:00:00 161.78571
## 2007-04-04 11:00:00 185.78571
## 2007-04-05 11:00:00 209.78571





Sys.setenv(TZ = originalTZ)
CHP
  • 16,981
  • 4
  • 38
  • 57
1

The noon-to-noon problem can easily be solved numerically. The key is that the start of a (GMT) day has a time_t value that is always divisible by 86400. This is specified by POSIX. For example, see: http://en.wikipedia.org/wiki/Unix_time

cuts <- unique(as.numeric(DF$dtm) %/% (86400/2)) * (86400/2)  # half-days
cuts <- c(cuts, cuts[length(cuts)]+(86400/2))                 # One more at the end
cuts <- as.POSIXct(cuts, tz="GMT", origin="1970-01-01")       # Familiar format
DF$halfday <-  cut(DF$dtm, cuts)                              # This is the cut you want.

Halfday_Avg <- aggregate(Count~halfday, data=DF, FUN=mean)

Halfday_Avg
##                halfday Count
## 1  2007-03-27 00:00:00   3.5
## 2  2007-03-27 12:00:00  12.5
## 3  2007-03-28 00:00:00  24.5
## 4  2007-03-28 12:00:00  36.5
## 5  2007-03-29 00:00:00  48.5
## 6  2007-03-29 12:00:00  60.5
## 7  2007-03-30 00:00:00  72.5
## 8  2007-03-30 12:00:00  84.5
## 9  2007-03-31 00:00:00  96.5
## 10 2007-03-31 12:00:00 108.5
## 11 2007-04-01 00:00:00 120.5
## 12 2007-04-01 12:00:00 132.5
## 13 2007-04-02 00:00:00 144.5
## 14 2007-04-02 12:00:00 156.5
## 15 2007-04-03 00:00:00 168.5
## 16 2007-04-03 12:00:00 180.5
## 17 2007-04-04 00:00:00 192.5
## 18 2007-04-04 12:00:00 204.5
## 19 2007-04-05 00:00:00 216.5
## 20 2007-04-05 12:00:00 228.5
## 21 2007-04-06 00:00:00 237.5

Now to extend this to solve the rest of the problem. Given here is the 6AM-7PM time range.

intraday <- as.numeric(DF$dtm) %% 86400

# Subset DF by the chosen range
New_Avg <- aggregate(Count~halfday, data=DF[intraday >= 6*3600 & intraday <= 19*3600,], FUN=mean)

New_Avg
##                halfday Count
## 1  2007-03-27 00:00:00   3.5
## 2  2007-03-27 12:00:00  10.5
## 3  2007-03-28 00:00:00  27.5
## 4  2007-03-28 12:00:00  34.5
## 5  2007-03-29 00:00:00  51.5
## 6  2007-03-29 12:00:00  58.5
## 7  2007-03-30 00:00:00  75.5
## 8  2007-03-30 12:00:00  82.5
## 9  2007-03-31 00:00:00  99.5
## 10 2007-03-31 12:00:00 106.5
## 11 2007-04-01 00:00:00 123.5
## 12 2007-04-01 12:00:00 130.5
## 13 2007-04-02 00:00:00 147.5
## 14 2007-04-02 12:00:00 154.5
## 15 2007-04-03 00:00:00 171.5
## 16 2007-04-03 12:00:00 178.5
## 17 2007-04-04 00:00:00 195.5
## 18 2007-04-04 12:00:00 202.5
## 19 2007-04-05 00:00:00 219.5
## 20 2007-04-05 12:00:00 226.5
Matthew Lundberg
  • 42,009
  • 6
  • 90
  • 112