Is there any similar approach to conditional forma

Question

I have a dataframe that is uploaded from excel and displayed as datatable in shiny, in excel we have used conditional formating to change the color of cells based on the values of both the cell itself and the value of other cells. So for example for columns ID, X,Y the logic is as follow:

• if -4 < X < 4 and Y < 10 color of X,Y is pink
• elseif Y >10 color of X,Y is bleu
• else X ="" or Y="" then color of X,Y is white

I tried to use DT package but with no success, could anyone help me to get through this or suggest some other approach? thank you in advance Here is my code with a reproducible dataframe.

ui <- shinyUI(fluidPage(
  titlePanel("Column Plot"),
  tabsetPanel(
    tabPanel("Upload File",
             titlePanel("Uploading Files"),
             sidebarLayout(
               sidebarPanel(
                 fileInput('file1', 'Choose xlsx file',
                           accept = c(".xlsx")
                 ),
                 tags$br(),
                 checkboxInput('header', 'Header', TRUE),
                 radioButtons('sep', 'Separator',
                              c(Comma=',',
                                Semicolon=';',
                                Tab='\t'),
                              ','),
                 radioButtons('quote', 'Quote',
                              c(None='',
                                'Double Quote'='"',
                                'Single Quote'="'"),
                              '"')

               ),
               mainPanel(
                 tableOutput('contents')
               )
             )
    ),
tabPanel("contents_extra",
             pageWithSidebar(
               headerPanel('contents_extra'),
               sidebarPanel(
                 checkboxInput('test', 'test', TRUE)

               ),
               mainPanel(
                 dataTableOutput('contents_extra')
               )
             )
    ),
output$contents_extra <- renderDataTable({
      df <- data.frame(
      id = 1:10, 
      X = c(-2, 4, 40, -0.1228, 2.9, 9, 2.7, 2.7, 31, -30),
      Y = c(-18.9, -19.5, 19.6, 12, 11.1, 73, 4.3, 39, 2.5, 1.6),
      A = c(-7.3, 5.1 ,0.12, 15, 21, 1.2, -0,07, 4.3, 39, 2.5) 
      B = c(-18.9, 0.12, 15, 11.1, 73, -2, 4, 40, -19.5, 19.6)
      C = c(4.3, 39, 2.5, 1.6, -7.3, 6, 5.1 ,0.12, -0.07, 4.3)
      library(DT)
      options(DT.options = list(pageLength = 100))
      datatable(df, options = list(
      columnDefs = list(list(targets = X, visible = TRUE)))) %>% formatStyle(
      columns = c("X","Y"),
      valueColumns = c("X","Y"),
      backgroundColor = styleEqual(c(X > -4 && X < 4 && Y < 10, Y > 10, X ="" or Y=""), c('pink', 'bleu','white'))
       )
    })

Answer 1

This is a question about DT (answer is the same whether you use shiny or not).

It's easier to derive the colors in R. Then use a rowCallback.

library(DT)
df <- data.frame(
  id = 1:10, 
  X = c(-2, 4, 40, -0.1228, 2.9, 9, 2.7, 2.7, 31, -30),
  Y = c(-18.9, -19.5, 19.6, 12, 11.1, 73, 4.3, 39, 2.5, 1.6)
)
colors <- with(df, ifelse(X > -4 & X < 4 & Y < 10, 
                          "pink", 
                          ifelse(Y > 10, 
                                 "blue", "white")))

rgbcolors <- apply(grDevices::col2rgb(colors), 2, 
                   function(rgb) sprintf("rgb(%s)", paste(rgb, collapse=",")))
columns <- c(2,3) # columns X and Y
jscode <- 
  paste("function(row, data, index) {",  
        sprintf("var colors=%s;\n%s", 
                sprintf("[%s]", 
                        paste(sprintf("'%s'", rgbcolors), collapse=", ")), 
                paste(sprintf("$(this.api().cell(index, %s).node()).css('background-color', colors[index]);", 
                              columns), collapse="\n")), 
        "}", sep="\n")
datatable(df, escape=FALSE, 
          options = list(rowCallback=JS(jscode))
)

The created Javascript code is:

> cat(jscode)
function(row, data, index) {
var colors=['rgb(255,192,203)', 'rgb(255,255,255)', 'rgb(0,0,255)', 'rgb(0,0,255)', 'rgb(0,0,255)', 'rgb(0,0,255)', 'rgb(255,192,203)', 'rgb(0,0,255)', 'rgb(255,255,255)', 'rgb(255,255,255)'];
$(this.api().cell(index, 2).node()).css('background-color', colors[index]);
$(this.api().cell(index, 3).node()).css('background-color', colors[index]);
}