built a lower triangular matrix from dataframe

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

built a lower triangular matrix from dataframe

nymphita
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.

Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

Tsjerk Wassenaar
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.
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

nymphita
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?

Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

nymphita
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
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

Michael Weylandt
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.
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

Rui Barradas
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
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

David Winsemius
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.
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

nymphita
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
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

nymphita
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)

Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

nymphita
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)
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

nymphita
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.
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

nymphita
In reply to this post by nymphita
Problem solved.
Many many thanks for your ideas!!

(this site is very stimulant) :)
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

Rui Barradas
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
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

Tsjerk Wassenaar
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.
Reply | Threaded
Open this post in threaded view
|

Re: built a lower triangular matrix from dataframe

Michael Weylandt
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.