R Shiny: Formatting reactive data.frame from sql query

Question

I am trying to figure out how i can change type of the column after getting the data from reactive sql query...

for example when i fetch the data from database, some of the columns are characters, and i wish them to be factors. And some of the columns are numeric (which is correct) but i need them to be displayed as factors in datatable (for use in the datatable, as the column named is Tolerance, and tolerance cannot be filtered by range, it should be one single number).

Simple code:

library(ROracle)
library(shiny)
library(DT)


server <- shinyServer(
  function(input, output, session) {

    con <- dbConnect(dbDriver("Oracle"),"xx/K",username="user",password="pwd")
    tableList <- dbListTables(con,schema="K")

    updateSelectizeInput(session, "tabnames", server = TRUE, choices = tableList)

      sqlOutput <- reactive({
        sqlInput <- paste("select rownum * from K.",input$tabnames)
        dbGetQuery(con$cc, sqlInput, stringsAsFactors = T)#it hasnt worked neither
      })

    output$table <- DT::renderDataTable(sqlOutput(), server=TRUE, rownames=TRUE, filter="top", options=list(pageLength=10))

    session$onSessionEnded(function() { dbDisconnect(con) })
  })

ui_panel <- 
  tabPanel("Test",
           sidebarLayout(
             sidebarPanel( 
             ),
             mainPanel(
               selectizeInput("tabnames",label = "server side", choices = NULL),

               tableOutput("out"),
               tableOutput("table")
             )
           )
  )


ui <- shinyUI(navbarPage("Test",ui_panel))

runApp(list(ui=ui,server=server))

When i have tried simply:

output$table <- DT::renderDataTable({

sqlOutput()$HOEHE_TOLP <- as.factor(sqlOutput()$HOEHE_TOLP)

datatable(sqlOutput(), server=TRUE, rownames=TRUE, filter="top", options=list(pageLength=10))})

It didnt work, and gave me an error:

Error in sqlOutput()$HOEHE_TOLP <- as.factor(sqlOutput()$HOEHE_TOLP) : 
  ungültige (NULL) linke Seite in Zuweisung

*invalid (NULL) left side of assignment

Any ideas how i can convert some columns to factors for reactive data frame?

Cheers

You can save the data frame locally in the reactive function and then invoke as.factor() on needed columns. — Gopala
– Gopala, Commented Feb 22, 2016 at 11:11
That might be tricky as there will be lots of users, that are going to use the app, second that filtered data is going to be quite big... — Mal_a
– Mal_a, Commented Feb 22, 2016 at 11:47
Not sure what you mean by that because saving and returning the data frame into a variable is no different from what the reactive returns (a data frame). — Gopala
– Gopala, Commented Feb 22, 2016 at 12:41

mlegge · Accepted Answer · 2016-02-24 14:38:40Z

1

EDIT:

Simply replace this expression:

output$table <- DT::renderDataTable(sqlOutput(), server=TRUE, 
  rownames=TRUE, filter="top", options=list(pageLength=10))

With:

output$table <- DT::renderDataTable({
  intermed <- sqlOutput()
  intermed$HOEHE_TOLP <- as.factor(intermed$HOEHE_TOLP)
  datatable(intermed) %>% formatStyle("RUND2_MITT", color = 'red', 
    backgroundColor = 'lightyellow', fontWeight = 'bold')
}, server=TRUE, rownames=TRUE, filter="top", options=list(pageLength=10))

Here is a self contained example:

library(DT)
library(shiny)

ui <- fluidPage(
  actionButton("inst", "Instigate Reactive"),
  dataTableOutput("test")
)

server <- function(input, output){
  data <- eventReactive(input$inst, {
    iris
  })

  output$test <- renderDataTable({
    set <- data()
    set$Sepal.Length <- as.factor(set$Sepal.Length)
    datatable(set) %>% formatStyle("Petal.Length", color = 'red', 
                                   backgroundColor = 'lightyellow',
                                   fontWeight = 'bold')
  })
}

shinyApp(ui, server)

edited Feb 24, 2016 at 14:38

answered Feb 22, 2016 at 20:51

mlegge

6,9233 gold badges44 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mal_a Over a year ago

Thanks so much!It worked perfectly, but just one thing: When i used %>% formatStyle("RUND2_MITT", color = 'red', backgroundColor = 'lightyellow', fontWeight = 'bold') for datatable, it gave me an error:

Error in appendFormatter(x$options$rowCallback, columns, colnames, rownames,  :    You specified the columns: RUND2_MITT, but the column names of the data are

. Do You have any idea how i can implement formatStyle?

Collectives™ on Stack Overflow

R Shiny: Formatting reactive data.frame from sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related