[R] Calculate average of many subsets based on columns in another dataframe

[David Winsemius]

> On Feb 10, 2016, at 12:18 PM, Peter Lomas <peter.br.lomas at gmail.com> wrote:
> 
> Hello, I have a dataframe with a date range, and another dataframe
> with observations by date.  For each date range, I'd like to average
> the values within that range from the other dataframe.  I've provided
> code below doing what I would like, but using a for loop is too
> inefficient for my actual case (takes about an hour).  So I'm looking
> for a way to vectorize.
> 
> 
> set.seed(345)
> date.range <- seq(as.POSIXct("2015-01-01"),as.POSIXct("2015-06-01"),
> by="DSTday")
> observations <- data.frame(date=date.range, values=runif(152,1,100) )
> groups <- data.frame(start=sample(date.range[1:50], 20), end =
> sample(date.range[51:152], 20), average = NA)
> 
> #Potential Solution (too inefficient)
> 
> for(i in 1:NROW(groups)){
> groups[i, "average"] <- mean(observations[observations$date >=
> groups[i, "start"] & observations$date <=groups[i, "end"], "values"])
> }
> 
The 'average' column could be added to groups with this value:

mapply( function(start,end){ mean(observations[['values']][
                     observations$date >= start & observations$date <=end])}, 
        groups$start, groups$end)

 [1] 50.96831 49.42286 47.27240 49.07534 47.66570 49.30977 48.47503 47.74036
 [9] 46.02527 58.76492 48.86580 49.90655 45.79705 48.84071 39.53846 46.44601
[17] 47.06631 47.74199 49.16980 46.85131

I don't really think this is fully "vectorized" in the usual R-meaning of the word. And I don't expect it to be any faster than the for-loop. Perhaps some of the range functions in the data.table package could accelerate your processing. If you don't get any volunteers in this list, you could repost the question on StackOverflow after a suitable pause that avoids accusations of cross-posting. SO has several skilled users of data.table functions.

> As an extension to this, there will end up being multiple value
> columns, and each range will also identify which column to average.  I
> think if I can figure out the first problem I can try to extend it
> myself.

Sorry, I didn't understand what was being described in that paragraph.

-- 

David Winsemius
Alameda, CA, USA