Indexing summarise function in shiny

[vinayprakash808]

Hi all, I am trying to summarise few numeric columns that is declared in input$parameters. Below is the code for that. But I am not getting the output

With Indexing (not Working)

sales <- Sample %>%
         group_by(`Order Date`, Category) %>%
         summarise(`Total Sales` = sum(input[[parameters]]))

Without Indexing (This is working)

sales <- Sample %>%
         group_by(`Order Date`, Category) %>%
         summarise(`Total Sales` = sum(Sales))

FYI : input$parameters consists of "Sales", "Profit" etc........

[Hlynur]

Hi there,

That's an exciting functionality. I've included an app (with some fake data for the Sample dataframe so that you can run it) that does what I think you're trying to do. If I'm not misunderstanding you're essentially trying to get the sum of the column that is selected, right? In my app I've given that input the name select_column to refer to it as input$select_column.

The thing that you need to keep in mind is that you can't pass the character string that you get from selectInput() into summarise(). For the summarise() function you need to input a symbol, not a string. So Sales instead of "Sales". What your summarise function is "seeing" in the code you posted is similar to this:

Sample %>% summarise(`Total Sales` = sum("Sales"))
#Error: Problem with `summarise()` input `Total Sales`.
#x invalid 'type' (character) of argument
#ℹ Input `Total Sales` is `sum("Sales")`.
#Run `rlang::last_error()` to see where the error occurred.

So what you need to do is change the string into a symbol, using the as.symbol function, and then tell R to evaluate it as such within the environment with the eval function. So this works:

> Sample %>% summarise(`Total Sales` = sum(eval(as.symbol("Sales"))))
## A tibble: 1 x 1
#  `Total Sales`
#          <dbl>
#1           600

So for an input called select_column this is possible:

Sample %>% 
      group_by(`Order Date`, Category) %>% 
      summarize(total = sum(eval(as.symbol(input$select_column))))

Here's that within the context of a functioning demo app that you can run. Hope this helps

library(shiny)
library(dplyr)
library(DT)

set.seed(0)
Sample <- tibble(`Order Date` = c(rep(as.Date("2020-08-01"), 10),
                                  rep(as.Date("2020-08-02"), 10)),
                 `Category` = c(rep("Category A", 5),
                                rep("Category B", 10),
                                rep("Category A", 5)),
                 Sales = round(rnorm(n = 20, mean = 30, sd = 5)),
                 Profit = Sales * rnorm(n = 20, mean = 0.75, sd = 0.1)
)

selectable_columns <- c("Sales", "Profit")

ui <- fluidPage(
  selectInput(inputId = "select_column", label = "Select which column to sum", 
                 choices = selectable_columns),
  dataTableOutput("my_table")
)

server <- function(input, output, session) {
  
  output$my_table <- renderDataTable({
    Sample %>% 
      group_by(`Order Date`, Category) %>% 
      summarize(total = sum(eval(as.symbol(input$select_column)))) %>% 
      DT::datatable()
      
  })
  
}

shinyApp(ui, server)

[vinayprakash808]

Great thanks a lot. I have a last question, Using this as.symbol can we also make the below changes

Instead of below

group_by(`Order Date`, Category)

Can we do below? where As per input$date and input@category the grouping happens

group_by(eval(as.symbol(input$Date)), eval(as.symbol(input$category)))

[Hlynur]

Hi, it's not exactly like that, but pretty close.

The first thing you have to change in your code is that you need to refer to the inputId of your selectInput when you use input$xyz... so if your inputId is select_group_columns you'll need to use input$select_group_columns.

As you now have the possibility of multiple columns to group by, you need to use rlang::syms (or at least, that's the way I do it). So to keep things consistant I've also changed as.symbol to sym (note that's a singular sym, not syms) which is the rlang equivelant of as.symbol. As far as this example is conserned, it's exactly the same. I've also changed eval to that bang bang operator !!, to match our use of the triple bang operator !!! for the possibility of multiple columns when we use syms for the grouping columns.

So now our code looks like this.

      group_by(!!!syms(input$select_group_columns)) %>% 
      summarize(total = sum(!!sym(input$select_sum_column))) %>% 

Here's that in the context of the demo app. Note that you now need to have rlang installed, and load it in your shiny app. Best of luck

library(shiny)
library(dplyr)
library(DT)
library(rlang)

set.seed(0)
Sample <- tibble(`Order Date` = c(rep(as.Date("2020-08-01"), 10),
                                  rep(as.Date("2020-08-02"), 10)),
                 `Category` = c(rep("Category A", 5),
                                rep("Category B", 10),
                                rep("Category A", 5)),
                 Sales = round(rnorm(n = 20, mean = 30, sd = 5)),
                 Profit = Sales * rnorm(n = 20, mean = 0.75, sd = 0.1)
)

selectable_group_columns <- c("Order Date", "Category")
selectable_sum_columns <- c("Sales", "Profit")

ui <- fluidPage(
  selectInput(inputId = "select_group_columns", label = "Select which column(s) to group by", multiple = TRUE,
              choices = selectable_group_columns),
  selectInput(inputId = "select_sum_column", label = "Select which column to sum", 
                 choices = selectable_sum_columns),
  dataTableOutput("my_table")
)

server <- function(input, output, session) {
  
  output$my_table <- renderDataTable({
    Sample %>% 
      group_by(!!!syms(input$select_group_columns)) %>% 
      summarize(total = sum(!!sym(input$select_sum_column))) %>% 
      DT::datatable()
      
  })
  
}

shinyApp(ui, server)

[vinayprakash808]

Thats excellent, Thanks a ton