Sortable, searchable, paginated tables with knitr and jquery.DataTables

Creating reports with knitr is becoming increasingly popular. Those documents are beautiful because they tightly link code to results. One drawback is that they lack an interactive element compared to server-client solutions like shiny. More recently java-script based solutions like rcharts, plotly, htmlwidgets, SortableHTMLTables have started to overcome this limitation and allow for dynamic interactive reporting using R-Markdown.

One popular way to add interactivity to html tables in the web-development community, is to use the powerful and elegant DataTable module, which is a plug-in for the jQuery. In this article we show how knitr and DataTables can be mixed to generated sortable, searchable, paginated tables using just Rmarkdown.

Setup

Somewhere at the beginning of our Rmd we need to setup the jquery/DataTable infrastructure by simply including the following lines

<link rel="stylesheet" type="text/css" href="http://cdn.datatables.net/1.10.5/css/jquery.dataTables.min.css">
<script src="http://code.jquery.com/jquery-2.1.2.min.js"></script>
<script src="http://cdn.datatables.net/1.10.5/js/jquery.dataTables.min.js"></script>

<script type="text/javascript">
         $(document).ready(function() {
             $("table").DataTable();
         } );
</script>

To get started we need data, thus we load ggplot and for convenience also dplyr.

require(dplyr)
require(ggplot2)

Now we can create sortable, paginated, searchable tables by adding a chunk with the option results='asis'

iris %>%  kable()

The scheme is not limited to a single table, but we can embed as many as we like

mtcars %>% kable()

Links

To also allow for interactive links we have to render html with kable without escaping the data:

iris %>%
    mutate(Species=paste0("<a href='https://www.google.com/?q=",Species,"'>",Species,"</a>")) %>%
    kable("html", escape=F)

Selective interactivity

Most of the time we do not need interactivity for all tables, but just for a subset for them. To achieve this, the jquery selector in the initialization method is changed to $(".dtable") and we decorate the tables that should be interactive with a dtable class attribute:

iris %>% kable("html", table.attr = "class='dtable'", escape=F)

Using this scheme we can now selectively render tables as simple html or if needed as dynamic DataTables.

Wrap up

By mixing jquery with knitr we’ve created a simplistic but yet very powerful scheme to include dynamic tables into R Markdown documents. Not covered here, but natural next steps, could be a function wrapper for kable to streamline the inclusion of the jquery libraries, or more DataTable customizations to allow for columnn filters, toggable column visibility, or to include any of the DataTable extensions. In most cases those will require almost no coding effort and are almost invisible when writing the R Markdown.

Update

Since I’ve written this blog post the folks from RStudio have released DT package which essentially hides the logic described in this article. However, at the moment it seems quite tied to rstudio so I think it’s still interesting to understand the undlying mechanism (which is what this article was about).