How to download data from renderDataTable filters to a .csv

[jrcha...@gmail.com]

For this question, I am using the R Shiny tutorial example found here:

http://rstudio.github.io/shiny/tutorial/#datatables

Running the code on this tutorial renders the application at the following URL

http://glimmer.rstudio.com/yihui/12_datatables/

What I would like to know is, once that data table is rendered, we can search it using the functionality built into the renderDataTable() function in R Shiny, but is it possible to download the data you have filtered to using the renderDataTable() function?

For instance, if in the data table search bar I type, "Very Good," only the records in the "cut" field which read "Very Good" are displayed. How would I then download that data set?

[Kevin Lindquist]

Hello,

I know it has been a while since this was posted, but I was interested in the same functionality. I managed to get it working with the following code. It takes some knowledge of jQuery and Javascript to put it all together.

ui.R

library(shiny)

shinyServer(function(input, output) {

    Dataset <- reactive({
        df <- mtcars
        # If the dataframe used to create the DataTable
        # happens to contain NA values, they need to
        # be replaced with an empty string. This ensures
        # that the array returned from the datatables
        # underscore method (i.e. the data contained in
        # input$filtered_table) contains an entry in every
        # cell. Otherwise the ProcessedFilteredData routine
        # will result in corrupted data
        df[is.na(df)] <- ""
        return(df)
    })

    output$data_table <- renderDataTable({
        Dataset()
    }, options = list(sDom = "ilftpr"))

    ProcessedFilteredData <- reactive({
        v <- input$filtered_table
        # This code assumes that there is an entry for every
        # cell in the table (see note above about replacing
        # NA values with the empty string).
        col_names <- names(Dataset())
        n_cols <- length(col_names)
        n_row <- length(v)/n_cols
        m <- matrix(v, ncol = n_cols, byrow = TRUE)
        df <- data.frame(m)
        names(df) <- col_names
        return(df)
    })

    output$downloadData <- downloadHandler(
        filename = function() { 'filtered_data.csv' }, content = function(file) {
            write.csv(ProcessedFilteredData(), file, row.names = FALSE)
        }
    )
})



server.R

library(shiny)

shinyUI(fluidPage(

    tags$head(
        tags$style(type="text/css", "tfoot {display: table-header-group}") # Move filters to top of datatable
    ),

    titlePanel("Filtered Data CSV Download"),
        sidebarLayout(
            sidebarPanel(
                HTML('
                    <script type="text/javascript">
                        $(document).ready(function() {
                            $("#downloadData").click(function() {
                                var filtered_table_data = $("#DataTables_Table_0").dataTable()._("tr", {"filter":"applied"});
                                Shiny.onInputChange("filtered_table", filtered_table_data);
                            });
                        });
                   </script>
                '),

                downloadButton('downloadData', 'Download Filtered Data')
            ),
            mainPanel(
                dataTableOutput("data_table")
            )
        )
))

Also, if you have more than one Datatable on your page you may need to change the 0 inside the "#DataTables_Table_0" expression to a different number.

-Kevin

[Herman Sontrop]

Hey Kevin,

very useful to be able to download filtered tables like this! small remark: in your post you mixed up the ui.r and server.r scripts ;-)

best Herman


Op dinsdag 27 mei 2014 07:53:08 UTC+2 schreef Kevin Lindquist:

[Kevin Lindquist]

Hi,

Thanks for pointing out the error. I'll blame it on a late-night copy&paste. I can't seem to edit my original post.

-Kevin

[David Bescond]

Hi Kevin,
thanks a lot for this code,
I noted that if the table has lot of records,  more that what is showed by the iDisplayLength )
the download concern only your selection and only what is show on the screen ie for me only 25 records instead of 1000
is it normal ?
could I fixed that

thanks

DAvid

[David Bescond]

ie the input$filtered_table return only what is on the screen ?

[Kevin Lindquist]

Hi David,

You are correct. When using pagination, the downloaded table only contains the filtered data that is currently displayed on the screen. I investigated trying to get around this limitation, but I did not have much luck. Shiny uses server-side processing to generate the table, so the data for non-diplayed table pages is not sent to the browser (as far as I can tell).

So, the only simple solution is to disable pagination.

Here is a new version of my code that has a couple changes. This code gives a table without pagination. Also, I changed the way I lookup the datatable object (hopefully this is more reliable). Changes are in bold below.

server.R

library(shiny)

