How to Customize a Table Using the Formattable R Package

When you need to create a table with very custom formatting, you may need to create it via a custom Calculation on the page. From a coding perspective, the easiest function to use is the CreateCustomTable function, which ultimately creates an HTML table. However, if you'd like spark graphs (i.e., spark lines, spark bars, etc), then you can use the formattable package of functions to create this. This article describes how to go from a default drag-and-drop table...

...to an R table created 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 General > General > Name in Properties .

3. From the toolbar, select Calculation > Custom Code.

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

5. In the Code panel, 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