Color specific datatable cells based on their factor values

Question

I have the dataframe below:

product<-c("ab","ab","ab","ac","ac","ac")
shop<-c("sad","sad","sad","sadas","fghj","xzzv")
category<-c("a","a","a","b","b","b")
tempr<-c(35,35,14,24,14,5)
value<-c(0,0,-6,8,4,0)
store<-data.frame(product,shop,category,tempr,value)

from which I create store2 with:

store2 <- matrix(NA,ncol=length(unique(store$shop)),nrow=length(unique(store$product)))
colnames(store2) <- unique(store$shop)
rownames(store2) <- unique(store$product)

for(i in 1:ncol(store)) {
  store2[store[i,'product'],store[i,'shop']] <- paste0(store[i,c('tempr')],'(',store[i,'value'],')')
}

I would like to create a datatable with the DT package colored according to the values of this new dataframe. More specifically if the number inside the parenthesis is positive then the cell should be colored green. In any other case (negative, 0 or NA its should be colored red. This is an example :

Jamie · Accepted Answer

I don't necessarily recommend this approach because I am fairly against embedding one language in another, but I think it solves your ask. You can probably save the JavaScript to a file and load that into a variable, and that would be a better approach I think, but, in the interest of a self-contained solution, I have inlined it.

Based on the DT documentation here, https://rstudio.github.io/DT/functions.html, it is clear that the desired approach is to use formatStyle in some way.

DT provides a few convenience methods to be used with formatStyle, whose source is here: https://github.com/rstudio/DT/blob/0b9710f5a9391c634a3865961083740f1cbf657b/R/format.R, which I have based my solution on.

Basically, we need to pass some JavaScript to formatStyle which will do all of the table styling based on a variable called value. We will need to do something like this:

datatable(store2) %>% formatStyle(colnames(store2), backgroundColor=JS(jsFunc))

Where the variable jsFunc is some JavaScript string. Since the source indicates this string must be an expression rather than a statement, and because this will be somewhat complicated, we will use an anonymous function which is immediately evaluated to perform the logic. This function must take a value and return a color based on that value. Here is the function we need.

function(value){
  // find a number preceeded by an open parenthesis with an optional minus sign
  var matches = /\((-?\d+)/.exec(value);
  // ignore values which do not match our pattern, returning white as the background color
  if(!matches || matches.length < 2) { 
    return 'white'; 
  }
  // attempt to convert the match we found into a number
  var int = parseInt(matches[1]); 
  // if we can't ignore it and return a white color
  if(isNaN(int)) { 
    return 'white';
  } 
  // if the value is negative, return red
  if(int < 0) { 
    return 'red' 
  }
  // otherwise, by default, return green
  return 'green';
}

We want to call this function immediately, so we wrap it in parenthesis, and pass the value argument to it.

(function(value){
  // find a number preceeded by an open parenthesis with an optional minus sign
  var matches = /\((-?\d+)/.exec(value);
  // ignore values which do not match our pattern, returning white as the background color
  if(!matches || matches.length < 2) { 
    return 'white'; 
  }
  // attempt to convert the match we found into a number
  var int = parseInt(matches[1]); 
  // if we can't ignore it and return a white color
  if(isNaN(int)) { 
    return 'white';
  } 
  // if the value is negative, return red
  if(int < 0) { 
    return 'red';
  }
  // otherwise, by default, return green
  return 'green';
})(value)

We wrap this value in a multi-line R string, escape any backslashes and double-quotes (I avoided using them), and assign it to the value jsFunc.

jsFunc <- "(function(value){
  // find a number preceeded by an open parenthesis with an optional minus sign
  var matches = /\((-?\d+)/.exec(value);
  // ignore values which do not match our pattern, returning white as the background color
  if(!matches || matches.length < 2) { 
    return 'white'; 
  }
  // attempt to convert the match we found into a number
  var int = parseInt(matches[1]); 
  // if we can't ignore it and return a white color
  if(isNaN(int)) { 
    return 'white';
  } 
  // if the value is negative, return red
  if(int < 0) { 
    return 'red' 
  }
  // otherwise, by default, return green
  return 'green';
})(value)"

Finally, we can call formatStyle using this variable

datatable(store2) %>% formatStyle(colnames(store2), backgroundColor=JS(jsFunc))

That should give us a result like:

Color specific datatable cells based on their factor values

Answers (2)

Related Questions