Coloring rows with kableExtra based on cell values

Question

I use R markdown in combination with LaTeX to create PDF reports. To generate tables I use the kableExtra package.

I'd like to change the styling of rows based on the values in a particular column. I've been trying cell_spec, but as far as I can see it only changes the styling of the value in the column and not the entire row.

For example, in the table below I'd like to highlight all cars with more than six cylinders. So the result should look like:

library(knitr)
library(kableExtra)
library(tidyverse)

mtcars |> 
  slice(1:5) |> 
  select(mpg:wt) |>
  kable(booktabs = T) %>%
  kable_styling() %>%
  row_spec(5, bold = T, color = "white", background = "red")

But here I specified a row number, which I don't want to do. It should be decided automatically based on the number of cylinders. Using cell_spec, I'm able to get the following:

mtcars |> 
  slice(1:5) |> 
  select(mpg:wt) |>
  rownames_to_column('cars') %>% # used to store row names (mutate deletes them)
  mutate(
  cyl = cell_spec(cyl, color = ifelse(cyl > 6, "white", "black"),
                  background = ifelse(cyl > 6, "red", "white"),
                  bold = ifelse(cyl > 6, T, F))) %>%
  column_to_rownames('cars') %>% # used to put row names back in place
  kable(escape = F, booktabs = T) %>%
  kable_styling()

But this changes only the value in the cylinder column, and not the rest of the row.

Is there a solution to my problem (preferrably without specifying the condition for every styling option)?

EDIT: This is a different question than this one, where someone wants to format a column based on values in that column, but wishes to exclude certain rows/entries. I don't want to exclude any rows, but I want the formatting to apply to the entire row (still conditioning on values in a particular column).

Abscess

side note: in the resulting latex, the code is: '\cellcolor{red}{\textcolor{white}{\textbf{8}}}' for the highlighted cell. If you want to compile this code elsewhere (e.g., overleaf) you need to include the latex package "colortbl" which provides \cellcolor support – Petronia

Answer 1

I think the easiest way is to pass a list of row to be coloured based on your criterias.

library(kableExtra)

df<- mtcars
color.me <- which(df$cyl >6)

df %>% 
  kable(booktabs = T) %>%
  kable_styling() %>%
  row_spec(color.me, bold = T, color = "white", background = "red")

or in the same pipe as:

df %>% 
  kable(booktabs = T) %>%
  kable_styling() %>%
  row_spec(which(df$cyl >6), bold = T, color = "white", background = "red")

Lueluebke

Answer 2

I like Jraku's answer better, though this answer resembles your first attempt. Making a function and applying it to the desired columns with mutate(across()) works as well.

library(knitr)
library(kableExtra)
library(tidyverse)

mtcars |> 
  slice(1:5) |> 
  select(mpg:wt) |>
  rownames_to_column('cars') %>% # used to store row names (mutate deletes them)
  mutate(across(where(is.numeric), 
                function(x){cell_spec(x, 
                                      color = ifelse(x > 6, "white", "black"),
                                      background = ifelse(x > 6, "red", "white"),
                                      bold = ifelse(x > 6, T, F))}
                )) %>% 
  column_to_rownames('cars') %>% # used to put row names back in place
  kable(escape = F, booktabs = T) %>%
  kable_styling()```

Bronez