R Shiny colour code table row depending on cell contents

Question

I am trying to colour code each row of a table depending on what data is in column 2. See picture below.

Basically to give you an example, the code needs to work dependent on column two, so if it says Sub-Saharan Africa for example, make the whole row a certain colour, same for Latin America and the Carribean but with a different colour. There are a few other columns to the right of the Region column. I think I might need a number of if statements in the dataTable function which is below but any help would be appreciated.

  output$Composite <- renderDataTable(datatable(FVI_DATA_COMPOSITE, 
                                                options = list(
                                                columnDefs = list(list(className = 'dt-center', targets = 3:9)))) 
                                                %>% formatRound(c(3:9), 2))

Edit: New code block that does not work currently.

 output$Composite <- renderDataTable(datatable(FVI_DATA_COMPOSITE, 
                                                options = list(
                                                columnDefs = list(list(className = 'dt-center', targets = 3:9)), pageLength = 50, lengthChange = FALSE))  
                                                %>% formatRound(c(3:9), 2))
                                                %>% formatStyle(0, target = "row", 
                                                backgroundColor = styleEqual(which(FVI_DATA_COMPOSITE$Region == "Sub-Saharan Africa")[1], "red"))

Tonio Liebrand · Accepted Answer · 2020-04-14T20:50:20.750

6

You can also format an entire row without JS if you use the set the target = "row" parameter:

library(shiny)
library(magrittr)
library(DT)
shinyApp(
  ui = fluidPage(
    fluidRow(
      column(12,
             dataTableOutput('table')
      )
    )
  ),
  server = function(input, output) {
    output$table <- renderDataTable({
      datatable(iris) %>% 
        formatStyle(0, target = "row", backgroundColor = styleEqual(which(iris$Sepal.Length < 5)[1], "red"))
    })
  }
)
}

edited Apr 14 '20 at 20:50

answered Jun 01 '17 at 13:29

Tonio Liebrand

17,189
4
39
59

Thank you, how would that fit into my code example? – OwlieW Jun 01 '17 at 13:30
You can learn from this that you should always provide a complete app and the dataset you are using. Or any other reproducible example,...You will have to replace the first value of the `styleEqual()` function to the conditions in your data,...or share example data with us. – Tonio Liebrand Jun 01 '17 at 13:32
I cant share the exact data, but let me add the code block I am trying to get work with your suggestions. See edited question – OwlieW Jun 01 '17 at 13:36
I see you are fairly new, so welcome to SO. Please consider reading this post carefully: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example?rq=1. Answering generic questions is what benefits more people, debugging individual code snippets is not useful,... – Tonio Liebrand Jun 01 '17 at 15:33
Yes I am, and thank you for your help. I will read what you suggest. In fact, I did get my code to work eventually – OwlieW Jun 01 '17 at 15:35

score 1 · Answer 2 · answered Jun 01 '17 at 13:21

To conditionally formatting a single cell is easily done with a DT helper function:

library(DT)
datatable(mtcars) %>% 
    formatStyle(0,
                backgroundColor = styleEqual(c('Mazda RX4', 'Mazda RX4 Wag'), 
                                             c('green', 'red')
                )
    )

Conditionally format an entire row, on the other hand, we have to use some JS in the rowCallback option.

Something along these lines:

library(DT)
datatable(FVI_DATA_COMPOSITE,
          options = list(
              rowCallback = JS('function(nRow, aData) {    
                                if (aData[1] == "Sub-Saharan Africa")
                                  $(nRow).css("background-color", "#9BF59B");
                                if (aData[1] == "Latin America ")
                                  $(nRow).css("background-color", "#yellow");
                                }'),
              columnDefs = list(list(className = 'dt-center', targets = 3:9))
          )
) %>% 
    formatRound(c(3:9), 2))

Thank you, can that code be used as is, or do I need to change anything. also I tried to put it in, but when i tried to open the table it just hung saying processing. Also, does it matter that the first column is country, and the second one is the region where we check to apply the colour. Should the index be 2 instead of 1? — OwlieW, Jun 01 '17 at 13:28
Concerning your first part: In case you did not see already, you can use `target = "row"` for rows (see the other post above),... — Tonio Liebrand, Jun 01 '17 at 15:34
I did see and upvoted :) did not want to update nor delete my answer, it may be helpful in edge cases (not sure which, but..) — GGamba, Jun 01 '17 at 15:38

R Shiny colour code table row depending on cell contents

2 Answers2