Why is xltabr not running?

Question

xltabr seems to be the only way to create excel contingency tables in R.

I am trying to use this write out a contingency tab to excel, but the auto_crosstab_to_wb command has been running for over an hour with no result. (I see the red icon on the console indicating that it is still running). I realize now that it is something to do with my studio/packages, so it's hard to reproduce the problem.

See here for the code required to reproduce the problem with xltabr:

    devtools::install_github("moj-analytical-services/xltabr")
    library(reshape2)
    library(Rcpp)
    iris
    ct <- reshape2::dcast(iris, Sepal.Length ~ Species, fun.aggregate = length, value.var = "Sepal.Length")
    wb <- xltabr::auto_crosstab_to_wb(ct) # this is the line taking hours to run

Any idea why this could be happening (to just me)? I updated all my packages but still no difference. What else can I do to debug this? I don't see any warnings so it's really baffling.
Is there any other function that allows one to write multiple contingency tables to either excel or csv? This is helpful: crosstable() export to csv but I have to run append hundreds of tables & I know this is a real pain to do in R.

Have you ever tried the package {crosstable}? It seems it would do what you want and it comes with exporting to Excel. You can read more on the doc: https://github.com/DanChaltiel/crosstable. — Dan Chaltiel, Oct 23 '21 at 16:09
@Dan Chaltiel Crosstable can't be written to excel can it? That is my main issue... I need my output in excel — NewBee, Oct 26 '21 at 15:44
I ran your code, it took only a few seconds, I have no idea why it would take so long on your computer. However, the result is not at all what I expected, what is your expected output for your `iris` example exactly? Of note, `crosstable::crosstable()` can be exported to excel easily, but I have no idea about `xltabr`. — Dan Chaltiel, Oct 26 '21 at 18:38

score 0 · Answer 1 · answered Oct 26 '21 at 18:57

Here is a solution using the package {crosstable}.

Disclaimer: I'm the author of this package.

Your code is calculating a contingency table on all values of Sepal.Length depending on values of Species. By default, crosstable will consider Sepal.Length numerical so you have to use unique_numeric=Inf to override the behavior. Also, crosstable will calculate proportion by default, you can prevent this using margin=-1.

You can search for even more options in the documentation.

Here is the result. I use as_flextable() to output an HTML table, which is more readable on StackOverflow but you don't have to.

library(crosstable)
crosstable(iris, Sepal.Length, by=Species, unique_numeric=Inf, margin=-1) %>% as_flextable()

wb = crosstable(iris, Sepal.Length, by=Species, unique_numeric=Inf, margin=-1) %>% as_workbook()
openxlsx::saveWorkbook(wb, "crosstable.xlsx")

However, your example makes little sense to me, do you really want to treat a numeric variable as a categorical one? I think this has more sense using default values in crosstable, hence calculating summaries (which you could choose if you want):

crosstable(iris, Sepal.Length, by=Species) %>% as_flextable()

^{Created on 2021-10-26 by the reprex package (v2.0.1)}

score -1 · Accepted Answer · answered Oct 18 '21 at 19:22

-1

I un-installed and re-installed R studio, and updated every package. For some reason, this seemed to solve the problem.

answered Oct 18 '21 at 19:22

NewBee

990
1
7
26

Why is xltabr not running?

2 Answers2