tableHTML"

knitr::opts_chunk$set(echo = TRUE)

When presenting data in a table, you might want to put emphasis on certain areas of your table. add_css_column() allows you to apply the same CSS to all rows in a column. E.g. you can change the background color of a column. What you cannot do though, is change the color of the column if a condition is met. This is why add_css_conditional_column() was implemented. You can choose from a range of conditionals, equalities and inequalities ("==", "!=", ">", ">=", "<", "<="), "min", "max", "top_n", "bottom_n", "between", "contains")

Highlight specific value in a column

The most basic use case of the function is highlighting a specific value in a column. In this case it will apply green background to all values that are equal to 21.4 in column 'mpg'.

library(tableHTML)
tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = '==', 
                             value = 21.4, 
                             css = list('background-color', 'green'), 
                             columns = 'mpg')

Highlight specific value in multiple columns

It is possible to apply the same condition to multiple columns in one function call.

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = '==', 
                             value = 3.15,
                             css = list('background-color', 'steelblue'), 
                             columns = c('drat', 'wt'))

Chaining

As usual, the function can be chained

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = '==', 
                             value = 21.4, 
                             css = list('background-color', 'green'), 
                             columns = 'mpg') %>%
  add_css_conditional_column(conditional = '==',
                             value = 3.15,
                             css = list('background-color', 'steelblue'), 
                             columns = c('drat', 'wt'))

\

The function can also be chained changing the same column

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = '==', 
                             value = 21.4, 
                             css = list('background-color', 'green'), 
                             columns = 'mpg') %>%
  add_css_conditional_column(conditional = '==', 
                             value = 15.2, 
                             css = list('background-color', 'steelblue'), 
                             columns = 'mpg')

\

You may want to apply styles depending on quantiles and use several conditionals to achieve this.

 qu_25_75 <- quantile(mtcars$disp, c(0.25, 0.75))

 tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
   add_css_conditional_column(conditional = "<",
                              value = qu_25_75[1], 
                              css = list('background-color', "green"),
                              columns = c("disp")) %>%
   add_css_conditional_column(conditional = "between",
                              between = qu_25_75, 
                              css = list('background-color', "orange"), 
                              columns = c("disp")) %>%
   add_css_conditional_column(conditional = ">", 
                              value = qu_25_75[2], 
                              css = list('background-color', "red"), 
                              columns = c("disp"))

Applying function on multiple columns

If you want to apply conditional formatting to multiple columns, you might want to compare the columns individually or together. E.g. you might want to find the minimum value in columns 'disp' and 'hp'. If you want to find the minimum in both columns, use same_scale = TRUE:

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = 'min', 
                             css = list('background-color', 'orange'), 
                             columns = c('disp', 'hp'),
                             same_scale = TRUE)

\

If you want to highlight the minimum in 'disp' and the minimum in 'hp' columns, use same_scale = FALSE:

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = 'min', 
                             css = list('background-color', 'orange'), 
                             columns = c('disp', 'hp'),
                             same_scale = FALSE)

\

If you want to find the minimum values in every column you can use same_scale = FALSE

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>% 
  add_css_conditional_column(conditional = "min", 
                             css = list('background-color', "green"),
                             columns = seq_along(mtcars),
                             same_scale = FALSE)

Equalities and inequalities

When evaluating equalities or inequalities there is a choice of all common operators. A value argument needs to be specified.

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = "==",
                             value = 14.3, 
                             css = list('background-color', "steelblue"), 
                             columns = 1) %>%
  add_css_conditional_column(conditional = "!=", 
                             value = 8,
                             css = list('background-color', "mediumvioletred"), 
                             columns = 2) %>%
  add_css_conditional_column(conditional = ">",
                             value = 440,
                             css = list('background-color', "orange"),
                             columns = 3) %>%
  add_css_conditional_column(conditional = ">=", 
                             value = 264,
                             css = list('background-color', "green"),
                             columns = 4) %>%
  add_css_conditional_column(conditional = "<",
                             value = 3, 
                             css = list('background-color', "yellow"),
                             columns = 5) %>%
  add_css_conditional_column(conditional = "<=", 
                             value = 2.20, 
                             css = list('background-color', "lightgray"), 
                             columns = 6)

Highest and lowest values

