KableExtra conditionally formatting specific rows on a column

I have just learnt KableExtra and know to how to use conditionally formating the entire column using mutate() as explained in the doc using mutate such as:

mutate(
   mpg = cell_spec(mpg, background = ifelse(mpg > 20, "red", "blue"))
)

But what I don't know is, how to change background colours of only certain rows in each column whilst all rows are being displayed.

For example, my data:

df <- data.frame( region1 = c("A", sample(1:5,3)),
                  region2 = c("B", sample(1:5,3)),
                  region3 = c("C", sample(1:5,3)),
                  region4 = c("A", sample(1:5,3)) )

Now I want to format only second and third row. I dont want to change the color background of first and last row. These second and third row should be 'red' when above 1, or yellow when equal to 1 or green when below 1.

Some one could help me this?

Solution

Here's an example that ignores the first and last rows and colours according to value, like you say, but ignores letters.

First, I load the libraries.

# Load libraries
library(knitr)
library(kableExtra)

Next, I create a dummy data frame.

# Create data frame   
df <- data.frame( region1 = c(sample(c(-5:5, letters[1:5]), 10, replace = TRUE)),
                  region2 = c(sample(c(-5:5, letters[1:5]), 10, replace = TRUE)),
                  region3 = c(sample(c(-5:5, letters[1:5]), 10, replace = TRUE)),
                  region4 = c(sample(c(-5:5, letters[1:5]), 10, replace = TRUE)), stringsAsFactors = FALSE )

Here, I define the function for formatting cells. I ignore the first and last rows and check if the character is a letter or number, then colour accordingly.

foo <- function(x, n, nmax){
  cell_spec(x, background = ifelse(is.na(as.numeric(x)), "white",
                                   ifelse(n == nmax | n == 1, "white",
                                          ifelse(x > 1, "red",
                                                 ifelse(x < 1, "green", "yellow")))))
}

Finally, I apply the function.

df %>% 
  mutate_all(funs(foo(., n = row_number(), nmax = n()))) %>% 
  kable(escape = FALSE) %>% 
  kable_styling()