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.
-
1Example of input and output (with small number of columns) would be nice. Should the columns be sorted by a header row?– Kusalananda ♦Jun 25, 2016 at 18:18
4 Answers
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 usecmp
)sort
: returns the sorted indexes from the array0..$#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
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
-
This works on one of my computers but not the other, and I'm trying to figure out why. Both are running Ubuntu 22.04. Edit: It seems to be due to gawk vs mawk. I'm guessing the former is assumed here on U&L.– laur34Feb 22 at 10:57
-
1@laur34: the discrepancy might come from how
gawk
andmawk
fetch values with hash-keys. I have made the for-loop explicit, so now it should be more portable.– ThorFeb 23 at 13:10
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.
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.