This article describes how to go from a table...
...to an R table using the formattable package which includes line charts from the sparklines package.
Requirements
- A table with multiple rows and a time period range in the 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 and sparkline R libraries and define the table using the name from step 2. In this example we are first adding the row headers of prevalence.tab as a new column and then removing these row headers:
library(formattable)
library(sparkline)
prevalence <- cbind("Indicator Name" = rownames(prevalence.tab), as.data.frame(prevalence.tab))
rownames(prevalence) <- c()
7. We now replace the third column with line charts based on all the original date columns (2 to 7) and specify which columns to keep via their index number. Finally, the table is saved as a formattable HTML widget with a column of line charts covering the entire date range in the middle.
library(formattable)
library(sparkline)
prevalence[3] = apply(prevalence[, 2:7], 1, FUN = function(x) as.character(htmltools::as.tags(sparkline(as.numeric(x), type = "line"))))
names(prevalence)[3] = "  "
new.prevalance = prevalence[, c(1, 2, 3, 7)]
final.table = as.htmlwidget(formattable(new.prevalance,
align = c("l",rep("r", NCOL(prevalence) - 1)),
list(`Indicator Name` = formatter("span", style = ~ style(color = "grey", font.weight = "bold")))))
final.table$dependencies <- c(final.table$dependencies, htmlwidgets:::widget_dependencies("sparkline", "sparkline"))
final.table
Next
How to Customize a Table Using the Formattable R Package
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