The “grammar of tables” is used to build tables with the R-package gt. Thomas Mock published an extension package called gtExtras which is used all over in tweets about NFL and Fantasy Football such as win and loss history information in a table.

gt follows a similar philosophy such as ggplot2. On the one hand there is the data to be shown (in a table resp. a plot). On the other hand there is the description how to visualize the data: What is the table header, are there subheaders, how are the cells grouped and formated?

So it was time to check out gt. During my tryouts I came across one demand I haven’t found an in-built solution for: I wanted to format a column with respect to the value of another column. Here’s the way I solved it:

Sample data

Let’s start with some sample data

1
2
3
4
5
6
7
8
9

options(tidyverse.quiet = TRUE)
library(tidyverse)

set.seed(42)
data <- tibble(
  case = letters[1:5],
  value = rnorm(5, 5, 10),
  status = c("ok", "unknown", "ok", "not ok", "unknown")
)

First gt-table

and create a simple gt-table

1
2
3
4

library(gt)

data %>% 
  gt()

case value status
a 18.7095845 ok
b -0.6469817 unknown
c 8.6312841 ok
d 11.3286260 not ok
e 9.0426832 unknown

That’s great! That’s a pretty table with just one command!

Colored column based on its own values

Now let’s format the column value depending on its value:

1
2
3
4
5
6

data %>% 
  gt() %>% 
  data_color(
    columns = value,
    colors  = c("red", "orange", "yellow", "green")
  )

case value status
a 18.7095845 ok
b -0.6469817 unknown
c 8.6312841 ok
d 11.3286260 not ok
e 9.0426832 unknown

Colored column based on value of another column

But now we want to format the column depending on the value in column status: When status is ok show a green background in column value, in case of “not okay” a red one, otherwise show a yellow background.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

custom_tab_style <- function(data, column, status_column) {
  condition_green = glue::glue("{status_column} == 'ok'")
  condition_red = glue::glue("{status_column} == 'not ok'")
  condition_yellow = glue::glue("{status_column} == 'unknown'")
  data %>% 
    tab_style(
      style = cell_fill(color = 'lightgreen'),
      locations = cells_body(
        columns = all_of(column),
        rows = eval(parse(text = condition_green))
      )
    ) %>% 
    tab_style(
      style = list(cell_fill(color = 'red'), cell_text(color = 'white')),
      locations = cells_body(
        columns = all_of(column),
        rows = eval(parse(text = condition_red))
      )
    ) %>% 
    tab_style(
      style = cell_fill(color = 'yellow'),
      locations = cells_body(
        columns = all_of(column),
        rows = eval(parse(text = condition_yellow))
      )
    )
}

data %>% 
  gt() %>% 
  custom_tab_style("value", "status")

case value status
a 18.7095845 ok
b -0.6469817 unknown
c 8.6312841 ok
d 11.3286260 not ok
e 9.0426832 unknown

As you can see I built a custom function custom_tab_style. Within this function I used the function tab_style and a arbitrary conditions to format the cells depending on the condition.

Hide the status column

Last we want to hide the column status. We can achieve this by using cols_hide()

1
2
3
4

data %>% 
  gt() %>% 
  custom_tab_style("value", "status") %>% 
  cols_hide(columns = "status")

case value
a 18.7095845
b -0.6469817
c 8.6312841
d 11.3286260
e 9.0426832