drgregmartin drgregmartin - 4 years ago 129
R Question

Aggregate data in an xts object by time and second (factor) variable

I'm new to time series analysis and using xts (and R in general) so please forgive the basic nature of the question.

I'm wanting to aggregate the data by a time frame (e.g. months) and by a second factor variable. To illustrate my question, please see the following:

require(xts)

# Create example df and convert it to an xts object

date <- sample(seq(as.Date("2015/01/01"), as.Date("2016/12/31"), by="day"),12)
colour <- c("Red", "Red", "Blue", "Blue", "Blue", "Blue", "Red", "Red", "Red",
"Red", "Blue", "Blue")
value <- sample(1:10, 12, replace = TRUE)
df <- cbind.data.frame(date, colour, value)
df <- xts(df[,-1], order.by = df$date)


This creates a sample dataframe that looks like this:

colour value
2015-01-30 "Blue" "2"
2015-03-15 "Blue" "9"
2015-03-22 "Blue" "9"
2015-08-13 "Blue" "5"
2015-09-01 "Blue" "8"
2015-11-10 "Red" "7"
2016-04-26 "Blue" "2"
2016-07-06 "Red" "9"
2016-07-07 "Red" "6"
2016-07-08 "Red" "2"
2016-10-01 "Red" "6"
2016-11-07 "Red" "2"


I can summarise the "value" variable using:

apply.monthly(df$value, FUN = mean)


To give me:

value
2015-01-30 2.000000
2015-03-22 9.000000
2015-08-13 5.000000
2015-09-01 8.000000
2015-11-10 7.000000
2016-04-26 2.000000
2016-07-08 5.666667
2016-10-01 6.000000
2016-11-07 2.000000


But I can't quite see how to aggregate by (in this case) the colour variable (I'd like the sum of each colour by month). Any help would be greatly appreciated.

Answer Source

How about this?

aggregate(as.numeric(df$value),
          list(Month = format(index(df), "%Y-%m"), 
               Colour = df$colour), 
          mean)

In response to your comment below:

# You can replace the format with the following to get a year month object
zoo::as.yearmon(index(df))

# Or you can covert to date by using the first of every month
as.Date(paste(format(index(df), "%Y-%m"), "-01", sep = ""))

You might find more ideas here: Converting year and month ("yyyy-mm" format) to a date in R?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download