How to average different rows of different groups?
2
1
Entering edit mode
10.1 years ago
kmrchllpn ▴ 10

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

unix excel R • 2.1k views
ADD COMMENT
1
Entering edit mode

What have you tried? This is simple in R.

ADD REPLY
1
Entering edit mode
10.1 years ago
Ido Tamir 5.2k

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

ADD COMMENT
1
Entering edit mode
10.1 years ago
1234Jc4321 ▴ 450

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)])
ADD COMMENT

Login before adding your answer.

Traffic: 3255 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6