# [R] folding table into a matrix

Gabor Grothendieck ggrothendieck at myway.com
Fri Sep 24 03:52:24 CEST 2004

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

:
: I'm just getting started with R, so feel free to point me to the
: 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))))

```