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

You can create tables in R that are more custom than what is available with our standard R tables using the formattable R package. You can use the formattable package when you want to start with a modern look, add custom shading, and sparkgraphs. It does not have the search and filtering capabilities that DT tables in R has nor does it distribute cells evenly like CreateCustomTables in R. This article describes how to go from two tables...

...to a formattable table with colored significance arrows based on the values in the second table.

Requirements

A table of values to add formatting to - for this specific example we use a table with multiple rows and a time period range in columns.
A table showing the z-Statistic for the desired columns - In this example, we are using the z scores from the 2016 data.

Method

1. Select Calculation > Custom Code and draw a box on the Page.

2. In the object inspector, go to Properties > R CODE and paste in the following code, editing as needed based on the comments:

#load the R package of functions needed
library(formattable)
#identify table of values - replace prevalence.table with your table name or click on a table
tab1 = prevalence.table
#identify table with z-statistic - replace prevalence.z.scores with your table name or 
#click on the z-statistic table to insert the name automatically
z = prevalence.z.scores

####Combine the data together and format into the data needed for the final table
#create copy of value table with rownames as its own column
prevalence <- cbind("Indicator Name" = rownames(tab1), 
                     as.data.frame(tab1))
#combine the z-statistic as a column to the table of values
prev.sig = cbind(prevalence,z=z[,NCOL(z)])
#remove rownames
rownames(prev.sig) <- c()
#keep the data you need using [rows, columns], below we keep the 1, 6, 7, and 10th columns
prev.sig = prev.sig[, c(1,6:7,10)]
#set the header of last column (column 4) to blank
names(prev.sig)[4] = "&nbsp"

####Create the final formattable table
#use formatter() to format the last column with the blank header.
#~style and ~icontext arguments set the colors and icons for the last column based
#on the values of the two 2015 and 2016 columns and the z-statistic (1.96 is 95% confidence)
formattable(prev.sig, #give it the table of data you want to show
            list("&nbsp" = formatter("span", 
                                      style = ~ style(color = ifelse(`2016` >`2015`, "green", "red")), 
                                              ~ icontext(sapply(`&nbsp`, function(x) if (x < -1.96) "arrow-down" else if (x > 1.96) "arrow-up" else "")))))