# Re: [R] Data frame with 3 columns to matrix

From: David Winsemius <dwinsemius_at_comcast.net>
Date: Tue, 19 Apr 2011 09:32:18 -0400

On Apr 19, 2011, at 8:16 AM, Michael Bach wrote:

> David Winsemius <dwinsemius@comcast.net> writes:
>
>> Perhaps but only if the third row of your example was incorrectly
>> constructed:
>>> dta <- rd.txt(" 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")
>> #rd.txt() is a combo fn of read.table and textConnection
>>
>>> mat <- matrix(NA, ncol=NROW(dta)+1, nrow=NROW(dta)+1)
>>> mat[2:NROW(mat),1] <- dta[["x"]]
>>> mat[1,2:NROW(mat)] <- dta[["y"]]
>>> diag(mat) <- c(NA, dta[["z"]])
>>> mat
>> [,1] [,2] [,3] [,4] [,5]
>> [1,] NA 5.0 5.0 7.0 9.0
>> [2,] 1.00 0.5 NA NA NA
>> [3,] 1.02 NA 0.7 NA NA
>> [4,] 1.04 NA NA 0.1 NA
>> [5,] 1.06 NA NA NA 0.4
>>
>>
>
>
> but this yields a diagonal matrix only. I think I did not make myself
> clear enough. In the original 3 column data frame, there could have
> been a pair of x and y with identical y's but different x's and z's.
> The way my data source is derived, there is a guarantee that there is
> are no two rows with identical x and y in the original data frame. In
> the end, x and y serve as a grid, with z values at each point in the
> grid or NA's if there is no z value for a x and y pair. The number of
> rows in the data frame is then equal to the number of non-NA values in
> the resulting matrix.
>
> Another try, lets assume this original data frame:
>
> x y z
> 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
>
> Then I would like to get
>
> [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9]
> [1,] NA 5 6 7 8 9 10 11 12
> [2,] 2 1 1
> [3,] 2
> [4,] 3 1 1 1
> [5,] 3
> [6,] 3
> [7,] 5 2 2 2
> [8,] 5
> [9,] 5
>
> I left out all the NA's, except the first, where there is no z value,
> say e.g. x=5 and y=8.
>
> Do you see what I mean?

I do, ... now anyway. Your earlier data example had non-integer x and y values which made what I will now offer infeasible (or at the very least ambiguous). Indexing with decimal numbers does not provoke an error and that the truncated value is used. With integer indices you can use a two column matrix as an argument to "["

``` > mat <- matrix(NA, nrow=max(dta[])+1, ncol=max(dta[])+1 )
> mat[data.matrix(dta[,1:2])] <- dta[,3]
> mat
[,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12]
[1,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
[2,]   NA   NA   NA   NA    1    1   NA   NA   NA    NA    NA    NA
[3,]   NA   NA   NA   NA   NA   NA    1    1    1    NA    NA    NA
[4,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
[5,]   NA   NA   NA   NA   NA   NA   NA   NA   NA     2     2     2
[6,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA
[,13]
[1,]    NA
[2,]    NA
[3,]    NA
[4,]    NA
```

[5,] NA
[6,] NA

I leave the insertion of the first row and columns and removal of the extra columns induced by the mismatch of the values and row numbers to you, since .....
> mat[, 4:12]

[,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9]

```[1,]   NA   NA   NA   NA   NA   NA   NA   NA   NA
[2,]   NA    1    1   NA   NA   NA   NA   NA   NA
[3,]   NA   NA   NA    1    1    1   NA   NA   NA
[4,]   NA   NA   NA   NA   NA   NA   NA   NA   NA
[5,]   NA   NA   NA   NA   NA   NA    2    2    2
[6,]   NA   NA   NA   NA   NA   NA   NA   NA   NA

```
```--

David Winsemius, MD
West Hartford, CT

______________________________________________
R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help