Flextable background can't find column or recognize {{ }} or . within my function

Question

I'm building a function that creates a table using flextable. I want to specify a color gradient, but some values are NA. I want those NA values--changed to be " - "--to be transparent. The dataset has someone's rating for a specific year (rating_2023, in the example), but I want the code to be flexible to different years rather than hardcoding a specific year.

For some reason, flextable cannot understand ., {{}}, or enquo() when I want to detect if a value is NA.

Specifically, here's my sample data:

library(dplyr)
library(flextable)
library(scales)

sample_data <- tibble(emp_id = c(1, 1, 1, 1,
                                 2, 2, 2, 2, 
                                 3, 3, 3, 3,
                                 4, 4, 4, 4),
                      survey_period = c("q4", "q3", "q2", "q1",
                                        "q4", "q3", "q2", "q1",
                                        "q4", "q3", "q2", "q1",
                                        "q4", "q3", "q2", "q1"),
                      rating_2023 = c(100, 90, 40, NA,
                                 -10, -20, 0, 10,
                                 80, -10, NA, NA,
                                 90, 50, -20, NA))

And my function works when I hard code in rating_2023 in i = ~is.na(rating_2023),

make_table <- function(data, time_period){
  
  gen_rating <- paste0("rating_", time_period)
  
  gradient <- col_numeric(
    palette = c("red", "transparent", "green"),
    domain = c(-100, 0, 100)
  )
  
  data %>%
    flextable() %>%
    colformat_num(na_str = " - ") %>%
    bg(
      bg = gradient,
      j = 3,
      part = "body"
    ) %>%
    bg(
      bg = "transparent",
      i = ~is.na(rating_2023),
      j = gen_rating
    )
  
}

make_table(sample_data, time_period = "2023")

But it yields Error in eval(as.call(f[[2]]), envir = data) : object 'gen_rating' not found when I try to pipe in an object. I get a similar answer when I try to use . too:

make_table <- function(data, time_period){
  
  gen_rating <- paste0("rating_", time_period)
  
  gradient <- col_numeric(
    palette = c("red", "transparent", "green"),
    domain = c(-100, 0, 100)
  )
  
  data %>%
    flextable() %>%
    colformat_num(na_str = " - ") %>%
    bg(
      bg = gradient,
      j = 3,
      part = "body"
    ) %>%
    bg(
      bg = "transparent",
      i = ~is.na({{gen_rating}}),
      j = gen_rating
    )
  
}

make_table(sample_data, time_period = "2023")

Why isn't this working? And is there a solution or am I forced to hardcode in the year?

Answer 1

use as.formula() or reformulate() - there is also a solution with 'rlang' but I don't remember how to code it:

library(dplyr)
library(flextable)
library(scales)

sample_data <- tibble(
  emp_id = c(
    1, 1, 1, 1,
    2, 2, 2, 2,
    3, 3, 3, 3,
    4, 4, 4, 4
  ),
  survey_period = c(
    "q4", "q3", "q2", "q1",
    "q4", "q3", "q2", "q1",
    "q4", "q3", "q2", "q1",
    "q4", "q3", "q2", "q1"
  ),
  rating_2023 = c(
    100, 90, 40, NA,
    -10, -20, 0, 10,
    80, -10, NA, NA,
    90, 50, -20, NA
  )
)

make_table <- function(data, time_period) {
  gen_rating <- paste0("rating_", time_period)

  gradient <- col_numeric(
    palette = c("red", "transparent", "green"),
    domain = c(-100, 0, 100)
  )

  data %>%
    flextable() %>%
    colformat_num(na_str = " - ") %>%
    bg(
      bg = gradient,
      j = 3,
      part = "body"
    ) %>%
    bg(
      bg = "transparent",
      i = reformulate(sprintf("is.na(`%s`)", gen_rating)),
      j = gen_rating
    )
}

make_table(sample_data, time_period = "2023")