1

I would like to sort columns and rows of a matrix in descending order by the sum of each row/column. However, my problem is that I cannot do so because I need to keep the heading of every column/row to track how they change when ordering the values.

A part of the matrix thereby looks as follows: 

{ {"", "A", "B", "C", "D"}
, {"A", 3784.77, 14.619, 154191., 1563.92}
, {"B", 27.8608, 29.7455, 148986., 414.57}
, {"C", 38936.7, 2059.22, 1.06501*10^9, 33946.7}
, {"D", 18.3014, 428.637, 2.12249*10^7, 24287.4}
}

As explained, now I would like to sum the rows and columns up, keeping the labels (A to D) and then order them from the largest to the smallest, first ordering the rows, then the columns. Can someone please help me?

Thank you in advance!

Best regards Alex

J. M.'s missing motivation
  • 124,525
  • 11
  • 401
  • 574
Alexander Hempfing
  • 333
  • 1
  • 6

3 Answers3

4

You can separate headers and use SortBy with a custom ordering function:

sortRows[matr_] := Join[{First[matr]}, SortBy[Rest[matr], (-Total[Rest[#]]) &]];
sortCols[matr_] := Transpose@sortRows@Transpose[matr];

sortCols[sortRows[matrix]]

This will result in:

{{"", "C", "D", "A", "B"},
{"C", 1.06501*10^9, 33946.7, 38936.7, 2059.22},
{"D", 2.12249*10^7, 24287.4, 18.3014, 428.637},
{"A", 154191., 1563.92, 3784.77, 14.619},
{"B", 148986., 414.57, 27.8608, 29.7455}}
Ray Shadow
  • 7,816
  • 1
  • 16
  • 44
  • Wow, thank you very much Shadowray! This is great! Just one more question: How would it be possible to transform the formula the way that the largest numbers (column/row C) are displayed at the top left of the matrix? I am not familiar with the code you just posted, so I would be very pleased if you could help here once more. Thanks! :) – Alexander Hempfing May 11 '17 at 14:27
  • The second argument of SortBy defines the function which is used for sorting. If you want to inverse the sort ordering you can, for example, negate that function: sortRows[matr_] := Join[{First[matr]}, SortBy[Rest[matr], (-Total[Rest[#]]) &]]; – Ray Shadow May 11 '17 at 14:34
  • Awesome, thanks! – Alexander Hempfing May 11 '17 at 14:43
4

The reason to post this answer is that although it is longer than a dedicated answer, its code is more systematic (and bureaucratic) through the use of the package RSparseMatrix.

Import["https://raw.githubusercontent.com/antononcube/\
MathematicaForPrediction/master/Misc/RSparseMatrix.m"]

matTbl = {{"", "A", "B", "C", "D"}, {"A", 3784.77, 14.619, 154191., 
    1563.92}, {"B", 27.8608, 29.7455, 148986., 414.57}, {"C", 38936.7,
     2059.22, 1.06501*10^9, 33946.7}, {"D", 18.3014, 428.637, 
    2.12249*10^7, 24287.4}};

rmat = ToRSparseMatrix[SparseArray[matTbl[[2 ;; -1, 2 ;; -1]]], 
  "RowNames" -> matTbl[[2 ;; -1, 1]], 
  "ColumnNames" -> matTbl[[1, 2 ;; -1]]];

MatrixForm[rmat]

enter image description here

rord = Ordering[-RowSums[rmat]];
cord = Ordering[-ColumnSums[rmat]];
MatrixForm[rmat[[rord, cord]]]

enter image description here

matTbl = Normal[SparseArray[rmat1]];
matTbl = MapThread[Prepend, {matTbl, RowNames[rmat1]}];
matTbl = Prepend[matTbl, Prepend[ColumnNames[rmat1], ""]];
Grid[matTbl]

enter image description here

(Also, see my answer to a related question, How to merge Datasets representing matrices with named rows/columns?.)

Anton Antonov
  • 37,787
  • 3
  • 100
  • 178
1

You can directly operate on the sub matrix:

matrix[[2 ;;, 2 ;;]] = 
  SortBy[Transpose@SortBy[Transpose@matrix[[2 ;;, 2 ;;]], Total],Total];
MatrixForm[m]

enter image description here

george2079
  • 38,913
  • 1
  • 43
  • 110