CODEWITHSUNDEEP
rknitrkablekableextra
Robert Kadlec
Robert Kadlec

Reputation: 33

Using kable to color a row of elements based off value

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))

Upvotes: 2

Views: 1644

Answers (1)

CL.
CL.

Reputation: 14997

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:

Image of rendered table

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.

Upvotes: 2

Related Questions