R/write_bg_split.R

In sarid-ins/saridr: An R package for common tasks related to market research analysis via R

#' Make proportions of a list of factorial variables, and write it in excel
#' each variable in its own table
#'
#' @param x a tbl() that contains the variables of interest, keyed by ID variable
#' @param ... list of variables names that would appear in output
#' @param folder_name where would the output files will be saved (project's home by default).
#' @param name name of xlsx output file.
#' @param transpose should it be horizontal (FALSE by default).
#'
#' @return nothing
#'
#' @examples
#' #example run
#' data=tribble(~ID,~car_size,~gender,
#'              "1","large","woman",
#'              "2","large","woman",
#'              "3","small","woman",
#'              "4","small","man",
#'              "5","small","man",
#'              "6","large","man",
#'              "7",NA,"man")
#'
#'data %>%
#'    write_bg_split(car_size,gender,folder_name = "R",transpose = F)

#'
#' @importFrom magrittr %>%
#' @importFrom dplyr enquos
#' @importFrom dplyr mutate
#' @importFrom dplyr rename
#' @importFrom dplyr count
#' @importFrom dplyr select
#' @importFrom dplyr everything
#' @importFrom openxlsx createWorkbook
#' @importFrom openxlsx addWorksheet
#' @importFrom openxlsx writeData
#' @importFrom openxlsx createStyle
#' @importFrom openxlsx saveWorkbook
#'
#' @export
#'
#get bg variables and make a wide format data summary foe each

write_bg_split <- function(x,...,folder_name=getwd(),transpose=F,
                           name="n_of_variables_split.xlsx"){
  #for every variable in variable list, export a table with n

  vlist <- enquos(...)

  ntotal <- nrow(x)

  wb <- createWorkbook()
  hs1 <- createStyle(
    fgFill = "#DCE6F1", halign = "CENTER", textDecoration = "bold",
    border = "bottom"
  )

  for (vname in vlist){

      sheet_name=paste0(as.character(vname)[2])
      addWorksheet(wb, sheet_name)

      # cat(paste("\nprinting-",name,"\n"))

      res <- x %>%
        mutate(var=as.character(vname)[2]) %>%
        count(var,!!vname) %>%
        rename(value=!!vname) %>%
        # mutate(value=paste0(var," : ",value)) %>%
        mutate(percent=paste0(round((n/ntotal)*100),"%")) %>%
        mutate(total=ntotal)


      if (transpose) {
        res <- res %>%
          select(-var) %>%
          rename(namevar = value)

        var_names <- res$namevar
        row_names <- c("n","total","percent")

        res <- t(res[,-1])
        colnames(res) <- var_names

        res <- as_tibble(res) %>%
          mutate(property=row_names) %>%
          select(property,everything())
      }

      writeData(wb, sheet_name, res,
                colNames = F,
                rowNames = F,
                startCol = "A", startRow = 2,
                borders = "surrounding", borderColour = "black"
      )
      writeData(wb, sheet_name, res,
                colNames = T,
                rowNames = F,
                startCol = "A", startRow = 1,
                borders = "surrounding", borderColour = "black",headerStyle = hs1
      )






  }
  saveWorkbook(wb,file = paste0(folder_name,"/",name), overwrite = TRUE)
}