R help - Apr 2009 - create objects in a loop and adding sqlQuery content to them

Hi there,

I've got a database or rather spreadsheet with several columns and rows.
For one column named sites I want to loop through all possible values  
and retrieve
all data out of the database where site = x and write it into an  
object named 'sitex_data'.

Somehow I'm really missing something as I'm not able to create these  
sitex_data objects with
the database values, neither using list nor assign...

Here some code snipplets:
 >library (RODBC)
 >channel <- odbcConnectExcel2007 ("biomass_data.xlsx")
 >site_data  <-  sqlQuery(channel, "select site_no from 
[biomass_data
$] group by site_no")

#Here the values I want to loop through
 >str(site_data)
'data.frame':   44 obs. of  1 variable:
  $ site_no: num  4 7 9 10 15 16 17 18 19 20 ...

#Here my first try [error message on the line within the loop, saying  
something like:
# 'recursive indexing on level 2 failed']
  sites_object_list <- vector("list",99)
  for(i in site_data) {
    sites_object_list[[i]] <- sqlQuery(channel, paste("select * from   
[biomass_data$] where site_no = ",i,sep=""))
  }

#Here my second try [error message  on the line within the loop,  
saying something like:
# 'only the first element will be used as variable name']
for(i in site_data) {
	assign(paste("site",i,"_data",sep=""),
sqlQuery(channel,
paste("select * from  [biomass_data$] where site_no =
",i,sep="")))

}

I would be very, very glad if anybody sends me a clue about this, as  
I'm a obviuos Newbie using
R...

Thanks,

          Katharina

Hello

> -----Original Message-----
> From: r-help-bounces at r-project.org
[mailto:r-help-bounces at r-project.org]
> On Behalf Of Katharina May
> Sent: Monday, April 20, 2009 5:05 PM
> To: r-help at r-project.org
> Subject: [R] create objects in a loop and adding sqlQuery content to
them
> 
> Hi there,
> 
> I've got a database or rather spreadsheet with several columns and
rows.
> For one column named sites I want to loop through all possible values
> and retrieve
> all data out of the database where site = x and write it into an
> object named 'sitex_data'.
> 
> Somehow I'm really missing something as I'm not able to create
these
> sitex_data objects with
> the database values, neither using list nor assign...
> 
> Here some code snipplets:
>  >library (RODBC)
>  >channel <- odbcConnectExcel2007 ("biomass_data.xlsx")
>  >site_data  <-  sqlQuery(channel, "select site_no from 
[biomass_data
> $] group by site_no")
> 
> #Here the values I want to loop through
>  >str(site_data)
> 'data.frame':   44 obs. of  1 variable:
>   $ site_no: num  4 7 9 10 15 16 17 18 19 20 ...
There's only 44 of them - can you show the whole thing?
e.g.
>site_data
Also check the class of site_data$site_no
It might be of class character or factor and
not just numeric as it might initially appear.

> 
> #Here my first try [error message on the line within the loop, saying
> something like:
> # 'recursive indexing on level 2 failed']
>   sites_object_list <- vector("list",99)
If there's a site number larger than 99 this will be problematic.
>   for(i in site_data) {
>     sites_object_list[[i]] <- sqlQuery(channel, paste("select *
from
> [biomass_data$] where site_no = ",i,sep=""))
>   }
> 
> #Here my second try [error message  on the line within the loop,
> saying something like:
> # 'only the first element will be used as variable name']
> for(i in site_data) {
> 	assign(paste("site",i,"_data",sep=""),
sqlQuery(channel,
> paste("select * from  [biomass_data$] where site_no =
",i,sep="")))
> 
> }
I'm guessing one of the site numbers is not just a simple integer
but has two numbers with a space or something in it.
> 
> I would be very, very glad if anybody sends me a clue about this, as
> I'm a obviuos Newbie using
> R...
> 
> Thanks,
> 
>           Katharina
> 
> ______________________________________________
> 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.



 

 

Steven McKinney, Ph.D.

Statistician
Molecular Oncology and Breast Cancer Program
British Columbia Cancer Research Centre

email: smckinney at bccrc.ca
tel: 604-675-8000 x7561

BCCRC
Molecular Oncology
675 West 10th Ave, Floor 4
Vancouver B.C. 
V5Z 1L3

Canada

> 
> >
> > #Here my first try [error message on the line within the loop,
saying
> > something like:
> > # 'recursive indexing on level 2 failed']
> >   sites_object_list <- vector("list",99)
> 
> If there's a site number larger than 99 this will be problematic.
Sorry, this isn't correct - R will just increase the list size if a 
site number is bigger than 99.


Steve McKinney

On Apr 20, 2009, at 8:04 PM, Katharina May wrote:
> Hi there,
>
> I've got a database or rather spreadsheet with several columns and  
> rows.
> For one column named sites I want to loop through all possible  
> values and retrieve
> all data out of the database where site = x and write it into an  
> object named 'sitex_data'.
>
> Somehow I'm really missing something as I'm not able to create
these
> sitex_data objects with
> the database values, neither using list nor assign...
>
> Here some code snipplets:
> >library (RODBC)
> >channel <- odbcConnectExcel2007 ("biomass_data.xlsx")
> >site_data  <-  sqlQuery(channel, "select site_no from 
[biomass_data
> $] group by site_no")
>
> #Here the values I want to loop through
> >str(site_data)
> 'data.frame':   44 obs. of  1 variable:
> $ site_no: num  4 7 9 10 15 16 17 18 19 20 ...
>
> #Here my first try [error message on the line within the loop,  
> saying something like:
> # 'recursive indexing on level 2 failed']
> sites_object_list <- vector("list",99)
> for(i in site_data) {
>   sites_object_list[[i]] <- sqlQuery(channel, paste("select * from
> [biomass_data$] where site_no = ",i,sep=""))
> }
This does not appear to be a dataset to which we have access, so I  
will take an untested stab at this. What happens if you assign the  
results of the function unique() around site_data$site_no to your  
index, i? That way you would stand a chance of referring to the sites  
in a manner that R might understand and to only use the site numbers  
once apiece in the loop. I'm not a competent SQL programmer so i have  
no comments on that part.

David Winsemius, MD
Heritage Laboratories
West Hartford, CT