R help - Aug 2009 - Read and merge a number of .xls files into one dataframe

Hi everybody
I have a large number of Excel speadsheets that I want to merge into one R
dataframe to process. I can read them one by one with read.xls but I really
need a function to read a whole directory at once and merge the columns with
the same name into a single dataframe.

here is what I have thus far (on Mac OS X 10.5.2. for R 2.8.0):

# Define the directory where the data is

path="/Users/home/mydata/"

# Make a list of the filenames (they are all .xls so no need for a regular
expression seekig out ones ending in .xls )

files=list.files(path="/Users/home/mydata/")

# Read them into a list called data

data=tapply(paste(path,files,sep=""),files[1:length(files)],read.xls)


This gives the data in a list. What I want is a dataframe. I can turn it
into a matrix with as.matrix() but that only has one column which is more or
less the same as a list.

I have tried to merge different parts of the list :

mdata=date.frame(merge(data[[1]],data[[2]]))

but that gives an empty data frame (with the correct variable names).


Even if it worked I need a way to do that with a large number of file (i.e.
not manualy).  I also tried to use lapply with merge as function but that
didn't work either


Does anyone have any ideas? I would much appreciate it


regards

Chrisitiaan

	[[alternative HTML version deleted]]

christiaan pauw-2 wrote:
> 
> Hi everybody
> I have a large number of Excel speadsheets that I want to merge into one R
> dataframe to process. I can read them one by one with read.xls but I
> really
> need a function to read a whole directory at once and merge the columns
> with
> the same name into a single dataframe.
> 
> here is what I have thus far (on Mac OS X 10.5.2. for R 2.8.0):
> 
> # Define the directory where the data is
> 
> path="/Users/home/mydata/"
> 
> # Make a list of the filenames (they are all .xls so no need for a regular
> expression seekig out ones ending in .xls )
> 
> files=list.files(path="/Users/home/mydata/")
> 
> # Read them into a list called data
> 
>
data=tapply(paste(path,files,sep=""),files[1:length(files)],read.xls)
> 
> 
> This gives the data in a list. What I want is a dataframe. I can turn it
> into a matrix with as.matrix() but that only has one column which is more
> or
> less the same as a list.
> 
> I have tried to merge different parts of the list :
> 
> mdata=date.frame(merge(data[[1]],data[[2]]))
> 
> but that gives an empty data frame (with the correct variable names).
> 
> 
> Even if it worked I need a way to do that with a large number of file
> (i.e.
> not manualy).  I also tried to use lapply with merge as function but that
> didn't work either
> 
> 
> Does anyone have any ideas? I would much appreciate it
> 
> 
> regards
> 
> Chrisitiaan
> 
> 	[[alternative HTML version deleted]]
> 
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
> 
> 

Hi,
 ya i am tried something on this.
read excel sheets in asingle excel file by using
>datasetname=read.xls("path of Excel file",sheet=sheet number in
that excel
sheet).
after creating R datasets to merge two datasets we use
>datasetname=merge (firsr dataset, second dataset, by.x="USUBJID",
by.y="USUBJID").
there should be a common variable in both the datasets then you will merge. 

-- 
View this message in context:
http://www.nabble.com/Read-and-merge-a-number-of-.xls-files-into-one-dataframe-tp25056047p25056401.html
Sent from the R help mailing list archive at Nabble.com.