# How to sort vertically and horizontally?

Before (INPUT.txt):

``````    Foo#1   Foo#2   Foo#3   Foo#4   Foo#4   Foo#5   SUM
Bar#1   0   0   0   0   3   0   3
Bar#2   2   0   1   0   0   0   3
Bar#3   0   0   0   2   2   0   4
Bar#4   0   0   1   1   2   0   4
Bar#5   1   0   1   0   0   0   2
Bar#6   3   20  0   0   1   0   24
Bar#7   1   0   2   0   0   0   3
SUM 7   20  5   3   8   0   43
``````

After (OUTPUT.txt):

``````    Foo#2   Foo#4   Foo#1   Foo#3   Foo#4   Foo#5   SUM
Bar#6   20  1   3   0   0   0   24
Bar#3   0   2   0   0   2   0   4
Bar#4   0   2   0   1   1   0   4
Bar#1   0   3   0   0   0   0   3
Bar#2   0   0   2   1   0   0   3
Bar#7   0   0   1   2   0   0   3
Bar#5   0   0   1   1   0   0   2
SUM 20  8   7   5   3   0   43
``````

Tricky question: How to sort vertically and horizontally by the SUM column and row in bash or perl?

Screenshots:

Before:

After:

-

That's relatively easy with `perl`:

``````perl -F'\s+' -lane '
push @row, [@F];
END{
@sum = @{pop @row};
@col = (0, (sort {\$sum[\$b] <=> \$sum[\$a]} (1..\$#sum-1)), \$#sum);
for \$i (\$row[0], (sort {\$b->[\$#sum] <=> \$a->[\$#sum]} @row[1..\$#row]), \@sum) {
print join "\t", @{\$i}[@col]
}
}'
``````
-
I think you are not normal. Are you an alien? :D Thanks :D – somelooser28533 Aug 20 '14 at 15:31
Relatively easy? Relative to what? – dotancohen Aug 20 '14 at 18:38
Easier than writing it in assembler :) – Barmar Aug 21 '14 at 0:04

The problem is two-fold, first you want to sort the `#bar` rows by the numerical value of column `H`, which is a fairly trivial sort operation in most of the line orientated command line tools, `sort -nr -k8,1 input.txt |column -t > intermediate1.txt`

The header of your table and the sum row require some manual shuffling, but after that an intermediate result of:

``````-      Foo#1  Foo#2  Foo#3  Foo#4  Foo#4  Foo#5  SUM
Bar#6  3      20     0      0      1      0      24
Bar#4  0      0      1      1      2      0      4
Bar#3  0      0      0      2      2      0      4
Bar#7  1      0      2      0      0      0      3
Bar#2  2      0      1      0      0      0      3
Bar#1  0      0      0      0      3      0      3
Bar#5  1      0      1      0      0      0      2
SUM    7      20     5      3      8      0      43
``````

The second is a bit more complex, shuffle the columns based on the column sum values in the bottom row.

The problem becomes a lot easier to solve when you first transpose your matrix, i.e. switch the columns to rows and vice-versa, as then your operation again is a simple column sort. Using this GNU awk code from stack overflow:

``````awk '
{
for (i=1; i<=NF; i++)  {
a[NR,i] = \$i
}
}
NF>p { p = NF }
END {
for(j=1; j<=p; j++) {
str=a[1,j]
for(i=2; i<=NR; i++){
str=str" "a[i,j];
}
print str
}
}' intermediate1.txt | column -t > intermediate2.txt
``````

gets the next intermediate result:

``````-      Bar#6  Bar#4  Bar#3  Bar#7  Bar#2  Bar#1  Bar#5  SUM
Foo#1  3      0      0      1      2      0      1      7
Foo#2  20     0      0      0      0      0      0      20
Foo#3  0      1      0      2      1      0      1      5
Foo#4  0      1      2      0      0      0      0      3
Foo#4  1      2      2      0      0      3      0      8
Foo#5  0      0      0      0      0      0      0      0
SUM    24     4      4      3      3      3      2      43
``````

This matrix can now be sorted on the value of the sum column `sort -k9,1 -nr intermediate2.txt > intermediate3.txt`, which after manually correcting the ordering of the header and sum lines looks like:

``````-      Bar#6  Bar#4  Bar#3  Bar#7  Bar#2  Bar#1  Bar#5  SUM
Foo#2  20     0      0      0      0      0      0      20
Foo#4  1      2      2      0      0      3      0      8
Foo#1  3      0      0      1      2      0      1      7
Foo#3  0      1      0      2      1      0      1      5
Foo#4  0      1      2      0      0      0      0      3
Foo#5  0      0      0      0      0      0      0      0
SUM    24     4      4      3      3      3      2      43
``````

Then using the same awk code as before, transpose the intermediate result above back to return to your original layout of columns and rows:

``````awk '
{
for (i=1; i<=NF; i++)  {
a[NR,i] = \$i
}
}
NF>p { p = NF }
END {
for(j=1; j<=p; j++) {
str=a[1,j]
for(i=2; i<=NR; i++){
str=str" "a[i,j];
}
print str
}
}' intermediate3.txt | column -t > output.txt
``````

and the nicely formatted result:

``````-      Foo#2  Foo#4  Foo#1  Foo#3  Foo#4  Foo#5  SUM
Bar#6  20     1      3      0      0      0      24
Bar#4  0      2      0      1      1      0      4
Bar#3  0      2      0      0      2      0      4
Bar#7  0      0      1      2      0      0      3
Bar#2  0      0      2      1      0      0      3
Bar#1  0      3      0      0      0      0      3
Bar#5  0      0      1      1      0      0      2
SUM    20     8      7      5      3      0      43
``````

The order of Bar#4 and Bar#3 is reversed from the example result because the sum value is identical, but the descending sort order is also followed in column A, the same for Bar#7, Bar#2 and Bar#1

-
Many thanks! The perl one is shorter, wow :\ – somelooser28533 Aug 20 '14 at 15:32

For the record, a `python` solution.

``````from pprint import pprint
x = [(0,0,0,0,3,0),
(2,0,1,0,0,0),
(0,0,0,2,2,0),
(0,0,1,1,2,0),
(1,0,1,0,0,0),
(3,20,0,0,1,0),
(1,0,2,0,0,0)
]
y = sorted(x, key=sum, reverse=True)
pprint(zip(*sorted(zip(*y), key=sum, reverse=True)))
[(20, 1, 3, 0, 0, 0),
(0, 2, 0, 0, 2, 0),
(0, 2, 0, 1, 1, 0),
(0, 3, 0, 0, 0, 0),
(0, 0, 2, 1, 0, 0),
(0, 0, 1, 2, 0, 0),
(0, 0, 1, 1, 0, 0)]
``````
-
Many thanks, wow :O – somelooser28533 Aug 22 '14 at 18:27