# built a lower triangular matrix from dataframe

15 messages
Open this post in threaded view
|
Report Content as Inappropriate

## built a lower triangular matrix from dataframe

 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.
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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-helpPLEASE do read the posting guide http://www.R-project.org/posting-guide.htmland provide commented, minimal, self-contained, reproducible code.
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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?
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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-helpPLEASE do read the posting guide http://www.R-project.org/posting-guide.htmland provide commented, minimal, self-contained, reproducible code.
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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.pngYou 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-helpPLEASE do read the posting guide http://www.R-project.org/posting-guide.htmland provide commented, minimal, self-contained, reproducible code.
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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)
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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)
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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.htmlSent from the R help mailing list archive at Nabble.com. ______________________________________________ [hidden email] mailing list https://stat.ethz.ch/mailman/listinfo/r-helpPLEASE do read the posting guide http://www.R-project.org/posting-guide.htmland provide commented, minimal, self-contained, reproducible code.
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 In reply to this post by nymphita Problem solved. Many many thanks for your ideas!! (this site is very stimulant) :)
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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
Open this post in threaded view
|
Report Content as Inappropriate

## Re: built a lower triangular matrix from dataframe

 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.htmlSent 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-helpPLEASE do read the posting guide http://www.R-project.org/posting-guide.htmland provide commented, minimal, self-contained, reproducible code.