R help - Sep 2004 - folding table into a matrix

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.

Thanks.

-- Gene

Have you looked at "index arrays" in "An Introduction to R",
available as the first menu option after "help.start()" in R?  The 
version I got with R 1.9.1 for Windows includes the following: 

     > x <- array(1:20,dim=c(4,5))   # Generate a 4 by 5 array.

     > x

          [,1] [,2] [,3] [,4] [,5]

     [1,]    1    5    9   13   17

     [2,]    2    6   10   14   18

     [3,]    3    7   11   15   19

     [4,]    4    8   12   16   20

     > i <- array(c(1:3,3:1),dim=c(3,2))

     > i                             # i is a 3 by 2 index array.

          [,1] [,2]

     [1,]    1    3

     [2,]    2    2

     [3,]    3    1

     > x[i]                          # Extract those elements

     [1] 9 6 3

     > x[i] <- 0                     # Replace those elements by zeros.

     > x

          [,1] [,2] [,3] [,4] [,5]

     [1,]    1    5    0   13   17

     [2,]    2    0   10   14   18

     [3,]    0    7   11   15   19

     [4,]    4    8   12   16   20

     >
      The key point here is that "i" is a 3x2 array, and x[i]
references
the 3 elements of x that have the row and column indices in x.  Does 
this provide the information you need? 

      hope this helps.  spencer graves

Gene Cutler wrote:
> 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.
>
> Thanks.
>
> -- Gene
>
> ______________________________________________
> 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

-- 
Spencer Graves, PhD, Senior Development Engineer
O:  (408)938-4420;  mobile:  (408)655-4567

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.
It's not completely trivial, since you're relying on ordering
information: the missing col.5 value goes in the 2nd row, but you only
know that because values are ordered in row blocks.

If you supply the rows that things belong in, the task does becomes
simple:

Row_Index <- rep(1:3,c(5,4,5))
M <- matrix(NA, 3, 5)
M[cbind(Row_Index,Column_Index)] <- Value

Now how to compute Row_Index from Column_Index? If you know that each
group starts with a "1", you might use (rename Column_Index as cc for
brevity) 
> cumsum(cc==1)
 [1] 1 1 1 1 1 2 2 2 2 3 3 3 3 3

If you can't make that assumption, you might consider something like
> cumsum(c(1,diff(cc)<0))
 [1] 1 1 1 1 1 2 2 2 2 3 3 3 3 3


-- 
   O__  ---- Peter Dalgaard             Blegdamsvej 3  
  c/ /'_ --- Dept. of Biostatistics     2200 Cph. N   
 (*) \(*) -- University of Copenhagen   Denmark      Ph: (+45) 35327918
~~~~~~~~~~ - (p.dalgaard at biostat.ku.dk)             FAX: (+45) 35327907

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))))