Formating DT table to add background color for interval values

datatable
#1

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

0 Likes

#2

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'))
  )
0 Likes

#3

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

0 Likes

#4

+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.

1 Like

#5

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 ?

0 Likes

#6

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')
0 Likes

#7

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.

0 Likes

#8

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.

0 Likes

#9

@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
0 Likes

#10

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)

0 Likes

#11

Thanks for advise and I updated the code above.

Happy weekend as well

0 Likes

#12

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')))

1 Like

#13

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

0 Likes

split this topic #14

2 posts were split to a new topic: How to style one column based on the values of another, using DT?

0 Likes

closed #15

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

0 Likes