2

I adapted a script to run the multiple (post-hoc) comparisons dunn.test function from the dunn.test package, on multiple variables, which generates multiple data frames, saved with the bellow (simplified) code in an .xls file as separate tables. What I want is to save with the each date frame also the name of variable (column) that was analyzed, as table titles (not column titles). I am looking for a solution for several hours, but without success. How to do this? They are too many and I do not want to enter them manually.

wb <- createWorkbook()
sheet <- createSheet(wb, sheetName="Sheet1")
# start analyzing data in a for loop here
# and add created data frames to a sheet
    addDataFrame(mydataframe, sheet)
# end loop here
saveWorkbook(wb, "myfile.xls", sep="")
Community
  • 1
  • 1
Yurié
  • 2,148
  • 3
  • 23
  • 40

2 Answers2

2

You could convert your source data.frame to a text matrix, prefix whatever title you want, and write that to file.

For example, given this data.frame:

dat <- data.frame(Fruit=c('Apple', 'Banana'), 
                  Quantity=1:2, 
                  Notes=c('Hello', 'Some Text'))

You could use a function like this:

text_matrix <- function(dat, table_title) {

  rbind(c(table_title, rep('', ncol(dat)-1)), # title
        rep('', ncol(dat)), # blank spacer row
        names(dat), # column names
        unname(sapply(dat, as.character))) # data

}

Then you can call the function and write the output to file (using write.table or one of the xls conversion packages if you must).

temp <- text_matrix(dat, table_title='Here is a table title')
write.table(temp, 'temp.csv', row.names=F, col.names=F, sep=',')

The result, opened in Excel, is this:

enter image description here

arvi1000
  • 9,393
  • 2
  • 42
  • 52
  • Excellent! Excepting that numeric values now are recognized by LibreOffice Calc (maybe also by Excel) only as a text and this is not good. – Yurié Dec 24 '14 at 18:00
  • 1
    You could add `quote=FALSE` to the write.table command, and the numbers should turn back to numbers in the spreadsheet. If you know none of the strings in your data need to be enclosed in quotes (the way string containing commas do, e.g.), that will be the easiest fix – arvi1000 Dec 24 '14 at 18:19
  • By the way, can be done this with the `write.xlsx` directly in a .xls file? – Yurié Dec 24 '14 at 19:29
0

Hope the following helps. I created quickly an example dataset. Output.csv can be found in your home directory. Opening it in excell will show the column titles.

n = 20
set.seed(12345)
data = data.frame(cbind(pond=1:2, habitat=1:3, value = rnorm(n)))

write.csv(data, file="output.csv")
Ruthger Righart
  • 4,799
  • 2
  • 28
  • 33
  • `Warning message: In cbind(pond = 1:2, habitat = 1:3, value = rnorm(n)) : number of rows of result is not a multiple of vector length (arg 2)`. But where is the title? – Yurié Dec 23 '14 at 21:06
  • the titles pond, habitat, value open in your spreadsheet if you open the .csv file with excel – Ruthger Righart Dec 23 '14 at 21:09
  • O, I see! But, it seems I was not very explicit. I want a title for the saved table (data frame), not for columns. – Yurié Dec 23 '14 at 21:18