question on how to re-order matrix

[katharinaf]

Dear all,

I wonder if you may point me in the right direction with a problem I have?
I have a matrix with pairwise estimates like this (note: individual names are not in order):

obs ind4 ind1 ind7 ind5 ...
ind3 0.2
ind2 0.3 0.1
ind4 0.2 0.4 0.2
...

I would like to reorder that matrix based in individual ID so that both columns and rows are in alphabetical order, and the values still only in the lower triangle of the matrix:
obs ind1 ind2 ind3 ind4 ...
ind2 0.1
ind3 0.5 0.1
ind4 0.2 0.4 0.2
...

I have already tried to sort it in Linux with simple

1. sort by line
2. transpose matrix
3. sort by line again,
   but this gives me a matrix with the values all over the place (instead of in the lower triangle).

I have searched for appropriate commands in tidyverse, but have so far only found ways to sort a file based on a certain column (NOT on all column AND all line names)...

Do you know of a way I can get the matrix in the desired order?
many thanks in advance and best regards,
Katharina

(as I don't even know with which tidyverse command to start, I cannot provide a reproducible example)

[Yarnabrina]

Welcome to the community!

A reproducible example is very useful, as it helps people to help you. You don't need to know tidyverse for it (believe me, I don't know either). Please take a look at this great post by Andres:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

I'll assume a random matrix to get you started, but it's not a proper thing to do. What you have is similar to a distance matrix, and I failed to anything as such. Still, I hope the following will give you some idea to reorder the rows and columns of a matrix.

set.seed(seed = 31782)

dataset <- matrix(data = rnorm(n = 16),
                  nrow = 4,
                  ncol = 4,
                  dimnames = list(c("ind2", "ind4", "ind1", "ind3"),
                                  c("ind1", "ind4", "ind3", "ind2")))
dataset
#>            ind1       ind4       ind3        ind2
#> ind2 -0.8865502 -0.6995333 -1.6436619  0.06188118
#> ind4  0.3434197 -0.4407043 -0.3167084  1.03358285
#> ind1 -0.1445536 -0.4691033  0.4753363  0.04370875
#> ind3 -0.3512611  1.1011608  0.1883204 -1.24454409

dataset[order(row.names(x = dataset)), order(colnames(x = dataset))]
#>            ind1        ind2       ind3       ind4
#> ind1 -0.1445536  0.04370875  0.4753363 -0.4691033
#> ind2 -0.8865502  0.06188118 -1.6436619 -0.6995333
#> ind3 -0.3512611 -1.24454409  0.1883204  1.1011608
#> ind4  0.3434197  1.03358285 -0.3167084 -0.4407043

^{Created on 2019-05-27 by the reprex package (v0.3.0)}

Good luck!

[katharinaf]

Dear Yarnabrina,

thank you very much for your kind reply and for your advice. I realize you are right, and I am currently looking into how I can best use the reprex package to properly show my error.
As I am still quite new to this, and have not figured out yet how to properly reproduce the kind of matrix that I have, I hope it might be ok to copy it in here again in text form.

Unfortunately, your advice - while working well on reordering - did not take care of one issue. My matrix is different from the one that you created in that it has only values in the lower triangle (the upper triangle consists of only NAs). Like this:

dataset
#>            ind1       ind4       ind3        ind2
#> ind2 -0.8865502 
#> ind4  0.3434197 -0.4407043 
#> ind1 -0.1445536 -0.4691033  0.4753363 
#> ind3 -0.3512611  1.1011608  0.1883204 -1.24454409

because of this, when I use your re-ordering command, I get as a result a matrix where the values are all over the place (some in the lower, some in the upper triangle).
Because of this, I have issues when continuing to work with that matrix. I wonder if there is any way at all to reorder and still keep a clean matrix only in the lower triangle...? Or maybe I simply have to reorder the file that was used to create that matrix already...

thanks again so much for your help,
kind regards