This tutorial demonstrates how to create interactive tables like the one below using the DT and rhandsontable libraries. Note that there are three interactive shiny apps associated with this tutorial:
These libraries are useful for visualising almost all rectangular datasets, with support for a variety of data types (dates, strings, URLS, etc).
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 = FALSE)
## kable is loaded from the knitr library to convert the first few lines of the datafile into a Markdown table for display purposes
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, 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 |
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.
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)
Before beginning to format the columns of the datatable, it is useful to remove the rownames
as they are typically not that useful for users:
datatable(desktopItems,
rownames = FALSE)
There are a number of utility functions for formatting columns:
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. For further information about these formats, refer to JavaScript documentation, for instance the MDN documentation.
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 which columns to format, it is therefore convenient to use the %>%
operator:
datatable(desktopItems,
rownames = FALSE) %>%
formatDate(~Timestamp, method = "toUTCString")
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,
rownames = FALSE,
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.
Datatables decide a suitable width for them to display all column headings (by default) and do not resize with the page, meaning that the users need to scroll horizontally to read all of the table. This is problematic for two reasons: 1) tables may necessarily have many columns and would never fit on a screen, 2) users may view your table on mobile devices that are very narrow. The modern method of laying out web content well on different screen sizes is called “responsive design” - elements reflow or resize as the page size changes.
Responsive datatables will collapse the columns that cannot be displayed within the width of the host page into green “+” symbols that can be clicked to expand the record, as shown in this screenshot:
To enable this responsive behaviour, add the argument extensions = "Responsive"
; if you’re reading this on a big screen you’ll need to change the window size to see the collapsing columns.
datatable(
desktopItems,
rownames = FALSE,
filter = 'top',
extensions = "Responsive",
options = list(
pageLength = 5
)
) %>% formatDate(~ Timestamp, method = "toUTCString")
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'
)
))
The text displayed within datatables is highly customisable, two frequently desired changes to datatables are the “no records found” text displayed when no records match the filter criteria and changing the label of the “search field” as technically it is a filter and not a search operation. Most of these customisations are considered to be “internationalisation” concerns and therefore addressed through the “language” option of datatable
as discussed here.
In the table below both the global “search” label and empty records information has been modified, note the datatable has been filtered by the string “nonexistant” using the "search"
API as documented here.
datatable(
desktopItems,
rownames = FALSE,
filter = 'top',
options = list(
pageLength = 5,
responsive = TRUE,
autoWidth = TRUE,
"language" = list("search" = "Filter Records here -->",
"zeroRecords" = "There's absolutely nothing matching that filter"),
"search" = list("search" = "nonexistant")
)
) %>% formatDate(~ Timestamp, method = "toUTCString")
The rhandsontables
library binds to the handsontable which is designed for displaying interactive spreadsheets - with a focus on editing. Note that the handsontable.JS license is licensed such that educational use is free, but commercial use is not - refer to https://handsontable.com/ for details.
The data.frame storing our data can be provided to rhandsontable
for display, note that values in the table can be updated freely.
library(rhandsontable)
rhandsontable(desktopItems)
Editing can be turned off globally or for individual columns with hot_col
and the readOnly
argument, as with most htmlwidgets
the %>%
(“pipe”) operator is used to modify existing output:
rhandsontable(desktopItems) %>% hot_col("Desktop.Items", readOnly = TRUE)
In this document we are only concerend with displaying data, we therefore do not discuss further customisation of rhandsontable
beyond highlighting the selected row and column with the highlight*
argument to hot_table
and enabling sorting of columns (double-click on headings)
rhandsontable(desktopItems) %>% hot_col("Desktop.Items", readOnly = TRUE) %>%
hot_table(highlightCol = TRUE, highlightRow = TRUE) %>%
hot_cols(columnSorting = TRUE)
A shiny app containing interactive versions of the datatables above is available here: https://livedataoxford.shinyapps.io/htmlwidget_template_datatables/.
The following types of interaction are supported: