Sort all rows horizontally by reference to columns in a particular row

Question

I need to sort horizontally, not vertically, and sort seems to be designed for vertical operation. q E.g., I have three rows as follows:

banana/orange/apple/mango
potato/tomato/onion/garlic
chair/table/carpet/window

All rows have the same number of columns, separated by a /. I want the columns to be re-arranged according to alphabetical order in row 1. So it will become:

apple/banana/mango/orange
onion/potato/garlic/tomato
carpet/chair/window/table

This might be better visualized as shown below:

 

i.e., like sort-by-column in a spreadsheet.

Answer 1

With GNU awk, you may dictate the order in which arrays are traversed by setting sorted_in within PROCINFO. In this instance, set it to @val_str_asc to force arrays to be traversed in ascending order of values.

Next split the first line into an array a Finally, for each line, traverse the array and print fields corresponding to the keys as they are retrieved.

awk -F'/' 'BEGIN{PROCINFO["sorted_in"]="@val_str_asc"};
   FNR == 1{n = split($0, a)};
   {x=0; for (k in a) printf "%s%s", $k, ++x == n? "\n": FS}' file

apple/banana/mango/orange
onion/potato/garlic/tomato
carpet/chair/window/table

Or, with python

from __future__ import print_function
with open('file') as f:
    keys = next(f).rstrip('\n').split('/')
    print(*sorted(keys), sep='/')
    for line in f:
            g = (m for l, m in sorted(zip(keys, line.rstrip('\n').split('/'))))
            print(*g, sep='/')

apple/banana/mango/orange
onion/potato/garlic/tomato
carpet/chair/window/table

Answer 2

I think the basic approach would be

• create a list of indices, sorted in the order of the first row - for example as described here: How to return a sorted list's index in Perl?

• slice the rows using the list

So

perl -F'/' -alne '
   our @inds = sort { $F[$a] cmp $F[$b] } 0..$#F if $. == 1; 
   print join "/", @F[@inds]
' file

Answer 3

On BSD with the rs utility:

$ rs -T -c'/' <data.in | sort | rs -T -C'/' >data.out

• -T transposes
• -c'/' sets the input column delimiter
• -C'/' sets the output column delimiter

Looking at it:

$ cat data.in
banana/orange/apple/mango
potato/tomato/onion/garlic
chair/table/carpet/window

$ cat data.out
apple/banana/mango/orange/
onion/potato/garlic/tomato/
carpet/chair/window/table/

Removing the superfluous / at the end:

$ sed 's#/$##' data.out
apple/banana/mango/orange
onion/potato/garlic/tomato
carpet/chair/window/table

Answer 4

1. Using GNU datamash, transpose, sort, and transpose again:

   datamash -t '/' transpose < file | datamash -t '/' -s -g1 transpose
   

   Output:

   apple/banana/mango/orange
   onion/potato/garlic/tomato
   carpet/chair/window/table
   

2. Various software tools:

   join -a 1 -t / -o $( head -n 1 file | \
                        tr / '\n' | \ 
                        nl  -n ln | \
                        sort  -k2 | \
                        cut   -f1 | \
                        sed -n 's/^/1./;H;1h;${x;s/\n/,/g;s/ //gp}' ) \
         file /dev/null
   

   How that works: join can reorder columns, via parameters passed to its -o option. So (using no variables and no arrays) the trick is to generate those parameters:

   1. head gets the first line,
   2. tr converts that to several lines,
   3. which nl numbers,
   4. sort by the 2nd column, (i.e. apple, etc.),
   5. and cut the reordered list of numbers in column #1,
   6. sed converts the numbers to a format that join likes.