Generate Drilling Report in R Shiny

Question

I am trying to display a data table with the number of columns "n" as shown below.

Begin Date | EndDate | Month | Year | Count of Students
2/1/2014 | 1/31/2015 | Jan | 2014 | 10
3/1/2014 | 2/28/2015 | Feb | 2014 | 20
4/1/2014 | 3/31/2015 | Mar | 2014 | 30
5/1/2014 | 4/30/2015 | Apr | 2014 | 40

I want to make this data table interactive by turning on the drilldown / tree scan function, where the user can click each of the values ​​in the "Number of students" field to see the original raw data behind these numbers 10, 20,30 and 40. For example, if the user clicks “10”, he / she should be able to see the student’s source data at this point. This is similar to the excel Pivot table concept where users can see the underlying data behind the Pivot tables. Is there a way I could do the same using R Shiny?

Answer 1

Yes, using a package DTto capture selected rows and a subset of the core set. Here is an example using a set iris:

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

# create a summary table
summary_iris <- group_by(iris, Species) %>%
  summarise(Count = n())

ui <- fluidPage(
  dataTableOutput("summary")
  , dataTableOutput("drilldown")
)


server <- function(input, output){

  # display the data that is available to be drilled down
  output$summary <- DT::renderDataTable(summary_iris)

  # subset the records to the row that was clicked
  drilldata <- reactive({
    shiny::validate(
      need(length(input$summary_rows_selected) > 0, "Select rows to drill down!")
    )    

    # subset the summary table and extract the column to subset on
    # if you have more than one column, consider a merge instead
    # NOTE: the selected row indices will be character type so they
    #   must be converted to numeric or integer before subsetting
    selected_species <- summary_iris[as.integer(input$summary_rows_selected), ]$Species
    iris[iris$Species %in% selected_species, ]
  })

  # display the subsetted data
  output$drilldown <- DT::renderDataTable(drilldata())
}

shinyApp(ui, server)