Re: [R] Make matrix from SQL query result

From: bogdan romocea <br44114_at_gmail.com>
Date: Sat 25 Jun 2005 - 03:49:19 EST


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@sabrefund.com]
> Sent: Friday, June 24, 2005 12:36 PM
> To: r-help@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@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
>



R-help@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 Received on Sat Jun 25 03:54:14 2005

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:33:02 EST