How do I get the data from the selected rows of a filtered datatable (DT)?

Question

The DT package allows you to get the indices of selected rows using input$tableID_rows_selected. This works great for tables that do not have filtered data. However, if we have a filtered dataset, we can't use this same approach, as the row indices are off.

For a filtered dataset, then, how would we get the data in the selected rows of a datatable?

Below, I've posted a basic shiny app that shows four tables: the first one is the original mtcars dataset and the second gets the selected rows in the first. The third and the fourth do the same thing, but after filtering the dataset on the "filter" sliderInput.

library(shiny)
library(DT)
library(dplyr)

ui <- fluidPage(
  DT::dataTableOutput("origTable"),
  DT::dataTableOutput("origTableSelected"),
  sliderInput("filter", label = "Filter by cyl", min = 4, max = 8, step = 2, value = 6),
  DT::dataTableOutput("filteredTable"),
  DT::dataTableOutput("filteredTableSelected")
)


server <- function(input, output, session) {

  output$origTable <- DT::renderDataTable({
    datatable(
      mtcars,
      selection = list(mode = "multiple"),
      caption = "Original Data"
    )
  })

  origTable_selected <- reactive({
    ids <- input$origTable_rows_selected
    mtcars[ids,]
  })

  output$origTableSelected <- DT::renderDataTable({
    datatable(
      origTable_selected(),
      selection = list(mode = "multiple"),
      caption = "Selected Rows from Original Data Table"
    )
  })

  output$filteredTable <- DT::renderDataTable({
    datatable(
      filter(mtcars, cyl == input$filter),
      selection = list(mode = "multiple"),
      caption = "Filtered Table (based on cyl)"
    )
  })

  filteredTable_selected <- reactive({
    ids <- input$filteredTable_rows_selected
    mtcars[ids,]
  })

  output$filteredTableSelected <- DT::renderDataTable({
    datatable(
      filteredTable_selected(),
      selection = list(mode = "none"),
      caption = "Table that gets data from unfiltered original data"
    )
  })
}

shinyApp(ui = ui, server = server)

Solution 1

One way: in your filteredTable_selected() function, where you're creating the data you'll put in your fourth DT, use filter(mtcars, cyl == input$filter) like you did for your third table instead of mtcars. This way, the row indices will match.

If you're worried about performance issues on larger datsets, just filter the data in a reactive expression, which caches its output. This way, you won't filter more than your input$filter value changes.

server <- function(input, output, session) {
  filteredTable_data <- reactive({
    filter(mtcars, cyl == input$filter)
  })

  output$filteredTable <- DT::renderDataTable({
    datatable(
      filteredTable_data(),
      selection = list(mode = "multiple"),
      caption = "Filtered Table (based on cyl)"
    )
  })

  filteredTable_selected <- reactive({
    ids <- input$filteredTable_rows_selected
    filteredTable_data()[ids,]
  })

  output$filteredTableSelected <- DT::renderDataTable({
    datatable(
      filteredTable_selected(),
      selection = list(mode = "none"),
      caption = "Table that gets data from unfiltered original data"
    )
  })
}

Solution 2

While the accepted answer gives a working solution for shiny, it wasn't obvious how to implement it inside an R Markdown document with flexdashboard.

In Rmd documents, render*() functions like DT::renderDataTable() are usually used anonymously, but it is possible to explicitly assign them to output slots in shiny. In this case, to use the input$tableID_rows_selected construct, it's necessary to do so.

This solution also sorts the index to always maintain the same order as in the original data frame.

---
title: "MRE"
author: ""
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: scroll
runtime: shiny
---

Page {data-orientation=columns}
=======================================================================

Column {data-width=650}
-----------------------------------------------------------------------

### .

```{r}
  require(DT)
  library(dplyr)
  library(tibble)

  sliderInput("filter", label = "Filter by cyl", min = 4, max = 8, step = 2, value = 6)


  filteredTable_data <- reactive({
    mtcars %>% rownames_to_column() %>%  ##dplyr's awkward way to preserve rownames
          filter(., cyl == input$filter) %>% column_to_rownames()
  })

  ##explicit assignment to output ID
  DT::dataTableOutput("filteredTable")
  output$filteredTable <- DT::renderDataTable({
    datatable(
      filteredTable_data(),
      selection = list(mode = "multiple"),
      caption = "Filtered Table (based on cyl)"
    )
  })

  filteredTable_selected <- reactive({
    ids <- input$filteredTable_rows_selected
    filteredTable_data()[sort(ids),]  ##sort index to ensure orig df sorting
  })

  ##anonymous
  DT::renderDataTable({
    datatable(
      filteredTable_selected(),
      selection = list(mode = "none"),
      caption = "Table that gets data from unfiltered original data"
    )
  })

```

And here's how the output looks like:

Comments

[landroni]

If you want to get the selected rows in the same order as in the original data.frame, then you need to sort the indexes, e.g.: filteredTable_data()[sort(ids),].