[R] create objects in a loop and adding sqlQuery content to them

[Steven McKinney]

 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