Using kable to color a row of elements based off value

Question

knitr::kable(pen_financials_matrix,
             align = c("l", "l", "c", "c", "c", "c"),
             caption = "Financial Report (Sorted by Pen Number)")%>%
  kable_styling(bootstrap_options = "striped")

Using this code (see below for the definition of pen_financials_matrix) I've created this table: pic of table

pen_financial_matrix is a data frame that holds columns of values for each pen given. In the picture you can see that there are 9 different pens listed, but this number could change based off the data given.

I am trying to change the text color for certain rows to red or green. For example "Total IOFC" should be green when greater than 0 and red when less.

I understand how to color columns using column_spec() but I don't know how reference values in a row.

dput(pen_financials_matrix)
structure(list(Variables = c("Pen Milk Income", "Pen Feed Cost", 
"Feed Efficiency", "Nitrogen Efficiency", "Feed Saved or Wasted", 
"Feed Cost / Cwt of Milk", "Total IOFC"), Units = c("$/day", 
"$/day", "NE Milk / NE Feed,%", "Milk N / Feed N, %", "$/pen/day", 
"(Pen Feed$/Pen milk) * 100", "Milk Income - Feed Cost"), `1` = c("96.48", 
"72", "1.27", "22.88", "0.798", "78.64", "24.48"), `2` = c("3440.41", 
"1672", "1.95", "31.24", "0.761", "51.21", "1768.41"), `3` = c("1199.66", 
"664", "1.71", "26.92", "0.093", "58.33", "535.66"), `4` = c("1638.10", 
"888", "1.75", "29.94", "0.794", "57.12", "750.10"), `5` = c("1155.19", 
"808", "1.36", "24.59", "0.380", "73.71", "347.19"), `7` = c("407.65", 
"312", "1.24", "23.48", "0.505", "80.65", "95.65"), `78` = c("171.45", 
"120", "1.36", "25.10", "0.426", "73.75", "51.45"), `88` = c("763.56", 
"456", "1.59", "27.76", "0.223", "62.93", "307.56"), `98` = c("763.56", 
"456", "1.59", "27.76", "0.223", "62.93", "307.56")), class = "data.frame", row.names = c(NA, 
-7L))

Could you please paste a copy of your data into the question: use `dput(pen_financials_matrix)`. — Peter, Jun 19 '21 at 18:50
Is this helpful to you? https://stackoverflow.com/a/50136295/2706569 — CL., Jun 20 '21 at 10:33
@CL the problem with that approach is it doesn't lend itself to a changing table size. It would work if the number of pens was constant, but I believe it won't when it changes. — Robert Kadlec, Jun 20 '21 at 16:47
@RobertKadlec, does this answer your question: https://stackoverflow.com/a/67455362/13249862? — bttomio, Jun 21 '21 at 06:53

score 1 · Accepted Answer · answered Jun 22 '21 at 14:45

The central function to use here is kableExtra::cell_spec, which "is designed to be used before the data.frame gets into the kable function". As I could not figure out another way to use this in a "tidy" pipeline, I suggest using the following wrapper function:

conditionalColor <- function(x, row, columnOffset, threshold, colorLarger, colorElse) {
  x[row, (columnOffset+1):ncol(x)] <- cell_spec(
    x[row, (columnOffset+1):ncol(x)], 
    color = ifelse(as.numeric(x[row, (columnOffset+1):ncol(x)]) < threshold, 
                   colorLarger, 
                   colorElse))
  return(x)
}

This colors the text in all cells of one row of x, conditional on the cell values being larger (colorLarger) than threshold or not (colorElse), ignoring columns up to columnOffset.

Note that cell_spec adds markup to the data. Therefore, set escape = FALSE in kable.

Sample usage with the object pen_financials_matrix as defined in the question: Highlight cells in the row "Total IOFC" (green if > 100, red otherwise):

conditionalColor(pen_financials_matrix, 
                 row = which(pen_financials_matrix[ , 1] == "Total IOFC"), 
                 columnOffset = 2, 
                 threshold = 100, colorLarger = "green", colorElse = "red") %>% 
  knitr::kable(escape = FALSE) %>%
  kable_styling(bootstrap_options = "striped")

Output:

In general, I think this would be easier if the data was transposed such that the variables are in columns instead of rows. Then more straightforeward solutions could be feasible.

Using kable to color a row of elements based off value

1 Answers1

Linked