Combining columns from several Excel files, in a specific order

Question

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") 

Answer 1

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).