R help - Mar 2009 - Creating an Excel file with multiple spreadsheets

DeaR all,

I'd like to know how to create an Excel file with multiple spreadsheets from
R. I searched the help files and found [1] but it is not what I want to do.

Let's say I have a data frame called DF as in the following code

# Some data
set.seed(123)
ID<-sample(5,100,replace=TRUE)
X<-rnorm(100)
Y<-rpois(100,10)
Z<-X*Y
DF<-data.frame(ID,X,Y,Z)
str(DF)

I'd like to split DF by ID and then save the result in an Excel file (say
"information.xls") with multiple spreadsheets (one per ID). Also, my
output
should include the original data as a new spreadsheet called, for example,
"original". At the end I will have (in this example) six different
spreadsheets in "information.xls".

To split() DF I can easily do

# Splitting data
sDF<-with(DF,split(DF,ID))
sDF

and to save the information per ID in different Excel files, rather than in
"information.xls" with a spreadsheet by ID, I can do


# Working directory  -- please change this when run the code in your R
session
setwd("C:\\")

# Saving the information by ID in a different Excel file
# called "information_ID_numberhere.xls"
lapply(1:5,function(i)

write.table(sDF[[i]][,-1],paste("information_ID_",i,".xls",sep=""),col.names=TRUE,row.names=FALSE,sep="\t")
     )

How can I export DF (the original data), DF such as ID==1, DF such as
ID==2,..., DF such as ID==5 to "information.xls" (just one file)
naming the
spreadsheets as "original", "ID_1",
"ID_2",...,"ID_5", respectively?

I'm running R 2.8.1 Patched on XP. Here is my sessionInfo()

R version 2.8.1 Patched (2009-01-01 r47434)
i386-pc-mingw32

locale:
LC_COLLATE=English_United States.1252;LC_CTYPE=English_United
States.1252;LC_MONETARY=English_United
States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252

attached base packages:
[1] grid      splines   stats     graphics  grDevices utils     datasets
 methods   base

other attached packages:
 [1] ggplot2_0.8.2      reshape_0.8.2      plyr_0.1.5         proto_0.3-8
     epicalc_2.8.1.1
 [6] survival_2.34-1    foreign_0.8-33     lattice_0.17-20
 xlsReadWrite_1.3.2 RODBC_1.2-5


Any help would be greatly appreciated.

Thanks in advance,


Jorge Ivan Velez


[1] http://tinyurl.com/aurprr

	[[alternative HTML version deleted]]

On Mon, 09-Mar-2009 at 02:34PM -0400, Jorge Ivan Velez wrote:

|> DeaR all,
|> 

|> I'd like to know how to create an Excel file with multiple
|> spreadsheets from R. I searched the help files and found [1] but it
|> is not what I want to do.

I use the Perl script from Marc Schwartz in that list.  It's very
useful.  I think if you can work out the R code to make the dataframes
you want, it's a matter of using write.csv() to make the CSV files that
the Perl code can use.



|> 
|> [1] http://tinyurl.com/aurprr
|> 


-- 
~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.   
   ___    Patrick Connolly   
 {~._.~}                   Great minds discuss ideas    
 _( Y )_  	         Average minds discuss events 
(:_~*~_:)                  Small minds discuss people  
 (_)-(_)  	                      ..... Eleanor Roosevelt
	  
~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.

Just wondering -- the referenced thread at

  [1] http://tinyurl.com/aurprr

included a comment from Gabor (I think) that some day the gdata package 
might include a   'write.xls' based on M Schwartz'  perl script.

Any progress there?


Carl

On Mon, 09-Mar-2009 at 02:34PM -0400, Jorge Ivan Velez wrote:
|> DeaR all,
|>
|> I''d like to know how to create an Excel file with multiple
|> spreadsheets from R. I searched the help files and found [1] but it
|> is not what I want to do.

If you''re happy to limit yourself to distributing your excel file to
people
who have excel 2007 (or have the converters for the older versions of
office), or with a manual step of opening it yourself in 2007 and saving-as
in an older version, I have a script that utilises the xml format that Excel
now accepts.  It''s a bit ugly as it''s got lots of stuff
specific to my
organisation, but if you want it can be obtained from
http://bioinformatics.cancerresearchuk.org/cms/index.php?page=gpk-resources

I really should contact the authors of the r/excel packages to see if any of
them would want a cleaned up version of this.

Regards - Gavin

	[[alternative HTML version deleted]]