How to average different rows of different groups?

Question

[kmrchllpn]

Hello all,

I have a data in the following format

A1    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.891146    0.108734    0.00001    0.00001    0.00001
A2    0.00001    0.010928    0.00001    0.00001    0.00001    0.00001    0.010163    0.00001    0.00001    0.315348    0.659607    0.003863    0.00001    0.00001
A3    0.017884    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.608175    0.373831    0.00001    0.00001    0.00001
A4    0.00001    0.009866    0.00001    0.00001    0.004547    0.00001    0.003794    0.00001    0.00001    0.392578    0.575197    0.002397    0.01155    0.00001
A5    0.00001    0.021613    0.00001    0.00001    0.00001    0.00001    0.00001    0.016251    0.00001    0.262446    0.699378    0.00001    0.000223    0.00001
A6    0.00001    0.000591    0.00001    0.00001    0.00001    0.00001    0.029345    0.00001    0.00001    0.387329    0.582635    0.00001    0.00001    0.00001
A7    0.00001    0.00001    0.01115    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.368629    0.620111    0.00001    0.00001    0.00001
A8    0.00001    0.010743    0.00001    0.00001    0.002378    0.00001    0.00001    0.00001    0.00001    0.225771    0.738416    0.018053    0.004559    0.00001
A9    0.00001    0.023161    0.00001    0.00001    0.016031    0.00001    0.00001    0.00001    0.00001    0.238668    0.72204    0.00001    0.00001    0.00001
A10    0.00001    0.017919    0.00001    0.003636    0.00001    0.00001    0.00001    0.00001    0.00001    0.299945    0.6784    0.00001    0.00001    0.00001
A11    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.027014    0.00001    0.312604    0.660271    0.00001    0.00001    0.00001
B1    0.00001    0.050104    0.00001    0.098948    0.00001    0.053074    0.00001    0.00001    0.774915    0.00001    0.022869    0.00001    0.00001    0.00001
B2    0.016258    0.091734    0.00001    0.059122    0.00001    0.035551    0.00001    0.00001    0.759745    0.00001    0.00001    0.00001    0.03751    0.00001
B3    0.00001    0.093645    0.00001    0.058207    0.00001    0.075342    0.00001    0.00001    0.763792    0.00001    0.00001    0.008925    0.00001    0.00001
B4    0.037132    0.082506    0.00001    0.00001    0.00001    0.006418    0.00001    0.00001    0.773647    0.00001    0.00001    0.055829    0.030906    0.013491
B5    0.00001    0.085847    0.00772    0.00001    0.00001    0.00001    0.033329    0.00001    0.809694    0.000095    0.00001    0.033829    0.00001    0.029416
C1    0.092266    0.00001    0.010997    0.049623    0.380852    0.011017    0.244976    0.047936    0.042348    0.010022    0.00001    0.00001    0.063346    0.046586
C2    0.114688    0.004414    0.00001    0.093292    0.402671    0.00001    0.19842    0.00001    0.00001    0.00001    0.00001    0.00478    0.161971    0.019704
C3    0.075297    0.00001    0.00001    0.03414    0.501045    0.00001    0.187556    0.005319    0.02793    0.00001    0.00001    0.00001    0.131885    0.036768
C4    0.115    0.00001    0.041102    0.065124    0.347882    0.05578    0.197517    0.032106    0.00001    0.00001    0.00001    0.002207    0.143232    0.00001
C5    0.152739    0.025286    0.010477    0.00001    0.406542    0.02176    0.219094    0.044572    0.00001    0.01274    0.00001    0.00001    0.103988    0.002762
C6    0.191319    0.00001    0.00001    0.079165    0.343816    0.00648    0.141037    0.041638    0.022693    0.00001    0.00001    0.026643    0.109719    0.037449
C7    0.102538    0.00001    0.088414    0.078552    0.379499    0.031857    0.182849    0.00001    0.020398    0.00001    0.00001    0.010135    0.093655    0.012064
C8    0.096661    0.00001    0.070106    0.028832    0.330699    0.020614    0.20455    0.04461    0.00001    0.006313    0.008697    0.00001    0.105221    0.083666
E1    0.814651    0.00001    0.00001    0.00001    0.151833    0.00001    0.007088    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.026328
E2    0.744489    0.00001    0.00001    0.00001    0.148372    0.045737    0.00001    0.00001    0.01611    0.00001    0.00001    0.003001    0.00001    0.04221
E3    0.774646    0.00001    0.00001    0.00001    0.207946    0.006951    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.00001    0.010358
E4    0.729396    0.00001    0.00001    0.00001    0.188963    0.00001    0.012583    0.035127    0.00001    0.00001    0.00001    0.032131    0.001721    0.00001
E5    0.692873    0.00001    0.00001    0.00001    0.20956    0.018129    0.00001    0.028463    0.00001    0.00001    0.00001    0.03228    0.018616    0.00001 

And now I need a function/command which can average separately all 14 rows for A, B, C and E groups.

Thanks

[Devon Ryan]

What have you tried? This is simple in R.

Answer 1

[Ido Tamir]

a) its general programming

b) you should post a simplified version of your original data + the desired outcome in a reproducible form

c) without testing:

library("plyr")
df$group <- substring(df[,1])
df <- df[,-1]
ddply(df, .(group), function(sub){ mean=mean(unlist(sub)) })

d) I'm procrastinating

Answer 2

[1234Jc4321]

with R:

df$V1<-substr(df$V1,1,1)
df2 <- aggregate(x = df[, 2:ncol(df)], by = list(group = df$V1), FUN = "mean", na.rm = T)
df3<-as.data.frame(df2$group)
df3$mean<-rowMeans(df2[,c(2:15)])