R help - Feb 2016 - Calculate average of many subsets based on columns in another dataframe

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"])
}

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.

Thanks,
Peter

> 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

A strategy:

 1. Convert your dates and intervals to numerics that give the days
since a time origin. See as.POSIXlt (or ** ct for details and an
example that does this). Should be fast...

2. Use the findInterval() function to get the interval into which each
date falls. This **is** "vectorized" and should be fairly fast.

3. Use the ave() function using the intervals as your factor that
splits your data column(s) for which you wish to compute statistics.
The basic statistics functions like mean, sum, etc. **are**
vectorized, so this should be fast.

As David said, the *apply functions will probably not be much, if at
all, faster than an explicit for() loop. Most of the time will be
spent spent comparing the dates to the intervals to find in which each
falls, and findInterval is a fast way to do this.

... I think.

If you try this, let me know (perhaps privately) how/if it works.

Cheers,
Bert
Bert Gunter

"The trouble with having an open mind is that people keep coming along
and sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )


On Wed, Feb 10, 2016 at 1:08 PM, David Winsemius <dwinsemius at
comcast.net> wrote:
>
>> 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
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

You could try pulling some of the repeated subscripting operations,
especially the insertions, out of the loop.  E.g.,

    values <- observations[,"values"];
    date <- observations[,"date"] ;
    groups$average <- vapply(seq_len(NROW(groups)), function(i)
mean(values[date >= groups[i, "start"] & date <= groups[i,
"end"]]),
FUN.VALUE=0)



Bill Dunlap
TIBCO Software
wdunlap tibco.com

On Wed, 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"])
> }
>
> 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.
>
> Thanks,
> Peter
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
	[[alternative HTML version deleted]]