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.
--
View this message in context:
http://r.789695.n4.nabble.com/For-loop-and-sqldf-tp3484559p3484559.html
Sent from the R help mailing list archive at Nabble.com.
Hi: Try split(DF, DF$C) Does that work? Dennis On Fri, Apr 29, 2011 at 1: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. > > > -- > View this message in context: http://r.789695.n4.nabble.com/For-loop-and-sqldf-tp3484559p3484559.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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. >
Great, thanks! Still need to figure out all these functions... ;) -- View this message in context: http://r.789695.n4.nabble.com/For-loop-and-sqldf-tp3484559p3484715.html Sent from the R help mailing list archive at Nabble.com.
On Apr 29, 2011, at 4:27 PM, mathijsdevaan 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)) ; require(sqldf); data <-list() # added inits> for (t in 1:length(list)) > { > year = as.character(list[t]) > data[year]<-sqldf('select * from DF where C = [year]')#I see you have already gotten a workable answer, but thought you might want to see if this would work: data[year]<-sqldf(paste('select * from DF where C = ', year, sep="") ) # Two changes ... let `year` get evaluated and don't put `year` in brackets.> } >> data $`1995` [1] 8025 8026 8029 $`1996` [1] 8026 $`1997` [1] 8025 8026 8027 $`1998` [1] 8026 8027 8029 $`1999` [1] 8026 8027 8028 8029> I am trying to split up the data.frame into 5 new ones, one for > every year. > > >-- David Winsemius, MD West Hartford, CT
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