Hello! I'm trying to build a lower triangular matrix (with zeros in the diagonal) from a particular dataframe. The matrix I have to construct has 203 rows and 203 columns and that makes 20503 values to be included within (that's why I can't do it manually). To illustrate the dataframe I have, I'll give you an example of a dataframe and matrix with dimensions 6x6 (to make it shorter!) My dataframe looks more or less like this (but longeeeer): (i= number of row, j=number of column, k=value to be included in the matrix) http://r.789695.n4.nabble.com/file/n4390813/df.png An the matrix I should look like this: http://r.789695.n4.nabble.com/file/n4390813/matrix.png Can anyone help me about how to do it? I'm a new R user, and I've tried several combinations of diag(), lower.tri(), matrix(), etc. without any luck... and I don't know if I'm unaware of a command that can work this out. -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4390813.html Sent from the R help mailing list archive at Nabble.com.
built a lower triangular matrix from dataframe
15 messages · Tsjerk Wassenaar, nymphita, R. Michael Weylandt +2 more
Hi Nymphita, ?upper.tri x <- as.data.frame(matrix(1:6,6,6)) x[upper.tri(x,diag=TRUE)] <- 0 x Cheers, Tsjerk
On Wed, Feb 15, 2012 at 4:33 PM, nymphita <nymphita at gmail.com> wrote:
Hello! I'm trying to build a lower triangular matrix (with zeros in the diagonal) from a particular dataframe. The matrix I have to construct has 203 rows and 203 columns and that makes 20503 values to be included within (that's why I can't do it manually). To illustrate the dataframe I have, I'll give you an example of a dataframe and matrix with dimensions 6x6 (to make it shorter!) My dataframe looks more or less like this (but longeeeer): (i= number of row, j=number of column, k=value to be included in the matrix) http://r.789695.n4.nabble.com/file/n4390813/df.png An the matrix I should look like this: http://r.789695.n4.nabble.com/file/n4390813/matrix.png Can anyone help me about how to do it? I'm a new R user, and I've tried several combinations of diag(), lower.tri(), matrix(), etc. without any luck... and I don't know if I'm unaware of a command that can work this out. -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4390813.html Sent from the R help mailing list archive at Nabble.com.
______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Tsjerk A. Wassenaar, Ph.D. post-doctoral researcher Molecular Dynamics Group * Groningen Institute for Biomolecular Research and Biotechnology * Zernike Institute for Advanced Materials University of Groningen The Netherlands
Hi Tsjerk! Thanks for your quick reply! It's a nice way to built a lower triangular matrix with zeros in the diagonal, but what I can't work out is *how to include the values of the third column of the dataframe inside the matrix*. I just realized that I forgot to explain something about the dataframe (the meaning of i, j ,k) in my post: The data frame has three columns, the first one (i) corresponds to the "row subscript" of the matrix [i, ], the second one (j) corresponds to the "column subscript" of the matrix [ ,j], and the third column in the dataframe (k) is the value that has to be in the matrix (in position [i, j]). http://r.789695.n4.nabble.com/file/n4391099/df.png The result is that the dataframe gives you a position and a value in a lower triangular matrix: http://r.789695.n4.nabble.com/file/n4391099/matrix.png Still can't find a solution to built a lower triangular matrix with the specific values of that dataframe... Any more ideas, please? -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4391099.html Sent from the R help mailing list archive at Nabble.com.
Sorry, I just realized that it's not a lower triangualr matrix, but an upper triangular matrix! But still the solution/s should be rather similar in both cases. http://r.789695.n4.nabble.com/file/n4391127/matrix2.png I apologize for creating confusion... Nymphita -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4391127.html Sent from the R help mailing list archive at Nabble.com.
Perhaps ignore the lower-triangularity for a moment and do something like this: x <- matrix(NA, ncol = max(j), nrow = max(i)) x[i, j] <- k Your code will be clearer if you use with() rather than df$i constructs. Hope this helps, Michael On Wed, Feb 15, 2012 at 11:47 AM, nymphita
<sandrablazquezcabrera at gmail.com> wrote:
Hi Tsjerk! Thanks for your quick reply! It's a nice way to built a lower triangular matrix with zeros in the diagonal, but what I can't work out is *how to include the values of the third column of the dataframe inside the matrix*. I just realized that I forgot to explain something about the dataframe ?(the meaning of i, j ,k) in my post: The data frame has three columns, the first one (i) corresponds to the "row subscript" of the matrix [i, ], the second one (j) corresponds to the "column subscript" of the matrix [ ,j], and the third column in the dataframe (k) is the value that has to be in the matrix (in position [i, j]). http://r.789695.n4.nabble.com/file/n4391099/df.png The result is that the dataframe gives you a position and a value in a lower triangular matrix: http://r.789695.n4.nabble.com/file/n4391099/matrix.png Still can't find a solution to built a lower triangular matrix with the specific values of that dataframe... Any more ideas, please? -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4391099.html Sent from the R help mailing list archive at Nabble.com.
______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Hello,
I'm trying to build a lower triangular matrix (with zeros in the diagonal) from a particular dataframe.
This example constructs a lower triangular matrix from a vector, not a data.frame. (In your example, you also use a vector, the last column of the DF.) x <- runif(15) y <- matrix(0, nrow=6, ncol=6) y[lower.tri(y)] <- x # See the result y Hope this helps, Rui Barradas -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4391162.html Sent from the R help mailing list archive at Nabble.com.
On Feb 15, 2012, at 11:47 AM, nymphita wrote:
Hi Tsjerk! Thanks for your quick reply! It's a nice way to built a lower triangular matrix with zeros in the diagonal, but what I can't work out is *how to include the values of the third column of the dataframe inside the matrix*. I just realized that I forgot to explain something about the dataframe (the meaning of i, j ,k) in my post: The data frame has three columns, the first one (i) corresponds to the "row subscript" of the matrix [i, ], the second one (j) corresponds to the "column subscript" of the matrix [ ,j], and the third column in the dataframe (k) is the value that has to be in the matrix (in position [i, j]). http://r.789695.n4.nabble.com/file/n4391099/df.png
You might be able to create a matrix of zeros withthis untested code: zmat <- matrix(0, ncol=1+max(j), nrow=1+max(i) ) # Then populate it with: zmat[ cbind(i,j) ] <- k (Tested solutions offered when reproducible code is posted. Png images of data on Nabble do not count as reproducible code in my estimation. I cannot understand why you wouldn't post that data in the body of the message.)
david. > > The result is that the dataframe gives you a position and a value in > a lower > triangular matrix: > > http://r.789695.n4.nabble.com/file/n4391099/matrix.png > > Still can't find a solution to built a lower triangular matrix with > the > specific values of that dataframe... > Any more ideas, please? > > > > -- > View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4391099.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. David Winsemius, MD West Hartford, CT
Hi Michael, Your answer was very interesting, thank you! However, I tried it and the result was:
df <- read.table(file="df.txt", head=T, sep="\t") df
i j k 1 1 2 5.2 2 1 3 9.1 3 1 4 8.0 4 1 5 2.3 5 1 6 8.4 6 2 3 6.6 7 2 4 7.4 8 2 5 7.1 9 2 6 5.5 10 3 4 4.1 11 3 5 3.9 12 3 6 9.2 13 4 5 8.5 14 4 6 7.6 15 5 6 9.9
x <- with(df, matrix(NA, ncol= max(j), nrow=max(i)+1)) x[with(df, i), with(df, j)] <- with(df, k) x
[,1] [,2] [,3] [,4] [,5] [,6] [1,] NA 8.4 8.4 8.4 8.4 8.4 [2,] NA 5.5 5.5 5.5 5.5 5.5 [3,] NA 9.2 9.2 9.2 9.2 9.2 [4,] NA 7.6 7.6 7.6 7.6 7.6 [5,] NA 9.9 9.9 9.9 9.9 9.9 [6,] NA NA NA NA NA NA It seems that R only reads the values [1,6], [2,6], [3,6], [4,6], and [5,6] and repeats them for every postition of the row... Still trying to find why and how to change it, but if someone finds a solution it will be appreciated -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4393528.html Sent from the R help mailing list archive at Nabble.com.
Hi Rui, Thank you very much for your idea. It works!!! I converted my dataframe into a vector (I first removed the header and the first and second column) and then tried your solution:
data <- as.vector(as.matrix(read.table(file="data.txt", head=F, sep="\t")[-c(1,2)])) data
[1] 5.2 9.1 8.0 2.3 8.4 6.6 7.4 7.1 5.5 4.1 3.9 9.2 8.5 7.6 9.9
y <- matrix(0, nrow=6, ncol=6) y[upper.tri(y)] <- data y
[,1] [,2] [,3] [,4] [,5] [,6] [1,] 0 5.2 9.1 2.3 7.4 3.9 [2,] 0 0.0 8.0 8.4 7.1 9.2 [3,] 0 0.0 0.0 6.6 5.5 8.5 [4,] 0 0.0 0.0 0.0 4.1 7.6 [5,] 0 0.0 0.0 0.0 0.0 9.9 [6,] 0 0.0 0.0 0.0 0.0 0.0 Perfect! :) -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4393562.html Sent from the R help mailing list archive at Nabble.com.
Hi David, What an good solution. It works perfectly and it's really simple. (I only removed the "1+" in ncol=1+max(j), it already has 6 columns) My result has been:
df <- read.table(file="df.txt", head=T, sep="\t") df
i j k 1 1 2 5.2 2 1 3 9.1 3 1 4 8.0 4 1 5 2.3 5 1 6 8.4 6 2 3 6.6 7 2 4 7.4 8 2 5 7.1 9 2 6 5.5 10 3 4 4.1 11 3 5 3.9 12 3 6 9.2 13 4 5 8.5 14 4 6 7.6 15 5 6 9.9
zmat <- with(df, matrix(0, ncol=max(j), nrow=1+max(i) )) # Then populate it with: zmat[with(df, cbind(i,j)) ] <- with(df, k) zmat
[,1] [,2] [,3] [,4] [,5] [,6] [1,] 0 5.2 9.1 8.0 2.3 8.4 [2,] 0 0.0 6.6 7.4 7.1 5.5 [3,] 0 0.0 0.0 4.1 3.9 9.2 [4,] 0 0.0 0.0 0.0 8.5 7.6 [5,] 0 0.0 0.0 0.0 0.0 9.9 [6,] 0 0.0 0.0 0.0 0.0 0.0 Great. (I only inluded some png images in the post because the matrix looked more neat to me that way... It was my first time on Nabble. Thanks for calling my attention on that, you are right) -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4393591.html Sent from the R help mailing list archive at Nabble.com.
Hi again, I just realized that in this solution there is something funny on the position the values in the matrix, they don't really correspond to the position indicated in the subscripts... However, David Winsemius has given a valid solution. Thank you for all your ideas! -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4393598.html Sent from the R help mailing list archive at Nabble.com.
Problem solved. Many many thanks for your ideas!! (this site is very stimulant) :) -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4393619.html Sent from the R help mailing list archive at Nabble.com.
Hello, I'm glad it helped. The difference in the ordering is due to the fact that R defaults to column-first ordering. David's solution uses row-first (which is what you wanted). Rui Barradas -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4394543.html Sent from the R help mailing list archive at Nabble.com.
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20120216/e21f2bd2/attachment.pl>
I didn't think through mine all the way -- you do need the cbind() call to do the indexing like I was thinking -- so mine when corrected just turns into David's. Michael On Thu, Feb 16, 2012 at 5:26 AM, nymphita
<sandrablazquezcabrera at gmail.com> wrote:
Hi Michael, Your answer was very interesting, thank you! However, I tried it and the result was:
df <- read.table(file="df.txt", head=T, sep="\t") df
? i j ? k 1 ?1 2 5.2 2 ?1 3 9.1 3 ?1 4 8.0 4 ?1 5 2.3 5 ?1 6 8.4 6 ?2 3 6.6 7 ?2 4 7.4 8 ?2 5 7.1 9 ?2 6 5.5 10 3 4 4.1 11 3 5 3.9 12 3 6 9.2 13 4 5 8.5 14 4 6 7.6 15 5 6 9.9
x <- with(df, matrix(NA, ncol= max(j), nrow=max(i)+1)) x[with(df, i), with(df, j)] <- with(df, k) x
? ? [,1] [,2] [,3] [,4] [,5] [,6] [1,] ? NA ?8.4 8.4 8.4 8.4 8.4 [2,] ? NA ?5.5 ?5.5 ?5.5 ?5.5 ?5.5 [3,] ? NA ?9.2 9.2 9.2 9.2 9.2 [4,] ? NA ?7.6 ?7.6 ?7.6 ?7.6 ?7.6 [5,] ? NA ?9.9 ?9.9 ?9.9 ?9.9 ?9.9 [6,] ? NA ? NA ? NA ? NA ? NA ? NA It seems that R only reads the values [1,6], [2,6], [3,6], [4,6], and [5,6] and repeats them for every postition of the row... Still trying to find why and how to change it, but if someone finds a solution it will be appreciated -- View this message in context: http://r.789695.n4.nabble.com/built-a-lower-triangular-matrix-from-dataframe-tp4390813p4393528.html Sent from the R help mailing list archive at Nabble.com.
______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.