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

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. From the toolbar, select Calculation > Custom Code.
2. Click onto the page to place the custom calculation.
3. The Code Editor will automatically pop up at the top where you can insert your R code.
4. Reference the formattable and sparkline R libraries and replace table.prevalence with the table name of your input table (you can click on the name that pops up by the table to insert that into the code). In this example we are first adding the row headers of prevalence.tab as a new column and then removing these row headers:

#load libraries
library(formattable)
library(sparkline)

#specify table of data
prevalence.tab = table.prevalence

#recreate table making the row headers a column labeled Indicator Name
prevalence <- cbind("Indicator Name" = rownames(prevalence.tab), as.data.frame(prevalence.tab))
#remove the old rownames to hide
rownames(prevalence) <- c()

5. 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.

#create sparklines in the 3rd column of the table based on columns 2-7
prevalence[3] = apply(prevalence[, 2:7], 1, FUN = function(x) as.character(htmltools::as.tags(sparkline(as.numeric(x), type = "line"))))

#make the header blank
names(prevalence)[3] = "&nbsp&nbsp"

#recreate the table and keep only the indicator, first value, sparkline, and last value columns
new.prevalance = prevalence[, c(1, 2, 3, 7)] 

#Create the final htmlwidget that will render nicely in Displayr
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"))

#return final table
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