Subsetting Data with averages across a large data set with variables

[Ruth_Walker]

Hi, I am trying to sort a complex set of data. I have a featurematrix.df which has lots of columns of numbers e.g. 2525.41 title with ~1000 values beneath it. I am trying to select a certain column e.g. 2525.41 based on different column features e.g. Treatment/ Bio for the whole column and then the column left contains 3 replicates each labelled in a second column "R1", "R2" and "R3". I need to average the values for each of these replicates. However, as you can see in the code below although I have started with N1 - I am trying to find a simpler way to code it as I also have N1:N36 and A1:A42 to average and put together to form a table that I can use to make a bar plot for each specified 'biomarker value' (or column value from the original table).

#Add metadata categories
Biomarker_Extract <- featureMatrix.df
Biomarker_Extract$Treatment = type$Treatment
Biomarker_Extract$Bio = type$Bio
Biomarker_Extract$Rep = type$Rep

#Select for biomarker
biomarker.2525.41 <- as.data.frame(Biomarker_Extract[,c("2525.41","Treatment","Bio","Rep")])
rownames(biomarker.2525.41) <- paste(type$Treatment, type$Bio, type$Sample, sep="_")
N <- biomarker.2525.41[biomarker.2525.41$Treatment=="Negative Control",]

#Subset for N1 treatment
N1 <- (N[N$Bio=="1",])

N1.1 <-(N1[N1$Rep=="R1",])
N1.1$R1 <-paste(N1.1$'2525.41')
N1.1$ID <- "ID"
N1.1$ID2 <- 1:nrow(N1.1)
N1.1$Identifier <- paste(N1.1$ID,N1.1$ID2)


N1.2 <-(N1[N1$Rep=="R2",])
N1.2$R2 <-paste(N1.2$'2525.41')
N1.2$ID <- "ID"
N1.2$ID2 <- 1:nrow(N1.2)
N1.2$Identifier <- paste(N1.2$ID,N1.2$ID2)


N1.3 <-(N1[N1$Rep=="R3",])
N1.3$R3 <-paste(N1.3$'2525.41')
N1.3$ID <- "ID"
N1.3$ID2 <- 1:nrow(N1.3)
N1.3$Identifier <- paste(N1.3$ID,N1.3$ID2)

#Merge N1 by "Identifier" column and then try and average
N1_merge <- merge(N1.1,N1.2,by="Identifier")
N1_merge <- merge(N1_merge,N1.3,by="Identifier")
N1_merge = as.data.frame(as.numeric(unlist(N1_merge[,c("R1","R2","R3")])))
N1_Average <- colMeans(N1_merge)

Problem: With the code which I have written below for "N1" I keep getting the error (below) which I have tried to fix with unlist() but I then don't have the data format I need to be able to average the required groups:

N1_Average <- colMeans(N1_merge)
Error in colMeans(N1_merge) : 'x' must be numeric
> N1_merge = as.numeric(N1_merge[,c("R1","R2","R3")])
Error: 'list' object cannot be coerced to type 'double'
> N1_merge = as.numeric(unlist(N1_merge[,c("R1","R2","R3")]))
> N1_Average <- colMeans(N1_merge)
Error in colMeans(N1_merge) : 
  'x' must be an array of at least two dimensions
> N1_merge = as.data.frame(as.numeric(unlist(N1_merge[,c("R1","R2","R3")])))
Error in N1_merge[, c("R1", "R2", "R3")] : incorrect number of dimensions

[Ruth_Walker]

I managed to get this sorted with a very long answer but for the first few Bio variables the answer is plotted below:

N1 <- (N[N$Bio=="1",])
N1$N1 <-paste(N1$'2525.41')
N1$ID <- 1:nrow(N1)
N1 <- subset(N1,select=c(N1,Rep,ID))

N2 <- (N[N$Bio=="2",])
N2$N2 <-paste(N2$'2525.41')
N2$ID <- 1:nrow(N2)
N2 <- subset(N2,select=c(N2,Rep,ID))

N3 <- (N[N$Bio=="3",])
N3$N3 <-paste(N3$'2525.41')
N3$ID <- 1:nrow(N3)
N3 <- subset(N3,select=c(N3,Rep,ID))

N4 <- (N[N$Bio=="4",])
N4$N4 <-paste(N4$'2525.41')
N4$ID <- 1:nrow(N3)
N4 <- subset(N4,select=c(N4,Rep,ID))

N_merge <- list(N1,N2,N3,N4)
N_merge %>% reduce(full_join,by="ID")
N_merge <- as.data.frame(N_merge)
N_merge <- subset(N_merge,select=c(N1,N2,N3,N4,Rep))

N_merge1 <-(N_merge[N_merge$Rep=="R1",])
N_merge1$ID <- 1:nrow(N_merge1)

N_merge2 <-(N_merge[N_merge$Rep=="R1",])
N_merge2$ID <- 1:nrow(N_merge2)

N_merge3 <-(N_merge[N_merge$Rep=="R1",])
N_merge3$ID <- 1:nrow(N_merge3)

N_merge_Rep <- merge(N_merge1,merge(N_merge2,N_merge3,by="ID"),by="ID")
N_merge_Rep <- N_merge_Rep[,c("N1","N2","N3","N4")]
N_merge_Rep <- as.data.frame(sapply(N_merge_Rep,as.numeric))
Average <- as.data.frame(apply(N_merge_Rep,2,mean))
Average$N1 <-paste(Average$`apply(N_merge_Rep, 2, mean)`)