This article describes how to go from a table...
...to an R table using the formattable package with formatted cell ranges.
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 the area argument within the formattable function. In this example, we have set all the columns other than Indicator Name as percentages and are using the built-in color_tile function to set the color gradient based on the cell values.
formattable(prevalence, align = c("l",rep("r", NCOL(prevalence) - 1)),
list(`Indicator Name` = formatter("span", style = ~ style(color = "grey",font.weight = "bold")),
area(col = 2:9) ~ function(x) percent(x / 100, digits = 0),
area(col = 2:7) ~ color_tile("#B1CBEB", "#3E7DCC")))
8. You can also create your own function for setting the gradient and other formatting. Here, custom_color_tile additionally applies custom padding, border radius, and font color.
custom_color_tile <- function (...)
{
formatter("span",
style = function(x) style(display = "block",
padding = "0 4px",
`color` = "white",
`border-radius` = "4px",
`background-color` = csscolor(gradient(as.numeric(x),
...))))
}
formattable(prevalence, align = c("l",rep("r", NCOL(prevalence) - 1)), list(
`Indicator Name` = formatter("span", style = ~ style(color = "grey",font.weight = "bold")),
area(col = 2:9) ~ function(x) percent(x / 100, digits = 0),
area(col = 2:7) ~ custom_color_tile("#B1CBEB", "#3E7DCC")))
Next
How to Customize 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