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