Aggregating, restructuring hourly time series data in R

Question

I have a year's worth of hourly data in a data frame in R:

> str(df.MHwind_load)   # compactly displays structure of data frame
'data.frame':   8760 obs. of  6 variables:
 $ Date         : Factor w/ 365 levels "2010-04-01","2010-04-02",..: 1 1 1 1 1 1 1 1 1 1 ...
 $ Time..HRs.   : int  1 2 3 4 5 6 7 8 9 10 ...
 $ Hour.of.Year : int  1 2 3 4 5 6 7 8 9 10 ...
 $ Wind.MW      : int  375 492 483 476 486 512 421 396 456 453 ...
 $ MSEDCL.Demand: int  13293 13140 12806 12891 13113 13802 14186 14104 14117 14462 ...
 $ Net.Load     : int  12918 12648 12323 12415 12627 13290 13765 13708 13661 14009 ...

While preserving the hourly structure, I would like to know how to extract

1. a particular month/group of months
2. the first day/first week etc of each month
3. all mondays, all tuesdays etc of the year

I have tried using "cut" without result and after looking online think that "lubridate" might be able to do so but haven't found suitable examples. I'd greatly appreciate help on this issue.

Edit: a sample of data in the data frame is below:

  Date Hour.of.Year  Wind.MW  datetime
1  2010-04-01  1  375  2010-04-01  00:00:00
2  2010-04-01  2  492  2010-04-01  01:00:00
3  2010-04-01  3  483  2010-04-01  02:00:00
4  2010-04-01  4  476  2010-04-01  03:00:00
5  2010-04-01  5  486  2010-04-01  04:00:00
6  2010-04-01  6  512  2010-04-01  05:00:00
7  2010-04-01  7  421  2010-04-01  06:00:00
8  2010-04-01  8  396  2010-04-01  07:00:00
9  2010-04-01  9  456  2010-04-01  08:00:00
10  2010-04-01  10  453  2010-04-01  09:00:00
..  ..  ...  ..........  ........
8758  2011-03-31  8758  302  2011-03-31  21:00:00
8759  2011-03-31  8759  378  2011-03-31  22:00:00
8760  2011-03-31  8760  356  2011-03-31  23:00:00

EDIT: Additional time-based operations I would like to perform on the same dataset 1. Perform hour-by-hour averaging for all data points i.e average of all values in the first hour of each day in the year. The output will be an "hourly profile" of the entire year (24 time points) 2. Perform the same for each week and each month i.e obtain 52 and 12 hourly profiles respectively 3. Do seasonal averages, for example for June to September

Answer 1

Convert the date to the format which lubridate understands and then use the functions month, mday, wday respectively.

Suppose you have a data.frame with the time stored in column Date, then the answer for your questions would be:

 ###dummy data.frame
 df <- data.frame(Date=c("2012-01-01","2012-02-15","2012-03-01","2012-04-01"),a=1:4) 
 ##1. Select rows for particular month
 subset(df,month(Date)==1)

 ##2a. Select the first day of each month
 subset(df,mday(Date)==1)

 ##2b. Select the first week of each month
 ##get the week numbers which have the first day of the month
 wkd <- subset(week(df$Date),mday(df$Date)==1)
 ##select the weeks with particular numbers
 subset(df,week(Date) %in% wkd)     

 ##3. Select all mondays 
 subset(df,wday(Date)==1)

Answer 2

1. First switch to a Date representation: as.Date(df.MHwind_load$Date)
2. Then call weekdays on the date vector to get a new factor labelled with day of week
3. Then call months on the date vector to get a new factor labelled with name of month
4. Optionally create a years variable (see below).

Now subset the data frame using the relevant combination of these. Step 2. gets an answer to your task 3. Steps 3. and 4. get you to task 1. Task 2 might require a line or two of R. Or just select rows corresponding to, say, all the Mondays in a month and call unique, or its alter-ego duplicated on the results.

To get you going...

newdf <- df.MHwind_load ## build an augmented data set
newdf$d <- as.Date(newdf$Date)
newdf$month <- months(newdf$d)
newdf$day <- weekdays(newdf$d)

## for some reason R has no years function.  Here's one
years <- function(x){ format(as.Date(x), format = "%Y") }

newdf$year <- years(newdf$d)

# get observations from January to March of every year
subset(newdf, month %*% in c('January', 'February', 'March'))

# get all Monday observations
subset(newdf, day == 'Monday')

# get all Mondays in 1999
subset(newdf, day == 'Monday' & year == '1999')

# slightly fancier: _first_ Monday of each month
# get the first weeks
first.week.of.month <- !duplicated(cbind(newdf$month, newdf$day)) 
# now pull out the mondays
subset(newdf, first.monday.of.month & day=='Monday')

Answer 3

Since you're not asking about the time (hourly) part of your data, it is best to then store your data as a Date object. Otherwise, you might be interested in chron, which also has some convenience functions like you'll see below.

With respect to Conjugate Prior's answer, you should store your date data as a Date object. Since your data already follows the default format ('yyyy-mm-dd') you can just call as.Date on it. Otherwise, you would have to specify your string format. I would also use as.character on your factor to make sure you don't get errors inline. I know I've ran into problems with factors-into-Dates for that reason (possibly corrected in current version).

df.MHwind_load <- transform(df.MHwind_load, Date = as.Date(as.character(Date)))

Now you would do well to create wrapper functions that extract the information you desire. You could use transform like I did above to simply add those columns that represent months, days, years, etc, and then subset on them logically. Alternatively, you might do something like this:

getMonth <- function(x, mo) {  # This function assumes w/in single year vector
  isMonth <- month(x) %in% mo  # Boolean of matching months
  return(x[which(isMonth)]     # Return vector of matching months
}  # end function

Or, in short form

getMonth <- function(x, mo) x[month(x) %in% mo]

This is just a tradeoff between storing that information (transform frame) or having it processed when desired (use accessor methods).

A more complicated process is your need for, say, the first day of a month. This is not entirely difficult, though. Below is a function that will return all of those values, but it is rather simple to just subset a sorted vector of values for a given month and take their first one.

getFirstDay <- function(x, mo) {
  isMonth <- months(x) %in% mo
  x <- sort(x[isMonth])  # Look at only those in the desired month.
                         # Sort them by date. We only want the first day.
  nFirsts <- rle(as.numeric(x))$len[1]  # Returns length of 1st days
  return(x[seq(nFirsts)])
}  # end function

The easier alternative would be

getFirstDayOnly <- function(x, mo) {sort(x[months(x) %in% mo])[1]}

I haven't prototyped these, as you didn't provide any data samples, but this is the sort of approach that can help you get the information you desire. It is up to you to figure out how to put these into your work flow. For instance, say you want to get the first day for each month of a given year (assuming we're only looking at one year; you can create wrappers or pre-process your vector to a single year beforehand).

# Return a vector of first days for each month
df <- transform(df, date = as.Date(as.character(date)))
sapply(unique(months(df$date)),  # Iterate through months in Dates
       function(month) {getFirstDayOnly(df$date, month)})

The above could also be designed as a separate convenience function that uses the other accessor function. In this way, you create a series of direct but concise methods for getting pieces of the information you want. Then you simply pull them together to create very simple and easy to interpret functions that you can use in your scripts to get you precise what you desire in the most efficient manner.

You should be able to use the above examples to figure out how to prototype other wrappers for accessing the date information you require. If you need help on those, feel free to ask in a comment.