How to reorder a large number of columns?

Question

I'm looking for a pipeable one-liner to reorder a large number of columns (where manually entering the column numbers in e.g. an awk command such as awk '{print $3,$2,$1}' is not feasible). The order could be given by a sorting scheme (alphabetical, numerical - so like 'sort' but acting on columns rather than rows.) or be arbitrarily given in a text file.

Answer 1

Simple solution with Perl.

First populate your array of values.

➜ ~ x="$(cat << END
22      79      83      16      25      1       4       82      34      68
48      43      2       26      39      2       71      43      57      41
77      70      73      18      76      33      21      54      67      50
6       65      46      92      25      70      53      28      3       40
32      60      76      39      26      44      34      91      24      39
59      75      96      85      52      98      69      28      72      94
48      0       88      55      6       78      1       54      83      81
3       43      48      24      23      87      28      98      38      67
97      73      74      24      92      67      1       27      90      85
32      55      52      44      26      37      87      37      100     92
END
)"

➜  ~ perl -lane '@i=sort({ @F[$a] <=> @F[$b] } 0..$#F) if $.==1; 
                 print join("\t", @F[@i])' <<< "$x"      

1       4       16      22      25      34      68      79      82      83
2       71      26      48      39      57      41      43      43      2
33      21      18      77      76      67      50      70      54      73
70      53      92      6       25      3       40      65      28      46
44      34      39      32      26      24      39      60      91      76
98      69      85      59      52      72      94      75      28      96
78      1       55      48      6       83      81      0       54      88
87      28      24      3       23      38      67      43      98      48
67      1       24      97      92      90      85      73      27      74
37      87      44      32      26      100     92      55      37      52

• -a: enables autosplit which automatically populates the @F array
• -n: reads each line in a while loop
• $#F: returns the largest 0-based index in the array
• <=>: comparison operator for sort functions (only numeric input, for string comparisons use cmp)
• sort: returns the sorted indexes from the array 0..$#F (using the built-in $a and $b variables)
• @i: contains the array of sorted indexes for @F (in this example, @i = 5 6 3 0 4 8 9 1 7 2)
• $. == 1: and do it only in the first line
• @F[@i]: sorts each line based on the sorted indexes

Source: https://learnbyexample.gitbooks.io/command-line-text-processing/content/perl_the_swiss_knife.html

Answer 2

Here is a streamable solution.

I assume you want to sort based on the first row of the columns, otherwise adapt to get the sorting key from somewhere else.

Generate sorting key (reusing Rush's array):

echo -e  "b a c\n5 4 6\n8 7 9" > data

key=$(head -n1 data | sed 's/ \+/\n/g' | nl -n ln | sort -k2 | cut -f1)
    

$key now holds:

2
1
3

Now use the key to sort columns:

awk -v key="$key" '
BEGIN { split(key, order, "\n") }

{ 
  for(i=1; i<=length(order); i++) { 
    printf("%s ", $order[i])
  }
  printf("\n");
}' data

Output:

a b c 
4 5 6 
7 8 9

Answer 3

I'm not sure it is the best solution and I'm not sure it will work fast on huge tables, but it should work:

echo -e  "2 1 3\n5 4 6\n8 7 9"  | \
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}}' \ 
     | sort -n  | \
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}}'

How it works: it transposes table, then it sorts the table and transposes it back.

btw echo -e "2 1 3\n5 4 6\n8 7 9" will result to

2 1 3
5 4 6
8 7 9

After script work it will result to

1 2 3
4 5 6
7 8 9

ps. I think it is possible to sort array in awk, unfortunately I haven't enough time to do it.

Answer 4

Assuming your file is xy.dat, and separated by blank:

cat xy.dat | while read line ; do  
   echo $line | tr ' ' '\n' | sort -nr | tr '\n' ' '
   echo
done

Since my testdata was numeric ascending, I use sort -nr in the heart, to make it descending, and see some effect.

Now to make it configurable, one would just pass as parameters the flags for sort, which allows ascending (none) and descending -r (reverse), but also -n (numerical) and many more (see: sort --help). Another thing you might like configurable is the delimiter. Blank/Tab/Semicolon/Comma? Maybe a regex-group like "[ \t]" to mean blank-or-tab? But what to use for output then? And you wouldn't like to hardcode the filename, but use your program as a filter. Here is a fast approach:

#!/bin/bash
flags=$1
delim=$2 
while read line ; do  
    echo $line | tr "$delim" '\n' | sort $flags | tr '\n' "$delim"
    echo
done

invocation:

cat num.dat | bash colsort.sh "-nr" ' ' 
4 3 2 1 
8 7 6 5 
11 10 9 

cat num.dat | bash colsort.sh "-r" ' ' 
4 3 2 1 
8 7 6 5 
9 11 10 

cat num.dat | bash colsort.sh "--" ' ' 
1 2 3 4 
5 6 7 8 
10 11 9 

See how it sorted by default with -- (alphabetical: 10 11 9), reverse (9 10 11) or numerical (11 10 9).

Mainly how to mask blank, tab and so on would be helpful, if documented.