[R] A complicated 'aggregate'

[Gabor Grothendieck]

Use wtd.mean from Hmisc and by:


Lines <- "
11:00:01        34      1000
11:00:01        35       500
11:00:01        35      1000
11:00:02        34       500
11:00:02        35       500
"


library(Hmisc) # for wtd.mean
library(zoo)
library(chron)

# replace with DF <- read.table("mytable.dat")
DF <- read.table(textConnection(Lines))
f <- function(x) {
    data.frame(time = x[1,1], price = wtd.mean(x[,2], x[,3]), vol = sum(x[,3]))
}
DFby <- do.call("rbind", by(DF, DF[1], f))
z <- zoo(cbind(price = DFby[,2], vol = DFby[,3]), times(DFby[,1]))
zr <- as.zooreg(z)




On 7/31/07, Josh Quigley <josh.quigley at tibra.com.au> wrote:
> Hi,
>
> I have a financial (zoo) time series with prices and volumes (although I can
> get the coredata as a matrix). Due to the data-source some indices have
> multiple observations. I want to aggregate these according to a weighted
> average.
>
> 11:00:01        34      1000
> 11:00:01        35       500
> 11:00:01        35      1000
> 11:00:02        34       500
> 11:00:02        35       500
>
> should become
>
> 11:00:01        34.6    2500
> 11:00:02        34.5    1000
>
> I currently do this using a loop, and the result is abysmally slow:
>
>
> f <- function(x)
> {
>  retval <- c(0, 0);
>  x <- coredata(x);
>
>  retval[2] <- sum(x[,2]);
>  retval[1] <- sum(x[,1] * x[,2]) / retval[2];
>  retval;
> }
>
> #ts is a zoo timeseries
> uniqueTimes <- unique(index(ts))
> tmpMat <- NULL
> for(i in 1:length(uniqueTimes))
> {
>    tmpMat <- rbind(tmpMat, f(ts[uniqueTimes[i]]));
> }
>
> ts.agg <- zooreg(tmpMat, order.by=uniqueTimes);
>
>
> I'm sure the above can be done with aggregate or tapply or by or something,
> but I haven't managed to get those to work.
>
> Any suggestions greatly appreciated!
>
> Cheers,
>
> Josh Quigley.
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> 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.
>