tabular exports

[aghaynes]

Is your feature request related to a problem? Please describe.
New feature. Wordy reports are possibly less useful here. Tables that give a good overview quickly are often useful.

Describe the solution you'd like
Easy export tables with coloured outcomes according to breakpoint

Describe alternatives you've considered
options might include

          kpi1    kpi2     kpi3
overall   v0      v0       v0
site1     v1      v2       v3
site2     v4      v5       v6

For each site (level of a by variable) and overall, different pieces of info for each KPI, where the stat column would be coloured by breakpoint

          stat          n             other_variables
kpi1       v1           v2            v3
kpi2       v4           v5            v6
kpi3       v7           v8            v9

Output types might be e.g. excel (particularly good for lots of sites, lots of KPIs, via openxlsx?), gt and/or DT (good for HTML, websites?), flextable (good for word and smaller tables...)

[aghaynes]

reactable for html?

[aghaynes]

I had a play... my feeling is that flextable is easiest (for non-excel based reports).

DT and gt can both do colours, but they seem to construct a palette and interpolate the colours along it (which might be handy?), unless you pass it a factor, but then we loose the precise info.

# create a kpilist
kpi1 <- mtcars %>%
   mutate(cylgt4 = cyl > 4) %>%
   kpi(var = "mpg",
       breakpoints = c(0, 22, 50),
       by = c("am", "cyl"),
       txt = "MPG",
       kpi_fn = kpi_fn_median)

kpi2 <- mtcars %>% 
  kpi("hp", by = c("am", "cyl"),
      breakpoints = c(0, 100, 350))

kpis <- c(kpi1, kpi2)
acc <- kpi_accumulate(kpis, split = FALSE)
am <- acc$am

# gt
library(gt)

am %>% 
  gt() %>% 
  data_color(columns = "stat", colors = as.character(acc$am$cols))

kpi1$cyl$calc %>% 
  gt() %>% 
  data_color(columns = "stat", colors = as.character(kpi1$cyl$calc$cols))

data.frame(x = runif(5),
           y = letters[1:5]) %>% 
  gt() %>% 
  data_color("x", colors = c("red", "red", "green", "green", "green"))

# flextable
library(flextable)
## long format
am %>% 
  flextable() %>% 
  bg(j = c("stat", "risk"), bg = as.character(am$cols))
## wide format
wide_stat <- am %>% 
  pivot_wider(id_cols = am, names_from = txt, values_from = stat)
wide_cols <- am %>% 
  mutate(cols = as.character(cols)) %>% 
  pivot_wider(id_cols = am, names_from = txt, values_from = cols)

ft <- wide_stat %>% 
  flextable()

for(i in seq_along(wide_stat)){
  print(i)
  if(i == 1) next
  ft <- ft %>% 
    bg(j = i, bg = wide_cols[[i]])
}
ft

# DT
library(DT)

am %>% 
  datatable() %>% 
  # formatStyle("stat", backgroundColor = "orange")
  formatStyle("stat", backgroundColor = styleInterval())

kpi1$cyl$calc %>% 
  datatable() %>% 
  formatStyle("stat", 
              backgroundColor = styleInterval(kpi1$settings$breakpoints[2], 
                                              kpi1$settings$riskcolors))

and here's some code for excel, although I've not tested it at all

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, sheetName = "Read Me")
addWorksheet(wb, sheetName = "Kpi Long")
addWorksheet(wb, sheetName = "Kpi Wide", zoom = 85)
addWorksheet(wb, sheetName = "Thresholds", visible = FALSE)
addWorksheet(wb, sheetName = "Listing")

# define colour fill for breached KPIs
Style_1 <- createStyle(bgFill = "#4dac26")
Style_2 <- createStyle(bgFill = "#b8e186")
Style_3 <- createStyle(bgFill = "#f1b6da")
Style_4 <- createStyle(bgFill = "#d01c8b")

# sheet 2: Kpi Long
freezePane(wb, sheet = 2, firstRow = TRUE, firstCol = FALSE) ## freeze first row
writeDataTable(wb, sheet = 2, x = Kpis,
               colNames = TRUE, rowNames = FALSE,
               headerStyle = createStyle(textDecoration = "bold"))
conditionalFormatting(wb, "Kpi Long", cols=7:12, rows = 1:(nrow(Kpis)+1), rule="$K1==1", style = Style_1)
conditionalFormatting(wb, "Kpi Long", cols=7:12, rows = 1:(nrow(Kpis)+1), rule="$K1==2", style = Style_2)
conditionalFormatting(wb, "Kpi Long", cols=7:12, rows = 1:(nrow(Kpis)+1), rule="$K1==3", style = Style_3)
conditionalFormatting(wb, "Kpi Long", cols=7:12, rows = 1:(nrow(Kpis)+1), rule="$K1==4", style = Style_4)
setColWidths(wb, 2, cols=1:12, 
             widths = c(7.86, 26.29, 7.71, 57.14, 18.86, 25.86, 9.57, 10.86, 55.57, 11.14, 10.43, 12.43))

could perhaps use a merged version of wide_stat and wide_cols in the conditionalFormatting and hide the stat part based on the cols part then hide the cols part?