[R] Make matrix from SQL query result

[bogdan romocea]

It may be better to do this in SQL. The code below works for an
arbitrary number of IDs and handles missing values.

test <- data.frame(id=rep(c(1,2),10),date=sort(c(1:10,1:10)),ret=0.01*-9:10)
idret <- list()
ids <- sort(unique(test$id))
for (i in ids) {
	idret[[as.character(i)]] <- test[test$id == i,]
	idret[[as.character(i)]] <- idret[[as.character(i)]][,-1]
	colnames(idret[[as.character(i)]])[2] <- paste("ret",i,sep="")
	}
allret <- idret[[as.character(ids[1])]]
for (i in ids[2:length(ids)]) {
	allret <- merge(allret,idret[[as.character(i)]],all=TRUE)
	}



> -----Original Message-----
> From: Gilbert Wu [mailto:gilbert.wu at sabrefund.com]
> Sent: Friday, June 24, 2005 12:36 PM
> To: r-help at stat.math.ethz.ch
> Subject: [R] Make matrix from SQL query result
> 
> 
> Hi,
>  
> I am trying to form a matrix from a SQL query result.
>  
> The SQL query result looks like this:
>  
> equity_id         date              returns
> 1                 20050619          0.12
> 2                 20050619          0.03
> 1                 20050620          -0.01
> 2                 20050620          0.01
>  
> The target matrix looks like this:
>  
>  
> date                     1               2
> 20050619           0.12            0.03
> 20050620           -0.01           0.01
>  
> Any suggestion would be much appreciated.
>  
> Many Thanks.
>  
> Gilbert
> 
> 	[[alternative HTML version deleted]]
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
>