[R] For loop and sqldf

[Gabor Grothendieck]

On Fri, Apr 29, 2011 at 4:27 PM, mathijsdevaan <mathijsdevaan at gmail.com> wrote:
> Hi list,
>
> Can anyone tell my why the following does not work? Thanks a lot! Your help
> is very much appreciated.
>
> DF = data.frame(read.table(textConnection("    B  C  D  E  F  G
> 8025  1995  0  4  1  2
> 8025  1997  1  1  3  4
> 8026  1995  0  7  0  0
> 8026  1996  1  2  3  0
> 8026  1997  1  2  3  1
> 8026  1998  6  0  0  4
> 8026  1999  3  7  0  3
> 8027  1997  1  2  3  9
> 8027  1998  1  2  3  1
> 8027  1999  6  0  0  2
> 8028  1999  3  7  0  0
> 8029  1995  0  2  3  3
> 8029  1998  1  2  3  2
> 8029  1999  6  0  0  1"),head=TRUE,stringsAsFactors=FALSE))
> list<-sort(unique(DF$C))
> for (t in 1:length(list))
>        {
>        year = as.character(list[t])
>        data[year]<-sqldf('select * from DF where C = [year]')
>        }
>
> I am trying to split up the data.frame into 5 new ones, one for every year.
>
>

This has already been answered but just thought I would point out that
the perhaps subtle point is that sqldf automatically loads data frames
that it finds in your sql statement into the data base but it does not
do anything with non-data frame variables.

Thus DF is a data frame in your workspace is loaded into the database
but year is not.

Also at least in sqlite you can't put a constant in square brackets.

To construct the desired sql string you can use paste, sprintf or
gsubfn's perl-like $ string interpolation which is invoked by
prefacing sqldf with fn$ and prefacing the variable to interpolate
with a $.   gsubfn is automatically loaded by sqldf.   See
http://gsubfn.googlecode.com for more on fn.

library(sqldf)

# test data
DF <- data.frame(a = 1:10, C = rep(1970:1971, each = 5))
year <- 1970

sqldf(paste("select * from DF where C =", year))

sqldf(sprintf("select * from DF where C=%s", year))

fn$sqldf("select * from DF where C = $year")

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