Formattable data frames are data frames to be rendered as HTML table with formatter functions applied, which resembles conditional formatting in Microsoft Excel.

Simple examples

Suppose we have the following data frame:

scores <- data.frame(id = 1:5,
  prev_score = c(10, 8, 6, 8, 8),
  cur_score = c(8, 9, 7, 8, 9),
  change = c(-2, 1, 1, 0, 1))

In the console, it is printed as plain texts:

scores

##   id prev_score cur_score change
## 1  1         10         8     -2
## 2  2          8         9      1
## 3  3          6         7      1
## 4  4          8         8      0
## 5  5          8         9      1

Using knitr::kable() or formattable(), the data frame can be rendered as HTML table which looks more friendly.

library(formattable)
formattable(scores)

In fact, formattable() calls knitr::kable() internally to translate data frame to HTML code. In addition, formattable() supports formatter functions to customize the transformation between values in the data frame to HTML code to generate.

plain_formatter <- formatter("span")
plain_formatter(c(1, 2, 3))

## [1] "<span>1</span>" "<span>2</span>" "<span>3</span>"

width_formatter <- formatter("span",
  style = x ~ style(width = suffix(x, "px")))
width_formatter(c(10, 11, 12))

## [1] "<span style=\"width: 10px\">10</span>"
## [2] "<span style=\"width: 11px\">11</span>"
## [3] "<span style=\"width: 12px\">12</span>"

The values of change can be positive, negative or zero. We can make positives green, negatives red, and zeros black by creating a formatter function that performs conditional transformation from value to HTML code.

sign_formatter <- formatter("span", 
  style = x ~ style(color = ifelse(x > 0, "green", 
    ifelse(x < 0, "red", "black"))))
sign_formatter(c(-1, 0, 1))

## [1] "<span style=\"color: red\">-1</span>" 
## [2] "<span style=\"color: black\">0</span>"
## [3] "<span style=\"color: green\">1</span>"

Note that we don't have to write HTML but use helper functions like style() and ifelse() to make it easier to specify conditions. Then we call formattable() on the data frame with a list of formatter functions so as to apply conditional formatting.

formattable(scores, list(change = sign_formatter))

We can also create another formatter function that makes above-average values bold while leaving others unchanged.

above_avg_bold <- formatter("span", 
  style = x ~ style("font-weight" = ifelse(x > mean(x), "bold", NA)))
formattable(scores, list(
  prev_score = above_avg_bold,
  cur_score = above_avg_bold,
  change = sign_formatter))

Cross formatting

Sometimes, we need to format one column based on the values of another column. This can be easily done with one-sided formula in formatter(). When using formatter("span", style = ~ expr), expr is evaluated in the data frame so that all columns are available for use.

formattable(scores, list(
  cur_score = formatter("span", 
    style = ~ style(color = ifelse(change >= 0, "green", "red")))))

Hiding columns

To hide columns, use FALSE formatter.

formattable(scores, list(prev_score = FALSE))

Using built-in formatters

To making formatting easier, formattable package provides a group of built-in formatter functions. Suppose we have the following data on a number of products. Some columns are already formattable vectors.

products <- data.frame(id = 1:5, 
  price = c(10, 15, 12, 8, 9),
  rating = c(5, 4, 4, 3, 4),
  market_share = percent(c(0.1, 0.12, 0.05, 0.03, 0.14)),
  revenue = accounting(c(55000, 36400, 12000, -25000, 98100)),
  profit = accounting(c(25300, 11500, -8200, -46000, 65000)))
products

##   id price rating market_share     revenue      profit
## 1  1    10      5       10.00%   55,000.00   25,300.00
## 2  2    15      4       12.00%   36,400.00   11,500.00
## 3  3    12      4        5.00%   12,000.00  (8,200.00)
## 4  4     8      3        3.00% (25,000.00) (46,000.00)
## 5  5     9      4       14.00%   98,100.00   65,000.00

Without any formatter functions applied, the formattable data frame is directly rendered as an HTML table.

formattable(products)

We can supply a list of formatter functions to make it look more colorful. For example, we apply sign_formatter to profit column so that values of different signs are displayed in different colors.

formattable(products, list(profit = sign_formatter))

Using built-in functions like color_tile() and color_bar() makes it easier to compare the magnitute of values of specified columns.

formattable(products, list(
  price = color_tile("transparent", "lightpink"),
  rating = color_bar("lightgreen"),
  market_share = color_bar("lightblue"),
  revenue = sign_formatter,
  profit = sign_formatter))

Area formatting

Sometimes, it is useful to apply a formatter function to an area so that all cells in the area share one benchmark. Area formatting is supported through the syntax of area(row, col) ~ formatter in the formatter list.

The following example renders the three columns altogether so that they share the same benchmark, not independently.

set.seed(123)
df <- data.frame(id = 1:10, 
  a = rnorm(10), b = rnorm(10), c = rnorm(10))
formattable(df, list(area(col = a:c) ~ color_tile("transparent", "pink")))

If a one-sided formula is supplied, the function will be applied to all cells.

formattable(df[, -1], list(~ percent))

Dynamically generating formatters

Since formattable() accepts a list of formatter functions, the list can be dynamically generated. For example, the following code applies row-wise formatting, that is, each row is colored independently.

df <- cbind(data.frame(id = 1:10), 
  do.call(cbind, lapply(1:8, function(x) rnorm(10))))
formattable(df, lapply(1:nrow(df), function(row) {
  area(row, col = -1) ~ color_tile("lightpink", "lightblue")
}))

Converting to DT::datatables

as.datatable() is designed to convert a formattable data frame to DT::datatables.

as.datatable(formattable(products))

## Loading required namespace: DT

Some formatters can be preserved well after the conversion.

as.datatable(formattable(products, list(
  price = color_tile("transparent", "lightpink"),
  revenue = sign_formatter,
  profit = sign_formatter)))