CODEWITHSUNDEEP
rexcelcontingency
Corel
Corel

Reputation: 633

CrossTable output to excel in R

When producing contingency tables I'm using the function CrossTable in R because it is the most informative and flexible that I have managed to find, and it gives you a lot of stuff right away without the need to calculate it seperately. However, the ouput it generates is a text output that is aimed mainly for the console in R. This is a big problem for me because I'm relying on outputing these kind of tables to excel at the next step of the analysis.

Is there any way to output the CrossTable output to an Excel spreadsheet so that each number will be in its own cell? (i.e., not as text that goes entirely to a single cell) ("text to columns" does not help)

Upvotes: 1

Views: 4002

Answers (2)

Martijn De Gussem
Martijn De Gussem

Reputation: 11

I wrote a function that turns the crosstable into an excel file. Maybe it'll be usefull to someone else too:

#writes output from gmodels::CrossTable to an excel file
#package "xlsx" should be installed
crossExcel <- function(Cross.Table, file, chi=F) {  
  Cross.Table <- x
  
  t <- reshape(as.data.frame(Cross.Table[["t"]]), idvar="x", timevar="y", direction="wide")
  row <- reshape(as.data.frame(Cross.Table[["prop.row"]]), idvar="x", timevar="y", direction="wide")
  col <- reshape(as.data.frame(Cross.Table[["prop.col"]]), idvar="x", timevar="y", direction="wide")
  tbl <- reshape(as.data.frame(Cross.Table[["prop.tbl"]]), idvar="x", timevar="y", direction="wide")
  
  xlsx::write.xlsx(t, file=file, sheetName="t", row.names=F)
  xlsx::write.xlsx(row, file=file, sheetName="prop.row", append=T, row.names=F)
  xlsx::write.xlsx(col, file=file, sheetName="prop.col", append=T, row.names=F)
  xlsx::write.xlsx(tbl, file=file, sheetName="prop.tbl", append=T, row.names=F)
  
  if (chi==T) {
    exp <- as.data.frame(x[["chisq"]][["expected"]])
    xlsx::write.xlsx(exp, file=file, sheetName="exp", append=T, row.names=T)
    
    t <- t[ ,2:3]
    chip <- ((exp-t)*(exp-t))/exp
    xlsx::write.xlsx(chip, file=file, sheetName="prop.chisq", append=T, row.names=T)
  }
  
  print(paste0("CrossTable exported to ", getwd() ,file))
}

Example of how it's used:

data(infert, package = "datasets") #obtaining exapmle data
x <- gmodels::CrossTable(infert$education, infert$induced) 
crossExcel(x, "output.xlsx")

Upvotes: 1

Petr Matousu
Petr Matousu

Reputation: 3140

# obtaining data
library('gmodels')
data(infert, package = "datasets")
# asign the result to object
d <- CrossTable(infert$education, infert$induced, expected = TRUE)
# now you can use d object to build something you could save using
# `write.table` to csv or directly to xlsx by other methods

# use str(d) to study structure of returned object
str(d)

Upvotes: 0

Related Questions