How can you filter a reactive data table with a checkbox in R shiny without breaking the "sprea-CodePudding

I am experimenting with the development of an interactive dashboard in R shiny. I pull data from relational databases using SQL into my workspace. The data looks similar to this:

and can be loaded with the following code

df<-data.frame(ID = c("a", "b", "a", "c", "a", "b", "c", "a", "d", "c", "b", "d", "a", "b"),
               Event = c("AF-11", "AM-12", "AM-15", "AF-20", "AF-21", "AM-17", "AM-50", "AF-30", "AM-19", "AF-45", "AF-46", "AM-22", "AM-29", "AM-33"))

In the app, I want to produce a data table from the above data that has been rearranged to look like this:

The following code gets us there:

d1<-df %>%
  group_by(ID) %>%
  add_column(variable = "Thing") %>%
  mutate(times = 1:n(),
         tot_times = max(times)) %>%
  unite(both, variable, times) %>%
  spread(both, Event) %>%
  arrange(desc(tot_times))

This code works just fine in Shiny. However, I want to add a mechanism that allows the user to toggle between event types using a checkbox. In order to do this, I pull the "AF" and the "AM out of the string using gsub in a "mutate" function and use a "filter" function to choose between the event types in the app based on what the user has checked in the checkbox. The app is below:

header <- dashboardHeader(title = "Stuffs")

sidebar <- dashboardSidebar(
    sidebarMenu(
        menuItem("The Thing", tabName = "thething", icon = icon("dashboard"))
    )
)

body <- dashboardBody(
    tabItems(
        tabItem( tabName = "thething",
                 h4("The Thing"),
                 fluidRow(box(width = 12, dataTableOutput("table1")),
                          box(width = 5, checkboxGroupInput("checkbox", "Thing Type", choices = c("AM", "AF"), selected = "CF", inline = TRUE))))
        
    )
)

ui <- dashboardPage(header, sidebar, body)

Server

server <- function(input, output, session) {
    df<-reactive({d1<-data.frame(ID = c("a", "b", "a", "c", "a", "b", "c", "a", "d", "c", "b", "d", "a", "b"),
                   Event = c("AF-11", "AM-12", "AM-15", "AF-20", "AF-21", "AM-17", "AM-50", "AF-30", "AM-19", "AF-45", "AF-46", "AM-22", "AM-29", "AM-33"))
    })
    
    output$table1 <- renderDataTable({
        
        d1<-df() %>%
            mutate(EventTypes = gsub("[^a-zA-Z]", "", Event)) %>%
            filter(EventTypes %in% input$checkbox) %>%
            group_by(ID) %>%
            add_column(variable = "Thing") %>%
            mutate(times = 1:n(),
                   tot_times = max(times)) %>%
            unite(both, variable, times) %>%
            spread(both, Event) %>%
            arrange(desc(tot_times))
    })
}

shinyApp(ui,server)

The checkbox correctly sorts between the two types depending on what is toggled, but it breaks the spread command and I no longer get one participant per row. Instead, each participant has a row for each event type.

Obviously the way I am going about this is incorrect, but I don't understand the code well enough to figure out the proper way to do this. Does anybody have any ideas? Thanks for your help.

CodePudding user response：

Try this change to your output$table1. Basically, I check if input$checkbox has been given more than one choice; if so, change EventTypes to a constant so that there is only one row

  output$table1 <- renderDataTable({
    
    res <- df() %>%
      mutate(EventTypes = gsub("[^a-zA-Z]", "", Event)) %>%
      filter(EventTypes %in% input$checkbox)

    if(length(input$checkbox)>1) {
      res <- mutate(res,EventTypes =paste0(input$checkbox,collapse="/"))
    }
    
    res %>% group_by(ID) %>%
      add_column(variable = "Thing") %>%
      mutate(times = 1:n(),
             tot_times = max(times)) %>%
      unite(both, variable, times) %>%
      spread(both, Event) %>%
      arrange(desc(tot_times))
  })