(Fake data used in this post generated by Mockaroo.)
All I wanted was a reactive data table with persistent filters. I spent hours of my life so that, you, dear reader, can have an easier time than I did creating a live data table in R Shiny. Here is some basic R code for making a data table with DT and displaying it in Shiny:
library(shiny) # Shiny web app
library(DT) # for data tables
# user interface just shows the table
ui <- fluidPage(fluidRow(column(12, div(dataTableOutput("dataTable")))))
# server is where all calculations are done, tables are pre-rendered
server <- function(input, output, session) {
# load CSV file
myCSV <- read.csv('www/MOCK_DATA.csv')
#-----------------------------------------------------------------------------
# render data table
#-----------------------------------------------------------------------------
output$dataTable <- renderDT(
myCSV, # data
class = "display nowrap compact", # style
filter = "top" # location of column filters
)
}
# run the app
shinyApp(ui, server)
Here's what that code looks like when it's rendered in R (all images in this post link to Shiny apps hosted on shinyapps.io):
(Here you can learn how to screenshot a web page in Chrome without adding any browser extensions.)
Looks great, right? There is a "global filter" (the search bar at the top-right) and there are "column filters" above each column. The global filter searches for a particular string within any cells and returns the rows which contain those cells, while the column filters let you restrict the entries you see based on ranges, factors, or sets of values.
But what if your data is changing? It's not uncommon for a user-facing application like this to access data which is currently in a state of flux. New records could be added or removed as the user is staring at the table. In that case, we need to change the code a bit and use a reactiveFileReader, which will check a file for changes on a predefined interval. Let's edit the code to do that. Also, let's allow the table to scroll in the horizontal direction so it doesn't get cut off like it did above:
library(shiny) # Shiny web app
library(DT) # for data tables
# user interface just shows the table
ui <- fluidPage(fluidRow(column(12, div(dataTableOutput("dataTable")))))
# server is where all calculations are done, tables are pre-rendered
server <- function(input, output, session) {
# load reactive CSV file
myCSV <- reactiveFileReader(100, session, 'MOCK_DATA.csv', read.csv)
#-----------------------------------------------------------------------------
# render data table
#-----------------------------------------------------------------------------
output$dataTable <- renderDT(
myCSV(), # reactive data
class = "display nowrap compact", # style
filter = "top", # location of column filters
options = list( # options
scrollX = TRUE # allow user to scroll wide tables horizontally
)
)
}
# run the app
shinyApp(ui, server)
Note, in particular, the reactiveFileReader and the fact that the data in renderDT() must now be followed by a pair of parentheses (), as it is now a reactive object. We now have a horizontal scrollbar, and our app will react to any changes in the data file:
Fantastic! What else can we do? Well, we can add some default search terms:
library(shiny) # Shiny web app
library(DT) # for data tables
# default global search value
if (!exists("default_search")) default_search <- ""
# default column search values
if (!exists("default_search_columns")) default_search_columns <- NULL
# user interface just shows the table
ui <- fluidPage(fluidRow(column(12, div(dataTableOutput("dataTable")))))
# server is where all calculations are done, tables are pre-rendered
server <- function(input, output, session) {
# load reactive CSV file
myCSV <- reactiveFileReader(100, session, 'MOCK_DATA.csv', read.csv)
#-----------------------------------------------------------------------------
# render data table
#-----------------------------------------------------------------------------
output$dataTable <- renderDT(
myCSV(), # reactive data
class = "display nowrap compact", # style
filter = "top", # location of column filters
options = list( # options
scrollX = TRUE, # allow user to scroll wide tables horizontally
# default column search strings and global search string
searchCols = default_search_columns,
search = list(regex = FALSE, caseInsensitive = FALSE, search = default_search)
)
)
}
# run the app
shinyApp(ui, server)
The problem with that is that these terms aren't persistent when the table refreshes. They are replaced by the default values whenever MOCK_DATA.csv is updated:
The prescribed solution to this is to set stateSave = TRUE within the options list of renderDT() and access the DataTable information with the variables defined in section 2.2 of Shiny's DT guide. This works, but only if you spend hours trying to figure out what is refreshed when. I've done that hard, hair-pulling work for you, so I'm not going to make this a pedagogical exercise, I'm going to do what I wish someone else had done before me and just give you the answer. Here's how you maintain those filters when the data file is refreshed:
library(shiny) # Shiny web app
library(DT) # for data tables
# default global search value
if (!exists("default_search")) default_search <- ""
# default column search values
if (!exists("default_search_columns")) default_search_columns <- NULL
# user interface just shows the table
ui <- fluidPage(fluidRow(column(12, div(dataTableOutput("dataTable")))))
# server is where all calculations are done, tables are pre-rendered
server <- function(input, output, session) {
# load reactive CSV file
myCSV <- reactiveFileReader(100, session, 'MOCK_DATA.csv', read.csv)
#-----------------------------------------------------------------------------
# update search strings
#-----------------------------------------------------------------------------
# watch for when myCSV updates, i.e. when the file changes
observe({ myCSV()
# when it updates, save the search strings so they're not lost
isolate({
# update global search and column search strings
default_search <- input$dataTable_search
default_search_columns <- c("", input$dataTable_search_columns)
# update the search terms on the proxy table (see below)
proxy %>% updateSearch(keywords =
list(global = default_search, columns = default_search_columns))
})
})
#-----------------------------------------------------------------------------
# render data table
#-----------------------------------------------------------------------------
output$dataTable <- renderDT(
myCSV(), # reactive data
class = "display nowrap compact", # style
filter = "top", # location of column filters
options = list( # options
scrollX = TRUE, # allow user to scroll wide tables horizontally
stateSave = FALSE,
# default column search strings and global search string
searchCols = default_search_columns,
search = list(regex = FALSE, caseInsensitive = FALSE, search = default_search)
)
)
# make a proxy of the data table so it can be edited after it's been rendered
proxy <- dataTableProxy('dataTable')
}
# run the app
shinyApp(ui, server)
And here it is running on shinyapps.io:
Note that it's probably easier if you copy-and-paste the above code into RStudio and download a mock data file from Mockaroo and do this locally. To "update" the file, you don't need to add or remove lines, just open it and save it. That will update the "last modified time" on disk and the reactiveFileReader will reload the file.
Explanation
Let's step through this piece-by-piece:
library(shiny) # Shiny web app
library(DT) # for data tables
These are the libraries we need to make a Shiny application in R and make a DataTable object (ported to R from JavaScript) in that application.
# default global search value
if (!exists("default_search")) default_search <- ""
# default column search values
if (!exists("default_search_columns")) default_search_columns <- NULL
These are our default values for the global filter and column filters. The global filter is a single string, but the column filters are a list of lists of the form:
searchCols = list(NULL, list(search = "1 ... 2"), list(search = '["Male"]'), ...
...where ranges are in the format A ... B and individual terms or factors are in the format '["X"]'. If a column is currently not being filtered, it's NULL. We can have a default setting of no column filters at all by setting searchCols = NULL, which is what we do in the final version of the script above.
# user interface just shows the table
ui <- fluidPage(fluidRow(column(12, div(dataTableOutput("dataTable")))))
This is the entire user interface (ui) -- just a single column of width 12 (out of 12) in a single row on the page. The table is contained within a div. The HTML generated by this R code is just:
<div class="container-fluid">
<div class="row">
<div class="col-sm-12">
<div>
<div id="dataTable" style="width:100%; height:auto; " class="datatables html-widget html-widget-output"></div>
</div>
</div>
</div>
</div>
...a div containing a data table, within a column, within a row, within a fluid page. That's it!
# server is where all calculations are done, tables are pre-rendered
server <- function(input, output, session) {
This line opens the definition of the server object, which is called when a new user tries to access the app. Note that the default searches, defined outside server are called only once, when the Shiny app is first run. Multiple users can access that same instance of the running app and so these objects are not instantiated multiple times. (Learn more about Shiny scoping.)
# load reactive CSV file
myCSV <- reactiveFileReader(100, session, 'MOCK_DATA.csv', read.csv)
Here is where we initialize the reactive CSV file. It's checked for updates every 100 milliseconds and it must be tied to a session -- in other words, it must be run within the server. The file we want to look at is MOCK_DATA.csv and the method we'll use to read that data is read.csv.
# watch for when myCSV updates, i.e. when the file changes
observe({ myCSV()
# when it updates, save the search strings so they're not lost
isolate({
# update global search and column search strings
default_search <- input$dataTable_search
default_search_columns <- c("", input$dataTable_search_columns)
# update the search terms on the proxy table (see below)
proxy %>% updateSearch(keywords =
list(global = default_search, columns = default_search_columns))
})
})
This block is the most important bit of this entire script. observe() is a reactive expression that returns no value. It is dependent on every reactive value within the () except ones within isolate() calls. This means that the above block updates when myCSV() updates and only when myCSV() updates. (And not, for instance, when input$dataTable_search updates.) When the file changes, then, the observe() block is triggered and the isolate() block within is executed; the default filters are updated with the values currently on the table and the proxy table (more on this later) is updated with those filter terms.
output$dataTable <- renderDT(
myCSV(), # reactive data
class = "display nowrap compact", # style
filter = "top", # location of column filters
options = list( # options
scrollX = TRUE, # allow user to scroll wide tables horizontally
stateSave = FALSE,
# default column search strings and global search string
searchCols = default_search_columns,
search = list(regex = FALSE, caseInsensitive = FALSE, search = default_search)
)
)
This is the block that renders the DataTable. The first argument is the (reactive) data set, followed by some styling (class) and the location of the filter boxes relative to the columns (on top of them). options is a list of options for rendering the table -- we allow the user to scroll horizontally if the table is too wide to fit on the web page (scrollX) and we don't save the state of the table (stateSave). The latter option means that the column filters aren't automatically reapplied. When this is set to true, sometimes the filters will be reapplied on the server side, but won't appear on the client side: it will look like there are no filters applied, but the table will say something like "Showing _ to __ of ___ entries (filtered from _____ total entries)".
The final two options are our default column filters (or "search") and our default global filter. These aren't strictly necessary here, but they're left in to show what we accessed within the proxy table when we manually set the filters previously.
# make a proxy of the data table so it can be edited after it's been rendered
proxy <- dataTableProxy('dataTable')
This bit is the proxy data table with which we can edit the table on the client-side, doing things like updating the table caption and editing the filters with updateSearch(). Since the filters are applied to the data on the server side, but applied to the UI on the client side, we need to manage both of these manually if we want to programatically populate the filters. Note that none of this is necessary if the user manually changes the filters within the UI because the client handles the graphical updates and the server is listening for those changes and updates the data accordingly.
# run the app
shinyApp(ui, server)
Finally, this bit takes the ui and server objects and weaves them together, creating the
Help
This is gross. The dataTableProxy has a method called reloadData() which, in my mind, should reload the data without changing the filters. But as far as I can tell, the steps I've outlined above are the only way to do this. And it's ugly. I spent two days trying to sift through documentation and error reports and random google debris and this is the best solution I've come up with. If anyone knows of an easier way to make a reactive table in R with persistent filtering, I'm all ears!
Oldest comments (3)
Awesome !
Outstanding!!! Thank you.
Q1. Would you have any suggestions on how to fix the column display to 8 columns?
Q2. Would you have any suggestions on how to post data bi-directionally after edit? Please see:
community.rstudio.com/t/bi-directi...
Awesome! I am trying to get replaceData to work too, but it's not. I followed your example and it still didn't work. I have a stackoverflow post here:
stackoverflow.com/questions/568796...