Data frame aggregation
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}}