2

I have a file like this (with hundreds of lines and columns)

1  2 3
4  5 6
7 88 9

and I would like to re-order columns basing on the last line values (or a specific line values)

1 3 2
4 6 5
7 9 88

How can I use awk (or other) to accomplish this task? Thank you in advance for your help

EDIT: I would like to thank everybody and to apologize if I wasn't enough clear. What I would like to do is:

  • take a line (for example the last one);
  • reorder the columns of the matrix using the sorted values of the chosen line to derermine the order.

So, the last line is 7 88 9, which sorted is 7 9 88, then the three columns have to be reordered in a way such that, in this case, the last two columns are swapped.

A four-column more generic example, based on the last line again:

Input:

1    2 3  4
4    5 6  7
7 88.0 9 -3

Output:

 4 1 3 2
 7 4 6 5
-3 7 9 88.0
leonard vertighel
  • 1,058
  • 1
  • 18
  • 37
  • do you mean sort the last line and all line before it do same col swaps ? – Kent Aug 29 '13 at 16:11
  • Why do the first two rows get swapped, they are no longer in sorted order in either direction!? – Chris Seymour Aug 29 '13 at 16:12
  • Yes, I would like to use a specific line (in this case the last one) to re-order all the columns basing on the values of that line – leonard vertighel Aug 29 '13 at 16:15
  • So what does the value 7, 9 or 88 have to with the orders of line 1 or 2? The only transformation I can infer is you switch the last two values in every row which has nothing to with the values in the last row. – Chris Seymour Aug 29 '13 at 16:16
  • 1
    I have absolutely no idea what this question is about. Anyone figured it out? – Ed Morton Aug 29 '13 at 16:21
  • 2
    @EdMorton I believe he wants to switch the order of columns such that the last row is ordered ascending. – Kevin Aug 29 '13 at 16:22
  • 1
    @EdMorton my understanding is, he wants to sort the fields in last line, during sorting, the fields would be swapped. (so called re-ordered), after the sorting of the last line, he wants all lines above to do the same (re-ordering). – Kent Aug 29 '13 at 16:26

3 Answers3

1

Here's a quick, dirty and improvable solution: (edited because OP clarified that numbers are floating point).

$ cat test.dat
1 2 3
4 5 6
.07 .88 -.09
$ awk "{print $(printf '$%d%.0s\n' \
                  $(i=0; for x in $(tail -n1 test.dat); do
                           echo $((++i)) $x
                         done |
                  sort -k2g) | paste -sd,)}" test.dat
3 1 2
6 4 5
-.09 .07 .88

To see what's going on there (or at least part of it):

$ echo "{print $(printf '$%d%.0s\n' \
                      $(i=0; for x in $(tail -n1 test.dat); do
                               echo $((++i)) $x
                             done |
                      sort -k2g) | paste -sd,)}" test.dat
{print $3,$1,$2} test.dat

To make it work for an arbitrary line, replace tail -n1 with tail -n+$L|head -n1

rici
  • 234,347
  • 28
  • 237
  • 341
  • @JonathanLeffler: Perl is really just an "improved" shell, with improved warts. – rici Aug 29 '13 at 17:03
  • Your code works for the example, but strangely it does not work with my large matrix (100 columns and 27000 lines). It can be due to the format? All numbers are floats like ` 0.018344 -0.001763 0` etc. – leonard vertighel Aug 29 '13 at 17:16
  • @leonardvertighel: you didn't mention that the numbers are floating point. `sort -n` doesn't work with floating point numbers. Try using `sort -k2g` instead of `sort -k2n` – rici Aug 29 '13 at 17:20
  • @rici unfortunately sort -k2g does not produce changes – leonard vertighel Aug 29 '13 at 17:27
  • @leonardvertighel: It works for me. Maybe you need to post a more realistic excerpt of your data. (And also the output of `sort --version`) – rici Aug 29 '13 at 17:50
  • 1
    `export LANG=en_US.UTF-8`!!!! Stupid locale with its comma-separated decimals. Thanks all for your patience – leonard vertighel Aug 29 '13 at 17:57
1

This problem can be elegantly solved using GNU awk's array sorting feature. GNU awk allows you to control array traversal using PROCINFO. So two passes of the file are required, the first pass to split the last record into an array and the second pass to loop through the indices of the array in value order and output fields based on indices. The code below probably explains it better than I do.

awk 'BEGIN{PROCINFO["sorted_in"] = "@val_num_asc"};
    NR == FNR {for (x in arr) delete arr[x]; split($0, arr)};
    NR != FNR{sep=""; for (x in arr) {printf sep""$x; sep=" "} print ""}' file.txt file.txt
4 1 3 2
7 4 6 5
-3 7 9 88.0
iruvar
  • 22,736
  • 7
  • 53
  • 82
0

Update:

Create a file called transpose.awk like this:

{ 
    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 OFS a[i,j];
        }
        print str
    }
}

Now here is the script that should do work for you:

awk -f transpose.awk file | sort -n -k $(awk 'NR==1{print NF}' file) | awk -f transpose.awk
1 3 2
4 6 5
7 9 88

I am using transpose.awk twice here. Once to transpose rows to columns then I am doing numeric sorting by last column and then again I am transposing rows to columns. It may not be most efficient solution but it is something that works as per the OP's requirements.

transposing awk script courtesy of: @ghostdog74 from An efficient way to transpose a file in Bash

Community
  • 1
  • 1
anubhava
  • 761,203
  • 64
  • 569
  • 643