How to Customize a Table Using the Formattable R Package

This article describes how to go from a table...

...to an R table using the formattable package with custom formatting.

Requirements

• A table with multiple rows and columns.

Method

1. Select your table.

2. Copy the name from Properties > GENERAL > Name.

3. Select Calculation > Custom Code.

4. Click onto the page to place the custom calculation.

5. In the object inspector go to Properties > R CODE.

6. Reference the formattable R library and define the table using the name from step 2. In this example we are first adding the row headers of prevalence.table as a new column and then removing these row headers:

library(formattable)
prevalence <- cbind("Indicator Name" = rownames(prevalence.table), as.data.frame(prevalence.table))
rownames(prevalence) <- c()

7. Use formattable's align argument to left or right align, the color_bar argument to create colored bars from a given column's values, and the style argument to set font color. In this example, we are aligning the first column to the left and the others to the right. We are also adding a color bar to the Average column and changing the color of the Improvement column cells to red or green based on whether the values are greater than 0.

formattable(prevalence, 
align = c("l",rep("r", NCOL(prevalence) - 1)),
list(`Indicator Name` = formatter("span", style = ~ style(color = "grey", font.weight = "bold")), 
`Average` = color_bar("#FA614B"), 
`Improvement` = formatter("span", 
style = x ~ style(color = ifelse(x < 0, "red", "green")))))

8. Instead of displaying the difference as a value, we can display it as a green Yes or red No with a tick or cross icon to match the colors by using the icontent argument.

formattable(prevalence, 
align = c("l",rep("r", NCOL(prevalence) - 1)),
list(`Indicator Name` = formatter("span", style = ~ style(color = "grey", font.weight = "bold")), 
`Average` = color_bar("#FA614B"), 
`Improvement` = formatter("span", 
x ~ icontext(ifelse(x > 0, "ok", "remove"), ifelse(x > 0, "Yes", "No")), 
style = x ~ style(color = ifelse(x < 0, "red", "green")))))

9. [OPTIONAL] If you wish to rescale the bar of color so it starts from the minimum value in your column rather than 0, you can create a custom function. Here, unit.scale is defined at the top and then included in the color_bar argument as a function.

unit.scale = function(x) (x - min(x)) / (max(x) - min(x))

formattable(prevalence, 
align = c("l",rep("r", NCOL(prevalence) - 1)),
list(`Indicator Name` = formatter("span", style = ~ style(color = "grey", font.weight = "bold")), 
`Average` = color_bar("#FA614B", fun = unit.scale), 
`Improvement` = formatter("span", 
x ~ icontext(ifelse(x > 0, "ok", "remove"), ifelse(x > 0, "Yes", "No")), 
style = x ~ style(color = ifelse(x < 0, "red", "green")))))

Next

How to Format Areas of a Table Using the Formattable R Package

How to Add Statistical Significance to a Table Using the Formattable R Package

How to Add Sparklines to a Table Using the Formattable R Package