R tip: Quick Interactive Tables

Hi, I’m Sharon Machlis at IDG Communications, here with Do More With R: Interactive tables with 1 line of code.
Tables you can sort and filter can be a good way to explore your data. They’re also handy when you want to share a data set, so other people can do some exploring. The R package DT (for Data Tables) makes creating such tables so easy. Let’s taek a look.
I’ll load 2 packages – DT, and rio for importing data. Next, I’ll import data about housing prices in 5 U.S. metro areas. This data is based on an index where every city’s home price starts at 100 in January of 1995, and then you can see the changes over time.
Let’s see what that data looks like.
This has data for every 2 years – 1st quarter of 1996, Q1 1998, and so on through the first quarter of 2018. There’s also a final column showing the change from that 100 starting index through Q1 2018. If you multiply that column by 100, you get the percent change.
Want that in an interactive table? Use DT’s datatable function.
Voila! A table. Let’s see what that looks like in a browser
Want to see which area had the highest price in 2010? You can sort by that column. One click sorts ascending; a second click sorts descending .
Would you like to just see San Francisco? Use the search box and there’s just the SanFran row.
There are a lot of other options for datatables. You can add filters with the filter argument
Now I’ll open the new table in a browser window.
I can filter for all values that are, say, above 250 in 2016
Don’t want the row numbers showing up? Use the rownames = FALSE argument.
Row numbers gone.
I can get rid of this paging menu. A dropdown that shows the first 10 rows doesn’t make sense when all the rows are already showing. I get rid of it by using the options argument, followed by a list, and then the paging option. See the format here in this line of code.
Now the paging menu is gone.
So. How do you know what options are available? And which ones are their own arguments to datatable, like filter and rownames, and which ones have to be in the options = list() argument? That confused me for awhile … until I finally broke down and read the help file for the datatable function
All the arguments that don’t need to be inside options = list are here – things like rownnames, colnames, filter. You can read about each argument in the help file. For what you can put inside the options list, the help file sends us somewhere else: Back to documentation for the datatables JavaScript library. That’s because DT is an R wrapper for the datatables JavaScript library. Click through
And you see all the available options you can use!
One more useful customization not here is formatting the columns. We can format the Change column so it displays as percents by using DT’s formatPercentage function. It’s a separate function, not an option or argument inside datatable.
You can see here that I’m piping the result of the initial table into the formatPercentage() function. Then that first argument in formatPercentage() is the name of my column, and the second one is how many digits I want to round to after the decimal place.
You can see that the Change column now displays as percents. There’s also a formatCurrency, which you can use to make a column show dollars and cents – or just add commas to numbers.
If you type DT::format in RStudio, you’ll see a dropdown list of other available DT format functions.
Want to save this table as an HTML file? You can with the HTML Widgets package.
I’ll load the htmlwidgets library, save the table to a variable called mytable, and then use the saveWidget function to save it to an HTML file called mytable.html.
Now I can open that mytable.html file in any browser.
Pretty cool.
That’s it for Episode 14 of Do More With R, thanks for watching! Next time, I’ll show you how to add a column of in-line mini graphs, called sparklines, to a data table. Meanwhile, for more R tips, head to the More With R video page at go.infoworld.com/morewithR. That’s https go dot infoworld dot com slash more with R, all lowercase except for the R. Or, you can add the “Do More With R” playlist to your YouTube library. So long, and hope to see you next episode for sparklines!