Filter datatable by two numeric columns separately in R Shiny App

Hi,

I am working with R Shiny App and interested in retrieving rows with values passing a particular cut-off. For example; retrieving only rows passing the cut-off that is value >= 10 in one column (Column_1) and <= 2 in another column (Column_2). I have shown an example app.R shiny script. I have earlier tried to add a filter across all columns. Perhaps, there must be two different filters to input values?

Thank you,
Abdul

library(shiny)
library(DT)
library(tidyverse)

df <- structure(list(Column_1 = c(10, 20, 20, -2, 96.57, 3.45, NA, 
                                  3.901, 40.24, 0), Column_2 = c(1, 2, 0.05, 1.58, 1.64, 
                                                                 3.58, NA, 4, 4.32, 4.522)), class = "data.frame", row.names = c("Row.1", 
                                                                                                                                 "Row.2", "Row.3", "Row.4", "Row.5", "Row.6", "Row.7", "Row.8", 
                                                                                                                                 "Row.9", "Row.10"))


ui <- fluidPage(
  conditionalPanel(
    'input.dataset === "df"',
  ),
  mainPanel(
    navlistPanel(
      id = "dataset",
      tabPanel("df", DT::dataTableOutput("mytable1"))
    ),
    numericInput("x", "Input a value to detect rows passing (>= & <=)", 0),
    actionButton("y", "Click to filter", icon = icon("redo"), class = "btn-success"),
  )
)

server <- function(input, output) {
  threshold <- eventReactive(input$y, {
    req(input$x)
  },ignoreNULL = FALSE)
  
  filter.df <- reactive({
    df %>%
      filter(if_all(everything(), ~ . >= req(threshold())))
  })
  
  output$mytable1 <- DT::renderDataTable({
    DT::datatable(req(filter.df()), options = list(paging = TRUE,
                                                   searching = TRUE,
                                                   scrollX=TRUE,
                                                   scrollCollapse = TRUE,
                                                   ordering = TRUE,
                                                   dom = 'tB'))
  })
}

shinyApp(ui, server)

Because 10, and 2 arent the same number they both cant simultaneously be the value of that one input$x.

So yes

@nirgrahamuk thank you. Does the below also change because I would be interested in filtering at least either of the column?

filter(if_all(everything(), ~ . >= req(threshold())))

let me ask you a question back.
Do you expect threshold to (potentially) represent the number 10 or 2 , or both simultaneously somehow ?
also you have >= , but havent provided a way to evaluate the <= you mentioned

@nirgrahamuk yes, I want to represent both values (10 & 2). Because mentioning both values are important for the filter. Does adding another line of filter for <= fix the issue and accomplish the filtering on two columns?

additive filters can be composed , for example

filter(mtcars,
          hp  >  110,
          mpg < 15)

this is also equivalent to

filter(mtcars, hp > 110 & mpg<15)

and

filter(mtcars,hp>110) |> 
filter(mpg<15)

@nirgrahamuk Thank you. I tried adding another filter box for <=, and applied filter as you suggested, but launching the app already gives me the filtered list (which is not useful), moreover filtering does not work if I input any value in the box. Basically, on the whole list, if I input any value >= in column 1 and <= in column 2, and then apply filtering then that would makes sense.

library(shiny)
library(DT)
library(tidyverse)

df <- structure(list(Column_1 = c(10, 20, 20, -2, 96.57, 3.45, NA, 
                                  3.901, 40.24, 0), Column_2 = c(1, 2, 0.05, 1.58, 1.64, 
                                                                 3.58, NA, 4, 4.32, 4.522)), class = "data.frame", row.names = c("Row.1", 
                                                                                                                                 "Row.2", "Row.3", "Row.4", "Row.5", "Row.6", "Row.7", "Row.8", 
                                                                                                                                 "Row.9", "Row.10"))


