Is there an efficient approach to use cell_spec() for multiple combinations of variables in R?

Question

I have a data set that contains three variables (var1, var2 and var3) and these same ones but in mean and SD form. I'm wanting to produce a conditionally formatted table using kableextra by referencing each variable's mean and SD counterpart.

Is there a quicker way to do this for ALL variables (var1, var2 and var3) than the example I've included below for df_kbl? My real data set contains 8-10 unique metrics, so repeating the same cell_spec() steps for each one seems inefficient. Is there a faster approach I can take to do this for var1, var2 and var3 at the same time? I'd preferably want to avoid any pivoting due to the nuances of the report I'm constructing. Thanks.

library(tidyverse)
library(kableExtra)

set.seed(10)
df <- data.frame(
  name = paste("Name", LETTERS[1:10]),
  var1 = rnorm(10, 50, 10),
  var2 = rnorm(10, 50, 10),
  var3 = rnorm(10, 50, 10),
  var1_mean = rnorm(10, 50, 10),
  var2_mean = rnorm(10, 50, 10),
  var3_mean = rnorm(10, 50, 10),
  var1_sd = rnorm(10, 5, 0.5),
  var2_sd = rnorm(10, 5, 0.5),
  var3_sd = rnorm(10, 5, 0.5)
  )

df_kbl <- df %>%
  mutate(var1 = cell_spec(
    var1,
    background = case_when(
      var1 > var1_mean + var1_sd ~ "red",
      var1 < var1_mean - var1_sd ~ "green",
      .default = "transparent"
    )
  ))

Answer 1

One option would be to use across with cur_column and get. Additionally I use a small custom convenience function:

library(tidyverse)
library(kableExtra)

set_bg <- function(x, mean, sd) {
  case_when(
    x > mean + sd ~ "red",
    x < mean - sd ~ "green",
    .default = "transparent"
  )
}

df_kbl <- df %>%
  mutate(
    across(
      matches("^var\\d+$"),
      ~ cell_spec(
        .x,
        background = set_bg(
          .x,
          get(paste0(cur_column(), "_mean")),
          get(paste0(cur_column(), "_sd"))
        )
      )
    )
  )

df_kbl |>
  kbl(escape = FALSE)