On Fri, Apr 29, 2011 at 4:27 PM, mathijsdevaan <mathijsdev...@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
______________________________________________
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.
