CODEWITHSUNDEEP
rexceldata.tablexlsx
Tim_Utrecht
Tim_Utrecht

Reputation: 1519

Write .xlsx in R with double header

Is it possible to write a data.frame/data.table to a .xlsx with a double header? My data contains numeric, date and string columns. Please find a example below.

example <- data.frame(Number=c(1:5),
                      Date=as.Date('1999-10-31','1999-10-31','1999-10-31','1999-10-31','1999-10-31','1999-10-31'),
                      String=LETTERS[1:5])

Say, I would like to have a double header with Number being "One", Date being "Two" and String being "Three". But obviously I can't place them on row 1 since they 2nd column names are strings, and some columns are dates/numerics.

Any idea on this?

@Edit: example:

example <- data.frame(Number=c("One",as.character(1:5)),
                      Date=c("Two",'1999-10-31','1999-10-31','1999-10-31','1999-10-31','1999-10-31'),
                      String=c("Three",LETTERS[1:5]))

 Number       Date String
1    One        Two  Three
2      1 1999-10-31      A
3      2 1999-10-31      B
4      3 1999-10-31      C
5      4 1999-10-31      D
6      5 1999-10-31      E

But now obviously all columns are strings... And I need them as numerics and dates for specific fields. Thanks.

Upvotes: 1

Views: 2560

Answers (2)

dickoa
dickoa

Reputation: 18437

You can use XLConnect package for example. Here is a toy example using the mtcars data. You just need to proceed in two step: first write just the colnames (in data.frame) and write the data as usual starting at row 2.

library(XLConnect)
header <- data.frame(t(colnames(mtcars)))
header
##    X1  X2   X3 X4   X5 X6   X7 X8 X9  X10  X11
## 1 mpg cyl disp hp drat wt qsec vs am gear carb
writeWorksheetToFile(file = "/tmp/file.xlsx", data = header, sheet = "test",
                     startRow = 1, header = FALSE)

### Write the data as usual keeping the colnames
writeWorksheetToFile(file = "/tmp/file.xlsx", data = mtcars, sheet = "test",
                     startRow = 2)

Using the data you provided, you can something like this:

header <- as.data.frame(rbind(c("One", "Two", "Three")))
example <- data.frame(Number=c(1:5),
                      Date=as.Date('1999-10-31','1999-10-31',
                                   '1999-10-31','1999-10-31',
                                   '1999-10-31','1999-10-31'),
                      String=LETTERS[1:5])


writeWorksheetToFile(file = "/tmp/file2.xlsx", data = header, sheet = "test",
                     startRow = 1, header = FALSE)
writeWorksheetToFile(file = "/tmp/file2.xlsx", data = example, sheet = "test",
                     startRow = 2)

Upvotes: 3

RDGuida
RDGuida

Reputation: 566

You can "override" the write.csv() function like that

New_write.csv <- function(file,example)
{
    Header1 <- paste(names(example),collapse=",")
    Header2 <- paste(c("one","Two","Three"),collapse=",")
    writeLines(paste(Header1,Header2,sep="\n"),file)
    write.table(example, file, sep = ",", append = TRUE, col.names = FALSE,row.names=F)
}

and then call it normally like that:

New_write.csv("example.csv",example)

Upvotes: 1

Related Questions