CODEWITHSUNDEEP
bashsortingawk
leonard vertighel
leonard vertighel

Reputation: 1068

How can I use awk to sort columns by the last value of a column?

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:

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

Upvotes: 2

Views: 1268

Answers (3)

iruvar
iruvar

Reputation: 23374

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

Upvotes: 1

rici
rici

Reputation: 241891

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

Upvotes: 1

anubhava
anubhava

Reputation: 785721

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

Upvotes: 0

Related Questions