Creating universal filters for multiple reactive DFs in Shiny App

Question

I am building a dashboard that needs to display multiple tables with a tab for each table. The data are all related and I would like to have universal filters that when applied will filter all of the tables. As a simplified example, lets say I have the following tables for a car dealership with information about customers, their vehicle(s), and the services performed on the vehicles:

Customer	Zip Code	Age
Mason	14350	44
Dole	14352	25
Rogers	14358	60

Vehicle ID	Model	Year	Customer
355	Impreza	2015	Mason
1324	Forester	2020	Dole
121	Camry	2018	Rogers

Service ID	Service	Vehicle ID
1	Oil Change	355
2	Brakes	355
3	sensor replacement	355
4	Oil Change	1324
5	Brakes	121

My goal is to present each of these tables as a data table in its own tab in a Shiny App. I then want universal filters that filter all tables based on relationships. For example, if I filtered the services table to oil changes, I would expect the services table to filter to oil changed only, but also expect the vehicle and customer tables to filter based on if the owner/vehicle meets the oil change criteria.

What would be the best way to accomplish this? I attempted to dynamically loop through these data sets and create a reactive data frame for each set along with an output table, but am struggling with how to apply filters to all of the data frames at the same time. Would creating one large data frame be a better option where the views are created by selecting specific columns for each view/frame?

user_11168766 · Answer

We can join the three tables together, filter them with selectizeGroup-module and then split them back again. Some duplicate rows can result from doing the joins and then splitting, but using dplyr::distinct solves it.

app:

library(tidyverse)
library(shiny)
library(shinyWidgets)
library(gt)

customer <-
  tribble(
    ~Customer, ~Zip_Code, ~Age,
    "Mason", 14350, 44,
    "Dole", 14352, 25,
    "Rogers", 14358, 60
  )

vehicle <-
  tribble(
    ~Vehicle_ID, ~Model, ~Year, ~Customer,
    355, "Impreza", 2015, "Mason",
    1324, "Forester", 2020, "Dole",
    121, "Camry", 2018, "Rogers"
  )
service <-
  tribble(
    ~Service_ID, ~Service, ~Vehicle_ID,
    1, "Oil Change", 355,
    2, "Brakes", 355,
    3, "sensor replacement", 355,
    4, "Oil Change", 1324,
    5, "Brakes", 121
  )

df_joined <- inner_join(customer, vehicle, by = "Customer")
df_joined <- left_join(df_joined, service, by = ("Vehicle_ID"))

nms <- names(df_joined)

params <- nms %>%
  map(~ list(inputId = ., title = str_to_title(.))) %>%
  set_names(nms)

ui <- fluidPage(
  fluidRow(selectizeGroupUI(id = "my_filters", params = params)),
  tabsetPanel(
    tabPanel("Service", gt_output({
      "service_table"
    })),
    tabPanel("Vehicle", gt_output({
      "vehicle_table"
    })),
    tabPanel("Customer", gt_output({
      "customer_table"
    }))
  )
)

server <- function(input, output, session) {
  res_mod <- callModule(
    module = selectizeGroupServer,
    id = "my_filters",
    data = df_joined,
    vars = nms
  )



  df_splitted <- reactive({
    map(
      map(list(customer, vehicle, service), names),
      ~ select(res_mod(), all_of(.x))
    ) %>%
      map(distinct) %>%
      set_names(c("customer", "vehicle", "service"))
  })

  observeEvent(df_splitted(), { # the order of the table names matter
    walk2(c("customer_table", "vehicle_table", "service_table"), df_splitted(), ~ {
      output[[.x]] <<- render_gt({
        .y
      })
    })
  })
}

shinyApp(ui, server)

Creating universal filters for multiple reactive DFs in Shiny App

1 Answers