Making Interactive Tables in R

Last updated on Jun 8, 2021 5 min read

library(readr)
library(tidyverse)
library(DT)
library(knitr)

If you are anything like me, you find R’s deafult print function for data frames and matrices to be an ineffective way to display information, especially in an HTML report. After an exercise in creating interactive maps, I thought it would only be right to follow it up with tables. It is critical to create tables that are complete but not too complicated so the reader can highlight trends or patterns in the data. R has numerous ways to create tables, yet the DT package is unparalleled because of all of its features!

Disclaimer! This post is not an exercise in statistical inference but rather a proof of concept of how to use the DT package.

DataTables is one of the most popular open-source Javascript libraries for HTML tables. It’s used by a wealth of companies, from all types of industries, such as Amazon, Tesla, UPS, Wells Fargo, and Adobe. The DT package in R provides an interface to DataTables and makes it easy to present matrices or data frames in a digestable and visually appealing manner. The tables allow for filtering, pagination, sorting, and a multitude of other elements.

Some of the most used and useful features are:

Pagination (Previous, next and page navigation)
Instant Search (Filter results by text search)
Multi-column ordering (Sort data by multiple columns at once)
Easily theme-able
Mobile friendly
Fully internationalisable (Easily translate DataTables into multiple languages)

Below I will include an explanation of how to use DT and customize your tables. For data, I will look at US minimum wages by state from 1968 to 2020. These data have observations for every year over several variables, but for simplicity we will only use a handful of the variables for the year 2020.

min.wage <- read.csv("Minimum Wage Data.csv")
min.wage <- min.wage %>% filter(Year == 2020)
min.wage <- select(min.wage, Year, State, State.Minimum.Wage, Federal.Minimum.Wage)

The main function in this package is datatable(). It creates an HTML widget to display R data objects with DataTables. Here is a “hello world” example with zero configuration:

min.wage %>% datatable()

Arguments

Making a Table Editable

You can enable table editing using the argument editable. The default is FALSE, but can be changed to TRUE(or “cell”) to enable editing for single cells, “row” to be able to edit a row, “column” to edit a column, or “all” to edit all cells on the current page of the table. The viewer will be able to double-click a cell to start editing values.

min.wage %>% datatable(editable = "cell")

You can even disable editing of certain regions of tables. For example:

min.wage %>% datatable(editable = list(target = "row", disable = list(columns = c(1,2))))

Customizing Column Names

By default, datatable() shows the column names of the data in the table, but you can use a custom character vector for the table header.

min.wage %>% datatable(colnames = c('Cool Name' = 3, 'Awesome Name' = 4))

By default, the column with the row names is blank. You might want to choose a name for that column.

min.wage %>% datatable(colnames = c('ID' = 1))

Adding Captions

Many times, tables are better understood when accompanied by a caption with a decription of the data being displayed. You can add a table caption via the caption argument.

min.wage %>% datatable(caption = "Table 1: This table shows minimum wages across states in 2020")

Filtering Columns

DataTables does not provide column filters by default. There is only a global filter (the search box on the top-right). We can add a filter argument in datatable() to automatically generate column filters. You can enable these filters by filter = 'top' or 'bottom', depending on whether you want to put the filters on the top or bottom of the table.

min.wage %>% datatable(filter = "top")

Depending on the type of a column, the filter control will be different. Initially, you see search boxes for all columns. When you click the search boxes, you see different controls:

For numeric/date/time columns, range sliders are used to filter rows within ranges
For factor columns, you can select one or multiple categories (note you can also type in the box to search in all categories)
For character columns, ordinary search boxes are used to match the values you typed in the boxes
If all the values in that column are the same, the filter will be disabled (This happens above for the Year and Federal Minimum Wage)

Adjusting the Number of Rows per Page

Very simply and self-explanatory, the pageLength argument allows you to set the default number of rows per page. The viewer can always change it in the top-left.

min.wage %>% datatable(options = list(pageLength = 7))

Exporting Data

One of my favorite aspects of the datatable function is the “Buttons” extension. This enables users to copy the table, save as a csv, excel or PDF file, or print the table. The table “remembers” what you’ve changed so far—so if you sort, filter, and select so that the copied/saved table will have these restrictions.

min.wage %>% datatable(extensions = "Buttons", 
                       options = list(dom = "Bftrip",buttons = c("copy","csv","excel","pdf","print")))

If one clicks copy, the table will be copied to your clipboard, “CSV”, “Excel”, and “PDF” will save the table to the give file type, and “print” will make the table into a print friendly format and will bring up the print dialog box.

Global Options

You might want to apply some or all of these options to all your tables in the same R session. Thankfully, you can use the global option named DT.options to do so. For example:

options(DT.options = list(pageLength = 10, filter = "top"))

Changing CSS classes

I did not go over it, but the class argument specifies the CSS classes of the table. The possible values can be found on the page of default styling options. The default value display basically enables row striping, row highlighting on mouse over, row borders, and highlighting ordered columns, but you can choose any combination (Ex: cell-border and stripe). Additionally, you can apply different CSS styles to cells according to their values. For example, use a red background for State.Minimum.Wage <= 7.25.

As always, if you have a question or a suggestion related to the topic covered in this article, please feel free to contact me!