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:[EMAIL PROTECTED] > 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