[R] averaging between rows with repeated data

[R. Michael Weylandt]

Good morning Rob,

First off, thank you for providing a reproducible example. This is one
of those little tasks that R is pretty great at, but there exist
>\infty ways to do so and it can be a little overwhelming for the
beginner: here's one with the base function ave():

cbind(ave(example[,2:4], example[,5]), id = example[,5])

This splits example according to the fifth column (id) and averages
the other values: we then stick another copy of the id back on the end
and are good to go.

The base function aggregate can do something similar:

aggregate(example[,2:4], by = example[,5, drop = F], mean)

Note that you need the little-publicized but super useful drop = F
command to make this one work.

There are other ways to do this with the plyr or doBy packages as
well, but this should get you started.

Hope it helps,

Michael

On Tue, Nov 15, 2011 at 5:52 AM, robgriffin247
<robgriffin247 at hotmail.com> wrote:
> *The situation (or an example at least!)*
>
> example<-data.frame(rep(letters[1:10]))
> colnames(example)[1]<-("Letters")
> example$numb1<-rnorm(10,1,1)
> example$numb2<-rnorm(10,1,1)
> example$numb3<-rnorm(10,1,1)
> example$id<-c("CG234","CG232","CG441","CG128","CG125","CG182","CG232","CG441","CG232","CG125")
>
> *this produces something like this:*
>  Letters     numb1      numb2        numb3    id
> 1        a 0.8139130 -0.9775570 -0.002996244 CG234
> 2        b 0.8268700  0.4980661  1.647717998 CG232
> 3        c 0.2384088  1.0249684  0.120663273 CG441
> 4        d 0.8215922  0.5686534  1.591208307 CG128
> 5        e 0.7865918  0.5411476  0.838300185 CG125
> 6        f 2.2385522  1.2668070  1.268005020 CG182
> 7        g 0.7403965 -0.6224205  1.374641549 CG232
> 8        h 0.2526634  1.0282978 -0.110449844 CG441
> 9        i 1.9333444  1.6667486  2.937252363 CG232
> 10       j 1.6996701  0.5964623  1.967870617 CG125
>
> *The Problem:*
> Some of these id's are repeated, I want to average the values for those rows
> within each column but obviously they have different numbers in the numbers
> column, and they also have different letters in the letters column, the
> letters are not necessary for my analysis, only the duplicated id's and the
> numb columns are important
>
> I also need to keep the existing dataframe so would like to build a new
> dataframe that averages the repeated values and keeps their id - my actual
> dataset is much more complex (271*13890) - but the solution to this can be
> expanded out to my main data set because there is just more columns of
> numbers and still only one alphanumeric id to keep in my example data, id
> CG232 occurs 3 times, CG441 & CG125 occur twice, everthing else once so the
> new dataframe (from this example) there would be 3 number columns (numb1,
> numb2, numb3) and an id the numb column values would be the averages of the
> rows which had the same id
>
> so for example the new dataframe would contain an entry for CG125 which
> would be something like this:
>
> numb1    numb2    numb3       id
> 1.2431     0.5688     1.403         CG125
>
> Just as a thought, all of the IDs start with CG so could I use then grep (?)
> to delete CG and replace it with 0, that way duplicated ids could be
> averaged as a number (they would be the same) but I still don’t know how to
> produce the new dataframe with the averaged rows in it...
>
> I hope this is clear enough! email me if you need further detail or even
> better, if you have a solution!!
> also sorry to be posting my second question in under 24hours but I seem to
> have become more than a little stuck – I was making such good progress with
> R!
>
> Rob
>
> (also I'm sorry if this appears more than once on the mailing list - I'm
> having some network & windows live issues so I'm not convinced previous
> attempts to send this have worked, but have no way of telling if they are
> just milling around in the internet somewhere as we speak and will decide to
> come out of hiding later!)
>
> --
> View this message in context: http://r.789695.n4.nabble.com/averaging-between-rows-with-repeated-data-tp4042513p4042513.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> 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.
>