Dear R Discussion List,
I would like to save my data as a xlsx file. But at first
I need to split it and then save each series into a Excel
column. Please take a look at the following code:
dados <- data.frame(matrix(c("2012-01-01","2012-02-01",
"2012-03-01","2012-04-01","2012-05-01","2012-06-01",
"2012-01-01","2012-02-01","2012-03-01","2012-04-01",
"2012-05-01","2012-06-01","2012-01-01","2012-02-01",
"2012-03-01","2012-04-01","2012-05-01","2012-06-01",
0.56,0.45,0.21,0.64,0.36,0.08,152136,153081,155872,
158356,162157,166226,33.47,34.48,35.24,38.42,35.33,
34.43,433,433,433,433,433,433,2005,2005,2005,2005,
2005,2005,3939,3939,3939,3939,3939,3939),nrow=18,
ncol=3,byrow=FALSE,dimnames=list(c(1,2,3,4,5,6,7,8,9,
10,11,12,13,14,15,16,17,18),c("date","value","code"))))
dados2 <- split(dados, dados$code)
dados2
library(XLConnect)
wb <- loadWorkbook("Teste.xlsx", create = TRUE)
createSheet(wb, name = "Teste1")
writeWorksheet(wb, dados2, sheet = "Teste1")
saveWorkbook(wb)
With this code I only get the "433" series. How could I
fix my code? How could I include the other series?
Many thanks in advance,
Henrique Andrade
Hi,
I think saving it as .csv file will be much easier.? I am getting all the series
in the wide format.
write.csv(dados2,"dados2.csv")
X2005.date X2005.value X2005.code X3939.date X3939.value X3939.code X433.date
X433.value X433.code
7 2012-01-01 152136 2005 2012-01-01 33.47 3939 2012-01-01 0.56 433
8 2012-02-01 153081 2005 2012-02-01 34.48 3939 2012-02-01 0.45 433
9 2012-03-01 155872 2005 2012-03-01 35.24 3939 2012-03-01 0.21 433
10 2012-04-01 158356 2005 2012-04-01 38.42 3939 2012-04-01 0.64 433
11 2012-05-01 162157 2005 2012-05-01 35.33 3939 2012-05-01 0.36 433
12 2012-06-01 166226 2005 2012-06-01 34.43 3939 2012-06-01 0.08 433
A.K.
----- Original Message -----
From: Henrique Andrade <henrique.coelho at gmail.com>
To: R Discussion List <r-help at r-project.org>
Cc:
Sent: Wednesday, August 8, 2012 4:10 PM
Subject: [R] Saving Splitted Series to Excel via XLConnect
Dear R Discussion List,
I would like to save my data as a xlsx file. But at first
I need to split it and then save each series into a Excel
column. Please take a look at the following code:
dados <- data.frame(matrix(c("2012-01-01","2012-02-01",
?
"2012-03-01","2012-04-01","2012-05-01","2012-06-01",
?
"2012-01-01","2012-02-01","2012-03-01","2012-04-01",
?
"2012-05-01","2012-06-01","2012-01-01","2012-02-01",
?
"2012-03-01","2012-04-01","2012-05-01","2012-06-01",
? 0.56,0.45,0.21,0.64,0.36,0.08,152136,153081,155872,
? 158356,162157,166226,33.47,34.48,35.24,38.42,35.33,
? 34.43,433,433,433,433,433,433,2005,2005,2005,2005,
? 2005,2005,3939,3939,3939,3939,3939,3939),nrow=18,
? ncol=3,byrow=FALSE,dimnames=list(c(1,2,3,4,5,6,7,8,9,
?
10,11,12,13,14,15,16,17,18),c("date","value","code"))))
dados2 <- split(dados, dados$code)
dados2
library(XLConnect)
wb <- loadWorkbook("Teste.xlsx", create = TRUE)
createSheet(wb, name = "Teste1")
writeWorksheet(wb, dados2, sheet = "Teste1")
saveWorkbook(wb)
With this code I only get the "433" series. How could I
fix my code? How could I include the other series?
Many thanks in advance,
Henrique Andrade
______________________________________________
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.
Dear Arun, Thanks a lot for your help, but I really need to save the splitted data in a Excel file (xlsx or xls). This is because I will need to make links between different Excel workbooks. Best regards, Henrique Andrade Enviado via iPhone Em 08/08/2012, ?s 17:25, arun <smartpink111 at yahoo.com> escreveu:> Hi, > > I think saving it as .csv file will be much easier. I am getting all the series in the wide format. > > write.csv(dados2,"dados2.csv") > > > X2005.date X2005.value X2005.code X3939.date X3939.value X3939.code X433.date X433.value X433.code > 7 2012-01-01 152136 2005 2012-01-01 33.47 3939 2012-01-01 0.56 433 > 8 2012-02-01 153081 2005 2012-02-01 34.48 3939 2012-02-01 0.45 433 > 9 2012-03-01 155872 2005 2012-03-01 35.24 3939 2012-03-01 0.21 433 > 10 2012-04-01 158356 2005 2012-04-01 38.42 3939 2012-04-01 0.64 433 > 11 2012-05-01 162157 2005 2012-05-01 35.33 3939 2012-05-01 0.36 433 > 12 2012-06-01 166226 2005 2012-06-01 34.43 3939 2012-06-01 0.08 433 > > A.K. > > > > ----- Original Message ----- > From: Henrique Andrade <henrique.coelho at gmail.com> > To: R Discussion List <r-help at r-project.org> > Cc: > Sent: Wednesday, August 8, 2012 4:10 PM > Subject: [R] Saving Splitted Series to Excel via XLConnect > > Dear R Discussion List, > > I would like to save my data as a xlsx file. But at first > I need to split it and then save each series into a Excel > column. Please take a look at the following code: > > dados <- data.frame(matrix(c("2012-01-01","2012-02-01", > "2012-03-01","2012-04-01","2012-05-01","2012-06-01", > "2012-01-01","2012-02-01","2012-03-01","2012-04-01", > "2012-05-01","2012-06-01","2012-01-01","2012-02-01", > "2012-03-01","2012-04-01","2012-05-01","2012-06-01", > 0.56,0.45,0.21,0.64,0.36,0.08,152136,153081,155872, > 158356,162157,166226,33.47,34.48,35.24,38.42,35.33, > 34.43,433,433,433,433,433,433,2005,2005,2005,2005, > 2005,2005,3939,3939,3939,3939,3939,3939),nrow=18, > ncol=3,byrow=FALSE,dimnames=list(c(1,2,3,4,5,6,7,8,9, > 10,11,12,13,14,15,16,17,18),c("date","value","code")))) > > dados2 <- split(dados, dados$code) > dados2 > > library(XLConnect) > > wb <- loadWorkbook("Teste.xlsx", create = TRUE) > createSheet(wb, name = "Teste1") > writeWorksheet(wb, dados2, sheet = "Teste1") > saveWorkbook(wb) > > With this code I only get the "433" series. How could I > fix my code? How could I include the other series? > > Many thanks in advance, > Henrique Andrade > > ______________________________________________ > 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. >
On Aug 8, 2012, at 1:10 PM, Henrique Andrade wrote:> Dear R Discussion List, > > I would like to save my data as a xlsx file. But at first > I need to split it and then save each series into a Excel > column.That doesn't make much sense. You have three columns. What are you calling a "series" (which is not a term that has any particular meaning in R.)> Please take a look at the following code: > > dados <- data.frame(matrix(c("2012-01-01","2012-02-01", > "2012-03-01","2012-04-01","2012-05-01","2012-06-01", > "2012-01-01","2012-02-01","2012-03-01","2012-04-01", > "2012-05-01","2012-06-01","2012-01-01","2012-02-01", > "2012-03-01","2012-04-01","2012-05-01","2012-06-01", > 0.56,0.45,0.21,0.64,0.36,0.08,152136,153081,155872, > 158356,162157,166226,33.47,34.48,35.24,38.42,35.33, > 34.43,433,433,433,433,433,433,2005,2005,2005,2005, > 2005,2005,3939,3939,3939,3939,3939,3939),nrow=18, > ncol=3,byrow=FALSE,dimnames=list(c(1,2,3,4,5,6,7,8,9, > 10,11,12,13,14,15,16,17,18),c("date","value","code")))) > > dados2 <- split(dados, dados$code) > dados2 > > library(XLConnect) > > wb <- loadWorkbook("Teste.xlsx", create = TRUE) > createSheet(wb, name = "Teste1") > writeWorksheet(wb, dados2, sheet = "Teste1") > saveWorkbook(wb)Perhaps: str(dados2) # it is a list of three dataframes writeWorksheet(wb, do.call(rbind, dados2), sheet = "Teste1") saveWorkbook(wb) This basically undoes the split() operation that created dados2. Please note that those dates are actually factor variables in R.> > With this code I only get the "433" series. How could I > fix my code? How could I include the other series? > > Many thanks in advance, > Henrique Andrade >-- David Winsemius, MD Alameda, CA, USA
Hello,
First of all, apologies to Henrique, he'll receive th same answer twice,
but I forgot to Cc the list.
In order to write 3 worksheets you need to create 3 worksheets. What
happened is that you were overwriting the previous sheets and ended up
just with the last one. So adopt a different method: lapply().
wb <- loadWorkbook("Teste.xlsx", create = TRUE)
series <- seq_along(dados2)
sheet <- paste0("Teste", series)
lapply(series, function(i){
createSheet(wb, name = sheet[i])
writeWorksheet(wb, dados2[[i]], sheet = sheet[i])})
saveWorkbook(wb)
This worked with me.
Hope this helps,
Rui Barradas
Em 08-08-2012 21:10, Henrique Andrade escreveu:> Dear R Discussion List,
>
> I would like to save my data as a xlsx file. But at first
> I need to split it and then save each series into a Excel
> column. Please take a look at the following code:
>
> dados <-
data.frame(matrix(c("2012-01-01","2012-02-01",
>
"2012-03-01","2012-04-01","2012-05-01","2012-06-01",
>
"2012-01-01","2012-02-01","2012-03-01","2012-04-01",
>
"2012-05-01","2012-06-01","2012-01-01","2012-02-01",
>
"2012-03-01","2012-04-01","2012-05-01","2012-06-01",
> 0.56,0.45,0.21,0.64,0.36,0.08,152136,153081,155872,
> 158356,162157,166226,33.47,34.48,35.24,38.42,35.33,
> 34.43,433,433,433,433,433,433,2005,2005,2005,2005,
> 2005,2005,3939,3939,3939,3939,3939,3939),nrow=18,
> ncol=3,byrow=FALSE,dimnames=list(c(1,2,3,4,5,6,7,8,9,
>
10,11,12,13,14,15,16,17,18),c("date","value","code"))))
>
> dados2 <- split(dados, dados$code)
> dados2
>
> library(XLConnect)
>
> wb <- loadWorkbook("Teste.xlsx", create = TRUE)
> createSheet(wb, name = "Teste1")
> writeWorksheet(wb, dados2, sheet = "Teste1")
> saveWorkbook(wb)
>
> With this code I only get the "433" series. How could I
> fix my code? How could I include the other series?
>
> Many thanks in advance,
> Henrique Andrade
>
> ______________________________________________
> 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.