shinyServer(function(input, output) {

    Dataset <- reactive({
        df <- mtcars
        # If the dataframe used to create the DataTable
        # happens to contain NA values, they need to
        # be replaced with an empty string. This ensures
        # that the array returned from the datatables
        # underscore method (i.e. the data contained in
        # input$filtered_table) contains an entry in every
        # cell. Otherwise the ProcessedFilteredData routine
        # will result in corrupted data
        df[is.na(df)] <- ""
        return(df)
    })

    output$data_table <- renderDataTable({
        Dataset()

    }, options = list(sDom = "ilftr", bPaginate = FALSE))

    ProcessedFilteredData <- reactive({
        v <- input$filtered_table
        # This code assumes that there is an entry for every
        # cell in the table (see note above about replacing
        # NA values with the empty string).
        col_names <- names(Dataset())
        n_cols <- length(col_names)
        n_row <- length(v)/n_cols
        m <- matrix(v, ncol = n_cols, byrow = TRUE)
        df <- data.frame(m)
        names(df) <- col_names
        return(df)
    })

    output$downloadData <- downloadHandler(
        filename = function() { 'filtered_data.csv' }, content = function(file) {
            write.csv(ProcessedFilteredData(), file, row.names = FALSE)
        }
    )
})

ui.R

library(shiny)

shinyUI(fluidPage(

    tags$head(
        tags$style(type="text/css", "tfoot {display: table-header-group}") # Move filters to top of datatable
    ),

    titlePanel("Filtered Data CSV Download"),
        sidebarLayout(
            sidebarPanel(
                HTML('
                    <script type="text/javascript">
                        $(document).ready(function() {
                            $("#downloadData").click(function() {

                                var filtered_table_data = $("#data_table").find("table").dataTable()._("tr", {"filter":"applied"});

                                Shiny.onInputChange("filtered_table", filtered_table_data);
                            });
                        });
                   </script>
                '),

                downloadButton('downloadData', 'Download Filtered Data')
            ),
            mainPanel(
                dataTableOutput("data_table")
            )
        )
))

[Albert Vilella]

I am trying to get this working, since TableTools from DataTables is not working for me.

I tried the code as is below, but I get this error. Any ideas what might be happening:

'data' must be of a vector type, was 'NULL'

[Leah B]

HI,

Did you ever resolve this issue?

I was getting the same error.

Thanks

[wangzx]

Hi Kevin,

  I was getting the error which is :Error in matrix(v, ncol = n_cols, byrow = TRUE) : 

  'data' must be of a vector type, was 'NULL', by using your code

  I am wondering that if there is some mistakes of my rstudio or package. Thanks a lot

在 2014年6月27日星期五UTC+8下午12时18分36秒，Kevin Lindquist写道：

[mbh]

Hello,

same error here. 

Error in matrix(v, ncol = n_cols, byrow = TRUE) : 

  'data' must be of a vector type, was 'NULL'

It would be great to download data from renderDataTable filters, I hope someone will find a solution :)

[Yihui Xie]

For those who have been looking for a solution to downloading filtered
data from DataTables, please see Section 2.2 here:
http://rstudio.github.io/DT/shiny.html If you are not familiar with
the DT package, you may need to start from the homepage of the
website.

Regards,
Yihui

[mbh]

Fantastic work Yihui, thanks ! It will be helpful for so many people :)

[dipali bhosale]

Hi all,

 

I am trying to build an simple app contating  filter functinality  as well as download filtered data. But when I am click on the download button same url is opening. If I remove filter='top' then its works perfectly fine.Could you please help me out.

 

server.r

library(shiny)
library(DT)

shinyServer(function(input, output, session) {

table1<-table1
  # render the table (with row names)

output$x1 = renderDataTable({
  table1[, input$show_vars, drop = FALSE]
}, table='top',options = list(lengthMenu = c(10,50,100,nrow(table1)) ,pageLength = 10),rownames=FALSE)
   
  # download the filtered data
  output$x3 = downloadHandler('SaverPartnerData.csv', content = function(file) {
    write.csv(table1, file,row.names=FALSE)
  })

  })

 

ui.r

# Define UI for dataset viewer application
shinyUI(pageWithSidebar(
 
  # Application title.
  headerPanel("Saver Overview by Partner"),
  sidebarPanel(
         checkboxGroupInput('show_vars', 'Display Columns:',
                           colnames(table1), selected = colnames(table1)
      ),
      downloadButton('x3',label='Download CSV')
   ),
    mainPanel(
     
        id = 'dataset',
        tabPanel('table1', dataTableOutput('x1'))
  
      )
    )
)

 

[samuel kellerhals]

Hi Yihui,

Thank you very very much for posting this answer! Everything works perfectly now.

Best wishes,
Samuel