Interactive tables with sort and filter capabilities 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 easy.
Creating data tables
First, I loaded two packages: DT and rio for importing data.
Next, I imported data about housing prices in five US metro areas. This data is based on an index where every city’s home price starts at 100 in January 1995, and then you can see the changes over time. There’s code at the bottom of this article if you want to use the same data to create a data frame named prices and follow along.
The data includes one price point for every two years—first 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 were to multiply that column by 100, it would be the percent change.
To make that into an interactive HTML table, I’d use DT’s
datatable() function. It’s as easy as
datatable(prices) and voilà!: a table that should look something like:
A table created with the R DT package
You can sort by any column: One click sorts ascending, and a second click sorts descending. The search box lets you filter rows.
There are a lot of other options for
You can add filters for each column with the
filter argument (note: the search box looks for text across all columns)
datatable(prices, filter = 'top')
Hiding row numbers
If you don’t want the row numbers showing up, use the
rownames = FALSE argument:
datatable(prices, filter = 'top', rownames = FALSE)
The row numbers should be gone.
You can get rid of the paging menu as well, which seems like a good idea because all the rows are already displaying. For this task, you need
options argument, which takes a list as its value. See the format:
datatable(prices, filter = 'top', options = list(paging = FALSE))
If you run the code, the paging menu should be gone.
How do you know what options are available? And which ones are their own arguments to datatable, like
rownames, and which ones have to be in the
options = list() argument? That confused me for a while—until I finally broke down and read the help file for the
All the arguments that don’t need to be inside
options = list() should be listed in the help file, which you can see by running
?datatable. For information about what you can put inside the
options list, however, the help file sends you somewhere else: to documentation for the
Formatting the columns is another type of useful customization—for example, formatting the Change column so it displays as percents. DT has several column format functions, including
formatCurrency(). These are separate functions, not an option or argument inside
datatable, and they take a data table as their first argument.
In the following code, I pipe the results from the initial
datatable() into the
formatPercentage() function. Then I tell
formatPercentage() first the name of my column to be formatted and then how many digits I want to round to after the decimal place.
datatable(prices, filter = 'top', options = list(paging = FALSE)) %>%
formatPercentage('Change', digits = 1)
Run that code to see the Change column display as percents.
If you type
DT::format in RStudio and pause, you’ll see a dropdown list of other available
DT format functions.
Saving as an HTML table
Do you want to save this table as an HTML file? You can with the HTML Widgets package.
The code below loads the htmlwidgets library, saves the table to a variable called
mytable, and then uses the
saveWidget function to save it to an HTML file called mytable.html.
mytable <- datatable(prices, filter = 'top', options = list(paging = FALSE)) %>%
formatPercentage('Change', digits = 1)
mytable.html is a conventional HTML file that can be opened in any browser and used on the web.
Code for the home prices data frame
prices <- data.frame(stringsAsFactors=FALSE,
MetroArea = c("Boston", "Detroit", "Phil", "SanFran", "SiValley"),
Q1_1996 = c(106.44, 107.99, 105.25, 100.72, 102.93),
Q1_1998 = c(116.78, 127.83, 107.15, 117.25, 126.01),
Q1_2000 = c(148.58, 150.8, 111.73, 159.11, 170.74),
Q1_2002 = c(189.41, 170.34, 132.86, 195.5, 205.14),
Q1_2004 = c(234.68, 181.89, 164.54, 223.02, 223.33),
Q1_2006 = c(272.14, 186.43, 219.74, 305.88, 311.17),
Q1_2008 = c(253.33, 158.29, 234.13, 291.35, 293.01),
Q1_2010 = c(227.91, 117.45, 219.46, 248.28, 238.12),
Q1_2012 = c(224.55, 111.14, 211.24, 238.37, 233),
Q1_2014 = c(237.61, 130.59, 214.87, 306.24, 300.89),
Q1_2016 = c(264.23, 148.26, 227.5, 387.34, 367.1),
Q1_2018 = c(300.96, 170.92, 258.49, 447.45, 428.45),
Change = c(2.01, 0.709, 1.585, 3.474, 3.284)