Search code examples
rr-markdownkablegtsummary

Create editable tables in rmarkdown

I am using a combination or rmarkdown and gtsummary to create descriptive tables. The tables looks great when knitted to html.

See here: enter image description here

But, they lose their formatting completely when knitted to word.

Since I need to create graphs from these tables, I am interested in knitting these tables to either word or excel. Based on my research it does not seem possible to knit these tables to excel.

So I tried wrapping the print statement with Kable and then knitting to word. But kable only seems to work with data frames, and the object created by gtsummary::tbl_cross is it's own class.

Error in as.data.frame.default(x) : 
  cannot coerce class 'c("tbl_cross", "tbl_summary", "gtsummary")' to a data.frame
Calls: <Anonymous> ... print -> kable -> as.data.frame -> as.data.frame.default
Execution halted

My question:

  1. Is there a way to use kable with gtsummary?
  2. If not, what is the best way of creating editable tables (tables that can be pasted into excel to make graphs) for a noob. Note that I only need the percentages included in the final table, not the frequency, but gtsummary does not have the option to only keep percentages.
  3. Is there a different approach altogether than I am missing?

Thanks so much for suggestions, see here for my code.

My code:

---
title: "Untitled"
author: "me"
date: "9/29/2021"
output:
  html_document: default
  word_document: default
---


```{r setup, include=FALSE}
# Remove items from memory and environment
rm(list = ls())
# load required libraries
library(magrittr)
library(dplyr)
  library(gtsummary)

# File locations

# read in data 
data_in <- readr::read_table2('Q17_1   Q17_2   Q17_3   Q17_4   survey
No  Yes No  Yes m1
No  Yes No  No  m2
Yes Yes Yes Yes m1
No  No  Yes No  m2
No  No  Yes Yes m1
Yes No  Yes No  m2
No  No  Yes Yes m1
No  No  Yes No  m2
Yes No  Yes Yes m1
')
vct <- data_in %>% select(matches("Q")) %>% names(.)
```



```{r , include=FALSE}
# set up user function create tables
run_xtab <- function(v1) {
  out <- data_in%>%
    tbl_cross(
      row = !!rlang::sym(v1),
      col = survey,
      percent = 'column',
      label = list(survey~v1),
      missing_text = "no"
    ) %>%
    add_p(source_note = TRUE) %>%
    bold_labels()
  
  return(out)
}

```



```{r loop_print, results = 'asis', warning=FALSE, message=FALSE,error=FALSE,echo=FALSE}
 # loop through the questions to create the xtables 
library(knitr)
for (i in vct) {
  tbl <- run_xtab(i)   # build gtsummary table
  # print(kable(tbl))   # Kable does not like gtsummary object
  # stargazer(tbl)      # neither does star gazers
  # tbl <-as.data.frame(tbl) # try converting table to df 
  print(tbl)  # simple print works with html, but knitting to word loses formatting 
}
```
Solution

  • I think your best resource will be the gtsummary vignette on R markdown. http://www.danieldsjoberg.com/gtsummary/articles/rmarkdown.html In the vignette, you'll find this table outlining how to export gtsummary tables to various R markdown output types. enter image description here

    In your case, I would recommend you convert the gtsummary tables to a flextable using the as_flex_table() function.

    For example, you could update your function to include this conversion. (More information/options outlined in the vignette.)

    ```{r , include=FALSE}
# set up user function create tables
run_xtab <- function(v1) {
  out <- data_in%>%
    tbl_cross(
      row = !!rlang::sym(v1),
      col = survey,
      percent = 'column',
      label = list(survey~v1),
      missing_text = "no"
    ) %>%
    add_p(source_note = TRUE) %>%
    bold_labels() %>%
    as_flex_table()
  
  return(out)
}
```