DT library tutorial

2019-10-01 1174 words 6 minutes

Background

Purpose

Show examples of the powerful javascript library DataTables with R package DT
These tables are displayed in HTML and I routinely use this package in my analysis reports
The tables are interactive! One can easily:
- Sort & search
- Add column filters based on the datatype
- Add plug-ins so one can download (as excel, csv, pdf, etc.) directly from the table.
  - This also keeps any changes you have made!
- embed html links in the table
This tutorial was based off of the DT github page.
This tutorial is meant to show options I find useful in my reports. Please see the github page for more details!

Load libraries

# load libraries 
library(knitr)

## Warning: package 'knitr' was built under R version 3.6.3

library(pander)
library(DT)

## Warning: package 'DT' was built under R version 3.6.3

# Set up code chunk options
opts_chunk$set(echo = TRUE, message = TRUE, results = 'markup', warning = FALSE)

Tutorial

Basics

Default table

Let’s start with a basic example: comparing print() to the default of DT::datatable()

print(head(mtcars))

##                    mpg cyl disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
## Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
## Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
## Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

datatable(mtcars)

The default settings includes a search bar, page lengths of 10 and an easy way to sort each column.
Rows will be numbered if there are no rownames.
Already much better!

Page length & Rownames

The default is to show 10 rows at a time. You can change that with the pageLength option.
Rownames are displayed by default and will be numbered if there are no given rownames. One can remove rownames by adding rownames = FALSE.
Many options, pageLength being one of them, are included using the options list. rownames is not.
See below:

# 5 rows, no rownames 
datatable(mtcars, options = list(pageLength = 5), rownames = FALSE)

Sorting

It is easy to pre-sort columns with the options order
You can sort by multiple columns at a time
Can sort by both ascending and descending (default is ascending)
Not the arrows show by the column headers are pointing in the direction of sorting

# sort by the 3rd column
datatable(mtcars, options = list(
  order = list(3)
))

# sort by column 3 by ascending order and column 5 by descending order
# sort by the 3rd column
datatable(mtcars, options = list(
  order = list(list(3, 'asc'), list(5, 'desc'))
))

Column filters

Numeric columns are filtered may be filtered with a slider
Columns of class factor are filtered with a dropdown menu

datatable(iris, filter = 'top', options = list(pageLength = 5))

Download table (buttons)

This is done by adding the “buttons” extension
I’ve added a button to:
- Copy
- Download as a csv
- Download as an excel file
- Download as a pdf
- Print
The downloaded/copied table will remeber the changes you’ve made, i.e., sorted or filtered the table

# 5 rows + buttons 
datatable(iris, extensions = 'Buttons',
          options = list(pageLength = 5,
                         dom = 'Bfrtip',
                         buttons = c('copy', 'csv', 'excel', 'pdf', 'print')))

Add links

Say you made a dataframe with links you want to work in your html report
- For example: a dataframe of variants w/ links to their position in a genome browser
This is done through not escaping content in the table, specifically the column with the links.
The links are made with html and must not be escaped to show up. This applies to other html as well.
For me, it was confusing that I had to not escape the html columns. Got it completely backwards the first time I tried it.

# Make dataframe
df.link <- data.frame(school=c("OSU", "UO", "Linfield", "Willamette"), 
                      mascot=c("beavers", "ducks", "wildcats", "bearcats"),
                      website=c('<a href="http://oregonstate.edu/">oregonstate.edu</a>',
                                '<a href="https://www.uoregon.edu/">uoregon.edu</a>',
                                '<a href="https://www.linfield.edu/">linfield.edu</a>',
                                '<a href="https://www.willamette.edu/">willamette.edu</a>'),
                      School_colors=c('<span style="color:orange">orange & black</span>', 
                                        '<span style="color:green">green & yellow</span>', 
                                        '<span style="color:purple">purple and red</span>', 
                                        '<span style="color:red">red and yellow</span>'))
# See html is not working:
datatable(df.link)

# When the html columns, 3 & 4, are not escaped, it works!
datatable(df.link, escape = c(1,2,3))

R-session Information

capture.output(sessionInfo())

##  [1] "R version 3.6.1 (2019-07-05)"                                                
##  [2] "Platform: x86_64-w64-mingw32/x64 (64-bit)"                                   
##  [3] "Running under: Windows 10 x64 (build 18363)"                                 
##  [4] ""                                                                            
##  [5] "Matrix products: default"                                                    
##  [6] ""                                                                            
##  [7] "locale:"                                                                     
##  [8] "[1] LC_COLLATE=English_United States.1252 "                                  
##  [9] "[2] LC_CTYPE=English_United States.1252   "                                  
## [10] "[3] LC_MONETARY=English_United States.1252"                                  
## [11] "[4] LC_NUMERIC=C                          "                                  
## [12] "[5] LC_TIME=English_United States.1252    "                                  
## [13] ""                                                                            
## [14] "attached base packages:"                                                     
## [15] "[1] stats     graphics  grDevices utils     datasets  methods   base     "   
## [16] ""                                                                            
## [17] "other attached packages:"                                                    
## [18] "[1] DT_0.12      pander_0.6.3 knitr_1.28  "                                  
## [19] ""                                                                            
## [20] "loaded via a namespace (and not attached):"                                  
## [21] " [1] Rcpp_1.0.4.6      bookdown_0.18     digest_0.6.25     mime_0.9         "
## [22] " [5] R6_2.4.1          jsonlite_1.6.1    magrittr_1.5      evaluate_0.14    "
## [23] " [9] blogdown_0.18     rlang_0.4.5       stringi_1.4.6     rmarkdown_2.1    "
## [24] "[13] tools_3.6.1       stringr_1.4.0     htmlwidgets_1.5.1 crosstalk_1.1.0.1"
## [25] "[17] xfun_0.12         yaml_2.2.1        compiler_3.6.1    base64enc_0.1-3  "
## [26] "[21] htmltools_0.4.0  "