Aggregate reactive data

[hv11709]

Hi all,

I am building an app using Shiny IO. This is how my data looks like
OrderDate Company TotalOrder ApprovalID State

I am letting users filter for OrderDate and Company using reactive. However, I want to aggregate that filtered data on ApprovalID, State and various other columns that I have to see within the chosen timeframe and company how does the total order vary by these factors

Here is my code:

if (interactive()) {

ui = fluidPage(theme = 'custom.css',

               h2("X",id="big-heading"),
               titlePanel(" ",windowTitle = "X"),    
               
               tabsetPanel(
                   tabPanel("Plot", fluid = TRUE,
                            sidebarLayout(
                                sidebarPanel(id = 'sidebar',
                                    dateRangeInput("dateinput","Selects Dates",start = Sys.Date()-30,end = Sys.Date()),
                                             selectInput("companyinput", "Select Comp", choices=choices1,multiple = TRUE,selected = NULL) 
                                             ),
                                mainPanel(fluidRow(
                                    splitLayout(cellWidths = c("50%", "50%"), plotOutput("coolplot"), plotOutput("coolplot3")),
                                    plotOutput("coolplot1"),
                                    textOutput("resultX")
                                )
                                )
                            ),
                            
                   ),
                   tabPanel("Summary", fluid = TRUE,
                            sidebarLayout(
                                sidebarPanel(id = 'sidebar',
                                    dateRangeInput("dateinput","Selects Dates",start = Sys.Date()-30,end = Sys.Date()),
                                             selectInput("companyinput", "Select Comp", choices=choices1,selected = NULL)
                                ),
                                mainPanel(
                                    textOutput("resultY")
                                )
                            )
                   )
                   
               )
) 

server = function(input, output) {
    
    filtered <- reactive({
        if(is.null(input$companyinput)){
        bc3 %>%
            filter(as.Date(OrderDate) > input$dateinput[1],
                   as.Date(OrderDate) >= input$dateinput[2],
            )
            
            }
        else{
            bc3 %>%
                filter(as.Date(OrderDate) > input$dateinput[1],
                       as.Date(OrderDate) >= input$dateinput[2],
                       CompanyNameID == input$companyinput
                )
            
        }
    })

"Is there a way I can create a data frame of the filtered data so I can manipulate and aggregate as and when I want

    output$results <- renderTable({
        filtered()
    },spacing = "s",align = 'c',digits = 0,bordered = TRUE,hover = TRUE,width = "auto")
    

    
    output$coolplot <- renderPlot({
        df <- cbind(filtered$BuyerID,filtered$DocTotalOrder)
        ag <- aggregate(df,by = df$BuyerID,fun = sum)
        ggplot(filtered(), aes(y=DocTotalOrder, x=BuyerID,label = DocTotalOrder)) +
            geom_bar(stat="identity",fill = "#FF9999")
    })

    
    output$coolplot1 <- renderPlot({
        ggplot(filtered()) + 
            stat_summary(aes(x = BuyerID, y = DocTotalOrder), 
                         fun.y = function(x) sum(x), 
                         geom = "bar")
    })
    
    output$coolplot3 <- renderPlot({
        ggplot(filtered(), aes(y=DocTotalOrder, x=BuyerID)) +
            geom_bar(stat="identity",fill = "#004701")
    })
    
    
    
}

# Run the application 
shinyApp(ui = ui, server = server)

}

[FJCC]

Does this do the sort of thing you want to do? The Date format in TBL is not right but the aggregation does work.

library(shiny)
library(dplyr)
DF <- data.frame(Date = seq.Date(from = as.Date("2019-01-01"), to = as.Date("2019-01-14"), by = "day"),
                 Company = rep(c("A", "B"), 7),
                 Value = rep(1:2,7))
ui <- fluidPage(
  dateRangeInput(inputId = "DATE", "Date", start = "2019-01-01", end = "2019-01-14"),
  selectInput("Comp", label = "Company", choices = list("A", "B"), multiple = TRUE, "A"),
  tableOutput("TBL"),
  tableOutput("AG")
)

server <- function(input, output) {
 Tbl <- reactive({
   DF %>% filter(Company %in% input$Comp, Date >= input$DATE[1], Date <= input$DATE[2])
 })
 
 output$TBL <- renderTable(DF)
 
 output$AG <- renderTable({
   Tbl() %>% group_by(Company) %>% summarize(SUM = sum(Value))
 })
  
}

