[R] folding table into a matrix

[Gabor Grothendieck]

Gene Cutler <gcutler <at> amgen.com> writes:

: 
: I'm just getting started with R, so feel free to point me to the 
: appropriate documentation if this is already answered somewhere (though 
: I've been unable to find it myself).  This does seem like a rather 
: basic question.
: I want to fold a table into a matrix.  The table is formatted like so:
: 
: Column_Index  Value
: 1             486
: 2             688
: 3             447
: 4             555
: 5             639
: 1             950
: 2             881
: 3             1785
: 4             1216
: 1             612
: 2             790
: 3             542
: 4             1310
: 5             976
: 
: And I want to end up with something like this:
: 
:        [,1]  [,2]  [,3]  [,4]  [,5]
: [1,]   486   688   447   555   639
: [2,]   950   881  1785  1216    NA
: [3,]   612   790   512  1310   976
: 
: Since not all the rows are complete, I can't just reformat using 
: matrix(), I need to go by the index information in the Column_Index 
: column.  This seems like something simple to do, but I'm stumped.

Suppose z is the two column matrix which we wish
to reshape. Using Peter's cumsum expression to distinguish
the groups, tapply ts to each group turning each into
a ts object.  cbind these ts objects together (which 
has the effect of adding the NAs at the end automatically)
and finally transpose the result (which also turns it
back into a matrix).  The following one liner solves
the example problem:

t(do.call("cbind",tapply(z[,2], cumsum(z[,1]==1), ts)))


This approach also extends to the case where the indices in
column 1 have gaps.  In this case we need an irregular time
series package.  Using zoo we carry out the analogous
operations using the indices in column 1 for a group as the
times and the values in column 2 as the time series values.
We make use of zoo's multiway merge and Peter's second, more
general, cumsum expression to define the groups:

require(zoo)
f <- function(i) zoo(z[i,2], z[i,1])
g <- cumsum(c(1,diff(z[,1])<0))
t(as.matrix(do.call("merge", tapply(1:nrow(z), g, f))))