For loop and sqldf

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

For loop and sqldf

mdvaan
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.
Reply | Threaded
Open this post in threaded view
|

Re: For loop and sqldf

djmuseR
Hi:

Try

split(DF, DF$C)

Does that work?

Dennis

On Fri, Apr 29, 2011 at 1:27 PM, mathijsdevaan <[hidden email]> 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.
>
> ______________________________________________
> [hidden email] 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.
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: For loop and sqldf

mdvaan
Great, thanks! Still need to figure out all these functions... ;)
Reply | Threaded
Open this post in threaded view
|

Re: For loop and sqldf

David Winsemius
In reply to this post by mdvaan

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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: For loop and sqldf

Jeff Newmiller
Putting SQL columns/variables into square brackets is valid syntax for sqlite. Expecting sqlite to share variables with R is not, so there was only one necessary change.
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<[hidden email]> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.

David Winsemius <[hidden email]> wrote:

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 brack
 ets. >
        } > > 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_____________________________________________
[hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: For loop and sqldf

mdvaan
This post has NOT been accepted by the mailing list yet.
Jeff Newmiller wrote
Putting SQL columns/variables into square brackets is valid syntax for sqlite. Expecting sqlite to share variables with R is not, so there was only one necessary change.
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<[hidden email]> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.

David Winsemius <[hidden email]> wrote:

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 brack
 ets. >
        } > > 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_____________________________________________
[hidden email] 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]]

______________________________________________
[hidden email] 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.
Thanks for the input, but I still can't get this to work:

DF = data.frame(read.table(textConnection("  A  B  C
80  8025  1995
80  8026  1995
80  8029  1995
81  8026  1996
82  8025  1997
82  8026  1997
83  8025  1997
83  8027  1997
90  8026  1998
90  8027  1998
90  8029  1998
84  8026  1999
84  8027  1999
85  8028  1999
85  8029  1999"),head=TRUE,stringsAsFactors=FALSE))
list<-sort(unique(DF$C))

for (t in 1:length(list))
        {
        year = as.character(list[t])
        data[year]<-sqldf(paste('select * from DF where C = [year]))
        }

# This statement does not close, but I don't see any missing brackets
#or

for (t in 1:length(list))
        {
        year = as.character(list[t])
        data[year]<-sqldf(paste('select * from DF where C = ', year,  sep=""))
        }

# This one gives an error: Error in data[year] <- sqldf(paste("select * from DF where C = ", year,  : object # of type 'closure' is not subsettable

What am I missing here? Thanks!
Reply | Threaded
Open this post in threaded view
|

Re: For loop and sqldf

Gabor Grothendieck
In reply to this post by mdvaan
On Fri, Apr 29, 2011 at 4:27 PM, mathijsdevaan <[hidden email]> 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

______________________________________________
[hidden email] 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.