Excel like Filter in Shiny

Deepanshu Bhalla Add Comment , ,
This post talks about how to implement excel like filtering and sorting menu in shiny application. Most of the stakeholders are very comfortable with MS Excel and generally demands similar kind of functionality in other applications or softwares when it comes to data manipulation. Recently I was asked how we can have excel like filter in shiny web app.
Features of filter option in MS Excel
  1. MS Excel allows you to select multiple values from the filter by clicking on values in the checkboxes that you want.
  2. Select / deselect all the unique values of the dropdown at one go without manually doing it one by one.
  3. Search bar functionality to search text and show relevant checkboxes accordingly
  4. Allows you to sort data in ascending and descending order
  5. Advanced filtering : apply logical conditions to filter values
In this post we have covered all the above features except the last one. In addition we have implemented a feature which is missing in Excel - Showing selected values of filter
Excel like Filter Menu in Shiny
The program below makes use of shinyWidgets package and its widgets. Hence it is important to install the package.
# Required libraries
library(shiny)
library(shinyWidgets)
library(dplyr)
library(DT)

# Constants
df <- iris #Dataframe Name
cols <- "Species" #Column for filtering / sorting

# Show selected values on button
Concatenate <- function(x, defaultText = "Show Values") {
  
  if(length(x)>0) {
    ifelse(nchar(paste(x,collapse = ", "))>25,
           paste(length(x),"selected"), 
           paste(x,collapse = ", "))
  } else {
    defaultText
  }
  
}

# Choices
letchoice <- unique(as.character(df[[cols]]))

# ------------------------
# App starts from here
# ------------------------

ui <- fluidPage(
  
  tags$head(tags$style("

.btn-dropdown-input button {background-color: #f3f3f3 !important;
    border: 1px solid #ddd; text-align:left; width: 100%; max-width: 100%;
}
    
.btn-dropdown-input .dropdown-toggle::after {
 content: '\\e114';
 font-family: \"Glyphicons Halflings\";
 margin-right: 0.3em;
 font-style: normal;
 float: right;
 border: none;
 font-size: 12px;
 color: #444;
}

.checkbox input {
    accent-color: black;
}

.btn-dropdown-input .caret {
  display: none;
}

")),
  
  tags$script('setTimeout(function(){
$("#asc, #desce").click(function() {
  $(".btn-dropdown-input.shiny-bound-input.open").removeClass("open"); 
});}, 200);'),
  
  titlePanel("Excel Like Filter"),
  
  fluidRow(
    column(
      width = 3,
      dropdownButton(
        inputId = "dropdownbtn",
        label = "Filter column", 
        status = "default",
        circle = FALSE,
        width = 300,
        
        fluidRow(
          column(width = 12,
                 actionGroupButtons(
                   inputIds = c("asc", "desce"),
                   labels = list(tags$span(icon("sort-alpha-asc"), "Sort A to Z"),
                                 tags$span(icon("sort-alpha-desc"), "Sort Z to A")
                   ),
                   fullwidth = T
                 ))
        ),
        
        div(style="margin-bottom:1em;"),
        
        textInputIcon(
          inputId = "search", label = NULL,
          placeholder = "Search",
          icon = icon("search"),
          width = "100%"
        ),
        
        fluidRow(
          column(width = 12,
                 actionGroupButtons(
                   inputIds = c("all", "deselect"),
                   labels = list(tags$span(icon("check"),"Select All"), 
                                 tags$span(icon("remove"), "Deselect All")
                   ),
                   fullwidth = T
                 ))
        ),
        br(),
        
        checkboxGroupInput(inputId = "mycheckbox", 
                           label = NULL, 
                           choices = letchoice,
                           width = '100%'
        )
      )
    ),
    
    column(
      width = 6,
      dataTableOutput("mytable")
    )
  )
)

server <- function(input, output, session) {
  
  # Button Labels Change
  observeEvent(input$mycheckbox, {
    
    updateActionButton(
      session = session, 
      inputId = "dropdownbtn",
      label = Concatenate(input$mycheckbox, "Filter column")
    )
    
  }, ignoreNULL = FALSE, ignoreInit = TRUE)
  
  # Live Search
  observeEvent(input$search, {
    
    updateCheckboxGroupInput(
      session = session, 
      inputId = "mycheckbox", 
      choices = letchoice[grepl(paste0(".*",
                                       input$search,
                                       ".*"), letchoice,
                                ignore.case = T)]
    )
    
  })
  
  
  # Sorting ascending
  myval<- reactiveValues()
  myval$count <- 0
  observeEvent(input$asc, {
    
    updateCheckboxGroupInput(
      session = session, inputId = "mycheckbox", selected = letchoice
    )
    
    myval$count <- 1
  })
  
  # Sorting descending
  observeEvent(input$desce, {
    
    updateCheckboxGroupInput(
      session = session, inputId = "mycheckbox", selected = letchoice
    )
    
    myval$count <- 2
    
  })
  
  
  # Select all
  observeEvent(input$all, {
    updateCheckboxGroupInput(
      session = session, inputId = "mycheckbox", selected = letchoice
    )
  })
  
  # Deselect all
  observeEvent(input$deselect, {
    
    updateCheckboxGroupInput(
      session = session, inputId = "mycheckbox", selected = ""
    )
                        
    updateTextInputIcon(session = session, 
                        inputId = "search",
                        value = "",
                        placeholder = "Search",
                        icon = icon("search"))
    
  })
  
  # Datatable
  cols <- sym(cols)
  output$mytable = renderDT(
    df %>% 
      dplyr::filter(!!cols %in% input$mycheckbox) %>% 
      {if(myval$count == 1) {
        arrange(., !!cols)
      } else if (myval$count > 1)  {
        arrange(., desc(!!cols))
      } else {
        .
      }
      }
  )
  
  
}

shinyApp(ui = ui, server = server)
In order to customise the above code for your own project, you can change constants df and cols. They refer to dataframe and column which you want to filter.
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 0 Response to "Excel like Filter in Shiny"
Next → ← Prev