Filter for RenderDatatable

Question

I have a simple data where I need to apply filter for renderDatatable (not renderTable). But I am not able to execute the below code

as <- data.frame(ColA = c("India","USA","Canada","India","USA","Canada","Australia"),ColB=c("A","B","C","D","E","F","G"),ColC=c("Jan","Jan","Mar","Feb","Jan","Apr","Apr"))
library(shiny)
library(DT)
ui <- fluidPage(
sidebarLayout(
sidebarPanel(selectInput("x","Operations",choices = 
                           c("table"),
                         multiple=FALSE,selectize = TRUE),
             (selectInput("ColB","Choices from ColB", choices = as$ColB,
                          multiple=TRUE,selectize = TRUE))),
mainPanel(h6("Here it is"),
          dataTableOutput("message")
)
)
)
server <- function(input, output, session) {
r1 <- reactive({
if(input$x == "table")
{
  tab <- as.data.frame.matrix(table(as$ColC, as$ColA))
  tab <- tab[rownames(tab) %in% input$ColB, ]
}
})
output$message <- renderDataTable({
datatable(r1(), rownames = TRUE)
})
}
shinyApp(ui, server)

I don't know what is the output you expect, but maybe you can replace ```as.data.frame(table(as$ColC,as$ColA[as$ColC == input$ColC]))``` by ```as.data.frame(as[as$ColC %in% input$ColC, ])```. Can you describe more precisely the output you expect ? — bretauv, Jul 10 '19 at 20:55
Thanks. I need to the split of ColC and ColA so i used table function. But i am not getting. — Jana P, Jul 11 '19 at 12:50
Please describe precisely what output you expect : for example, do you want the three columns of the ```as``` dataframe to be displayed ? What column do you want the filter to be based on ? I can display a table with a filter but it is really hard to know if the output I obtain is the one you want — bretauv, Jul 11 '19 at 19:56
The output should be table(as$ColC,as$ColA) but with filtering option of ColC. The output I need should be under renderDatatable and not under rendertable — Jana P, Jul 12 '19 at 11:19

bretauv · Answer 1 · 2019-07-14T20:58:23.447

0

As said in comments, I set up as reactive the ColC choices in server. To do that, you need to create an input ColC in the ui part. I then removed the second column of your dataframe (ColB) in server.

I think the solution to your problem is :

as <- data.frame(ColA = c("India","USA","Canada","India","USA","Canada","Australia"),ColB=c("A","B","C","D","E","F","G"),ColC=c("Jan","Jan","Mar","Feb","Jan","Apr","Apr"))
library(shiny)
ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(selectInput("x","Operations",choices = 
                               c("table"),
                             multiple=FALSE,selectize = TRUE),
                 (selectInput("ColC","Choices from ColC", choices = as$ColC,
                              multiple=TRUE,selectize = TRUE))),
    mainPanel(h6("Here it is"),
              dataTableOutput("message")
    )
  )
)
server <- function(input, output, session) {
  r1 <- reactive({
    if(input$x == "table")
    {
      tab <- as.data.frame(as[as$ColC %in% input$ColC, ])
      tab <- tab[, -2]
    }
  })
  output$message <- renderDataTable({
    r1()
    })
}
shinyApp(ui, server)

EDIT : you need to transform a table in a dataframe. In order to do that, you need to use as.data.frame.matrix function as described here (How to convert a table to a data frame). The final code is :

as <- data.frame(ColA = c("India","USA","Canada","India","USA","Canada","Australia"),ColB=c("A","B","C","D","E","F","G"),ColC=c("Jan","Jan","Mar","Feb","Jan","Apr","Apr"))
library(shiny)
ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(selectInput("x","Operations",choices = 
                               c("table"),
                             multiple=FALSE,selectize = TRUE),
                 (selectInput("ColC","Choices from ColC", choices = as$ColC,
                              multiple=TRUE,selectize = TRUE))),
    mainPanel(h6("Here it is"),
              dataTableOutput("message")
    )
  )
)
server <- function(input, output, session) {
  r1 <- reactive({
    if(input$x == "table")
    {
      tab <- as.data.frame.matrix(table(as$ColC, as$ColA))
      tab <- tab[as$ColC %in% input$ColC, ]
    }
  })
  output$message <- renderDataTable({
    r1()
  })
}
shinyApp(ui, server)

EDIT #2 : I replace as$ColC by rownames(tab). Also I add library(DT) in order to put the option rownames = TRUE in renderDataTable. Here's the final solution (I hope so) :

  as <- data.frame(ColA = c("India","USA","Canada","India","USA","Canada","Australia"),ColB=c("A","B","C","D","E","F","G"),ColC=c("Jan","Jan","Mar","Feb","Jan","Apr","Apr"))
library(shiny)
library(DT)
ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(selectInput("x","Operations",choices = 
                               c("table"),
                             multiple=FALSE,selectize = TRUE),
                 (selectInput("ColC","Choices from ColC", choices = as$ColC,
                              multiple=TRUE,selectize = TRUE))),
    mainPanel(h6("Here it is"),
              dataTableOutput("message")
    )
  )
)
server <- function(input, output, session) {
  r1 <- reactive({
    if(input$x == "table")
    {
      tab <- as.data.frame.matrix(table(as$ColC, as$ColA))
      tab <- tab[rownames(tab) %in% input$ColC, ]
    }
  })
  output$message <- renderDataTable({
    datatable(r1(), rownames = TRUE)
  })
}
shinyApp(ui, server)

