R help - Mar 2011 - SQLDF - Submitting Queries with R Objects as Columns

Fellow R programmers,

I'd like to submit SQLDF statements with R objects as column names.

For example, I want to assign "X" to "var1" 
(var1<-"X") and then refer to
"var1" in the SQLDF statement.  SQLDF needs to understand that when I
reference "var1", it should look for "X" in the dataframe.

This is necessary because my SQLDF is part of a larger function that I call
that repeatedly with different column names.

Code below... thank you in advance!

Mike


library(sqldf)

testdf<-data.frame(c(1,2,3,4,5,6,7,8,9,10),c(1,1,1,2,2,2,3,3,3,3))
names(testdf)<-c("X","Y")

# Works as intended
sqldf("select sum(X) as XSUM,
       Y             as Y
       from testdf
       group by Y")

# Now... can I reference var1 in the code?
var1<-"X"

# Unsuccessful Atteps
sqldf("select sum(var1) as XSUM,
       Y             as Y
       from testdf
       group by Y")

sqldf("select sum(get(var1)) as XSUM,
       Y             as Y
       from testdf
       group by Y")

sqldf("select sum(return(var1)) as XSUM,
       Y             as Y
       from testdf
       group by Y")



-- 
Michael Schumacher
mike.schumacher@gmail.com
Manager Data & Analytics, ValueClick
818-851-8638

	[[alternative HTML version deleted]]

On Wed, Mar 9, 2011 at 11:41 AM, Mike Schumacher
<mike.schumacher at gmail.com> wrote:
> Fellow R programmers,
>
> I'd like to submit SQLDF statements with R objects as column names.
>
> For example, I want to assign "X" to "var1"
?(var1<-"X") and then refer to
> "var1" in the SQLDF statement. ?SQLDF needs to understand that
when I
> reference "var1", it should look for "X" in the
dataframe.
>
> This is necessary because my SQLDF is part of a larger function that I call
> that repeatedly with different column names.
>
> Code below... thank you in advance!
>
> Mike
>
>
> library(sqldf)
>
> testdf<-data.frame(c(1,2,3,4,5,6,7,8,9,10),c(1,1,1,2,2,2,3,3,3,3))
> names(testdf)<-c("X","Y")
>
> # Works as intended
> sqldf("select sum(X) as XSUM,
> ? ? ? Y ? ? ? ? ? ? as Y
> ? ? ? from testdf
> ? ? ? group by Y")
>
> # Now... can I reference var1 in the code?
> var1<-"X"
>
Here are two ways:

sqldf(sprintf("select sum(%s) XSUM, Y from testdf group by Y", var1))

fn$sqldf("select sum($var1) XSUM, Y from testdf group by Y")

See ?sprintf

fn comes from the gsubfn package (which is automatically pulled in by
sqldf) and adds quasi perl style string interpolation to the arguments
passed to the function call it prefaces.  See
http://gsubfn.googlecode.com and ?fn


-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

You're submitting queries for SQLDF to execute as strings. So, if you want
to use a variable column name, sprintf() or paste() your statement together,
like:

sqldf(sprintf('select sum(%s) as XSUM, Y as Y from testdf group by Y',
var1))

--
Robert Tirrell | rpt@stanford.edu | (607) 437-6532
Program in Biomedical Informatics | Butte Lab | Stanford University

> sqldf("select sum(return(var1)) as XSUM,
>       Y             as Y
>       from testdf
>       group by Y")
>
>
>
> --
> Michael Schumacher
> mike.schumacher@gmail.com
> Manager Data & Analytics, ValueClick
> 818-851-8638
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> 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.
>
	[[alternative HTML version deleted]]