You can use 'min' and 'max' to highlight the minimum or maximum value in a column (or columns). 'bottom_n' and 'top_n' are similar functions, only that they allow to hightlight n values, where 'bottom_n' with n = 1 is an equivalent to 'min' and 'top_n' with n = 1 is an equivalent to 'max':

Notice that you can use same_scale to define the context for a function:

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = "min",
                             css = list('background-color', "steelblue"), 
                             columns = 1) %>%
  add_css_conditional_column(conditional = "max", 
                             css = list('background-color', "mediumvioletred"), 
                             columns = 2) %>%
  add_css_conditional_column(conditional = "bottom_n",
                             n = 5, 
                             css = list('background-color', "green"), 
                             columns = c(3, 4), 
                             same_scale = FALSE) %>% 
  add_css_conditional_column(conditional = "top_n",
                             n = 5, 
                             css = list('background-color', "orange"), 
                             columns = c(5, 6)) 

Between

The between operator can be used to format values that are in a defined range. The operator is very much like SQL with inclusive lower and upper bound. You need to provide a vector with two elements in between.

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = "between",
                             between = c(15, 25),
                             css = list('background-color', "steelblue"), 
                             columns = 1)

\

Again, the 'between' conditional can be applied to multiple columns in one function call.

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = "between", 
                             between = c(20, 22), 
                             css = list('background-color', "steelblue"), 
                             columns = c(1, 7))

Character columns - Pattern matching

If you want to highlight elements that contain a specific substring, you can use 'contains'. The function will check if a pattern or regular expression can be found (Note: case sensitive). The R coecion rules apply: if you use it on numeric columns, they will be evaluated as character.

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(conditional = "contains", 
                             value = "[0-9]", 
                             css = list('background-color', "steelblue"), 
                             columns = "rownames") %>%
  add_css_conditional_column(conditional = "contains", 
                             value = "Honda",
                             css = list('background-color', "silver"), 
                             columns = "rownames")

Update to sorting factors with levels

In the old version 1.1.0 of tableHTML, the default order of factor levels was alphabetic, because the data was parsed from the HTML. Since version 2.0.0, the data comes from the tableHTML objects attributes, so the factors and levels are preserved. The levels argument is therfore deprecated.

df <- data.frame(factor_alphabetic = c('d', 'a', 'e', 'a', 'd', 'd', 'a', 'c', 'd', 'a'),
                 factor_ordered = c('D', 'A', 'E', 'A', 'D', 'D', 'A', 'C', 'D', 'A'),
                 stringsAsFactors = TRUE)

df$factor_ordered <- factor(df, levels = c('B', 'D', 'A', 'E', 'C'))

tableHTML(df, 
          rownames = FALSE) %>%
  add_css_conditional_column(color_rank_theme = 'White-Green',
                             columns = 1) %>%
  add_css_conditional_column(color_rank_theme = 'White-Green', 
                             columns = 2)

Color rank

A common conditional formatting usecase is to apply a color rank to columns. There are a few of them pre-defined:

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(color_rank_theme = "RAG", columns = 1) %>%
  add_css_conditional_column(color_rank_theme = "Spectral", columns = 2) %>%
  add_css_conditional_column(color_rank_theme = "Rainbow", columns = 3) %>%
  add_css_conditional_column(color_rank_theme = "White-Green", columns = 4) %>%
  add_css_conditional_column(color_rank_theme = "White-Blue", columns = 5) %>%
  add_css_conditional_column(color_rank_theme = "White-Red", columns = 6) 

\

You can reverese the color by using decreasing = TRUE

tableHTML(mtcars,
          widths = rep(100, 12)) %>%
  add_css_conditional_column(color_rank_theme = "RAG", 
                             columns = 1, 
                             decreasing = TRUE) 

\

Color ranks can be applied to multiple columns with one function call as well and the context can be set using same_scale:

tableHTML(data.frame(a = 1:20, b = rep(1:5, 4), c = 1:20, d = rep(1:5, 4)), 
          width = rep(80, 4),
          second_headers = list(c(2, 2), 
                                c("same_scale = TRUE",
                                  "same_scale = FALSE")),
          rownames = FALSE) %>%
  add_css_conditional_column(color_rank_theme = "RAG",
                             columns = c(1, 2), 
                             decreasing = FALSE, 
                             same_scale = TRUE) %>%
  add_css_conditional_column(color_rank_theme = "RAG", 
                             columns = c(3, 4), 
                             decreasing = FALSE, 
                             same_scale = FALSE)

