2

I do have a specific question regarding the write.xlsx() function of the xlsx package in R.

I do have a matrix containing outputs of panel data regressions. For convenience I do want to export these outputs to a .xlsx file to include it in a Word document. However, when I do export the matrix via write.xlsx the column names are automatically edited to the syntactically valid style with no symbols other than points, letters and numbers. So, the column vector of the matrix

table_columns <- c("Estimate (All periods)", "", "s.e. (All periods)", "Estimate (Periods 0 & 1)", "", "s.e. (Periods 0 & 1)")

("" are empty column names because these columns contain star indicators for p-values) becomes

Estimate..All.periods.|V2|s.e...All.periods.|Estimate..Periods.0...1.|V5|s.e...Periods.0...1.

Is there a way to suppress this reformatting such that I do not need to edit the .xlsx file after output?

-Edit- to make things easier to understand:

I do have a vector table_rows with c("All periods", " Std. error (All periods)", "Periods 0 & 1", " Std. error (Periods 0 & 1)") and a vector table_columns with c("(1) 1AM - 6AM", "", "(2) 6AM - 10AM", "", "(3) 10AM - 6PM", "", "(4) 6PM - 10PM", "", "(5) 10PM - 1AM", "", "(6) Daily", "")

These are used to set the dimnames of the matrix table with matrix(c(table_1, table_1p, table_2, table_2p, table_3, table_3p, table_4, table_4p, table_5, table_5p, table_6, table_6p), 4, 12, dimnames = list(table4_rows, table4_columns))

where table_odd and table_oddp denote the column vectors containing estimantes and their p-level indicators and table_even the standard errors in parantheses. All vectors are of type chr

I do run write.xlsx(table, paste0(getwd(), "/results.xlsx"), sheetName = "Results") to convert the matrix to .xlsx

Icipher
  • 23
  • 1
  • 5
  • 3
    Please provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). How exactly are you calling `write.xlsx`? How exactly is the data.frame you are trying to write formatted. What are you doing with the `table_columns` vector? – MrFlick Aug 28 '17 at 17:48
  • I don't have `xlsx` installed so I haven't verified, but my guess is this: you call `write.xlsx` on a `matrix` object. Then it is internally converted to a `data.frame` (see [lines 46 and 47 here](https://github.com/cran/xlsx/blob/master/R/write.xlsx.R)), which runs `make.names()` on your column names. I bet if you pass `write.xlsx` an object that is *already* a `data.frame` with the column names as you want them, that will solve your problem. – Gregor Thomas Aug 28 '17 at 18:32

1 Answers1

2

You have to convert the matrix to a data frame before you pass it wo write.xlsx. Otherwise write.xlsx will do the job with defaul arguments. However, you need to set check.names=F and fix.empty.names=F to disable syntax checking and replacing empty column names.

Here's a reproducible example - please post future questions like this:

library(xlsx)
table_rows <- c("All periods", "  Std. error (All periods)", "Periods 0 & 1", "  Std. error (Periods 0 & 1)") 
table_columns <- c("(1) 1AM - 6AM", "", "(2) 6AM - 10AM", "", "(3) 10AM - 6PM", "", "(4) 6PM - 10PM", "", "(5) 10PM - 1AM", "", "(6) Daily", "")
m <- matrix(seq_len(4*12), 4, 12, dimnames = list(table_rows, table_columns))
# convert matrix to list so that one can use as.data.frame's fix.empty.names=F:
df <- as.data.frame(setNames(lapply(1:ncol(m), function(x) m[, x]), colnames(m)), check.names=F, fix.empty.names = F)
write.xlsx(df, tf <- tempfile(fileext = ".xlsx"))
shell.exec(tf)
lukeA
  • 53,097
  • 5
  • 97
  • 100
  • This did work, but i fixed it by using `table <- as.data.frame(table, optional = TRUE)` as a result of Gregors comment. I did change the `""` to `" "` (with a space in between) so that these columns are not renamed to `V2` etc. – Icipher Aug 28 '17 at 19:09
  • @Icipher However, that only affects the `check.names`, not `fix.empty.names`. Renaming the column names seems a bit more hackish to me. ;) – lukeA Aug 28 '17 at 19:13
  • `fix.empty.names` however does not work when I include it in `table <- as.data.frame(table, optional = TRUE, fix.empty.names = FALSE)`. Columns are named `V2` etc in this case. – Icipher Aug 28 '17 at 19:22
  • @Icipher huh, you are right, `fix.empty.names` seems to be available only for `as.data.frame.list`, not for the matrix method. That's odd... – lukeA Aug 28 '17 at 20:06