Apply two crosstalk filters to two data frames in R

I am trying to develop an interactive dashboard of my office's antimicrobial usage. Basically, I want to create two graphs (one that shows data by quarter, the other that shows data by month) that can be filtered on the antimicrobial group and the inpatient location. The quarter and month data are in two different data frames (called quarterly and monthly below)

### Create sample quarterly dataset
quarterly = data.frame(
  "Quarter" = rep(c("2022 Q4"), times = 6),
  "Entity" = rep(c("Hospital 1"), times = 6),
  "Location" = rep(c("Hem/Onc","Ward","ICU"), each = 2),
  "Antimicrobial Group" = rep(c("Gram-Negative","Anti-MRSA"), times = 3),
  "Agent" = c("aztreonam","ceftaroline","cefepime","dalbavancin","ceftazidime","linezolid"),
  "Rate" = c(sample(25:100, 6))
)

### Create sample monthly dataset
monthly = data.frame(
  "Month" = rep(c("2022-10-01","2022-11-01","2022-12-01"), times = 3),
  "Entity" = rep(c("Hospital 1"), times = 9),
  "Location" = rep(c("Hem/Onc","Ward","ICU"), each = 3),
  "Antimicrobial Group" = rep(c("Gram-Negative","Anti-MRSA"), each = 9),
  "Agent" = c("aztreonam","cefepime","ceftazidime","aztreonam","cefepime","ceftazidime","aztreonam","cefepime","ceftazidime",
              "ceftaroline","dalbavancin","linezolid","ceftaroline","dalbavancin","linezolid","ceftaroline","dalbavancin","linezolid"),
  "Rate" = c(sample(1:25, 9))
)

I have attempted using the group function of crosstalk, which seems to work on filtering the quarterly level data but the monthly data goes haywire with showing the wrong antimicrobial agents:

### Create filters
test_quarter <- SharedData$new(quarterly, group = "abx")
test_month <- SharedData$new(monthly, group = "abx")

c1 <- filter_checkbox(id = "abx",sharedData = test_quarter, group = ~Antimicrobial.Group,
                      label = "Select Antibiotic Group")

c2 <- filter_checkbox(id = "abx",sharedData = test_quarter, group = ~Location,
                      label = "Select Location")

p <- plot_ly(test_quarter, type = 'bar', x = ~Quarter, y = ~Rate, 
        color = ~Agent, 
        legendgroup = ~Agent,
        colors = brewer.pal(length(unique(test_quarter$Agent)),"Paired")) %>%
  layout(title = 'Utilization Rates by Quarter and Filtered Antibiotic Group', barmode = 'stack',
         xaxis = list(title = "", showline = TRUE),
         yaxis = list(side = 'left', ticks = "outside", title = 'Utilization Rate/1000 Patient-Days', showgrid = FALSE, zeroline = FALSE, showline = TRUE, rangemode = 'tozero'))

p2 <- plot_ly(test_month, type = 'bar', x = ~Month, y = ~Rate, 
              color = ~Agent, 
              legendgroup = ~Agent,
              colors = brewer.pal(length(unique(test_month$Agent)),"Paired")) %>%
  layout(title = 'Utilization Rates by Month, Filtered NHSN Location, and Filtered Antibiotic Group', barmode = 'stack',
         xaxis = list(title = "", showline = TRUE, type = "date", dtick = "M1"),
         yaxis = list(side = 'left', ticks = "outside", title = 'Utilization Rate/1000 Patient-Days', showgrid = FALSE, zeroline = FALSE, showline = TRUE, rangemode = 'tozero'))

bscols(widths = c(4,4),
       list(c1,c2), p,p2)

Any advice on how to connect these data frames in a way that the multiple crosstalk filters will work on both graphs is much appreciated!

Solution

You could try using a key. I had a similar problem and keys solved my issue.

Example using 2 data frames - financial year and monthly. 'fy' and 'month' are the "raw" data frames before using SharedData

fy_new <- fy %>% 
  mutate(my_key = paste(Ind,Age,Measurement,sep = "_")) %>% 
  select(my_key,everything())

month_new <- month %>% 
  mutate(my_key = paste(Ind,Age,Measurement,sep = "_")) %>% 
  select(my_key,everything())

Then using key and group , create the SharedData objects: (same group and key)

fy_shared <- SharedData$new(fy_new, ~my_key, group = "Group X")
month_shared <- SharedData$new(month_new, ~my_key, group = "Group X")

Then I created the filters:

crosstalk::filter_select(
    id = "Ind",
    label = "Ind",
    sharedData = fy_shared,
    group = ~Ind,
    multiple = FALSE
  )
crosstalk::filter_select(
    id = "Measurement",
    label = "Measurement",
    sharedData = fy_shared,
    group = ~Measurement,
    multiple = FALSE
  )
crosstalk::filter_select(
    id = "Age",
    label = "Age",
    sharedData = fy_shared,
    group = ~Age,
    #selected="All Ages",
    multiple = FALSE
  )

Then I created the plots/tables as normal using the 'fy_shared' and 'month_shared' objects, eg

month_shared %>% 
    plot_ly(x = ~ `Month`, y = ~ `Value`, ...

I think keys make note of every row and use rows as reference. (Could be wrong tho)

Hope this helps!