Interactive Tables Tutorial

This tutorial demonstrates how to create interactive tables like the one below using the DT library, which is documented at https://rstudio.github.io/DT/:

The DT library binds to the extremely popular JavaScript library, datatables.net. It is pretty much the defacto standard for interactive (responsive) tables, they have the added advantage of being able to paginate data in a variety of ways (including on a server-side engine). Any data.frame can be provided to datatables, and little effort must be put into pre-processing data ready for display in the table.

Import and Display Data

The data for this template is a .csv file accessed from Figshare here using read.csv.

desktopItems <- read.csv(file = "https://ndownloader.figshare.com/files/5360960", stringsAsFactors = F)
knitr::kable(head(desktopItems))

Timestamp	Desktop.Items	Operating.System	University.Department	University	Country
9/30/2015 13:07:58	5	Mac (OS X)	IT Services	University of Oxford	UK
11/06/2015 12:20	87	Linux	Physics	University of Durham	UK
11/06/2015 12:33	25	Windows 10	Physics	Queen’s University Belfast	UK
11/06/2015 12:46	20	Windows 7	Physics	University of Leeds	UK
11/06/2015 12:48	64	Windows 8	International Office	University of the West of England	UK
11/06/2015 12:50	34	Windows 7	Biology	King’s College London	UK

The first columns contain dates which need to be converted into a date format that datatable can handle, for which we use the lubridate library. Unfortunately, the dates are slightly complicated - they are formatted mm/dd/yyyy hh:mm:ss but not all entries have seconds. The argument truncated=2 allows us to handle these dates easily:

library(lubridate)
desktopItems$Timestamp <- mdy_hms(desktopItems$Timestamp, truncated = 2)
knitr::kable(head(desktopItems))

Timestamp	Desktop.Items	Operating.System	University.Department	University	Country
2015-09-30 13:07:58	5	Mac (OS X)	IT Services	University of Oxford	UK
2015-11-06 12:20:00	87	Linux	Physics	University of Durham	UK
2015-11-06 12:33:00	25	Windows 10	Physics	Queen’s University Belfast	UK
2015-11-06 12:46:00	20	Windows 7	Physics	University of Leeds	UK
2015-11-06 12:48:00	64	Windows 8	International Office	University of the West of England	UK
2015-11-06 12:50:00	34	Windows 7	Biology	King’s College London	UK

This data.frame can now be provided to datatable from the DT library for display, but note the poor formatting of the timestamp

library(DT)
datatable(desktopItems)

Column Formatting

There are a number of utility functions for formatting columns:

formatCurrency
formatPercentage
formatRound
formatDate

In the case of date, there are a number of different formats available, which are hidden in the symbol DateMethods, we will use toUTCString to format the data nicely.

DT:::DateMethods

## [1] "toDateString"       "toISOString"        "toLocaleDateString"
## [4] "toLocaleString"     "toLocaleTimeString" "toString"          
## [7] "toTimeString"       "toUTCString"

The format*() functions must be provided with both a datatable to operate on and a which columns to format, it is therefore convenient to use the %>% operator:

datatable(desktopItems) %>% formatDate(~Timestamp, method = "toUTCString")

Column Filters

The default datatable output includes a box in the top-right corner labelled “search” that allows the entire table to be filtered, but it’s often convenient to enable per column filtering. This is particularly useful for dates, the “Timestamp” column can now be filtered according to a date range. In addition to the column filters, the argument pageLength has been added to options to reduce the scrolling required to navigate through this tutorial.

datatable(desktopItems,
          filter = 'top',
          options = list(pageLength = 5)) %>% formatDate( ~ Timestamp, method = "toUTCString")

Please note that at present there is not a simple, non-JavaScript method known for formatting the date range filter.

Responsive Reflow

datatable(desktopItems,
          filter = 'top',
          options = list(pageLength = 5, responsive = TRUE, autoWidth = TRUE)) %>% formatDate( ~ Timestamp, method = "toUTCString")

Server Side Processing

Often datatables are used to present a small amount of data from a large (probably multiple tabled) database, in such cases it’s important not to download the entire dataset into the client’s browser and instead to rely on the server to paginate the data. The following example is from http://rstudio.github.io/DT/server.html.

There is a JSONP dataset provided by datatables.net that is setup to paginate server side here: https://datatables.net/examples/server_side/scripts/jsonp.php. In order to populate a datatable with the data we must build a data.frame with the right structure to contain the data:

container_df <- data.frame(
  "First name" = character(),
  "Last name" = character(),
  "Position" = character(),
  "Office" = character(),
  "Start date" = character(),
  "Salary" = numeric(),
  check.names = FALSE
  )

Datatable is instructed to use ajax to pull the data server side from the JSONP data source as follows:

datatable(container_df, rownames = FALSE, options = list(
  pageLength = 5,
      ajax = list(
        serverSide = TRUE, processing = TRUE,
        url = 'https://datatables.net/examples/server_side/scripts/jsonp.php',
        dataType = 'jsonp'
      )
    ))