reordering a data.frame

See ?reshape

Uwe Ligges


Achaz von Hardenberg wrote:

Hi,
I have a data.frame which looks like this:

id     est0     est1  est2    est3    est4    est5    est6    est7
 1    1       2       3       1       7       9       3       4
 2    4       1       1       7       6       5       1       2
[...]


I would like to reorder it to obtain the following:

id    est     VALUE
1     0       1
1     1       2
1     2       3
1     3       1
1     4       7
1     5       9
1     6       3
1     7       4
2     0       4
2     1       1
2     2       1
2     3       7
2     4       6
2     5       5
2     6       1
2     7       2
[...]

I would probably use reshape as others have already pointed out
but just for the fun of it note that if you convert it from a data frame
to a matrix to a table to a data frame in this order:

  data.frame ==> matrix ==> table ==> data.frame

We can get the result by using matrix() followed by as.data.frame.table()
(which converts it to table and then data frame all in one).  The
one feature of this solution is that each step is relatively simple.

I assume the row and column ordering is important, given the Subject, 
but if it is not then the second line can be simplified to 
just:   as.data.frame.table(mat, resp = "VALUE")

mat <- matrix(unlist(dat[,-1]), nrow(dat), dimnames = list(id = dat$id, est = 0:7))
as.data.frame.table(t(mat), resp = "VALUE")[c(2:1,3)]

id est VALUE
1   1   0     1
2   1   1     2
3   1   2     3
4   1   3     1
5   1   4     7
6   1   5     9
7   1   6     3
8   1   7     4
9   2   0     4
10  2   1     1
11  2   2     1
12  2   3     7
13  2   4     6
14  2   5     5
15  2   6     1
16  2   7     2