Reorder a matrix columns according to the values of one of its rows

[Francisco Oteiza]

I have a matrix A in STATA and I want to obtain a new matrix B with the same contents as A but with its columns reordered according to the values of a given row.
For example, if A looks like:
1 2 3
6 8 7
I want to reorder this based on the second row to obtain B:
1 3 2
6 7 8
Is there a straightforward way of doing this in STATA without having to use MATA commands? I'm not familiar with them but willing to learn if needed.

[John Mullahy]

I'm not sure of the easiest way to do this in Stata, but it's straightforward using Mata

matrix define A=(1,2,3)\(6,8,7)
mata st_matrix("B",sort(st_matrix("A")',2)')
matrix list A
matrix list B

[Francisco Oteiza]

Thanks John, this works like a charm!

Is there any way I could also keep my column and row labels from matrix A for matrix B? I apologize for not including this in my example, but lets say A has row labels (r1, r2) which are strings, and column labels (c1 and c2) which are strings as well.

[John Mullahy]

Francisco: You will probably need to use the Mata functions st_matrixcolstripe and st_matrixrowstripe. There's a more elegant way to do this, but here's the basic idea

matrix define A=(1,2,3)\(6,8,7)
matrix rownames A=ROW1A ROW2A
matrix colnames A=COL1A COL2A COL3A
mata ATEMP=st_matrix("A")
mata ATEMP=sort((ATEMP\(1..cols(ATEMP)))',2)'
mata ra=rows(ATEMP)
mata colinda=ATEMP[ra,.]
mata ATEMP=ATEMP[1..(ra-1),.]
mata st_matrix("B",ATEMP)
mata csa=st_matrixcolstripe("A")[colinda,.]
mata rsa=st_matrixrowstripe("A")
mata st_matrixcolstripe("B",csa)
mata st_matrixrowstripe("B",rsa)
matrix list A
matrix list B

The key issue is how to endow the column names with the same sort order that is used to generate matrix B. There is probably a better way to do this than my example shows.

[Francisco Oteiza]

I see, I'll give this a try, thanks!

[Anne Kaduk]

Thanks to everyone on this thread! Francisco - did you end up finding a better way to do this? I would find it quite useful as well, although I want to sort rows by a column and keep the row / column labels as well. Thanks for any insight you have!

[Andrew Musau]

A Stata solution as posed by John Mullahy could be to convert the matrix to a dataset, sort and convert back. The default command svmat does not allow one to export row names, but you can install svmat2 from the Stata Journal that has this capability. Note that whether you need to sort rows or columns does not matter as you can transpose the matrix prior to conversion. Taking John's code in #4, here is an illustration:

matrix define A=(1,2,3)\(6,8,7)
matrix rownames A=ROW1A ROW2A
matrix colnames A=COL1A COL2A COL3A
*TYPE findit svmat2 AND INSTALL
mat B= A'
svmat2 B, names(col) r(row)
sort ROW2A
ds row, not
mkmat `r(varlist)', rownames(row) mat(C)
mat B= C'

Res.:

. mat l A

A[2,3]
       COL1A  COL2A  COL3A
ROW1A      1      2      3
ROW2A      6      8      7

. mat l B

B[2,3]
       COL1A  COL3A  COL2A
ROW1A      1      3      2
ROW2A      6      7      8

[Anne Kaduk]

Thanks, Andrew Musau! This worked when I adapted it to my dataset. I also was able to use preserve [then clear] and restore to do this without messing up the rest of my dataset.

Do you have any idea how to make this work and keep spaces in the row labels? I assume probably not, but that would be super helpful!

And then for context in case anyone else had thoughts on this method, I spent some time today trying to learn to use Mata, and here's the (not quite there) solution I got for my situation, based on the code above. If anyone has ideas on how to make this work, I'd love to hear it!

    matrix define tempmat = (1,2,3)\(9,5,2)\(6,8,7)
    matrix rownames tempmat=ROW1A ROW2A ROW3A
    matrix colnames tempmat=COL1A COL2A COL3A
    mata
        ATEMP = st_matrix("tempmat")
        col_lab = st_matrixcolstripe("tempmat") // pulls column labels to reapply to labeled matrix
        row_lab_names = st_matrixrowstripe("tempmat") // pulls row names in original order
        row_lab_temp = order(ATEMP, -1) // pulls the order that the row labels will need to be sorted into, from original order
        ATEMP = sort(ATEMP,-1) // sorts the main part of the matrix
        st_matrix("tempmat_labeled", ATEMP) // creates new Stata matrix, tempmat_labeled
        st_matrixcolstripe("tempmat_labeled", col_lab) // adds column labels
        nrows = rows(ATEMP) // pulls number of rows
        row_lab = row_lab_names, row_lab_temp // this does not work since I apparently can't combine string and numeric matrices :(
        row_lab = sort(row_lab, -2) // this would sort the row labels in the right order
        /* then I would need to pull just the first column of row_lab but I don't know how to do that */ 
        st_matrixrowstripe("tempmat_labeled", row_lab) // thiis would apply the row labels from just the first column of row_lab once I deleted the second one
    end
    matlist tempmat
    matlist tempmat_labeled

[Anne Kaduk]

Update - there is a "full" option for svmat2 to get full rownames, and I thought it was working in my code, but it's not. Any ideas would be welcome! Thank you!


For anyone's future reference, my (simpler since I'm sorting rows vs columns) syntax is below:

    matrix define A = (1,2,3)\(9,5,2)\(6,8,7)
    matrix rownames A=ROW1A ROW2A ROW3A
    matrix colnames A=COL1A COL2A COL3A
    svmat2 A, names(col) r(row) full
    gsort -COL1A
    ds row, not
    mkmat `r(varlist)', rownames(row) mat("A_sorted")

[Mike Lacy]

-search matrix sort- reveals the existence of the user-written module -matsort-, available at SSC.

[Andrew Musau]

I had forgotten about matsort, so Mike's advice should be the most efficient way to handle this. It is not that much difficult to do it directly. The approach below does no longer require svmat2.

clear
matrix define A = (1,2,3)\(9,5,2)\(6,8,7)
matrix rownames A="ROW 1 A" "ROW 2 A" "ROW 3 A"
matrix colnames A=COL1A COL2A COL3A
local rows: di `"`:rowname A, quoted'"'
svmat A, names(col)
gen row=""
local i 1
foreach row of local rows{
    replace row = "`row'" in `i'
    local ++i
}
gsort -COL1A
ds row, not
mkmat `r(varlist)', rown(row)  mat("A_sorted")
local rows: di `"`:rowname A_sorted, quoted'"'
local rows: subinstr local rows "_" " ", all
mat rown A_sorted= `rows'

Res.:

. mat l A

A[3,3]
         COL1A  COL2A  COL3A
ROW 1 A      1      2      3
ROW 2 A      9      5      2
ROW 3 A      6      8      7

. mat l A_sorted

A_sorted[3,3]
         COL1A  COL2A  COL3A
ROW 2 A      9      5      2
ROW 3 A      6      8      7
ROW 1 A      1      2      3

[Nick Cox]

svmat2 is something that I wrote in 1999 and published in 2000 but haven't used in an age. Here's the provenance:

STB-56 dm79 . . . . . . . . . . . . . . . . . . Yet more new matrix commands
(help matcorr, matewmf, matvsort, svmat2 if installed) . . N. J. Cox
7/00 pp.4--8; STB Reprints Vol 10, pp.17--23
commands to produce a correlation matrix, elementwise monadic
function of another matrix, selected subsets of matrix rows
and columns, vec or vech of a matrix, elements sorted within
a vector, matrix from a vector, and commands to save matrices
see mata matrix language incorporated into Stata 9.0


As Stata indicates on a search user-programmers would be (should be) reaching now for Mata here, which is more than fine by me, but the code still is valid.

The claim or implication in #9 by Anne Kaduk that it is not working is mystifying to me. Here's that syntax again with extra list statements.

What is considered wrong here?

. clear 

. 
. matrix define A = (1,2,3)\(9,5,2)\(6,8,7)

.     matrix rownames A=ROW1A ROW2A ROW3A

.     matrix colnames A=COL1A COL2A COL3A

.     svmat2 A, names(col) r(row) full
number of observations will be reset to 3
Press any key to continue, or Break to abort
Number of observations (_N) was 0, now 3.

.         list 

     +-------------------------------+
     | COL1A   COL2A   COL3A     row |
     |-------------------------------|
  1. |     1       2       3   ROW1A |
  2. |     9       5       2   ROW2A |
  3. |     6       8       7   ROW3A |
     +-------------------------------+

.     gsort -COL1A

.     ds row, not
COL1A  COL2A  COL3A

.     mkmat `r(varlist)', rownames(row) mat("A_sorted")

.         list 

     +-------------------------------+
     | COL1A   COL2A   COL3A     row |
     |-------------------------------|
  1. |     9       5       2   ROW2A |
  2. |     6       8       7   ROW3A |
  3. |     1       2       3   ROW1A |
     +-------------------------------+

[Andrew Musau]

I think the complaint in #9 refers to the case where there are spaces in the row names. My code in #11 attempts to address this.

clear
matrix define A = (1,2,3)\(9,5,2)\(6,8,7)
matrix rownames A="ROW 1 A" "ROW 2 A" "ROW 3 A"
matrix colnames A=COL1A COL2A COL3A
svmat2 A, names(col) r(row) full
l

Res.:

. svmat2 A, names(col) r(row) full
number of observations will be reset to 3
Press any key to continue, or Break to abort
number of observations (_N) was 0, now 3

. 
. l

     +-----------------------------+
     | COL1A   COL2A   COL3A   row |
     |-----------------------------|
  1. |     1       2       3   ROW |
  2. |     9       5       2     1 |
  3. |     6       8       7     A |
     +-----------------------------+

[Nick Cox]

As spaces aren't allowed in variable names it would be an odd criticism of svmat2 that it can't produce them,

[Anne Kaduk]

Thanks, Mike Lacy! matsort is what I was looking for, and for some reason couldn't find. Hopefully this post will help someone else find it / avoid the headache I've had about it. However, it does not appear to appropriately handle row names with spaces in it, so I'm grateful for the code Andrew provided below.

Thanks, Andrew Musau, too! This code is just what I needed to appropriately handle row names with spaces! I really appreciate the time you put into it.

Nick Cox - Thank you for the details! The example code works using svmat2, but my row names have spaces in them, which that solution / svmat2 does not appear to handle well (but the other two solutions above both work). I can't get it to run at all today, though, so I may be doing something wrong. Regardless, apologies for being unclear on what was not working / not specifying that my use case had row names with spaces in them, and thank you for all the work you do here.