How to sort columns based on the first line?

Question

I need to sort the columns of a very big dataset (1000 lines and 700000 columns). As an example, my columns are randomly arranged like: col1 col4 col3 col2, and I need to sort that.

I have been trying some commands, but no success.

example:

ID M2 M5 M8 M1 M3 M9 .....M7000000
Animal1 1 0 2 1 0 2 .....1
Animal2 0 1 2 0 1 1 .....0
Animal3 2 1 0 1 2 1 .....0
.
.
.
.
Animaln

In this example, dots means that I have a lot of columns and lines. Again, I need to sort the columns to be like:

ID M1 M2 M3 M4 M5 M6 .....M7000000
Animal1 1 0 2 1 0 2 .....1
Animal2 0 1 2 0 1 1 .....0
Animal3 2 1 0 1 2 1 .....0
.
.
.
.
Animaln

Thank you

Answer 1

With GNU datamash and GNU sort:

datamash transpose -t ' ' -H <file_in.csv | sort -V | datamash transpose -t ' ' -H >file_out.csv

This works fine for "reasonably small" data. It may or may not work with your file.

Edit: The solutions below without transpositions should be less resource-intensive.

Answer 2

perl -pale '
   $. == 1 and
   @I = map  { $_->[1] }
        sort { $a->[0] <=> $b->[0] }
        map  { [ $F[$_] =~ /^M(\d+)$/, $_ ] } 1..$#F;
   $_ = "@F[0, @I]";
' yourlargefile

---

1. For the first line, we numerically sort it's 2nd...last columns using their numeric portions after the digit M occurring at the beginning, using the well known Schwartzian maneuver. This affords us the indices reordered so that columns come out in numerically sorted order (M1, M2, M3, ...)
2. All that remains is to use these indices coming from @I to re-arrange the @F elements.
3. Assigning the array in a double-quoted form converts it into a string with elements space separated.
4. -p option to Perl enables the autoprint of $_ contents, -l shall add the newline.

Answer 3

Using the perl module Sort::Naturally

input data

ID M2 M5 M8 M1 M3 M9 M700000
A1 m1,2 m1,5 m1,8 m1,1 m1,3 m1,9 m1,7000000
A2 m2,2 m2,5 m2,8 m2,1 m2,3 m2,9 m2,7000000
A3 m3,2 m3,5 m3,8 m3,1 m3,3 m3,9 m3,7000000
A1000 m1000,2 m1000,5 m1000,8 m1000,1 m1000,3 m1000,9 m1000,7000000

perl -MSort::Naturally -lane '
  if ($. == 1) {
    @indices = (0, map  { $_->[0] }
                   sort { ncmp($a->[1], $b->[1]) }
                   map  { [$_, $F[$_]] }
                   1..$#F
               );
    $, = " ";
  }
  print @F[@indices]
' test.data

output

ID M1 M2 M3 M5 M8 M9 M700000
A1 m1,1 m1,2 m1,3 m1,5 m1,8 m1,9 m1,7000000
A2 m2,1 m2,2 m2,3 m2,5 m2,8 m2,9 m2,7000000
A3 m3,1 m3,2 m3,3 m3,5 m3,8 m3,9 m3,7000000
A1000 m1000,1 m1000,2 m1000,3 m1000,5 m1000,8 m1000,9 m1000,7000000

Answer 4

If you have the rs utility installed, you can do this:

rs -c' ' -T | {
    stdbuf -i0 sed "1q"
    sort -V
} | rs -C' ' -T

Or all on one line:

rs -c' ' -T | { stdbuf -i0 sed "1q"; sort -V ; } | rs -C' ' -T

• The first rs transposes the input data (with space-sparated fields)
• The command group:
  • sed reads the first line, outputs it, then quits, leaving the rest of the pipe from rs untouched. stdbuf is required to ensure that sed only reads up to the first newline and no further, by turning off input buffering
  • sorts the remaining lines
• The second rs transposes the resulting stream back to its original format.

rs is installed by default on MacOS. On Linux systems you may have to install it - e.g.

sudo apt install rs

Caveat: stdbuf and sorts -V option are GNU-specific so won't work on unmodified MacOS.

Answer 5

If you have GNU awk, you could try this:

NR == 1 {
    for (i = 2; i <= NF; i++) {
        columns[substr($i, 2)] = i;
    }
    count = asorti(columns, sorted, "@ind_num_asc");
    printf("%s", $1);
    for (i = 1; i <= count; i++) {
        printf(" M%s", sorted[i]);
        indx[i] = columns[sorted[i]];
    }
    print "";
    next;
}
{
    printf("%s", $1);
    for (i = 1; i <= count; i++) {
        printf(" %s", $(indx[i]));
    }
    print "";
}

Answer 6

In Python:

from csv import DictReader, DictWriter
with open('in_file.csv') as infile, open('out_file.csv') as outfile:
  reader = DictReader(infile)
  writer = DictReader(outfile, fieldnames=sorted(reader.fieldnames))
  writer.writerows(reader)

Answer 7

I don't know if you considered this as a good answer, but...

Why you don't use a database to solve this problem? you could import your dataset as a temporary table, and then do a

SELECT column1, column2, ... column-n FROM my_temp_table

You could use another filters, or transformations as you need. Then, you could reformat your output as you need.

All of this tasks could be programed as a bash script, and chaining outputs using pipes.

Sometimes I have been used "pv" command to see the output progress between commands.

To import the dataset you could program an ETL using Pentaho Data Integration.

Answer 8

Maybe this could also help you.

1. First you can use transpose your file (one of https://stackoverflow.com/questions/1729824/an-efficient-way-to-transpose-a-file-in-bash)
2. Sort first column with sort command.
3. Transpose again.

Ex:

$ echo "ID M2 M5 M8 M1 M3 M9 .....M7000000
Animal1 1 0 2 1 0 2 .....1
Animal2 0 1 2 0 1 1 .....0
Animal3 2 1 0 1 2 1 .....0
.
.
.
.
Animaln" | 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
    }
}'
ID M1 M2 M3 M5 .....M7000000 M8 M9
Animal1 1 1 0 0 .....1 2 2
Animal2 0 0 1 1 .....0 2 1
Animal3 1 2 2 1 .....0 0 1
.       
.       
.       
.       
Animaln