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]]
Gilbert,
This is untested, but something like:
function(x) {
uniq.col <- unique(x[,1])
uniq.row <- unique(x[,2])
ans <- array(NA, c(length(uniq.row), length(uniq.col)),
list(uniq.row, uniq.col))
ans[cbind(match(x[,2], uniq.row), match(x[,1], uniq.col))] <- x[,3]
ans
}
Pat
Gilbert Wu wrote:
>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
>
>
>
>
>
You can use reshape which seems popular this week!> rrequity_id date returns 1 1 20050619 0.12 2 2 20050619 0.03 3 1 20050620 -0.01 4 2 20050620 0.01> reshape(rr,timevar="equity_id",direction="wide",idvar="date")date returns.1 returns.2 1 20050619 0.12 0.03 3 20050620 -0.01 0.01 -----Original Message----- From: r-help-bounces at stat.math.ethz.ch [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Gilbert Wu Sent: 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
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
>