4

I am creating some contingency tables/crosstabs in R that I want to move to Excel for use in a Word document. I've found discussions on how to write to excel in a few questions- How to export multivariate forecast results from R to excel Export data from R to excel. But contingency tables' formats (either using base R or packages like descr or gmodels) don't seem to translate well to Excel.

I've come up with my own solution, which produces what I want, but I wonder if there's a more efficient way, e.g. a package I'm overlooking. 

library(xlsx)
test.data <- as.data.frame(matrix(sample.int(2,size = 10*2, TRUE), nrow = 10, ncol = 2))
out1 <- table(test.data$V1, test.data$V2)
out.p <- prop.table(out1,2)
out.d <- as.data.frame.matrix(out1)
out.pd <- round(as.data.frame.matrix(out.p),2)
colnames(out.d) <- c("No", "Yes")
colnames(out.pd) <- c("No", "Yes")
out.d1 <- paste(out.d$No, " (",out.pd$No,")",sep="")
out.d2 <- paste(out.d$Yes," (",out.pd$Yes,")", sep="")
out2 <- cbind(out.d1,out.d2)
toadd.r <- c("No","Yes")
out2 <- cbind(toadd.r,out2)
sig <- unlist(summary(table(test.data$V1,test.data$V2)))
sig <- rep(round(sig[6],2),nrow(out2))
out2 <- cbind(out2, sig)
colnames(out2) <- c("Outcome","No","Yes","sig")
write.xlsx(out2, file ="Output.xlsx", sheetName = "Test1")
Community
  • 1
  • 1
PSR
  • 197
  • 1
  • 7
  • Questions asking for "most efficient way to do X" are generally to broad for SO. This question is likely to be closed as such unless you provide a more more specific definition of what you want. – alexwhitworth Jun 16 '16 at 19:00

3 Answers3

3

I am creating some contingency tables/crosstabs in R that I want to move to Excel for use in a Word document.

if your final intention is to export to MS Word you should consider making use of the ReporteRs package.

Example

On example of your data you could use the code below to directly create a table in MS Word.

library(ReporteRs)
mydoc <- docx()
mydoc <- addFlexTable(mydoc, flextable = FlexTable(out2))
writeDoc(mydoc, file = "example.docx")

Results

You will get a nice table in MS Word. exported table

Side point

The FlexTable object gives you a lot of options to adjust formatting and presentation.

sample flex table with options Source: ReporteRs examples.

Konrad
  • 17,740
  • 16
  • 106
  • 167
  • Perhaps mention that this can also be accomplished using `library(pander)` on an Rmarkdown file? Using `pander(out2)` in an R chunk can be used to export directly to a Word document as well. – Twitch_City Jun 16 '16 at 18:39
  • @Twitch_City Integration with MS Office, can be achieved in a number of ways. RMarkdown is very good, I've suggested a solution using ReporteRs as, in my view, reflects OP's desire to 1) export to MS Word, 2) have some control over format, etc. Nothing wrong with using RMarkdown or any other solution. – Konrad Jun 16 '16 at 18:46
  • this is helpful, thanks. I will look into Rmarkdown and reporteRs. – PSR Jun 17 '16 at 14:54
2

I've been unable to find a package that outputs richly formatted contingency tables to Excel from R, and have therefore been working on a package that does that:

The Github repo is here and docs here.

And you can install it using: devtools::install_github("moj-analytical-services/xltabr")

In your use case, you'd need to run the following code:

df  <- data.frame(out2)
wb <- xltabr::auto_df_to_wb(df, left_header_colnames = "Outcome")
openxlsx::openXL(wb)

Which would produce the following output in Excel:

enter image description here

RobinL
  • 11,009
  • 8
  • 48
  • 68
0

How about simply

write.csv(out2, "myContingencyTable.csv")
Matias Thayer
  • 571
  • 3
  • 8