Re: [R] Reference a variable inside a string and another for object assingments
Thanks Gabor! This is exactly what I was searching for! --- And it works like a charm. On Fri, Aug 17, 2012 at 1:03 PM, Gabor Grothendieck wrote: > On Thu, Aug 16, 2012 at 3:30 PM, Kenneth Rose wrote: >> Hi R community >> >> I copied a bit of my R code that gets some data from a database. You >> won't be able to run the code, but I am a beginner so you will >> probably understand what going on. >> >> I would like to make a variable I can refer to inside the sqlQuery. >> Instead of writing the start date and time (ex SP.lokaldatotid >= >> '2005-01-01 00:00:00') inside the query I would like to define it in >> the beginning of the code, so I don't have to fiddle with a lot of >> dates each time I wan't to change it. I would like to do this for a >> few of the variables and maybe even make a list/array I can loop >> through, so I don't have to write the same code multiple times (for >> SYS and DK1). >> >> I have searched for a solution for two days now, but I am not sure >> what it's called and are probably writing the wrong queries :-) >> >> >> Thank you for your help! >> >> Kenneth >> >> My code: >> >> library(xts) >> library(RODBC) >> >> >> #Define channnel (i configured my own SYSTEM-DNS, via ODBC) >> ch <- odbcConnect("DI2") >> >> # >> ## GET DATA## >> # >> >> ## SYSTEM spot ## >> # Hent data fra SQL Server >> sys <- sqlQuery (ch, paste("SELECT SP.lokaldatotid, SP.pris FROM >> DataIndsamling2.dbo.SpotPriser SP", >>"WHERE (SP.omraade_id= 0 AND >> SP.lokaldatotid >= '2005-01-01 00:00:00')")) >> #Definer dato og tid kolonne >> sys$lokaldatotid <- as.POSIXct(sys$lokaldatotid) >> >> #Make a XTS object >> sys_xts <- xts(sys[,-1], order.by=sys[,1]) >> >> # Recalculate data from hours to daily, monthly and yearly averages >> sys_xts_daily <- apply.daily(sys_xts, FUN=mean) >> sys_xts_monthly <- apply.monthly(sys_xts, FUN=mean) >> sys_xts_yearly <- apply.yearly(sys_xts, FUN=mean) >> >> >> ## DK1 spot # >> # Hent data fra SQL Server >> dk1 <- sqlQuery (ch, paste("SELECT SP.lokaldatotid, SP.pris FROM >> DataIndsamling2.dbo.SpotPriser SP", >>"WHERE (SP.omraade_id= 5 AND >> SP.lokaldatotid >= '2005-01-01 00:00:00')")) > > You can use paste as shown in the example in ?sqlGetResults or > fn$ in the gsubfn package can do quasi-perl-like string interpolation. > With fn you just preface any command with fn$ and then its > arguments are subject to string interpolation as explained further > in ?fn and http://gsubfn.googlecode.com. e.g. > > library(gsubfn) > > id <- 5 > date <- '2005-01-01 00:00:00' > > dk1 <- fn$sqlQuery (ch, "SELECT SP.lokaldatotid, SP.pris FROM > DataIndsamling2.dbo.SpotPriser SP > WHERE (SP.omraade_id = $id AND > SP.lokaldatotid >= '$date' )" ) > > -- > Statistics & Software Consulting > GKX Group, GKX Associates Inc. > tel: 1-877-GKX-GROUP > email: ggrothendieck at gmail.com __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reference a variable inside a string and another for object assingments
I tried these two and testquery2 now does the job :) Somehow the str_c function from the stringr package doesn't work when I run the sqlQuery function even though the testquery string is the same as testquery2 string. Thanks for the pointers! fromdate <- c("'2005-01-01 00:00:00'") testquery <- str_c("SELECT SP.lokaldatotid, SP.pris FROM DataIndsamling2.dbo.SpotPriser SP", "WHERE (SP.omraade_id= 0 AND SP.lokaldatotid >= ",fromdate[1],")") testquery2 <- paste("SELECT SP.lokaldatotid, SP.pris FROM DataIndsamling2.dbo.SpotPriser SP", "WHERE (SP.omraade_id= 0 AND SP.lokaldatotid >=" ,fromdate[1],")") I still can't figure out how to assign an existing xts object to a name from a list or array. I have data that i want to rename to one of the names i a character vector. Example: names <- c("data2", "data3") data <- xts(11:10, Sys.Date()+1:10) # The next part is not real code but just so you get the basic idea: names[1] <- data remove(data) data2 should now be assigned to data. Thanks for your help with the first part!! /Kenneth On Thu, Aug 16, 2012 at 10:57 PM, MacQueen, Don wrote: > I sometimes do this sort of thing with "tricks" like this: > > sql <- "select * from mytable where dt >= 'ADATE'" > > dbGetQuery( con, gsub('ADATE', '2012-06-12 23:14', sql) ) > > > > Or if mydates is a vector of dates stored as a POSIXt object: > > for (id in mydates) { > dbGetQuery( con, gsub('ADATE', format(id), sql) ) > } > > > -Don > > -- > Don MacQueen > > Lawrence Livermore National Laboratory > 7000 East Ave., L-627 > Livermore, CA 94550 > 925-423-1062 > > > > > > On 8/16/12 12:30 PM, "Kenneth Rose" wrote: > >>Hi R community >> >>I copied a bit of my R code that gets some data from a database. You >>won't be able to run the code, but I am a beginner so you will >>probably understand what going on. >> >>I would like to make a variable I can refer to inside the sqlQuery. >>Instead of writing the start date and time (ex SP.lokaldatotid >= >>'2005-01-01 00:00:00') inside the query I would like to define it in >>the beginning of the code, so I don't have to fiddle with a lot of >>dates each time I wan't to change it. I would like to do this for a >>few of the variables and maybe even make a list/array I can loop >>through, so I don't have to write the same code multiple times (for >>SYS and DK1). >> >>I have searched for a solution for two days now, but I am not sure >>what it's called and are probably writing the wrong queries :-) >> >> >>Thank you for your help! >> >>Kenneth >> >>My code: >> >>library(xts) >>library(RODBC) >> >> >>#Define channnel (i configured my own SYSTEM-DNS, via ODBC) >>ch <- odbcConnect("DI2") >> >># >>## GET DATA## >># >> >>## SYSTEM spot ## >># Hent data fra SQL Server >>sys <- sqlQuery (ch, paste("SELECT SP.lokaldatotid, SP.pris FROM >>DataIndsamling2.dbo.SpotPriser SP", >> "WHERE (SP.omraade_id= 0 AND >>SP.lokaldatotid >= '2005-01-01 00:00:00')")) >>#Definer dato og tid kolonne >>sys$lokaldatotid <- as.POSIXct(sys$lokaldatotid) >> >>#Make a XTS object >>sys_xts <- xts(sys[,-1], order.by=sys[,1]) >> >># Recalculate data from hours to daily, monthly and yearly averages >>sys_xts_daily <- apply.daily(sys_xts, FUN=mean) >>sys_xts_monthly <- apply.monthly(sys_xts, FUN=mean) >>sys_xts_yearly <- apply.yearly(sys_xts, FUN=mean) >> >> >>## DK1 spot # >># Hent data fra SQL Server >>dk1 <- sqlQuery (ch, paste("SELECT SP.lokaldatotid, SP.pris FROM >>DataIndsamling2.dbo.SpotPriser SP", >> "WHERE (SP.omraade_id= 5 AND >>SP.lokaldatotid >= '2005-01-01 00:00:00')")) >>#Definer dato og tid kolonne >>dk1$lokaldatotid <- as.POSIXct(dk1$lokaldatotid) >> >>#Lav om til xts object >>dk1_xts <- xts(dk1[,-1], order.by=dk1[,1]) >> >>#Data omregnet fra time ->> daglig, måned, årlige gennemsnit >>dk1_xts_daily <- apply.daily(dk1_xts, FUN=mean) >>dk1_xts_monthly <- apply.monthly(dk1_xts, FUN=mean) >>dk1_xts_yearly <- apply.yearly(dk1_xts, FUN=mean) >> >>__ >>R-help@r-project.org mailing list >>https://stat.ethz.ch/mailman/listinfo/r-help >>PLEASE do read the posting guide >>http://www.R-project.org/posting-guide.html >>and provide commented, minimal, self-contained, reproducible code. > __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
[R] Reference a variable inside a string and another for object assingments
Hi R community I copied a bit of my R code that gets some data from a database. You won't be able to run the code, but I am a beginner so you will probably understand what going on. I would like to make a variable I can refer to inside the sqlQuery. Instead of writing the start date and time (ex SP.lokaldatotid >= '2005-01-01 00:00:00') inside the query I would like to define it in the beginning of the code, so I don't have to fiddle with a lot of dates each time I wan't to change it. I would like to do this for a few of the variables and maybe even make a list/array I can loop through, so I don't have to write the same code multiple times (for SYS and DK1). I have searched for a solution for two days now, but I am not sure what it's called and are probably writing the wrong queries :-) Thank you for your help! Kenneth My code: library(xts) library(RODBC) #Define channnel (i configured my own SYSTEM-DNS, via ODBC) ch <- odbcConnect("DI2") # ## GET DATA## # ## SYSTEM spot ## # Hent data fra SQL Server sys <- sqlQuery (ch, paste("SELECT SP.lokaldatotid, SP.pris FROM DataIndsamling2.dbo.SpotPriser SP", "WHERE (SP.omraade_id= 0 AND SP.lokaldatotid >= '2005-01-01 00:00:00')")) #Definer dato og tid kolonne sys$lokaldatotid <- as.POSIXct(sys$lokaldatotid) #Make a XTS object sys_xts <- xts(sys[,-1], order.by=sys[,1]) # Recalculate data from hours to daily, monthly and yearly averages sys_xts_daily <- apply.daily(sys_xts, FUN=mean) sys_xts_monthly <- apply.monthly(sys_xts, FUN=mean) sys_xts_yearly <- apply.yearly(sys_xts, FUN=mean) ## DK1 spot # # Hent data fra SQL Server dk1 <- sqlQuery (ch, paste("SELECT SP.lokaldatotid, SP.pris FROM DataIndsamling2.dbo.SpotPriser SP", "WHERE (SP.omraade_id= 5 AND SP.lokaldatotid >= '2005-01-01 00:00:00')")) #Definer dato og tid kolonne dk1$lokaldatotid <- as.POSIXct(dk1$lokaldatotid) #Lav om til xts object dk1_xts <- xts(dk1[,-1], order.by=dk1[,1]) #Data omregnet fra time ->> daglig, måned, årlige gennemsnit dk1_xts_daily <- apply.daily(dk1_xts, FUN=mean) dk1_xts_monthly <- apply.monthly(dk1_xts, FUN=mean) dk1_xts_yearly <- apply.yearly(dk1_xts, FUN=mean) __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.