How to transfer the data of columns to rows (with awk)?

Question

I have a file like this:

n A B C D 
1 01 02 01 01
2 02 02 01 01

and I want to transfer the columns by rows, so the output should be like this:

n 1 2
A 01 02
B 02 02
C 01 01 
D 01 01

I have wrote this command:

awk '{ for (i=1;i<=NF;i++ ) printf $i " " }' file.txt > out-file.txt

the problem is that this command put everything on one line! so the output is like this:

n 1 2 A 01 02 B 02 02 C 01 01 D 01 01

Tina

Have a look here : https://mcmap.net/q/158913/-an-efficient-way-to-transpose-a-file-in-bash – Shuttle

is this not what a pivot table in Excel does? – Salmi

Answer 1

This might work:

awk '{
       for (f = 1; f <= NF; f++) { a[NR, f] = $f } 
     }
     NF > nf { nf = NF }
     END {
       for (f = 1; f <= nf; f++) {
           for (r = 1; r <= NR; r++) {
               printf a[r, f] (r==NR ? RS : FS)
           }
       }
    }' YOURINPUT

See it in action @ Ideone.

Jaimeejaimes

Answer 2

This uses of arrays of arrays which is a gawk extension:

tp(){ awk '{for(i=1;i<=NF;i++)a[i][NR]=$i}END{for(i in a)for(j in a[i])printf"%s"(j==NR?"\n":FS),a[i][j]}' "FS=${1-$'\t'}";}

Another option is to use rs (which is a BSD utility that also comes with macOS):

$ cat /tmp/a
n A B C D
1 01 02 01 01
2 02 02 01 01
$ rs -c' ' -C' ' -T</tmp/a|sed 's/.$//'
n 1 2
A 01 02
B 02 02
C 01 01
D 01 01

-c changes the input column separator, -C changes the output column separator, and -T transposes rows and columns.

When an output column separator is specified using -C, an extra column separator character is added to the end of each output line, but you can remove it with sed 's/.$//':

$ seq 4|paste -d, - -|rs -c, -C, -T
1,3,
2,4,
$ seq 4|paste -d, - -|rs -c, -C, -T|sed 's/.$//'
1,3
2,4

The rs command fails in the case where the first line ends with one or more empty columns, because the number of columns is determined based on the number of columns on the first line:

$ rs -c, -C, -T<<<$'1,\n3,4'
1,3,4,

Saidel

bravo~ 👏👏👏👏👏 – Pill

rs works well if each column has a header value, it falls apart if some headers values are empty. Its important to fill each header before using it. Example is looking at /proc/interrupts. Here is my code for cleaning up column headers and moving columns around before using rs. cat /proc/interrupts | grep 'CPU\|enp94s0f2v1' | sed -e '1 s/^/remove /' | sed -e '1 s/$/remove remove Interface/' | column -t | awk '{tmp=$1; $1=$NF; $NF=tmp; print}' | awk 'NF-=3' | sed 's/ */ /g' | rs -c' ' -C' ' -T | column -t – Drysalter

Answer 3

Save this script as transpose.awk and chmod u+x transpose.awk. It's a modification of Tim Sherwood's transpose.

#!/usr/bin/gawk -f

BEGIN {
    max_x =0;
    max_y =0;
}

{
    max_y++;
    for( i=1; i<=NF; i++ )
    {
        if (i>max_x) max_x=i;
        A[i,max_y] = $i;
    }
}

END {
    for ( x=1; x<=max_x; x++ )
    {
        for ( y=1; y<=max_y; y++ )
        {
            if ( (x,y) in A ) printf "%s",A[x,y];
            if ( y!=max_y ) printf " ";
        }
        printf "\n";
    }
}

Example:

$ ./transpose.awk example
n 1 2
A 01 02
B 02 02
C 01 01
D 01 01

Baldachin

Answer 4

Here is a different solution, which involve only one for loop at the end:

{ for (i=1; i<=NF; i++) col[i] = col[i] " " $i }
END { 
    for (i=1; i<=NF; i++) { 
        sub(/^ /, "", col[i]); 
        print col[i] 
    } 
}

Discussion

• This solution uses a single-dimension array col, which stores the value for the whole column. col[1] is the first column.
• For every line, we append the column to col[i]. Because we blindly append, the col[i] value will contain a leading space.
• At the end, the sub() function removes the leading white space before printing out the column (now a row)

Shortterm

Answer 5

Well, without awk we can do this with cat:

for x in `cat filename`
do
echo $x
done

With this script the result will emerge in cols.

Wheeze