Formating DT table to add background color for interval values

[AbhishekHP]

Hi R experts,

Please run the code below:

datatable(iris) %>% 
  formatStyle('Sepal.Length', fontWeight = styleInterval(5, c('normal', 'bold'))) %>%
  formatStyle(
    'Sepal.Width',
    color = styleInterval(c(3.4, 3.8), c('white', 'blue', 'red')),
    backgroundColor = styleInterval(3.4, c('gray', 'yellow'))
  ) %>%
  formatStyle(
    'Petal.Length',
    background = styleColorBar(iris$Petal.Length, 'steelblue'),
    backgroundSize = '100% 90%',
    backgroundRepeat = 'no-repeat',
    backgroundPosition = 'center'
  ) %>%
  formatStyle(
    'Species',
    transform = 'rotateX(45deg) rotateY(20deg) rotateZ(30deg)',
    backgroundColor = styleEqual(
      unique(iris$Species), c('lightblue', 'lightgreen', 'lightpink')
    )
  )

Now, lets say, we want to have 4 intervals in Sepal.Width. (< 0, Until 50, More than 100, Inf).
I tried

datatable(iris) %>% 
  formatStyle('Sepal.Length', fontWeight = styleInterval(5, c('normal', 'bold'))) %>%
  formatStyle(
    'Sepal.Width',
    backgroundColor  = styleInterval(c(0, 50,100,Inf), c('white', 'blue', 'red', 'green','yellow'))
  )

i.e
Values below 0 in Sepal.Width column would be White
Value between 0 and 50 in the Sepal.Width column would be Blue
Value between 50 and 100 in the Sepal.Width column would be Red
Value between 100 and Inf in the Sepal.Width column would be Green
Values more than Infinite in the Sepal.Width column would be Yellow

But Infinity values in the column is not recognized in the column and Color is not recognized or had any backgroundcolor.

Thanks in advance
Abi

[andresrcs]

@AbhishekHP

• Value between 100 and Inf in the Sepal.Width column would be Green
• Values more than Infinite in the Sepal.Width column would be Yellow

By definition there is no values over Infinity, so nothing will fall within your last cut (Yellow), you shouldn't be using Inf

datatable(iris) %>% 
  formatStyle('Sepal.Length', fontWeight = styleInterval(5, c('normal', 'bold'))) %>%
  formatStyle(
    'Sepal.Width',
    backgroundColor  = styleInterval(c(0, 50, 100), c('white', 'blue', 'red', 'green'))
  )

[AbhishekHP]

Thanks @andresrcs,

Actually, in my actual dataset, I have +Inf and NaN
Which would need to be displayed in White and Yellow. But there was no background color change for those values.

Could you please guide me on this

[andresrcs]

+Inf is going to fall within the ">100" range ( "> +Inf" this is not possible or logical) and there is no possible range for NaN because that literally means "Not a Number", you would have to replace those NaNs first.

[AbhishekHP]

Thanks @andresrcs, I can accept your answer

Actually, Inf and NaN are not at all affected by formating background. As you suggested, I will replace NaN but how can I deal with Inf ? Should I replace them as well ?

[andresrcs]

You can set an additional range between a big number (big enough for the rest of your data) and +Inf, for example.

styleInterval(c(0, 50, 100, 99999), c('white', 'blue', 'red', 'green', 'yellow')

[AbhishekHP]

Had tried that approach, but no matter how large I keep this value, Inf and NaN completely ignores the formating background.

I can replace NaN with something but still, I would like to color coded and similarly for Inf values.

key is background color for both should be unique.

[andresrcs]

It's hard to help you any further without sample data, could you ask this with a minimal REPRoducible EXample (reprex)? A reprex makes it much easier for others to understand your issue and figure out how to help.

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

[AbhishekHP]

@andresrcs Thanks for your time and advice.

# Input data
example <- data.frame(Mydata = c(Inf,NaN,NaN,NaN,0,23.07692,NaN,NaN,Inf,NaN,NaN,-100,11.1111))
library(DT)
# Reproducible code
DT::datatable(example, options = list(lengthMenu = c(15, 20, 25), pageLength = 15)) %>% formatStyle(
'Mydata',
backgroundColor = styleInterval(c(-10, 10, 1000), c('red','white','green','yellow'))
)

Expected output is the display of the table as above but 
**NaN and Inf should have different background color** because currently it has no background color

[andresrcs]

Have you checked the link that I gave you in the previous post? It tells you how you should share sample data and the correct way to make a reproducible example (BTW pasting a screenshot is not helpful)

[AbhishekHP]

Thanks for advise and I updated the code above.

Happy weekend as well

[andresrcs]

I can't manage to make styleInterval to take Inf into account but this is a walk-around

example <- data.frame(Mydata = c(Inf,NaN,NaN,NaN,0,23.07692,NaN,NaN,Inf,NaN,NaN,-100,11.1111))
library(DT)
library(dplyr)

example %>%
  mutate(Mydata = if_else(Mydata == Inf, +1001, Mydata, -11)) %>% 
  datatable(options = list(lengthMenu = c(15, 20, 25),
                           pageLength = 15)) %>%
  formatStyle('Mydata',
  backgroundColor = styleInterval(c(-10, 10, 1000),
                                  c('red','white','green','yellow')))

[AbhishekHP]

Excellent Andresrcs for helping us with your solution. I really appreciate your work especially on Friday