[hv11709]

@FJCC

Thanks for the input.

I want to clarify something.

Here is how my input looks:
OrderDate | Company | ApprovalID | Amount |State
2009-01-02 | A | AC | 200 | NY
2009-01-05 | A | BT | 20000 | NY
2009-01-04 | A | AC | 500 | CO
2009-01-02 | B | XY | 2000 | TX
2009-05-41 | B | XY | 30 | TX
2009-04-21 | D | CR | 2222 | BR
I am letting my user input a date range and company
So if the user chooses dates between 2009-01-01 and 2009-01-31 and company A. I want to have a table that shows:

OrderDate | Company | ApprovalID | Amount |State
2009-01-02 | A | AC | 200 | NY
2009-01-05 | A | BT | 20000 | NY
2009-01-04 | A | AC | 500 | CO
But then in the same dashboard I want to show different charts:

1. Aggregated at ApprovalID and Amount i.e. let us say a bar chart showing
   AC spent 700 and BT spent 20000 (previous filters or order date and company intact)

then other chart
Aggregated at State and Amount i.e let us say a bar chart showing state and amount (once again order date and company that user has chosen)

If I use your code, it changes all these charts together. I want the subset aggregation to not affect the actual table output or anything.

Thanks,
HV

[FJCC]

Sorry, I guess I am being dense. My example has two versions of the data available for charting or tables. The variable DF contains the original unfiltered data set and the function Tbl() returns a filtered version of DF with filtering chosen by the user. If you want a graph to always show the original data, build it from DF. If you want to graph the filtered data, use Tbl() as the data source.

Can you provide some code that illustrates the problem you are having? When you paste the code into your post, place three back tics, ```, on the line above and below the code so that it gets formatted as code. The back tic key is just above the TAB key on US keyboards.

[hv11709]

@FJCC

Hi. Thanks for the response. The thing is I do not want to chart from the original data. But I do want the filtered data to be able to subset further.

For example,

I am only letting my user filter out between dates so the tbl() will store this information. But how do I ensure that this can be grouped by further:

Let us say you are the user and you choose the dates 10-21-2019 to 11-21-2019.

Now,

1. I want to show you the records between this date which is simple render table df
2. I want to show you the records between these dates further aggregated at state or approver level i.e.
   how do I further show you that between this set of dates, this user spent this much amount or this total amount was spent by this state.

[FJCC]

You can change the Variable input and the graph will show either the sum of Value by State or ApprovalID. This could also be done with functions from dplyr instead of the aggregate function, but I do not know how to set that up for the nonstandard evaluation used in dplyr.

library(shiny)
library(dplyr)
library(ggplot2)

set.seed(1)
DF <- data.frame(Date = seq.Date(from = as.Date("2019-01-01"), 
                                 to = as.Date("2019-01-14"), by = "day"),
                 Company = rep(c("A", "B"), 7), 
                 ApprovalID = sample(c("AD", "GF"), 14, replace = TRUE),
                 State = sample(c("NY", "CO"), 14, replace = TRUE),
                 Value = rep(1:2,7))
ui <- fluidPage(
  fluidRow(
  column(width = 3, dateRangeInput(inputId = "DATE", "Date", 
                                   start = "2019-01-01", end = "2019-01-14")),
  column(width = 3, selectInput("Comp", label = "Company",
                                choices = list("A", "B"), multiple = TRUE, "A")),
  column(width = 3), selectInput("VAR", "Variable", 
                                 choices = list(  "ApprovalID" = "3", "State"= "4"))#choices are the column numbers.
  ),
  fluidRow(
  column(width = 4, tableOutput("TBL"), tableOutput("AG")),
  column(width = 8, plotOutput("Plot"))
  )
)

server <- function(input, output) {
  Tbl <- reactive({
    DF %>% filter(Company %in% input$Comp, Date >= input$DATE[1], Date <= input$DATE[2])
  })
  
  output$TBL <- renderTable(DF)
  
  output$AG <- renderTable({
    Tbl() 
  })
  output$Plot <- renderPlot({
    tmp <- aggregate(Tbl()[["Value"]], by = Tbl()[as.numeric(input$VAR)], FUN = sum)
    ggplot(tmp, aes_string(x = colnames(tmp)[1], y = "x")) + geom_col()
  })
}

# Return a Shiny app object
shinyApp(ui = ui, server = server)