How to have shiny app that allows users to identify the key column and then filter based off columns and rows inputs?

Question

I have the following shiny app that almost does what I want. My goal is to have a user be able to upload a csv file into my app and have it output as a table. I would also want my users to be able to have the following few inputs.

1. Select the columns needed from the data frame.
2. Of the selected columns, allow the user to identify the primary key
3. From the primary key allow the user to filter what rows they need.

Right now I am stuck on number 3 and can't get the the table to filter the rows.

Here is the code

library(shiny)
library(tidyverse)
library(readxl)
library(shinyWidgets)
library(data.table)
means_out <- mtcars


setDT(means_out, keep.rownames = TRUE[])

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      fileInput("file1", "Choose CSV File",
                accept = c(
                  "text/csv",
                  "text/comma-separated-values,text/plain",
                  ".csv")
      ),
      #Staff would need to specify the rows and columns from the extract to be included in the table.
      uiOutput("colControls"),
      div(style="text-align:left","Select Columns:"),
      textOutput("selectedTextc"),
      #ROWKEY
      uiOutput("keyControls"),
      #ROWS
      uiOutput("rowControls"),
      div(style="text-align:left","Select Rows:"),
      textOutput("selectedTextr")
    )
    ,
    mainPanel(
      tableOutput("tbl")
    )
  )
)

server <- function(input, output) {
  
  #FILE SECTION
  get_file_or_default <- reactive({
    if (is.null(input$file1)) {
      means_out
    } else {
      read.csv(input$file1$datapath)
    }
  })
  
  #COLUMNS 
  output$colControls <- renderUI({
    pickerInput(inputId="cols", "Choose Columns", choices= get_file_or_default() %>% colnames(),
                multiple = TRUE)
  })
  
  txtc <- reactive({ input$cols })
  output$selectedTextc <- renderText({paste0(txtc() ,sep=", ") })
  
  #ROW KEY
  output$keyControls <- renderUI({
    selectInput(inputId="key", "Identify the Key Column", choices= get_file_or_default() %>% colnames(),
                multiple = FALSE)
  })
  
  txtkey <- reactive({ input$key })
  
  #SELECT ROWS NEEDED 
  output$rowControls <- renderUI({
    pickerInput(inputId="rows", "Choose Rows", choices= get_file_or_default() %>% select(txtkey()),
                multiple = TRUE)
  })
  
  txtr <- reactive({ input$rows })
  output$selectedTextr <- renderText({paste0(txtr() ,sep=", ") })
  
  output$tbl <- renderTable({
    if (is.null(input$cols) & is.null(input$rows)) {
      get_file_or_default()
    } else {
      get_file_or_default() %>% select({paste0(txtc()) }) #%>% filter(input$key %in% c(input$rows))
    }
  })
  
}
shinyApp(ui, server)

Answer 1

As the key variable may not be unique for all rows, you need to identify the selected rows. It is easier to do that in DT by using the input$tbl_rows_selected feature. Select the rows in the displayed table at the top, and selected rows are shown at the bottom in a separate table. Try this

library(shiny)
library(tidyverse)
library(readxl)
library(shinyWidgets)
library(data.table)
library(DT)
means_out <- mtcars


setDT(means_out, keep.rownames = TRUE[])

ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(
      fileInput("file1", "Choose CSV File",
                accept = c(
                  "text/csv",
                  "text/comma-separated-values,text/plain",
                  ".csv")
      ),
      #Staff would need to specify the rows and columns from the extract to be included in the table.
      uiOutput("colControls"),
      div(style="text-align:left","Select Columns:"),
      textOutput("selectedTextc"),
      #ROWKEY
      uiOutput("keyControls"),
      #ROWS
      #uiOutput("rowControls"),
      div(style="text-align:left","Select Rows:"),
      textOutput("selectedTextr")
    )
    ,
    mainPanel(
      DTOutput("tbl"), DTOutput("selectedtable")
    )
  )
)

server <- function(input, output) {
  
  #FILE SECTION
  get_file_or_default <- reactive({
    if (is.null(input$file1)) {
      means_out
    } else {
      read.csv(input$file1$datapath)
    }
  })
  
  get_file_or_defaultt <- reactive({ 
    req(get_file_or_default()) 
    get_file_or_default() %>% mutate(row_num = row_number())
  })
  
  #COLUMNS 
  output$colControls <- renderUI({
    req(get_file_or_default())
    pickerInput(inputId="cols", "Choose Columns", choices= get_file_or_default() %>% colnames(),
                multiple = TRUE)
  })
  
  txtc <- reactive({ input$cols })
  output$selectedTextc <- renderText({paste0(txtc() ,sep=", ") })
  
  #ROW KEY
  output$keyControls <- renderUI({
    req(get_file_or_default())
    if (is.null(input$cols)) df <- get_file_or_default() 
    else df <- get_file_or_default() %>% dplyr::select(all_of(input$cols))
    selectInput(inputId="key", "Identify the Key Column", choices = df %>% colnames() ,
                multiple = FALSE)
  })
  
  txtkey <- reactive({ input$key })
  
  #SELECT ROWS NEEDED 
  output$rowControls <- renderUI({
    pickerInput(inputId="rows", "Choose Rows", choices= get_file_or_default() %>% select(txtkey()),
                multiple = TRUE)
  })
  
  output$tbl <- renderDT({
    if (is.null(input$cols)) {
      get_file_or_default()
    } else {
      get_file_or_default() %>% select({paste0(txtc()) })
    }
  }, rownames = FALSE)
  
  selectedRows <- eventReactive(input$tbl_rows_selected,{
    if (is.null(input$cols)) df <- get_file_or_default() 
    else df <- get_file_or_default() %>% dplyr::select(all_of(input$cols))
    df[c(input$tbl_rows_selected),]
  })
  
  output$selectedtable <- renderDT({
    selectedRows()
  }, rownames = FALSE)
  
  selectedRow <- eventReactive(input$tbl_rows_selected,{
    row.names(get_file_or_default())[c(input$tbl_rows_selected)]
  })
  
  output$selectedTextr <- renderText({paste0(selectedRow()) })
  
}
shinyApp(ui, server)