R help - Oct 2011 - Data frame aggregation

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 registered office is at City
Gate House, 39-45 Finsbury Square, London EC2A 1 PQ and which is authorized and
regulated by the UK Financial Services Authority registered under number 187492.
Bloomberg Tradebook Europe's services are only available to professional
investors. This e-mail is not an offer or a solicitation to buy or sell, and
does not constitute any investment advice or recommendation, of any security or
other financial instrument.

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

On Oct 7, 2011, at 14:53 , PHILIP GLADWIN, BLOOMBERG TRADEBOOK: 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?

You can do it in steps:

1. calculate a new variable, say TMP <- SHARES*PERFORMANCE
2. aggregate SHARES and TMP using sum()
3. in the aggregated data compute PERFORMANCE <- TMP/SHARES

(Interesting to see that we have users inside Bloomberg, give the recent
trademark complaint against the rbloomberg package...)

-pd

> Thank you,
> Phil,
> 
>
-------------------------------------------------------------------------------
> This e-mail is communicated by Bloomberg Tradebook Europe Limited a company
registered in England & Wales No. 3556095 whose registered office is at City
Gate House, 39-45 Finsbury Square, London EC2A 1 PQ and which is authorized and
regulated by the UK Financial Services Authority registered under number 187492.
Bloomberg Tradebook Europe's services are only available to professional
investors. This e-mail is not an offer or a solicitation to buy or sell, and
does not constitute any investment advice or recommendation, of any security or
other financial instrument.
> ______________________________________________
> R-help at r-project.org 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.
-- 
Peter Dalgaard, Professor
Center for Statistics, Copenhagen Business School
Solbjerg Plads 3, 2000 Frederiksberg, Denmark
Phone: (+45)38153501
Email: pd.mes at cbs.dk  Priv: PDalgd at gmail.com