Kenneth Rose
2012-Aug-16 19:30 UTC
[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)
Greg Snow
2012-Aug-16 20:15 UTC
[R] Reference a variable inside a string and another for object assingments
Perhaps the sprintf function is what you are looking for. It is one way to insert information from a variable into a string. A couple of other options are paste, paste0, and the gsubfn package, but I think sprintf will be simplest for what you are asking. On Thu, Aug 16, 2012 at 1:30 PM, Kenneth Rose <kennethrose82 at gmail.com> 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 at 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.-- Gregory (Greg) L. Snow Ph.D. 538280 at gmail.com
MacQueen, Don
2012-Aug-16 20:57 UTC
[R] Reference a variable inside a string and another for object assingments
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" <kennethrose82 at gmail.com>
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 at 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.
Gabor Grothendieck
2012-Aug-17 11:03 UTC
[R] Reference a variable inside a string and another for object assingments
On Thu, Aug 16, 2012 at 3:30 PM, Kenneth Rose <kennethrose82 at gmail.com> 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