R/write_bg_long.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
#' all in one 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 file 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 computet tbl with the proportions of the variables of interest (as well as saved files)
#'
#' @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")
#'
#'long <- data %>%
#'   write_bg_long(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 long format data summary for all together


write_bg_long <- function(x,...,folder_name=getwd(),
                          transpose=F,
                          name="n_of_variables.xlsx"){
  #for all variable in variable list make a big table with n's

  vlist <- enquos(...)

  ntotal <- nrow(x)

  #name <- paste0("n_of_variables.csv")
  # cat(paste("printing-",name))

  reslist <- list()

  for (vname in vlist){
    reslist[[as.character(vname)[2]]] <- x %>%
      mutate(var=as.character(vname)[2]) %>%
      count(var,!!vname) %>%
      rename(value=!!vname) %>%

      mutate(percent=paste0(round(n/ntotal*100,1),"%")) %>%
      mutate(total=ntotal)

  }

  x <- do.call(rbind, reslist)

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

  if (transpose) {
      x <- x %>%
        mutate(value=paste0(var," = ",value)) %>%
        select(-var) %>%
        rename(namevar = value)

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

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

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


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




  } else {

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



  }


  saveWorkbook(wb,file = paste0(folder_name,"/",name), overwrite = TRUE)
  # readr::write_excel_csv(x,file = paste0(folder_name,"/",name))

  return(x)
}