Question: Merge txt files in R into single Excel
0
gravatar for ab123
2.3 years ago by
ab12340
London
ab12340 wrote:

HI guys,

I have been searching for an answer for this online, but cannot seem to find one that suits my needs. That is:

I'm trying to merge around 600 txt files from a microarray experiment into a single Excel (ideally) file using R. Rather than tediously copy and pasting the data of each file into Excel, there must be a faster way through R?

Most of the answers I have found suggest loading in each single file and then merging. At 600 files that's still too complicated.

Is there a way to batch merge these in R Studio?

Many thanks!

excel microarray data merge R • 5.7k views
ADD COMMENTlink modified 2.3 years ago by ivivek_ngs4.8k • written 2.3 years ago by ab12340

what are the contents of this 600 text files? do they have probe one column and the intensity on the other? are the probe ids exactly the same number and in the same order in each file? these information's are required before doing any kind of merge.

ADD REPLYlink written 2.3 years ago by ivivek_ngs4.8k

Hi there, yes they are all the same. Merging them as per the below suggestion didn't work for me, as it doesn't open the files...but then again I'm an R beginner so the code alone below is rather puzzling to me :-( Any suggestions?

ADD REPLYlink modified 2.3 years ago • written 2.3 years ago by ab12340

Great, will give that a go. Many thanks!

ADD REPLYlink written 2.3 years ago by ab12340
1
gravatar for RossCampbell
2.3 years ago by
RossCampbell140
USA/Frederick
RossCampbell140 wrote:

If you put all 600 files (and nothing else) in their own directory, you can use list.files() to pull in all 600 file names into a variable, then read each one individually and append it to a new data frame with either rbind() or cbind(), depending on whether you want to append each new file as rows or columns. For example:

setwd("Your/favorite/directory")
Allfiles = list.files()
for (file in Allfiles){
    #create the new file in the first iteration of the loop by checking if it exists
    if(!exists(df)){df = read.table(file)} #make sure you check if your file has a header
    #now, since we've already created the new file, we can just append to it each time
    if(exists(df)){
         df_temp = read.table(file)
         df = rbind(df, df_temp)
    }
}
#then write your final file
write.table(df,"600Files.txt",sep="\t")

If you need it in Excel format, open the text file in Excel and save as a .XLSX file.

ADD COMMENTlink written 2.3 years ago by RossCampbell140
1

If you need it in Excel format, open the text file in Excel and save as a .XLSX file.

There is also a package to save a table in XLSX format.

ADD REPLYlink written 2.3 years ago by Charles Plessy2.7k
0
gravatar for ivivek_ngs
2.3 years ago by
ivivek_ngs4.8k
Seattle,WA, USA
ivivek_ngs4.8k wrote:

This should be able to work. I reckon they are microarray files so each file should have the first column as probe id and second column as the intensity. So you will have to merge all the files by the column of probe id. Then this should do the work

library(reshape)
file_list <- list.files()
list_of_files <- lapply(file_list, read.csv) # Read in each file if its a csv file or `read.table` or `read.delim`
mm<- merge_all(list_of_files, by = "ID") # ID refers to the column with probe ID so you are merging all your files based on the probe id

list.files() is a function which you point out to the folder/directory where all your files are kept, it should be better to put them all in one. Once you are done you can save the mm dataframe in tab-delimited form and open in excel or use a package to save in excel format.

ADD COMMENTlink modified 2.3 years ago • written 2.3 years ago by ivivek_ngs4.8k
Please log in to add an answer.

Help
Access

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.
Powered by Biostar version 2.3.0
Traffic: 1633 users visited in the last hour