CODEWITHSUNDEEP
rdatatable
tsouchlarakis
tsouchlarakis

Reputation: 1619

Conditional Formatting Cell R DataTable

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

Upvotes: 2

Views: 3261

Answers (1)

cgpu
cgpu

Reputation: 56

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:

enter image description here

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

Upvotes: 4

Related Questions