[R] SQLDF - Submitting Queries with R Objects as Columns

[Gabor Grothendieck]

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