Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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:

enter image description here

After:

enter image description here

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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]
    }
  }'
share|improve this answer
    
I think you are not normal. Are you an alien? :D Thanks :D –  somelooser28533 Aug 20 at 15:31
    
Relatively easy? Relative to what? –  dotancohen Aug 20 at 18:38
    
Easier than writing it in assembler :) –  Barmar Aug 21 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

share|improve this answer
    
Many thanks! The perl one is shorter, wow :\ –  somelooser28533 Aug 20 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)]
share|improve this answer
    
Many thanks, wow :O –  somelooser28533 Aug 22 at 18:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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