Using custom CSS

You can also provide custom css. The css needs to be a named list where the name corresponds to the name of the column the css should be applied to. The elements of that named list a vector of css style attributes and a list of style attribute values. For each style attribute you need to have a list of attribute values and you need to have an attribute value for every row in the column.

You can use make_css_color_rank_theme() with specific colors that you wish to apply. You can provide your own colors here or use palettes from e.g. RColorBrewer, as long as they are a valid argument to col2rgb().

color_rank_css <- 
  make_css_color_rank_theme(list(qsec = mtcars$qsec),
                             colors = RColorBrewer::brewer.pal(9, "Set1"))

tableHTML(mtcars,
          widths = c(140, rep(45, 11))) %>%
  add_css_conditional_column(color_rank_theme =  "Custom", 
                             color_rank_css = color_rank_css, 
                             columns = 7)

Integration with other tableHTML functions

The functions add_css_column() and add_css_conditinal_column() can be combined.

tableHTML(mtcars,
          widths = c(120, 200, rep(100, 11)),
          row_groups = list(c(10, 10, 12), c('Group 1', 'Group 2', 'Group 3'))) %>%
 add_theme('rshiny-blue') %>%
 add_css_column(css = list('border', '1px solid'), columns = 1) %>%
 add_css_conditional_column(color_rank_theme = "RAG", columns = 1) %>%
 add_css_conditional_column(conditional = "contains",
                            value = "1", 
                            css = list(c('color', 'font-size', 'border'), 
                                       c('steelblue', '20', '1px solid steelblue')),
                            columns = "row_groups") %>%
 add_css_conditional_column(conditional = "contains",
                            value = "2", 
                            css = list(c('color', 'font-size', 'border'),
                                       c('royalblue', '30', '1px solid royalblue')),
                            columns = "row_groups") %>%
 add_css_conditional_column(conditional = "contains",
                            value = "3", 
                            css = list(c('color', 'border'),
                                       c('navy', '1px solid navy')),
                            columns = "row_groups")

\

In the example below you can see how to combine a few other functions from the add_css_ family. First, a tableHTML is created with rownames, an outside boarder, specified column widths, row groups, and second headers. CSS is then applied to the second header, the header, every second row, a color rank is added to the row groups, and in addition conditional formatting if the row groups contain specific numbers.

tableHTML(mtcars, 
          border = 2,
          rownames = TRUE, 
          widths = c(80, 140, rep(50, 11)),
          row_groups = list(c(10, 10, 12), c('Group 1', 'Group 2', 'Group 3')),
          second_headers = list(c(3, 4, 6), c('col1', 'col2', 'col3'))) %>%
  add_css_second_header(css = list(c('background-color', 'color', 'height'),
                                   c('#2E5894', 'white', '50px')),
                        second_headers = 1:3) %>%
  add_css_header(css = list(c('transform', 'height'),
                            c('rotate(-45deg)', '50px')),
                 headers = 3:13) %>%
  add_css_row(css = list('background-color', '#f2f2f2'), 
              rows = even(3:34)) %>%
  add_css_conditional_column(color_rank_css = 
                               make_css_color_rank_theme(list(row_groups = 1:3),
                                                          colors = c('#00b200',
                                                                     '#007f00',
                                                                     '#004c00'),
                                                          css_property = 'color'),
                             columns = 'row_groups') %>%
  add_css_conditional_column(conditional = "contains",
                             value = "1", 
                             css = list('background-color', '#F5F5F5'),
                             columns = "row_groups") %>%
  add_css_conditional_column(conditional = "contains",
                             value = "2", 
                             css = list('background-color', '#D0D0D0'),
                             columns = "row_groups") %>%
  add_css_conditional_column(conditional = "contains",
                             value = "3", 
                             css = list('background-color', '#A9A9A9'),
                             columns = "row_groups") %>%
  add_css_conditional_column(color_rank_theme = 'RAG', 
                             columns = 4)

\



Try the tableHTML package in your browser

Any scripts or data that you put into this service are public.

tableHTML documentation built on March 21, 2021, 5:06 p.m.