0

There are multiple solutions like this posted in this portal to calculate mean for values in a column based on another column by grouping using R. However, I am not able to calculate mean of replicate experiment result values in a column based on multiple columns using R. I need to keep all the columns except the Rep column, and the value column replaced with mean values based on Rep and all other columns.

library(dplyr)

Cmpd1  <-  c("abc1","abc1","abc1","abc1","abc1","abc1","abc1","abc1","abc1","abc1","abc1","abc1")
cmpd1_conc  <-  c("0.0412","0.0412","0.1235","0.1235","0.3704","0.3704","1.1111","1.1111","3.3333","3.3333","10","10")
Cmpd2 <-  c("xyz1","xyz1","xyz1","xyz1","xyz1","xyz1","xyz1","xyz1","xyz1","xyz1","xyz1","xyz1")
cmpd2_conc   <- c("1","1","1","1","1","1","1","1","1","1","1","1")
value    <- c(157120,144160,110480,92600,100800,97600,92240,78800,67920,61520,37640,34240)
Plate  <- c("Plate1","Plate1","Plate1","Plate1","Plate1","Plate1","Plate1","Plate1","Plate1","Plate1","Plate1","Plate1")
CL  <- c("CL1","CL1","CL1","CL1","CL1","CL1","CL1","CL1","CL1","CL1","CL1","CL1")
Rep   <-  c(1,2,1,2,1,2,1,2,1,2,1,2)

Result <- data.frame(Cmpd1,cmpd1_conc, Cmpd2, cmpd2_conc, value, Plate, CL, Rep)

MeanResult <- Result %>%
  group_by(Cmpd1, cmpd1_conc, Cmpd2, 
           cmpd2_conc, Plate, CL, Rep) %>% 
  summarize(MeanValue = mean(value))

I tried several ways as suggested in multiple entries in this portal in vain. I wish to know what am I missing.

Rui Barradas
  • 70,273
  • 8
  • 34
  • 66
RanonKahn
  • 853
  • 10
  • 34
  • 2
    Unclear what do you want to do. What is the mean you are calculating? Your code shows that you include too many grouping variables. Eventually, each row becomes its own group and mean is the same as the original value for each row. – www Aug 15 '19 at 22:28
  • Given your description of the problem you have to summarise by all columns *except* for `Rep`. Try removing it from `group_by`. Also, there is no need for `as.numeric`. – Rui Barradas Aug 15 '19 at 22:31
  • These are values from replicate experiments 1 and 2. I want to average the values obtained and need help with that. – RanonKahn Aug 15 '19 at 22:32
  • You replicate each experiment twice? – Rui Barradas Aug 15 '19 at 22:33
  • yes. Rep 1 and Rep 2 – RanonKahn Aug 15 '19 at 22:37
  • Then do what I've said in my first comment, remove `Rep` from `group_by`. And get rid of `as.numeric` too. – Rui Barradas Aug 15 '19 at 22:37
  • I did. I tried this: Result2 <- Result %>% summarize(Cmpd1,cmpd1_conc, Cmpd2, cmpd2_conc, Plate, CL, value) but I am not getting the mean values. – RanonKahn Aug 15 '19 at 22:39

1 Answers1

1

If I understand correctly the code you need is:

res2 <- aggregate(value~Cmpd1 + cmpd1_conc + Cmpd2 + cmpd2_conc + Plate + CL, FUN = mean, data = Result)

it produces the data.frame

 res2
  Cmpd1 cmpd1_conc Cmpd2 cmpd2_conc  Plate  CL  value
1  abc1     0.0412  xyz1          1 Plate1 CL1 150640
2  abc1     0.1235  xyz1          1 Plate1 CL1 101540
3  abc1     0.3704  xyz1          1 Plate1 CL1  99200
4  abc1     1.1111  xyz1          1 Plate1 CL1  85520
5  abc1         10  xyz1          1 Plate1 CL1  35940
6  abc1     3.3333  xyz1          1 Plate1 CL1  64720
Grada Gukovic
  • 1,228
  • 7
  • 13