Assistance in data filtering using Shiny App

Question

I have a table that summarizes ~100 clinical trials. This table needs to be added to a shiny app and then the user filter the table based on the different variables in the table like endpoint, intervention, etc.

The problem with that the table is that every study, as far as I know, has to be in only one row. But in excel, each study needs more than one row since variables like "Intervention" and "Endpoint" has so many levels, and I will need to subset on my data based on these variables.

For example, user choose to subset on studies that included outcome 1 or maybe intervention 1. Many studies has a mix of these, as in the screenshot below.

This makes it impossible for each study to fit in one row especially that a study can have up to 15 different interventions (there is a long list of interventions that the use will subset based on)

How to approach this?

Answer 1

It sounds like you need to figure out how to "fill in" all of the empty rows with the preceding row's data. Ignoring the shiny component for now, if I have data (in xlsx, though it doesn't matter if CSV) like the following:

We can read in and fix with zoo::na.locf or tidyr::fill:

dat <- readxl::read_excel("~/Downloads/something.xlsx")
dat
# # A tibble: 6 × 5
#   Author    Year Methodology    `Intervention(s)` `Endpoint(s)`
#   <chr>    <dbl> <chr>          <chr>             <chr>        
# 1 X et al.  2022 Prospective    Intervention 1    Endpoint 1   
# 2 NA          NA NA             Intervention 2    Endpoint 2   
# 3 NA          NA NA             Intervention 3    Endpoint 3   
# 4 Y et al.  2021 Something else Intervention 4    Endpoint 4   
# 5 NA          NA NA             Intervention 5    Endpoint 5   
# 6 NA          NA NA             Intervention 6    Endpoint 6   

and fix with one of the following:

tidyr::fill(dat, Author, Year, Methodology)
# # A tibble: 6 × 5
#   Author    Year Methodology    `Intervention(s)` `Endpoint(s)`
#   <chr>    <dbl> <chr>          <chr>             <chr>        
# 1 X et al.  2022 Prospective    Intervention 1    Endpoint 1   
# 2 X et al.  2022 Prospective    Intervention 2    Endpoint 2   
# 3 X et al.  2022 Prospective    Intervention 3    Endpoint 3   
# 4 Y et al.  2021 Something else Intervention 4    Endpoint 4   
# 5 Y et al.  2021 Something else Intervention 5    Endpoint 5   
# 6 Y et al.  2021 Something else Intervention 6    Endpoint 6   

dat[] <- lapply(dat, zoo::na.locf)
dat
# # A tibble: 6 × 5
#   Author    Year Methodology    `Intervention(s)` `Endpoint(s)`
#   <chr>    <dbl> <chr>          <chr>             <chr>        
# 1 X et al.  2022 Prospective    Intervention 1    Endpoint 1   
# 2 X et al.  2022 Prospective    Intervention 2    Endpoint 2   
# 3 X et al.  2022 Prospective    Intervention 3    Endpoint 3   
# 4 Y et al.  2021 Something else Intervention 4    Endpoint 4   
# 5 Y et al.  2021 Something else Intervention 5    Endpoint 5   
# 6 Y et al.  2021 Something else Intervention 6    Endpoint 6   

From here, you can filter however you need without losing information on the author/year/etc.