Convert Metadata Table to Group Mapping File and Attribute Mapping File

load library



In [13]:

    
library(plyr)

create your Metadata Table in Excel and export as .csv.
put your .csv file in the same folder as the script in jupyter and specify its name here



In [14]:

    
MetaData <- read.csv("metadata_table.csv", sep=";", dec=".",header = TRUE)

convert file



In [15]:

    
ngroups <- ncol(MetaData)-1
l <- list()

for (i in 1:ngroups){
  l[[i]] <- table(MetaData[,c(1,i+1)]) 
  l[[i]] <- as.data.frame.matrix(l[[i]])
  l[[i]]$SampleNames <- rownames(l[[i]]) 
}

MetaTable <- join(l[[1]],l[[2]],by="SampleNames")

if (ngroups != 2){
  for (i in 3:length(l)){
    MetaTable <- join(MetaTable,l[[i]],by="SampleNames")
  }
}

GMtab <- data.matrix(MetaTable[,-which(colnames(MetaTable)=="SampleNames")])
rownames(GMtab) <- MetaTable$SampleName

vec <- c()
for (i in 1:ncol(GMtab)){
  vec <- c(vec,paste("GROUP_",colnames(GMtab)[i],"=",rownames(GMtab)[which(GMtab[,i]!=0)],sep=""))
}

vec <- gsub("\\s","", vec) #remove spaces
vec <- gsub("\\(|\\)", ":", vec) # "()" brackets are not recognized, replace them by ":"

groups <- gsub("=.*","",vec)
groups <- gsub("\\s","", groups) #remove spaces
groups <- unique(groups)
groups <- gsub("\\(|\\)", ":", groups)

mat <- matrix(0,length(groups),100000)

for (i in 1:length(groups)){
  x <- paste(groups[i],"=",sep="")
  pos <- c()
  for (j in 1:length(vec)){
    if (length(grep(x,vec[j])==1)==1){
      pos <- c(pos,j)
      mat[i,1:length(pos)] <- gsub(".*=","",vec[pos])
    } 
  }
}

rownames(mat) <- groups

col <- c()
for (i in 1:nrow(mat)){
  len <- length(which(mat[i,] != "0"))
  col <- c(col,paste(mat[i,1:len],collapse=";"))
}

v <- c()
for (i in 1:length(col)){
  v <- c(v,paste(groups[i],"=",col[i],sep=""))
}

write Group Mapping file (specify name)



In [10]:

    
write.table(v,file="GroupMapping.txt",row.names=F,col.names=F,quote=F)

create Attribute Mapping File



In [11]:

    
av <- c()
for (i in 2:ncol(MetaData)){
  f <- unique(MetaData[,i])
  f <- gsub("\\s","", f) #remove spaces
  f <- gsub("\\(|\\)", ":", f) #remove brackets
  q <- colnames(MetaData)[i]
  q <- gsub("\\s","", q)
  q <- gsub("\\(|\\)", ":", q) #remove brackets
  av <- c(av,paste(colnames(MetaData)[i],"=",paste(f,collapse=";"),sep=""))
}

write Attribute Mapping file (specify name)



In [12]:

    
write.table(av,file="AttributeMapping.txt",row.names=F,col.names=F,quote=F)



In [ ]: