Shiny Screening App - User Filter / Mutate / Modify

Question

I'm in the process of creating a Shiny screening application, which would allow users to type R commands like filter and mutate on a predetermined dataframe.

Personally, I think the most user friendly way to do this would be to have a blank dataframe column containing screening parameters, where users could input commands like filter(mpg >= 21) or mutate(cyl_sq = cyl^2) using the mtcars dataset as an example.

Simple application to show the intended functionality:

library(shiny)
library(shinydashboard)
library(DT)
library(dplyr)
# Header ------------------------------------------------
header <- dashboardHeader(title = "Example Screening")
# Sidebar ------------------------------------------------
sidebar <- dashboardSidebar()
# Body ------------------------------------------------
body <-
dashboardBody(
  fluidRow(
       column(6,h3("Screening Parameters")),
       column(6,h3("Filtered/Modified Results"))),
  fluidRow(
       column(6,DT::dataTableOutput("screening_params")),
       column(6,DT::dataTableOutput("filtered_results")))
)
# APP ------------------------------------------------
shinyApp(ui <- dashboardPage(
  header,
  sidebar,
  body
),
# Server ----------------------------------------------------------
shinyServer(function(input,output){

  output$screening_params <- renderDataTable({
    tibble(params = c("filter(mpg >= 21)",
                      "mutate(cyl_sq = cyl^2)",
                      rep_len(NA_character_,8))) %>%
      DT::datatable(rownames = F,
                    editable = T)
  })

  output$filtered_results <- renderDataTable({
    mtcars %>%
      # input$screening_params
      DT::datatable()

  })

})) 

I would be open to using either the DT or rhandsontable or any alternative that someone can think of. Unfortunately it doesn't look like you can grab the edited table values in DT, but hopefully it provides a good example of what I am after.

I have tried every combination of quosures and mapping functions that I can think of to no avail.

Anyone have any ideas?

Answer 1

This is a slightly different approach, but maybe you'll find it useful. I'm using sqldf as an SQL query engine, and users can manipulate the data using ad-hoc SQL queries on the given dataset. If this isn't what you're after, I at least hope it will give you some hints on how to do it with dplyr syntax.

If you still go for the dplyr option and you have a string with the requested manipulation, you can use the reactive method getDataset to evaluate the expression you received from the user, manipulate your dataset. Then call getDataset in the renderDataTable method, like I did in the attached code.

Example for evaluating a string expression:

eval(parse(text="res <- mtcars %>% filter(mpg < 20)"))

For the SQL option:

library(shiny)
library(shinydashboard)
library(DT)
library(dplyr)
library(sqldf)
# Header ------------------------------------------------
header <- dashboardHeader(title = "Example Screening")
# Sidebar ------------------------------------------------
sidebar <- dashboardSidebar(collapsed = TRUE)
# Body ------------------------------------------------
body <-
  dashboardBody(
    fluidRow(
      #column(6,h3("Screening Parameters")),
      column(6,h3("Filtered/Modified Results"))),
    fluidRow(
      textInput("sql","SQL Query",value = "SELECT * FROM dataset"),
      DT::dataTableOutput(("filtered_results"))
    )
  )
# APP ------------------------------------------------
shinyApp(ui <- dashboardPage(
  header,
  sidebar,
  body
),
# Server ----------------------------------------------------------
shinyServer(function(input,output){

  ## A new function to load data and perform the SQL query on it
  getDataset <- reactive({
    query <- input$sql
    dataset <- mtcars
    sqldf::sqldf(query)
  })

  output$filtered_results <- renderDataTable({
    getDataset() %>%
      DT::datatable()

  })

})) 

Answer 2

Thanks to Omri374 for bouncing ideas back and forth with me.

Simple Application that satisfies the requirements:

library(shinydashboard)
library(DT)
library(dplyr)
library(rhandsontable)
# Header ------------------------------------------------
header <- dashboardHeader(title = "Example Screening")
# Sidebar ------------------------------------------------
sidebar <- dashboardSidebar()
# Body ------------------------------------------------
body <-
  dashboardBody(
    fluidRow(
      column(6,h3("Screening Parameters")),
      column(6,h3("Filtered/Modified Results"))),
    fluidRow(
      column(6, rHandsontableOutput("hot")),
      column(6, dataTableOutput("filtered_results")))
  )
# APP ------------------------------------------------
shinyApp(ui <- dashboardPage(
  header,
  sidebar,
  body
),
# Server ----------------------------------------------------------
shinyServer(function(input,output){

  output$hot <- renderRHandsontable({
    tibble(params = c("filter(mpg >= 21)",
                      "filter(cyl == 4)",
                      "mutate(cyl_sq = cyl^2)",
                      "select(cyl,mpg,drat)",
                      rep_len(NA_character_,6))) %>%
      rhandsontable() %>%
      hot_cols(colWidths = 500)
  })

  output$filtered_results <- renderDataTable({


    df <- mtcars

    params <- input$hot %>%
      hot_to_r() %>%
      filter(!is.na(params),params != "") %>%
      mutate(params = ifelse(row_number() == max(row_number()),
                             params,
                             paste0(params," %>% "))) %>%
      pull(params) %>%
      str_c(collapse = "")


    if(length(params)>=1){

      eval(parse(text = paste0("filt_df <- df %>%",params)))

    } else {

      filt_df <- df

    }

    filt_df %>%
      datatable()



  })

}))