Friday, October 22, 2021
No menu items!
HomeData Analytics and VisualizationExcel like Filter in Shiny

Excel like Filter in Shiny

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

MS Excel allows you to select multiple values from the filter by clicking on values in the checkboxes that you want. Select / deselect all the unique values of the dropdown at one go without manually doing it one by one. Search bar functionality to search text and show relevant checkboxes accordingly Allows you to sort data in ascending and descending order Advanced filtering : apply logical conditions to filter valuesIn 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

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 cols
# Show selected values on button
Concatenate
if(length(x)>0) {
ifelse(nchar(paste(x,collapse = “, “))>25,
paste(length(x),”selected”),
paste(x,collapse = “, “))
} else {
defaultText
}

}

# Choices
letchoice
# ————————
# App starts from here
# ————————

ui
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
# 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 myval$count observeEvent(input$asc, {

updateCheckboxGroupInput(
session = session, inputId = “mycheckbox”, selected = letchoice
)

myval$count })

# Sorting descending
observeEvent(input$desce, {

updateCheckboxGroupInput(
session = session, inputId = “mycheckbox”, selected = letchoice
)

myval$count
})

# 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 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. Read MoreListenData

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments