Odp: Data frame with 3 columns to matrix
Hi Michael Bach <phaebz at gmail.com> napsal dne 19.04.2011 14:21:13:
Petr PIKAL <petr.pikal at precheza.cz> writes:
Hi r-help-bounces at r-project.org napsal dne 19.04.2011 09:46:47:
Dear R Users,
Lets assume I have this data frame:
x y z
1 1.00 5 0.5
2 1.02 5 0.7
3 1.04 7 0.1
4 1.06 9 0.4
x and y columns are sorted and the values not necessarily integers. z
values are not sorted. Now I would like to create a matrix out of
this
with x as first column values and y as first row values. Matrix
element
a_11 shall be left NA. The a_ij should have the z value for the corresponding x and y pair. The result shall be some sort of a grid
and
then e.g. look like:
[,1] [,2] [,3] [,4] [,5]
[1,] NA 5 6 7 9 (y)
[2,] 1.00 0.5 NA NA NA
[3,] 1.02 0.7 NA NA NA
[4,] 1.04 NA NA 0.1 NA
[5,] 1.06 NA NA NA 0.4
(x)
This example is just for illustration. The resulting matrix should
have
more numeric values than NA's.
I am not sure if this is the solution you want tab<-xtabs(z~x+y, data=df) tab[tab==0]<-NA
This looks right, but the resulting table does not have rows == columns and there are also no missing values (0 or NA) which should be there. I
They are there try str(tab) With little effort you can get exactly the structure you want.
data
x y zdata
1 2 5 1
2 2 6 1
3 3 7 1
4 3 8 1
5 3 9 1
6 5 10 2
7 5 11 2
8 5 12 2
tab<-xtabs(zdata~x+y, data)
tab[tab==0]<-NA
tab
y
x 5 6 7 8 9 10 11 12
2 1 1
3 1 1 1
5 2 2 2
str(as.matrix(tab))
xtabs [1:3, 1:8] 1 NA NA 1 NA NA NA 1 NA NA ...
- attr(*, "dimnames")=List of 2
..$ x: chr [1:3] "2" "3" "5"
..$ y: chr [1:8] "5" "6" "7" "8" ...
- attr(*, "class")= chr [1:2] "xtabs" "table"
- attr(*, "call")= language xtabs(formula = zdata ~ x + y, data = data)
tab1<-cbind(as.numeric(rownames(tab)), tab)
rbind(c(NA,as.numeric(colnames(tab))), tab1)
5 6 7 8 9 10 11 12
NA 5 6 7 8 9 10 11 12
2 2 1 1 NA NA NA NA NA NA
3 3 NA NA 1 1 1 NA NA NA
5 5 NA NA NA NA NA 2 2 2
rbind(c(NA,as.numeric(colnames(tab))), tab1)
If you want to get rid of column and row names set them to NULL.
Regards
Petr
think I did not make myself clear, please see also my reply to the previous answer to my question by David Winsemius. Thanks for your reply though!