Using styleInterval to color only specific rows based on values across selected columns

Question

I'm trying to color each row in a datatable based on the values present. For example, if using the iris dataset:

Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
5.1	3.5	1.4	0.2	setosa
4.9	3	1.4	0.2	setosa

I'd like to have the values in row 1 to appear in red if the cell value is below 2 (in any column), and in black otherwise. The last column (Species) would be excluded.

This would be a different thresholds for row 2, 3, and so forth. For example, row 2 would be red if value in the cell is below 4.

I can change the color of a given row (excluding the Species column) as follows:

datatable(iris, options = list(pageLength = 5)) %>% 
  formatStyle(colnames(iris)[-5], target = "cell", color = styleRow(1, 'red'))

But if I try to add styleInterval to the formatting to achieve what I need nothing gets colored anymore. As follows:

datatable(iris, options = list(pageLength = 5)) %>% 
  formatStyle(colnames(iris)[-5], target = "cell", color = styleRow(1, styleInterval(2, c('red','black')))) %>%
  formatStyle(colnames(iris)[-5], target = "cell", color = styleRow(2, styleInterval(4, c('red','black'))))

Of note, I've also tried the above with target = "row" with no success.

I can use this to color columns normally, for example, with the iris dataset transposed:

datatable(as.data.frame(t(iris))) %>% formatStyle("V1", color = styleInterval(2, c('red','black'))) %>% formatStyle("V2", color = styleInterval(4, c('red','black')))

However, I can't find a way to transpose the colored datatable back for display.

Lastly, in my actual datatable, I'll have to do this for several rows based on different value intervals per row. This will also need to be applicable to different datasets of varying number of columns. So I don't think manually changing the CSS color for each cell would be feasible.

What is below 2? A specific column? sum of the values? row number? — M--, Mar 23 '23 at 17:19
The value of the actual cell would be below 2. For example, in the 1st row, 1.4 and 0.2 would show in red. I've edited above for clarity. Thanks — Juliana Capitanio, Mar 23 '23 at 17:21
I posted an answer. See this as well: https://stackoverflow.com/questions/55241627/need-to-apply-conditional-formatting-across-one-row-of-a-datatable However, this makes it a little more complicated than it needs to be for simpler cases like yours. — M--, Mar 23 '23 at 17:36

M-- · Accepted Answer · 2023-03-23T21:58:47.543

library(DT)

datatable(iris, options = list(pageLength = 5)) %>% 
  formatStyle(colnames(iris)[-5], target = "cell", 
              color = styleInterval(2, c('red','black')))  %>% 
  formatStyle(colnames(iris)[-5], target = "cell", 
              color = styleRow(2:nrow(iris), 'black'))

Update:

If you have multiple rows and not just one to be modified, then we can create a matrix/dataframe with all the colors based on the desired conditions, and set the colors accordingly; (refer to this answer for the original callback function https://stackoverflow.com/a/60093182/6461462).

library(tidyverse)

data.frame(cl = 1:ncol(iris), nm = names(iris)) -> match_vec

iris %>% 
  mutate(across(-Species,
                   list(clr = ~case_when(row_number() == 1 & .x <= 2 ~ "red",
                                         row_number() == 2 & .x > 3 ~ "blue",
                                         row_number() == 3 & .x > 2.5 ~ "orange",
                                         TRUE ~ "black"),
                        cn = ~with(match_vec, 
                                   match_vec[nm == unique(cur_column()), "cl"]),
                        rn = ~row_number()),
                   .names = "{col}_{fn}")) %>% 
  dplyr::select(-names(iris)) %>% 
  split.default(., sub('_.*', '', names(.))) %>% 
  map(~ setNames(., c("col","cn","rn"))) %>% 
  bind_rows() %>% 
  as_tibble() -> col_df

library(DT)

changeCellsColor <- function(colors, cols, rows) {
  stopifnot(length(rows) == length(cols))
  c(
    "function(row, data, num, index){",
    sprintf("  var rows = [%s];", paste0(rows - 1, collapse = ",")),
    sprintf("  var cols = [%s];", paste0(cols, collapse = ",")),
    sprintf("  var colors = [%s];", paste0("'", colors, "'", collapse = ",")),
    "  for(var i = 0; i < rows.length; ++i){",
    "    if(index == rows[i]){",
    "      $('td:eq(' + cols[i] + ')', row)",
    "        .css({'color': colors[i]});",
    "    }",
    "  }",
    "}"
  )
}


datatable(iris,
          options = list(
            dom = "t",
            rowCallback = JS(changeCellsColor(col_df$col, col_df$cn, col_df$rn))
          )
)

Thanks for the fast response @M--, this definitely works for the reprex I posted. However, that was a simplification of the problem. In my actual table, I need to do this for several rows, each with their own thresholds for color changes. I've also checked the link you suggested, but I think I run into the same problem there, right? This part: var color = x <= 21200 ? 'red' : x <= 22000 ? 'white' : 'green';" would need to be different depending on the row I'm changing. Is there a way to make 21200 and 22000 variables I can change for each row? Thanks again for all the help. — Juliana Capitanio, Mar 23 '23 at 17:54
@JulianaCapitanio updating the question after you get an answer is frowned upon. You should accept my answer if ti works for your original question, then post a follow up question, with a link referring to this. This way you'd get more attention as well on the new question. — M--, Mar 23 '23 at 18:38
Thanks for letting me know @M--. I'll follow your suggestion. Just in case others come across this discussion, here is the sentence I added to the question after your answer "This would have different thresholds for row 2, 3, and so forth. For example, row 2 would be red if value in cell is below 4.". Thanks again for your help. — Juliana Capitanio, Mar 24 '23 at 03:36
Just to clarify, my updated answer does provide an answer to your newly proposed use-case and gives a different style/color to each row based on a specific condition to that row. — M--, Mar 24 '23 at 14:39
Thank you so much for taking the time to help me with this @M--. Perfect! I really appreciate it. — Juliana Capitanio, Mar 28 '23 at 16:18

Using styleInterval to color only specific rows based on values across selected columns

1 Answers1

Update: