how to take tables of (x,y,V) rows and get a matrix/table where the value at position x,y is V

Question

I have a data frame that looks like:

> ta
   ranks omp  ALLA1
1    512   4  772.9
2   1024   2  769.9
3   2048   1  914.2
4    256   8  932.3
5    128  16 1352.0
6    256  16  948.4
7    512   8  761.5
8   1024   4  667.9
9   2048   2  744.9
10  4096   1  956.7

and I want to end up with some kind of matrix that looks like:

    256   512   1024    2048   4096
 1                      914.2  956.7
 2               769.9  744.9
 4        772.9  667.9
 8  932.3 761.5 
16

I'm not too fussed what appears in missing entries.

http://stackoverflow.com/a/9617424/210673 now has a big list of ways to do this. — Aaron left Stack Overflow, Mar 23 '12 at 16:23

score 3 · Accepted Answer · answered Mar 30 '11 at 11:55

Try this:

> xtabs(ALLA1 ~ omp + ranks, ta)
    ranks
omp     128    256    512   1024   2048   4096
  1     0.0    0.0    0.0    0.0  914.2  956.7
  2     0.0    0.0    0.0  769.9  744.9    0.0
  4     0.0    0.0  772.9  667.9    0.0    0.0
  8     0.0  932.3  761.5    0.0    0.0    0.0
  16 1352.0  948.4    0.0    0.0    0.0    0.0

score 1 · Answer 2 · answered Mar 30 '11 at 11:06

Running this:

with(ta, reshape(ta[order(omp, ranks),], v.names="ALLA1", idvar="omp",
  timevar="ranks", direction="wide"))

You get something very similar:

  omp ALLA1.2048 ALLA1.4096 ALLA1.1024 ALLA1.512 ALLA1.256 ALLA1.128
3   1      914.2      956.7         NA        NA        NA        NA
2   2      744.9         NA      769.9        NA        NA        NA
1   4         NA         NA      667.9     772.9        NA        NA
4   8         NA         NA         NA     761.5     932.3        NA
5  16         NA         NA         NA        NA     948.4      1352

score 0 · Answer 3 · answered Mar 30 '11 at 10:40

To write an answer reconstructing the ta data frame may be useful:

lines = "   ranks omp  ALLA1
1    512   4  772.9
2   1024   2  769.9
3   2048   1  914.2
4    256   8  932.3
5    128  16 1352.0
6    256  16  948.4
7    512   8  761.5
8   1024   4  667.9
9   2048   2  744.9
10  4096   1  956.7"
cn = as.character(read.fwf(textConnection(lines), width=c(3, 5, 4, 7),
  stringsAsFactors=FALSE, strip.white=TRUE)[1,])
ta = read.fwf(textConnection(lines), width=c(3, 5, 4, 7), skip=1,
  col.names=cn)[,-1]

score 0 · Answer 4 · answered Apr 28 '11 at 07:26

Well, someone should show how to do it with row/column indexing...

ta <- structure(list(ranks = c(512L, 1024L, 2048L, 256L, 128L, 256L, 
512L, 1024L, 2048L, 4096L), omp = c(4L, 2L, 1L, 8L, 16L, 16L, 
8L, 4L, 2L, 1L), ALLA1 = c(772.9, 769.9, 914.2, 932.3, 1352, 
948.4, 761.5, 667.9, 744.9, 956.7)), .Names = c("ranks", "omp", 
"ALLA1"), class = "data.frame", row.names = c(NA, -10L))

out <- with(ta, {
  ranks <- factor(ranks)
  omp <- factor(omp)
  out <- matrix(nrow=nlevels(omp), ncol=nlevels(ranks), 
                dimnames=list(levels(omp), levels(ranks)))
  out[cbind(omp, ranks)] <- ALLA1
  out
})

With a result of

> print(out, na="")
    128   256   512  1024  2048  4096
1                         914.2 956.7
2                   769.9 744.9      
4             772.9 667.9            
8       932.3 761.5                  
16 1352 948.4

how to take tables of (x,y,V) rows and get a matrix/table where the value at position x,y is V

4 Answers4