I am working on a project where I am given a large table of numbers, in which we are hoping to see certain patterns. For example (using R):

mat <- matrix(rnorm(100), 10, 10)
colnames(mat) <- letters[1:10]
rownames(mat) <- 1:10
round(mat, 3)

        a      b      c      d      e      f      g      h      i      j
1  -0.550 -2.941 -0.254 -2.362  0.003 -1.305  0.399 -1.638  0.751  0.877
2   1.091 -0.243  1.519 -0.551 -0.531  0.887  0.027 -0.332 -0.067  0.835
3   0.640 -0.141  1.781 -0.305 -0.710  2.336 -1.001  0.448 -0.504 -0.048
4   1.043 -0.033 -0.879 -0.750 -0.291  0.503  0.009  0.272 -0.160 -3.410
5   0.170  0.280 -1.529  0.144  0.885 -2.268 -0.164 -0.254 -0.093 -1.513
6   1.138  0.590  0.136 -0.549 -0.154 -2.032  0.423  2.348  0.474  0.252
7  -0.971  1.024 -0.709  0.160 -0.954 -0.138 -0.424 -0.213  0.131 -0.473
8  -0.132  2.107 -1.410 -0.088  0.667 -0.953 -0.470  0.051  0.717  0.977
9   0.146  0.155  1.831  0.081  0.388  1.578  0.172 -2.246 -0.003  2.435
10  1.441  0.913  1.290  0.899  0.549 -1.248  1.847  0.920 -2.177 -0.082

My goal is to be able to sort arbitrary sized matrices (not necessarily square) by switching rows and columns to minimize cell-wise differences. For example, all the values on line 1 should stay on line 1, but perhaps it makes more sense distance-wise for line 1 to appear on line 8, and vice versa regarding the columns. This problem reminds me of finding the inverse of a matrix using linear algebra.

Note: I realize there is not a unique solution to this problem, so resampling is OK (ideally, rerunning the function will produce different clusterings). As a starting point, optimally larger values would float to the top while smaller values would tend toward the bottom.

In the above example, perhaps the largest numbers are all in row 9 and so one move might be to bring it to the top. Similarly, perhaps the largest numbers are found in column c and that is moved to the left. Basically, I want to reorder the rows and columns of the table so that the magnitude of the difference between each entry (e.g. a1 vs a2/b1; c2 vs. the four surrounding entries) is minimized - but with the restriction that the operations happen row- and column-wise.

I am primarily interested in a theoretical approach that will show me how to accomplish this, but will eventually be implementing the solution in R, so assistance on that front would also be appreciated.

To make this more concrete: this idea is to be applied to the results from large-scale simulation studies, where the entries might be Type I error or power rates, the rows might pertain to a design condition (like sample size) and the columns might pertain to different multiple comparison procedures or something like that. The goal is to rearrange the entries of this table so that we might be able to glance at the table and see where clusters of "good" procedures are and where the "bad" ones are.

  • 1
    $\begingroup$ Could you please explain in more detail, what the "cell-wise differences" are, that are to be minimized? You describe that "larger values are to float to the top and smaller values to the bottom": That is for rows, but you want to switch columns as well? $\endgroup$
    – Bernhard
    Jun 10 '16 at 13:28
  • $\begingroup$ Yes, I will try to add more detail about the goal - I realize my language is fuzzy! I will edit the original post now. Thanks :) $\endgroup$ Jun 10 '16 at 13:35
  • 1
    $\begingroup$ The answer crucially depends on precisely what you mean by a "cell-wise difference." Please tell us how these differences are to be measured. $\endgroup$
    – whuber
    Jun 10 '16 at 13:39
  • $\begingroup$ I've added a better description of my intended goal, as well as how I want to apply this idea. Hopefully it makes more sense now. $\endgroup$ Jun 10 '16 at 13:49

Each row has a distance to every other row, each column has a distance to each other column. You could use hierarchical clustering on each of them to sort similar ones next to each other. Please have a look at this heatmap: https://upload.wikimedia.org/wikipedia/commons/4/48/Heatmap.png As you can see, they used hierarchical clustering for sorting the rows and hierarchical clustering for sorting the columns and thus find squares of similarity within the matrix. Is that, what you are looking for?

Here is more on hierarchically sorted heatmaps, including instructions for R: http://www.r-bloggers.com/drawing-heatmaps-in-r/


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.