How highlight specific cells in knitr and kable packages in R

Question

I have the following dataframe:

library(knitr)
library(kableExtra)
library(dplyr)
df<- structure(list(date = c("2021-04-12", "2021-04-13", "2021-04-14","2021-04-15", "2021-04-16", "Avg", "Change", "Change (%)"), 
               Audi = c(3.3, 3.7, 6.6, 6.9, 6.8, 6.3, 0.5, 5.5), 
               Bmw = c(1.1, 1.7, 4.1, 4.2, 4, 3.4, -2.5, -4.1), Lexus = c(9.8, 0.3,2.3, 2.2, 2, 1.4, 1.9, 3.2)), row.names = c(NA, -8L), class = "data.frame")

I wanted to highlight rows that satisfy the condition df$date == c("Change", "Change (%)") in specific columns using this code. Additionally I want to highlight with gray colour the row which(df$date == "Avg") for all columns except column date.

df %>% 
  kable()  %>%  
  kable_classic(full_width = F, html_font = "Calibri")  %>%
  row_spec(which(df$date == "Avg"), bold = T, background = "#F2F2F2")  %>%
  row_spec(which(df$date == c("Change", "Change (%)")), bold = T, background = ifelse(df$Audi >= 0, "#C6EFCE", "#FFC7CE"))

I found a little bit similar question here but did not figure out how to implement it in my code

1. How can I highlight the rows which(df$date == c("Change", "Change (%)")) for all columns except date (condition: positive values are green and negative are red)

2. How can I highlight with gray color the rows which(df$date == c("Avg")) for all columns except date

3. I also found here that I can change the colour of all values in a table adding mutate_all(~cell_spec(.x, color = ifelse(.x < 0, "red"," green"))) , however got an error

Error in UseMethod("tbl_vars") : no applicable method for 'tbl_vars' applied to an object of class "c('kableExtra', 'knitr_kable')"

How can I solve these issues?

Answer 1

I have it partially working, maybe it helps you a bit. You can use the across funtion to select the columns you want. I tried using where(is.numeric) instead of across(contains(c("Audi", "Bmw", "Lexus")) (would be much simpler), but it only works ones!

my_df %>% 
  # across with where(is.numeric) not working
  mutate(across(contains(c("Audi", "Bmw", "Lexus")), ~ifelse(date == "Avg",
                                                             cell_spec(x = .x, 
                                                                       background = "gray",
                                                                       bold = TRUE,
                                                                       escape = FALSE),
                                                             .x)),
         across(contains(c("Audi", "Bmw", "Lexus")), ~ifelse(date %in% c("Change", "Change (%)"),
                                                             cell_spec(x = .x, 
                                                                       background = ifelse(.x < 0, 
                                                                                           "red", 
                                                                                           "green"),
                                                                       bold = TRUE,
                                                                       escape = FALSE),
                                                             .x))) %>% 
  
  kable() %>%
  kable_classic(full_width = FALSE,
                html_font = "Calibri")

The strange thing is it doesn't give a correct html table back. It shows me:

<span style=" font-weight: bold; border-radius: 4px; padding-right: 4px; padding-left: 4px; background-color: gray !important;" >6.3</span>

for all cells. It does show the correct background color, but there is something with the html code. I also tried adding escape = FALSE to cell_spec (found this in old issue here), but this didn't make any difference. I don't know why. Maybe this is a bug in knitr or kableExtra, but I couldn't get it right.

Hope the approach helps you a bit.