edited Jul 14 '19 at 20:58

answered Jul 12 '19 at 11:27

bretauv

7,756
2
20
57

Almost close. But when i apply the filter, it filters as a dataframe. But table(as$ColC,as$ColA) gives me Australia Canada India USA Apr 1 1 0 0 Feb 0 0 1 0 Jan 0 0 1 2 Mar 0 1 0 0 Above table I need as a dataframe – Jana P Jul 12 '19 at 14:53
some issues in the code I believe. When I filtered April, no data is showing up. Actually, there is a data for April – Jana P Jul 13 '19 at 05:28
Also Yes, the table values are not correct. Because when I filtered for Jan, there should be 1 for India and 2 for USA. But it is showing differently. Can you try the code once – Jana P Jul 13 '19 at 05:31
Hope you got my point :) I am still trying and not able to solve this – Jana P Jul 13 '19 at 14:08
I found the solution and add a second edit to my post – bretauv Jul 14 '19 at 20:55
Please tell me if it works. If it does, then I will delete this answer (and its comments) and will simplify it because I think there's no need to have such a long post with two major edits – bretauv Jul 14 '19 at 22:10
Perfect thanks. It is working. But just to change a bit, if I change the filter to input$ColB (by changing selectInput("ColB","Choices from ColB", choices = as$ColB,multiple=TRUE,selectize = TRUE)). It should work right? But it is not. However this was my counter question because the solution should always be adaptive right so. Anyways thanks – Jana P Jul 15 '19 at 15:36
it is working for me, maybe you forgot to replace ```ColC``` by ```ColB``` in the ```server``` part. I'm re-writing the solution, please validate it once it is done (I will delete this one after you validate the "clean" one) – bretauv Jul 15 '19 at 16:59
nope. Looks like you have missed to replace ColC by ColB. It is still the same. Infact i did change in server also – Jana P Jul 15 '19 at 17:28
verify again, I replaced every ```ColC``` by ```ColB``` in the script (except when you generate the ```as``` dataframe of course) – bretauv Jul 15 '19 at 17:40
Nope it is not replaced:) Guess some issues. I do not see ColC replaced by ColB – Jana P Jul 15 '19 at 17:43
I mean I did not replace the code in the answer but I replaced it in my personal script and it worked. There's no interest in putting it in the answer – bretauv Jul 15 '19 at 17:44
Nope it is not working, I have edited my code for your reference. Please check – Jana P Jul 15 '19 at 17:50
is there an error ? mine works perfectly fine with ColB instead of ColC everywhere – bretauv Jul 15 '19 at 17:51
well I don't know why. Check a last time that you use the final code I provided, replace ColC by ColB everywhere except in the first line (where ```as``` is created) and try. If this does not work, I can't figure out why. However, you can validate the answer I created today and which reunites the two edits on the first answer because you said it works well – bretauv Jul 15 '19 at 17:56
also, you should remove the updates you made to the code in your question : the principle is that you ask a question and somebody answers. If you replace your question by the answer, then a random visitor doesn't know what was the original question and how it was solved, he just sees some functional code, which is useless for him – bretauv Jul 15 '19 at 19:57
the problem is that you let ColC instead of ColB in ```tab <- as.data.frame.matrix(table(as$ColC, as$ColA))``` – bretauv Jul 15 '19 at 21:21

score 0 · Answer 2 · answered Jul 15 '19 at 17:03

As said in comments, I set up as reactive the ColC choices in server. To do that, you need to create an input ColC in the ui part. I then removed the second column of your dataframe (ColB) in server.

Moreover, you need to transform a table in a dataframe. In order to do that, you need to use as.data.frame.matrix as described here (How to convert a table to a data frame).

Finally, I replace as$ColC by rownames(tab) in the server part. Also I add library(DT) in order to put the option rownames = TRUE in renderDataTable. Here's the final solution :

  as <- data.frame(ColA = c("India","USA","Canada","India","USA","Canada","Australia"),ColB=c("A","B","C","D","E","F","G"),ColC=c("Jan","Jan","Mar","Feb","Jan","Apr","Apr"))
library(shiny)
library(DT)
ui <- fluidPage(
  sidebarLayout(
    sidebarPanel(selectInput("x","Operations",choices = 
                               c("table"),
                             multiple=FALSE,selectize = TRUE),
                 (selectInput("ColC","Choices from ColC", choices = as$ColC,
                              multiple=TRUE,selectize = TRUE))),
    mainPanel(h6("Here it is"),
              dataTableOutput("message")
    )
  )
)
server <- function(input, output, session) {
  r1 <- reactive({
    if(input$x == "table")
    {
      tab <- as.data.frame.matrix(table(as$ColC, as$ColA))
      tab <- tab[rownames(tab) %in% input$ColC, ]
    }
  })
  output$message <- renderDataTable({
    datatable(r1(), rownames = TRUE)
  })
}
shinyApp(ui, server)

Filter for RenderDatatable

2 Answers2