How to merge a "static dataframe" and a "reactive dataframe"

[Igor G]

Hello everybody,

I have a doubt about how to merge a "static dataframe" and a "reactive dataframe". I am getting my data from a ODBC passing input values from ui.R into the WHERE clause, so this way I have a "reactive dataframe" which is refreshed whenever I change the input settings (in this case they are dates).

Let's say my "reactive dataframe" has 4 variables (v1, v2, v3, v4) where v1 and v2 are factors which could have NA values. Let's say my "static dataframe" contains two columns called v1 and v2 (of the same length) which are the lists of all posible values (levels) for factors v1 and v2, and an extra column called w1.

What I would like to do is to add to my "reactive dataframe"  the values from the column w1 according to values of v1 and v2. For example:

Reactive dataframe:

v1   v2   v3    v4
A    A     5      01-01-13
A    A     10    03-01-13
B           15    03 -01-13
B    A     6      04 -01-13
....

Static dataframe:

v1   v2   w1   
A    A     Bart    
A    B     Lisa        
B    A     Homer     
B    B     Marge

The resulting dataframe I'd like to get:

v1   v2   v3    v4                w1
A    A     5      01-01-13     Bart
A    A     10    03-01-13     Bart
B           15    03 -01-13   
B    A      6     04 -01-13    Homer
...

I have tried to get it with merge() function but it seems to be a recursion problem because I get this error:

Error : evaluation nested too deeply: infinite recursion / options(expressions=)?

I provide a basic code to illustrate the problem. Any help is appreciated.

Thanks in advance.
Igor





ui.R ========================================================================

library(shiny)

shinyUI(pageWithSidebar(
 

 sidebarPanel(

   

    # The input fields for starting and ending dates

    textInput("dateSTA","Starting date (dd-mm-yy)", '01-01-13'),
    textInput("dateEND","Ending date(dd-mm-yy)", '10-01-13'),

     

),

 

 mainPanel(

  

    tableOutput("outputtable")

   

  )

 
  )
)

server.R ========================================================================

library(shiny)

# Here I create a new table from a csv file on my disk
external_table<-read.csv(file="mypath", options)


# Here I connect with my database
library(RODBC)
mychannel<-odbcConnect("myDB", uid = "myui", pwd = "mypwd", believeNRows=FALSE)


shinyServer(function(input, output) {
 

 # Here I get a table from a query againts my database

 mytable<-reactive({ sqlQuery(conexion, paste('

SELECT

v1, v2, v3, v4

FROM adatabasetable

WHERE var4 BETWEEN \'',input$dateSTA,'\' AND \'',input$dateEND,'\'

'))
                             
})

  # Then I merge both tables this way (I think this is the wrong part of my code) and refresh mytable
 
 mytable<-reactive({ merge(mytable(), external_table, all.x=TRUE) })


# Then I print  mytable in the output
 
  output$outputtable<- renderTable({     
     

as.data.frame(mytable())  

  })

})

[Mike C]

have you tried a simple intermediate?

mytable<-reactive({ merge(mytable(), external_table, all.x=TRUE) })

to

mytable<-reactive({
mine=mytable()
this=merge(mine, external_table, all.x=TRUE)
this
})

[Greg D]

This is caused because both your functions are called mytable and the infinite recursion should be clear from this line:

 mytable<-reactive({ merge(mytable(), external_table, all.x=TRUE) })

You simply need to have

mytable <-reactive({ do SQL stuff here })
mytable2 <-reactive({merge(mytable(), external_table, all.x=TRUE) })

And then call mytable2() in your output. When input changes, it will cause mytable() to reactivate, which will cause mytable2() to reactivate, which will cause output to change.

[Igor G]

Hello,

Thank you very much. Yesterday I just tried to do what you say and it works.

Thanks a lot