[R] Data frame aggregation

[jim holtman]

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