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.

``````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