thr3ads.net - R help - [R] Reference a variable inside a string and another for object assingments [Aug 2012]

If this information is useful, please help other people find it:
Share via:

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

head link

[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

head link

[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

head link

[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

R help - Aug 2012 - Reference a variable inside a string and another for object assingments

[R] Reference a variable inside a string and another for object assingments

[R] Reference a variable inside a string and another for object assingments

[R] Reference a variable inside a string and another for object assingments

[R] Reference a variable inside a string and another for object assingments

Possibly Parallel Threads