{"id":64,"date":"2019-08-06T13:50:53","date_gmt":"2019-08-06T13:50:53","guid":{"rendered":"http:\/\/blogs.oregonstate.edu\/cgrb\/?p=64"},"modified":"2024-09-18T13:36:15","modified_gmt":"2024-09-18T20:36:15","slug":"r-tips-a-table-makeover-with-dt","status":"publish","type":"post","link":"https:\/\/blogs.oregonstate.edu\/cqls\/2019\/08\/06\/r-tips-a-table-makeover-with-dt\/","title":{"rendered":"R-tips: A Table Makeover with DT"},"content":{"rendered":"\n

R\u2019s default print function for data frames and matrices is not an effective way to display the contents, especially in a html report. RStudio created a R package, DT<\/a>, that is an interface to the JavaScript library DataTable. DT allows users to have interactive tables that includes searching, sorting, filtering and exporting! I routinely use these tables in my analysis reports. <\/p>\n\n\n\n

Install the DT package from cran <\/h2>\n\n\n\n

First, one must install and load the DT package. Open up RStudio and run the following commands to install and load the DT package: <\/p>\n\n\n

\n# Install the DT package\ninstall.packages("DT")\n# Load the DT package\nlibrary(DT)\n<\/pre><\/div>\n\n\n
 Example Table <\/h2>\n\n\n\n
The print function is not the most effective was to display a table in an HTML R Markdown report. <\/p>\n\n\n
\nprint(head(mtcars))\n<\/pre><\/div>\n\n\n
\"This<\/figure>\n\n\n\n
Now let’s look at the datatable <\/code>function for comparison. The input to the datatable <\/code>function is a data frame or matrix. Let\u2019s make a table with the preloaded iris data that\u2019s in a data.frame. The basic call is DT::datatable(iris)<\/code> but in our example I\u2019ve added the filter option to the top of the table, and limited the number of entries to 5 per table. See code and table features below:   <\/p>\n\n\n
\ndatatable(iris, filter = "top", \n          options = list(pageLength = 5))\n<\/pre><\/div>\n\n\n
 A screen shot of the output looks like: <\/p>\n\n\n\n
\"This<\/figure>\n\n\n\n
Already, the readability is much better than the base r function print. This is a JavaScript based table, stored in a HTML widget, so a flat image doesn’t convey all of the interactive features.  <\/p>\n\n\n\n
Features<\/h2>\n\n\n\n
NUMBER OF ENTRIES TO DISPLAY<\/h3>\n\n\n\n
You’ll notice that there is a drop down menu that says: “Show 5 entries”.  The default is 10, but I specified 5 as default with the code pageLength=5<\/code>. One may select the number of entries to show by using the drop down menu like so: <\/p>\n\n\n\n
\"This<\/figure>\n\n\n\n
SEARCH BAR<\/h3>\n\n\n\n
The widget also includes a search bar on the top right corner which can be very useful when interactively exploring data. Note at the bottom of the table it shows you how many entries (rows) were found and are being displayed.<\/p>\n\n\n\n
\"This<\/figure>\n\n\n\n
\"This<\/figure>\n\n\n\n
SORT COLUMNS<\/h3>\n\n\n\n
Notice that to the right of each column name are two arrows: \"\"  One may sort by ascending or descending order and the direction of the blue arrow indicates by which direction you sorted the column.  <\/p>\n\n\n\n
\"This<\/figure>\n\n\n\n
FILTER COLUMNS<\/h3>\n\n\n\n
The datatable<\/code> function also allows users to filter each column depending on the datatype:  filter numeric columns<\/strong> with a slider &  filter columns of class factor <\/strong>with a drop down menu. One must add the filter = \"top\"<\/code> (or bottom, etc.) to the code to enable this feature. <\/p>\n\n\n\n
\"This
Numeric columns have sliders<\/figcaption><\/figure>\n\n\n\n
\"This
Columns of class factor have a drop down menu<\/figcaption><\/figure>\n\n\n\n
Export Data<\/h2>\n\n\n\n
Another useful aspect of the datatable function is the \u201cButtons\u201d extension. This enables users to copy the table, save as a csv, excel or PDF file, or print the table. The table \u201cremembers\u201d what you\u2019ve changed so far\u2014so if you sort by Sepal Length, filter pedal width to > 1 and select species \u201cversicolor\u201d the copied\/saved table will have these same restrictions.<\/p>\n\n\n
\ndatatable(iris, \n          extensions = 'Buttons',\n          options = list(dom = 'Bfrtip', \n                         buttons = c('copy', 'csv', 'excel', 'pdf', 'print'))\n<\/pre><\/div>\n\n\n
The above code adds “buttons” to the top of the table like so: <\/p>\n\n\n\n
\"This<\/figure>\n\n\n\n
If one clicks “copy”, the table will be copied to your clipboard, “CSV” or “PDF” will save the table to the give file type, and “print” will bring put the table into a print friendly format and will bring up the print dialog box.<\/p>\n\n\n\n
Links and Color<\/h2>\n\n\n\n
One may also have links in their table.  Say you made a data frame with links you want to work in your html report. For example: a data frame 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<\/strong> be escaped to show up. This applies to other html as well; including color. For me, it was confusing that I had to not<\/strong> escape the html columns. Got it completely backwards the first time I tried it. NOTE<\/strong>: ><\/code> got replaced with  “& gt;”  (with no spaces) when it is rendered on the blog… Need to find a fix! <\/p>\n\n\n
\n# Make dataframe\ndf.link <- data.frame(school=c("OSU", "UO", "Linfield", "Willamette"), \n                      mascot=c("beavers", "ducks", "wildcats", "bearcats"),\n                      website=c('<a href="http:\/\/oregonstate.edu\/">oregonstate.edu<\/a>',\n                                '<a href="https:\/\/www.uoregon.edu\/">uoregon.edu<\/a>',\n                                '<a href="https:\/\/www.linfield.edu\/">linfield.edu<\/a>',\n                                '<a href="https:\/\/www.willamette.edu\/">willamette.edu<\/a>'),\n                      School_colors=c('<span style="color:orange">orange & black<\/span>', \n                                        '<span style="color:green">green & yellow<\/span>', \n                                        '<span style="color:purple">purple and red<\/span>', \n                                        '<span style="color:red">red and yellow<\/span>'))\n\n# When the html columns, 3 & 4, are not escaped, it works!\ndatatable(df.link, escape = c(1,2,3))\n<\/pre><\/div>\n\n\n
\"This<\/figure>\n\n\n\n
Column Visibility<\/h2>\n\n\n\n
One may also hide columns from visibility and add a button to add the column back interactively.  For example, say we have a data frame called sv.all.i.in<\/code>. We can hide columns 3 and 4, which are long sequences and disrupt the readability of the table, with the following code: <\/p>\n\n\n
\ndatatable(sv.all.i.in, extensions = 'Buttons',\n          options = list(dom = 'Bfrtip', columnDefs = list(list(visible=FALSE, targets=c(3,4))), \n                         buttons = list(I('colvis'),c('copy', 'csv', 'excel', 'pdf', 'print'))))\n<\/pre><\/div>\n\n\n
\"This<\/figure>\n\n\n\n
Learn More<\/h2>\n\n\n\n
There are many more useful features that you can add to your datatable<\/code>! Learn more here:  https:\/\/rstudio.github.io\/DT\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
R\u2019s default print function for data frames and matrices is not an effective way to display the contents, especially in a html report. RStudio created a R package, DT, that is an interface to the JavaScript library DataTable. DT allows users to have interactive tables that includes searching, sorting, filtering and exporting! I routinely use… Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":9584,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1370809,2991,1370810,1370811,1370806],"tags":[712761,1414,1365448,913],"class_list":["post-64","post","type-post","status-publish","format-standard","hentry","category-bioinformatics","category-documentation","category-r","category-rmarkdown","category-tutorial","tag-r","tag-report","tag-rmarkdown","tag-tutorial"],"_links":{"self":[{"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/posts\/64","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/users\/9584"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/comments?post=64"}],"version-history":[{"count":13,"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/posts\/64\/revisions"}],"predecessor-version":[{"id":77,"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/posts\/64\/revisions\/77"}],"wp:attachment":[{"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/media?parent=64"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/categories?post=64"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.oregonstate.edu\/cqls\/wp-json\/wp\/v2\/tags?post=64"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}