I have 12 Excel files, each of them having 6 columns, 259 rows. Everything is numbers, no text. I have to make a table of 72 columns and 259 rows, combining the 12 files respecting this rule:
The first column should be:
file 1 column 1, file 2 column 1, … file 6 column 1,
file 1 column 2, file 2 column 2, … file 6 column 2,
…
file 1 column 6, file 2 column 6,…file 6 column 6
file 7 column 1, file 8 column 1, … file 12 column 1,
file 7 column 2, file 8 column 2, … file 12 column 2,
…
file 12 column 6, file 12 column 6, … file 12 column 6
In the final table I need to add two more columns with text and four rows with text. I tried this approach:
M1<-cbind(file1[1],file2[1],file3[1],file4[1],file5[1],file6[1])
and then merging the temporary tables but it gets huge when writing everything manually using cbind/rbind and merge. I wonder if I can make it in a shorter way.
Is there a possibility to do this more easily?
The script is used to combine the columns from 6 .txt files or 6 excel files. First I tried with 6 files to see how it works, but I have to work with 12 files in the final form (excel or .txt). It looks horrible because I am a beginner in R. Still learning. Thank you for your time.
rm(list=ls())
setwd("path_to_the_folder_where_my_files_are")
############ the files are declared
MAM <- c("G","H","I","M","N")
TAM <-c("a","b","c","d","e")
Index1f <-c("a","b","c","d","e")
Index2f <-c("a","b","c","d","e")
Index3f <-c("a","b","c","d","e")
Index4f <-c("a","b","c","d","e")
Index5f <-c("a","b","c","d","e")
Index6f <-c("a","b","c","d","e")
file7 <-c("","","Text 1","","","")
file7a <-c("","","Text 2","","","")
file7b <-c("","","Text 3","","","")
file7c <-c("","","Text 4","","","")
file7d <-c("","","Text 5","","","")
file7e <-c("","","Text 6","","","")
file8 <-c("","","Text 7","","","")
file9 <-c("G", "H","I", "M", "N", "E ")
file10 <-c("","","Text 8 ","","","")
######### import data from the files
for (i in 1:5) {
TAM [i] <- paste(MAM[i],"2000.txt",sep="_")
}
# The end of the loop for the files import
F1 <- read.table("G2000.txt", header=TRUE,sep="\t")
F2 <- read.table("H2000.txt", header=TRUE,sep="\t")
F3 <- read.table("I2000.txt", header=TRUE,sep="\t")
F4 <- read.table("M2000.txt", header=TRUE,sep="\t")
F5 <- read.table("N2000.txt", header=TRUE,sep="\t")
F6 <- read.table("E2000.txt", header=TRUE,sep="\t")
F11 <- read.table("Gnames.txt", header=TRUE,sep="\t")
#compute the indicators through the combination of the different columns or rows
Index1 <- cbind(file1[1],file2[1],file3[1],file4[1],file5[1],file6[1])
write.table(Index1, "Index1.txt", row.names=F, col.names=T, sep="\t")
Index2 <- cbind(file1[2],file2[2],file3[2],file4[2],file5[2],file6[2])
write.table(Index2, "Index2.txt", row.names=F, col.names=T, sep="\t")
Index3 <- cbind(file1[3],file2[3],file3[3],file4[3],file5[3],file6[3])
write.table(Index3, "Index3.txt", row.names=F, col.names=T, sep="\t")
Index4 <- cbind(file1[4],file2[4],file3[4],file4[4],file5[4],file6[4])
write.table(Index4, "Index4.txt", row.names=F, col.names=T, sep="\t")
Index5 <- cbind(file1[5],file2[5],file3[5],file4[5],file5[5],file6[5])
write.table(Index5, "Index5.txt", row.names=F, col.names=T, sep="\t")
Index6 <- cbind(file1[6],file2[6],file3[6],file4[6],file5[6],file6[6])
write.table(Index6, "Index6.txt", row.names=F, col.names=T, sep="\t")
Index1f <- rbind(file7, file8, file9, file10, Index1) # I added 3 rows at the beginning of the table
write.table(Index1f, "Index1f.xls", row.names=F, col.names=F, sep="\t")
Index2f <- rbind(file7a, file8, file9, file10, Index2) # I added 3 rows at the beginning of the table
write.table(Index2f, "Index2f.xls", row.names=F, col.names=F, sep="\t")
Index3f <- rbind(file7b, file8, file9, file10, Index3) # I added 3 rows at the beginning of the table
write.table(Index3f, "Index3f.xls", row.names=F, col.names=F, sep="\t")
Index4f <- rbind(file7c, file8, file9, file10, Index4) # I added 3 rows at the beginning of the table
write.table(Index4f, "Index4f.xls", row.names=F, col.names=F, sep="\t")
Index5f <- rbind(file7d, file8, file9, file10, Index5) # I added 3 rows at the beginning of the table
write.table(Index5f, "Index5f.xls", row.names=F, col.names=F, sep="\t")
Index6f <- rbind(file7e, file8, file9, file10, Index6) # I added 3 rows at the beginning of the table
write.table(Index6f, "Index6f.xls", row.names=F, col.names=F, sep="\t")
IndexA <- cbind(Index1f, Index2f) # I joined some index
write.table(IndexA, "IndexA.xls", row.names=F, col.names=T, sep="\t")
IndexB <- cbind(Index3f, Index4f) # I joined some index
write.table(IndexB, "IndexB.xls", row.names=F, col.names=T, sep="\t")
IndexC <- cbind(Index5f, Index6f) # I joined some index
write.table(IndexC, "IndexC.xls", row.names=F, col.names=T, sep="\t")
IndexD <- cbind(IndexA, IndexB) # I joined some index
write.table(IndexD, "IndexD.xls", row.names=F, col.names=T, sep="\t")
IndexE <- cbind(IndexD, IndexC) # I joined some index
write.table(IndexE, "Firstpartofthetable.xls", row.names=F, col.names=T, sep="\t")
from what i can understand from your description, this should work:
install.packages('readxl')
library(readxl)
#here i am assuming the folder only contains your 12 files
setwd('path_to_a_folder_where_your_files_are')
files <- list.files()
#read all your tables into one big table, side by side
res_tmp <- NA
for(x in files){
tmp <- read_excel(x)
res_tmp <- cbind(res_tmp, tmp)
}
res_tmp <- res_tmp[, -1]
#rearrange columns according to your specification
ind <- do.call(c, lapply(1 : 6, FUN = function(i) seq(i, 72, by = 6)))
res <- res_tmp[, ind]
res should be your 259 x 72 matrix.
it is not clear what the output is for the second part of your question (adding rows and columns of text).