Message-ID: <CAAxdm-4Fh+QSvrTOo9GyxDJ-0s=ENMpKx+q57nNpO9vA00vW2Q@mail.gmail.com>
Date: 2011-10-07T13:25:05Z
From: jim holtman
Subject: Data frame aggregation
In-Reply-To: <4E8EF64B020204AA003902C6_0_44248@p057>
Here is one way to do it using data.table package:
> x <- read.table(textConnection("NAME TICKER SHARES PERFORMANCE
+ John ABC 100 0.05
+ John ABC 1000 1.5
+ Alice EFG 20 0.3
+ Paul HIJ 50 1.0
+ Paul JKL 60 2.0
+ Paul MNO 12 3.0"), header = TRUE, as.is = TRUE)
> closeAllConnections()
> require(data.table)
> x <- data.table(x) # convert to data.table
> x[
+ , list(SHARES = sum(SHARES)
+ , PERFORMANCE = sum(SHARES * PERFORMANCE) / sum(SHARES)
+ )
+ , by = list(NAME, TICKER)
+ ]
NAME TICKER SHARES PERFORMANCE
[1,] John ABC 1100 1.368182
[2,] Alice EFG 20 0.300000
[3,] Paul HIJ 50 1.000000
[4,] Paul JKL 60 2.000000
[5,] Paul MNO 12 3.000000
>
On Fri, Oct 7, 2011 at 8:53 AM, PHILIP GLADWIN, BLOOMBERG TRADEBOOK:
<pgladwin2 at bloomberg.net> wrote:
> Hello,
> Could anybody help me with this question?
>
> Example data frame
> NAME ? ? TICKER ? ? SHARES ? ? PERFORMANCE
> John ? ? ?ABC ? ? ? ?100 ? ? ? ?0.05
> John ? ? ?ABC ? ? ? ?1000 ? ? ? 1.5
> Alice ? ? EFG ? ? ? ?20 ? ? ? ? 0.3
> Paul ? ? ?HIJ ? ? ? ?50 ? ? ? ? 1.0
> Paul ? ? ?JKL ? ? ? ?60 ? ? ? ? 2.0
> Paul ? ? ?MNO ? ? ? ?12 ? ? ? ? 3.0
>
>
> I would like to aggregate this dataframe by NAME, TICKER, and SHARES and to
> perform a weighted mean. ?I expect the output to be
> NAME ? TICKER ? ? SHARES ? ? ? PERFORMANCE
> John ? ABC ? ? ? ?1000+100 ? ? ( (100 * 0.05) + (1000 * 1.5) ) / (100 + 1000)
> Alice ?EFG ? ? ? ?20 ? ? ? ? ? 0.3
> Paul ? HIJ ? ? ? ?50 ? ? ? ? ? 1.0
> Paul ? JKL ? ? ? ?60 ? ? ? ? ? 2.0
> Paul ? MNO ? ? ? ?12 ? ? ? ? ? 3.0
>
> and I would like the output to be in a data frame.
>
> Can anybody help?
> Thank you,
> Phil,
>
> -------------------------------------------------------------------------------
> This e-mail is communicated by Bloomberg Tradebook Europe Limited a company registered in England & Wales No. 3556095 whose regis...{{dropped:23}}