Insert tbl_summary table on excel

General
1

Hi, is there any way to achieve this in R?

What I need is the tbl_summary table on excel, with each number in a cell, so I export via write.csv() and then in Excel I separate the text in 2 columns using the '(' as separator and then delete the ')'.

It is quite complicated and time consuming so I wanted to know if there is an easier way to do it. The image that I upload is what I would like as target, and this is the code that I use in R

df<-
  tibble(a= sample(c('red', 'blue', 'pink'),
                   size= 10000,
                   replace= T),
         b= sample(c('car', 'bike', 'boat'),
                   size= 10000,
                   replace= T))



df %>% 
  mutate_all(as.factor) %>%
  tbl_summary(statistic = list(all_categorical() ~ "{n} ({p})"),
              digits = list(all_categorical() ~ c(0,1))) %>% 
  gtsummary::as_tibble() %>% 
  write.csv(., na = "")
Characteristic N = 10000
a
blue 3421 34.2
pink 3363 33.6
red 3216 32.2
b
bike 3362 33.6
boat 3304 33.0
car 3334 33.3
2

Hi @juandmaz,

I think this is pretty close to what you're after.

I used purrr::map_dfr() to iterate over the variables in df and summarise their levels using a custom function summarise_var().

# define the data
df <-
  tibble::tibble(a= sample(c('red', 'blue', 'pink'),
                           size= 10000,
                           replace= T),
                 b= sample(c('car', 'bike', 'boat'),
                           size= 10000,
                           replace= T))

# function to summarise values for a variable
summarise_var <- function(df, var) {
  
  summary <-
    dplyr::bind_rows(
      # set the header row with the name of the var
      tibble::tibble(
        Characteristic = var,
        n = NA,
        prop = NA
      ),
      
      # summarise the levels of var
      df |> 
        dplyr::rename(Characteristic = dplyr::any_of(var)) |> 
        dplyr::count(Characteristic) |> 
        dplyr::mutate(prop = proportions(n)*100)
    )
  
  return(summary)
}

# iterate over all variables in df
df_summary <-
  purrr::map_dfr(
    .x = names(df),
    .f = \(.x) summarise_var(df, var = .x)
  )

# see the results
df_summary
#> # A tibble: 8 × 3
#>   Characteristic     n  prop
#>   <chr>          <int> <dbl>
#> 1 a                 NA  NA  
#> 2 blue            3281  32.8
#> 3 pink            3389  33.9
#> 4 red             3330  33.3
#> 5 b                 NA  NA  
#> 6 bike            3361  33.6
#> 7 boat            3318  33.2
#> 8 car             3321  33.2

Created on 2025-02-13 with reprex v2.1.1

df_summary can then be exported using utils::write.csv(df_summary)or copied to your clipboard clipr::write_clip(df_summary) for pasting to an Excel workbook.