vignettes/formattable-data-frame.Rmd
formattable-data-frame.RmdFormattable data frames are data frames to be rendered as HTML table with formatter functions applied, which resembles conditional formatting in Microsoft Excel.
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 1Using knitr::kable() or formattable(), the
data frame can be rendered as HTML table which looks more friendly.
library(formattable)
formattable(scores)| id | prev_score | cur_score | change |
|---|---|---|---|
| 1 | 10 | 8 | -2 |
| 2 | 8 | 9 | 1 |
| 3 | 6 | 7 | 1 |
| 4 | 8 | 8 | 0 |
| 5 | 8 | 9 | 1 |
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.
## [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))| id | prev_score | cur_score | change |
|---|---|---|---|
| 1 | 10 | 8 | -2 |
| 2 | 8 | 9 | 1 |
| 3 | 6 | 7 | 1 |
| 4 | 8 | 8 | 0 |
| 5 | 8 | 9 | 1 |
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))| id | prev_score | cur_score | change |
|---|---|---|---|
| 1 | 10 | 8 | -2 |
| 2 | 8 | 9 | 1 |
| 3 | 6 | 7 | 1 |
| 4 | 8 | 8 | 0 |
| 5 | 8 | 9 | 1 |
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")))))| id | prev_score | cur_score | change |
|---|---|---|---|
| 1 | 10 | 8 | -2 |
| 2 | 8 | 9 | 1 |
| 3 | 6 | 7 | 1 |
| 4 | 8 | 8 | 0 |
| 5 | 8 | 9 | 1 |
To hide columns, use FALSE formatter.
formattable(scores, list(prev_score = FALSE))| id | cur_score | change |
|---|---|---|
| 1 | 8 | -2 |
| 2 | 9 | 1 |
| 3 | 7 | 1 |
| 4 | 8 | 0 |
| 5 | 9 | 1 |
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.00Without any formatter functions applied, the formattable data frame is directly rendered as an HTML table.
formattable(products)| id | price | rating | market_share | revenue | profit |
|---|---|---|---|---|---|
| 1 | 10 | 5 | 10.00% | 55,000.00 | 25,300.00 |
| 2 | 15 | 4 | 12.00% | 36,400.00 | 11,500.00 |
| 3 | 12 | 4 | 5.00% | 12,000.00 | (8,200.00) |
| 4 | 8 | 3 | 3.00% | (25,000.00) | (46,000.00) |
| 5 | 9 | 4 | 14.00% | 98,100.00 | 65,000.00 |
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))| id | price | rating | market_share | revenue | profit |
|---|---|---|---|---|---|
| 1 | 10 | 5 | 10.00% | 55,000.00 | 25,300.00 |
| 2 | 15 | 4 | 12.00% | 36,400.00 | 11,500.00 |
| 3 | 12 | 4 | 5.00% | 12,000.00 | (8,200.00) |
| 4 | 8 | 3 | 3.00% | (25,000.00) | (46,000.00) |
| 5 | 9 | 4 | 14.00% | 98,100.00 | 65,000.00 |
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))| id | price | rating | market_share | revenue | profit |
|---|---|---|---|---|---|
| 1 | 10 | 5 | 10.00% | 55,000.00 | 25,300.00 |
| 2 | 15 | 4 | 12.00% | 36,400.00 | 11,500.00 |
| 3 | 12 | 4 | 5.00% | 12,000.00 | (8,200.00) |
| 4 | 8 | 3 | 3.00% | (25,000.00) | (46,000.00) |
| 5 | 9 | 4 | 14.00% | 98,100.00 | 65,000.00 |
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")))| id | a | b | c |
|---|---|---|---|
| 1 | -0.56047565 | 1.2240818 | -1.0678237 |
| 2 | -0.23017749 | 0.3598138 | -0.2179749 |
| 3 | 1.55870831 | 0.4007715 | -1.0260044 |
| 4 | 0.07050839 | 0.1106827 | -0.7288912 |
| 5 | 0.12928774 | -0.5558411 | -0.6250393 |
| 6 | 1.71506499 | 1.7869131 | -1.6866933 |
| 7 | 0.46091621 | 0.4978505 | 0.8377870 |
| 8 | -1.26506123 | -1.9666172 | 0.1533731 |
| 9 | -0.68685285 | 0.7013559 | -1.1381369 |
| 10 | -0.44566197 | -0.4727914 | 1.2538149 |
If a one-sided formula is supplied, the function will be applied to all cells.
formattable(df[, -1], list(~ percent))| a | b | c |
|---|---|---|
| -56.05% | 122.41% | -106.78% |
| -23.02% | 35.98% | -21.80% |
| 155.87% | 40.08% | -102.60% |
| 7.05% | 11.07% | -72.89% |
| 12.93% | -55.58% | -62.50% |
| 171.51% | 178.69% | -168.67% |
| 46.09% | 49.79% | 83.78% |
| -126.51% | -196.66% | 15.34% |
| -68.69% | 70.14% | -113.81% |
| -44.57% | -47.28% | 125.38% |
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")
}))| id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
|---|---|---|---|---|---|---|---|---|
| 1 | 0.42646422 | -0.69470698 | 0.25331851 | 0.37963948 | -0.4910312 | 0.005764186 | 0.9935039 | -0.71040656 |
| 2 | -0.29507148 | -0.20791728 | -0.02854676 | -0.50232345 | -2.3091689 | 0.385280401 | 0.5483970 | 0.25688371 |
| 3 | 0.89512566 | -1.26539635 | -0.04287046 | -0.33320738 | 1.0057385 | -0.370660032 | 0.2387317 | -0.24669188 |
| 4 | 0.87813349 | 2.16895597 | 1.36860228 | -1.01857538 | -0.7092008 | 0.644376549 | -0.6279061 | -0.34754260 |
| 5 | 0.82158108 | 1.20796200 | -0.22577099 | -1.07179123 | -0.6880086 | -0.220486562 | 1.3606524 | -0.95161857 |
| 6 | 0.68864025 | -1.12310858 | 1.51647060 | 0.30352864 | 1.0255714 | 0.331781964 | -0.6002596 | -0.04502772 |
| 7 | 0.55391765 | -0.40288484 | -1.54875280 | 0.44820978 | -0.2847730 | 1.096839013 | 2.1873330 | -0.78490447 |
| 8 | -0.06191171 | -0.46665535 | 0.58461375 | 0.05300423 | -1.2207177 | 0.435181491 | 1.5326106 | -1.66794194 |
| 9 | -0.30596266 | 0.77996512 | 0.12385424 | 0.92226747 | 0.1813035 | -0.325931586 | -0.2357004 | -0.38022652 |
| 10 | -0.38047100 | -0.08336907 | 0.21594157 | 2.05008469 | -0.1388914 | 1.148807618 | -1.0264209 | 0.91899661 |
DT::datatables
as.datatable() is designed to convert a formattable data
frame to DT::datatables.
as.datatable(formattable(products))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)))