Ytukyg

I need to implement conditional formatting in my datatable. See an example below. What I need is to highlight the third row, "PercentDone", based on if that number is greater than the thresholds in rows 4 and 5.

If the number is greater than 50%, I would like to highlight it green.

If it is between 25% and 50%, I would like to highlight it yellow.

If it is under 25%, I would like to highlight it red.

This is similar to something someone would do in Excel with conditional formatting, I am just unsure as to how to implement it in a datatable in R.

In the example below, the 46% in column 1 should be yellow, the 11% in column 2 should be red, and the 65% in column 3 should be green.

df = data.frame(

  c(51, 59, '46%', '25%', '50%'),

  c(12, 93, '11%', '25%', '50%'),

  c(40, 22, '65%', '25%', '50%'))



colnames(df) = c('Location1', 'Location2', 'Location3')

rownames(df) = c('Done', 'Need', 'PercentDone', 'Threshold1', 'Threshold2')



DT = datatable(df) %>%

  formatStyle(...)

Late reply, but might prove useful for someone else so I'm posting.

If transposing the table is not a problem, you can try the following.


The final table should look like this:

DF = data.frame(

 c(51, 59, '46%', '25%', '50%'),

 c(12, 93, '11%', '25%', '50%'),

 c(40, 22, '65%', '25%', '50%'), stringsAsFactors = FALSE )# variables as chr, w/o factor levels)





colnames(DF) = c('Location1', 'Location2', 'Location3')

rownames(DF) = c('Done', 'Need', 'PercentDone', 'Threshold1', 'Threshold2')



head(DF)

Retrieve only the numbers from the percentages, convert to numeric to be able to perform the comparisons:

# Define function for retrieving digits; One-liner courtesy of @stla at GitHub

Numextract <- function(string){ unlist(regmatches(string,gregexpr("[[:digit:]]+\.*[[:digit:]]*",string)))}



# Apply Numextract to all dataframe; 

# retrieves only digits but still class is chr

DF [,] <- lapply(DF[,], Numextract)



# Convert to numeric to allow for comparison

DF [,] <- lapply(DF[,], as.numeric)



# Transpose dataframe to access the `PercentDone` as a column

DF = t(DF)

Consider removing the values from the dataframe and have as vars

Threshold1 = 25

Threshold2 = 50

Customizing datatable: Highlight PercentDone

DT::datatable(DF, 

          filter  = "bottom",

          caption = "I am the title",





          # OPTIONS:

          options = list(



            initComplete = JS(

              "function(settings, json) {",

              "$(this.api().table().header()).css({'background-color': '#000', 'color': '#fff'});",

              "}"),

            columnDefs = list(list(targets = length(colnames(DF)), visible = TRUE)))) %>% 



# Change fontsize of cell values

 formatStyle(columns    = c(1:length(colnames(df))), 

           fontSize   = "85%",

          fontFamily = "Verdana")%>%







 # Format column based on P.Value levels

 formatStyle(fontWeight = 'bold',

          # Format this:

          "PercentDone",



          # Font color

          color = styleInterval(c(0.0), c('black', 'black')),

          backgroundColor = styleInterval(c(Threshold1, Threshold2), 

                                          c('lightgreen', '#f4d35e', "tomato"))) -> fancyTable





# Print customized color-coded datatable:

fancyTable