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) An the matrix I should look like this: 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. |
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 <[hidden email]> 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. > > ______________________________________________ > [hidden email] 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 ______________________________________________ [hidden email] 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. |
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]). The result is that the dataframe gives you a position and a value in a lower triangular matrix: Still can't find a solution to built a lower triangular matrix with the specific values of that dataframe... Any more ideas, please? |
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. I apologize for creating confusion... Nymphita |
In reply to this post by nymphita
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 <[hidden email]> 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. > > ______________________________________________ > [hidden email] 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. ______________________________________________ [hidden email] 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. |
In reply to this post by nymphita
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 |
In reply to this post by nymphita
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. > > ______________________________________________ > [hidden email] 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 ______________________________________________ [hidden email] 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. |
This post was updated on .
In reply to this post by Michael Weylandt
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... I'm trying to find why and how I can change it, but if someone finds a solution it will be appreciated |
This post was updated on .
In reply to this post by Rui Barradas
Hi Rui,
Thank you very much for your idea. It almost 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 However, there is something funny on the position the values in the matrix, they don't really correspond to the position indicated in the subscripts for an upper triangular matrix... (it probably works for a lower triangular matrix) |
In reply to this post by David Winsemius
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) |
In reply to this post by nymphita
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. ______________________________________________ [hidden email] 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. |
In reply to this post by nymphita
Problem solved.
Many many thanks for your ideas!! (this site is very stimulant) :) |
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 |
Hey,
You could also use (after initializing x): x[lower.tri(x)] <- data$k x <- t(x) Cheers, Tsjerk On Feb 16, 2012 6:59 PM, "Rui Barradas" <[hidden email]> wrote: 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. __________________________________________... [[alternative HTML version deleted]] ______________________________________________ [hidden email] 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. |
In reply to this post by nymphita
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 <[hidden email]> 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. > > ______________________________________________ > [hidden email] 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. ______________________________________________ [hidden email] 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. |
Free forum by Nabble - Free Resume Builder | Edit this page |