ui <- fluidPage(
  conditionalPanel(
    'input.dataset === "df"',
  ),
  mainPanel(
    navlistPanel(
      id = "dataset",
      tabPanel("df", DT::dataTableOutput("mytable1"))
    ),
    numericInput("x", "Column 1 - Input a value to detect rows passing (>=)", 0),
    actionButton("y", "Click to filter", icon = icon("redo"), class = "btn-success"),
    numericInput("x.1", "Column 2 - Input a value to detect rows passing (<=)", 0),
    actionButton("y.1", "Click to filter", icon = icon("redo"), class = "btn-success"),
  )
)

server <- function(input, output) {
  threshold <- eventReactive(input$y, {
    req(input$x)
  },ignoreNULL = FALSE)
  
  filter.df <- reactive({
    df %>%
      #filter(if_all(everything(), ~ . >= req(threshold())))
      filter(., Column_1 >= 10)
  })
  
  output$mytable1 <- DT::renderDataTable({
    DT::datatable(req(filter.df()), options = list(paging = TRUE,
                                                   searching = TRUE,
                                                   scrollX=TRUE,
                                                   scrollCollapse = TRUE,
                                                   ordering = TRUE,
                                                   dom = 'tB'))
  })
  
  threshold.1 <- eventReactive(input$y.1, {
    req(input$x.1)
  },ignoreNULL = FALSE)
  
  filter.df.1 <- reactive({
    df %>%
      #filter(if_all(everything(), ~ . <= req(threshold.1())))
      filter(., Column_2 <= 2)
  })
  
  output$mytable1 <- DT::renderDataTable({
    DT::datatable(req(filter.df.1()), options = list(paging = TRUE,
                                                   searching = TRUE,
                                                   scrollX=TRUE,
                                                   scrollCollapse = TRUE,
                                                   ordering = TRUE,
                                                   dom = 'tB'))
  })
}

shinyApp(ui, server)

library(shiny)
library(DT)
library(tidyverse)

df <- structure(list(Column_1 = c(10, 20, 20, -2, 96.57, 3.45, NA, 
                                  3.901, 40.24, 0), Column_2 = c(1, 2, 0.05, 1.58, 1.64, 
                                                                 3.58, NA, 4, 4.32, 4.522)), class = "data.frame", row.names = c("Row.1", 
                                                                                                                                 "Row.2", "Row.3", "Row.4", "Row.5", "Row.6", "Row.7", "Row.8", 
                                                                                                                                 "Row.9", "Row.10"))


ui <- fluidPage(
  
  sidebarLayout(
    
    # Sidebar with a slider input
    sidebarPanel(
                 numericInput("col_1_filt_val", "Column 1 - Input a value to detect rows passing (>=)", 0),
                 actionButton("col_1_filt_trigger", "Click to filter", icon = icon("redo"),class="btn-primary"),
                 numericInput("col_2_filt_val", "Column 2 - Input a value to detect rows passing (<=)", 0),
                 actionButton("col_2_filt_trigger", "Click to filter", icon = icon("redo"),class="btn-primary"),
    ),
  mainPanel( DT::dataTableOutput("mytable1")
    )))

server <- function(input, output) {
  col1_threshold <- eventReactive(input$col_1_filt_trigger, {
    v <- input$col_1_filt_val
    if(!isTruthy(v) |input$col_1_filt_trigger==0 ){
      v<- -Inf
    } 
    v
  },ignoreNULL = FALSE)
  
  
  col2_threshold <- eventReactive(input$col_2_filt_trigger, {
    v <- input$col_2_filt_val
    if(!isTruthy(v) |input$col_2_filt_trigger==0){
      v<- Inf
    } 
    v
  },ignoreNULL = FALSE)
 
  
  output$mytable1 <- DT::renderDataTable({
    showme <- df |> filter(
      Column_1 >= !!col1_threshold(),
      Column_2 <= !!col2_threshold()
    )
    
    DT::datatable(showme, options = list(paging = TRUE,
                                                     searching = TRUE,
                                                     scrollX=TRUE,
                                                     scrollCollapse = TRUE,
                                                     ordering = TRUE,
                                                     dom = 'tB'))
  })
  observeEvent(input$col_2_filt_val,{
    
  })
}

shinyApp(ui, server)
1 Like

Thank you very much @nirgrahamuk This is exactly that I was interested in and very helpful.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.