Reorder Columns using awk

Question

I am trying to reorder the columns using awk, sed... but I could not find the correct answer for that, Basically, I want to reorder the Values from:

time 012016 022016 032016 04216

John 231 321 121 432

Mary 456 213 654 735

Charles 325 867 984 235

to:

time John Mary Charles

012016 231 456 325

022016 321 213 867

032016 121 654 984

042016 432 735 235

I tried to use something like but I am missing the numbers:

awk '{print $1}' ./database.dat | paste -d,  -s

output:

time John Mary Charles

Also see http://www.thelinuxrain.com/articles/transposing-rows-and-columns-3-methods — user2138595, Oct 16 '16 at 20:42

score 1 · Answer 1 · edited May 23 '17 at 10:32

Use ghostdog74's awk script three times to transpose your file:

#!/bin/bash

transpose () {
  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
    }
  }'
}

transpose | transpose | transpose

Usage: ./this_script.sh < filename

Output:

time John Mary Charles
012016 231 456 325
022016 321 213 867
032016 121 654 984
04216 432 735 235

I assume your file contains no empty lines.

Or modify ghostdog74's script to rotate in other direction.

sjsam · Answer 2 · 2016-10-16T08:52:03.030

Hope this is what you're looking for :

awk script

awk '{for(i=1;i<=NF;i++){val[NR][i]=$i}}
     NR==FNR{fc=NF}
     END{for(i=1;i<=fc;i++){
     for(rec=1;rec<=FNR;rec++){printf "%s%s",val[rec][i],(rec<FNR)?"\t":ORS}
     }}' filename

Output

time        John    Mary    Charles
012016      231     456     325
022016      321     213     867
032016      121     654     984
04216       432     735     235

^{Assumption: Every record in the file has the same number of fields}

Sundeep · Accepted Answer · 2016-10-17T02:01:18.417

$ cat ip.txt 
time 012016 022016 032016 04216
John 231 321 121 432
Mary 456 213 654 735
Charles 325 867 984 235

Using tr, wc and pr

$ tr ' ' '\n' < ip.txt | pr -$(wc -l < ip.txt)t
time          John          Mary          Charles
012016        231           456           325
022016        321           213           867
032016        121           654           984
04216         432           735           235

With perl, should be faster than tr, wc, pr mash

$ perl -lane '                                                                                                      
push(@a, @F);
END
{
    $r = $.; $c = $#F + 1;
    foreach $i (0..$#F)
    {
        print join "\t", @a[map {$i + $_*$c} 0..$r];
    }
}' ip.txt
time    John    Mary    Charles 
012016  231 456 325 
022016  321 213 867 
032016  121 654 984 
04216   432 735 235

Input line is split on spaces, which gets saved in @F array. That array is concatenated for every line to @a array
After entire file is processed, $. will have number of lines in input file. $#F gives index of last element of @F array
Then print in required format

"should be faster than ..." ... well, except for the load time ;-> . Good luck to all. — shellter, Oct 17 '16 at 01:53

Kaz · Answer 4 · 2016-10-17T03:56:11.480

The rs utility from BSD (obtained in Ubuntu using apt-get install rs).

$ rs -t 5
time 012016 022016 032016 04216

John 231 321 121 432

Mary 456 213 654 735

Charles 325 867 984 235
[Ctrl-D][Enter]
time     John     Mary     Charles
012016   231      456      325
022016   321      213      867
032016   121      654      984
04216    432      735      235

Transpose with TXR Lisp:

$ txr -e '(put-lines (mapcar (op cat-str @1 " ") (transpose (build (awk (f (add f)))))))' data
time John Mary Charles
012016 231 456 325
022016 321 213 867
032016 121 654 984
04216 432 735 235

Reorder Columns using awk

4 Answers4

Linked