Transpose matrix based on second column using awk

Shell Programming and Scripting
1

Hi, Is it possible to transpose the matrix like this using awk ? Many thanks in advance

Input

abc     Name_1  0
abc     Name_2  1
abc     Name_3  2
abc     Name_4  0.4
def     Name_1  0
def     Name_2  9
def     Name_3  78
def     Name_4  1

Output

        abc     def
Name_1  0       0
Name_2  1       9
Name_3  2       78
Name_4  0.4     1
2

Short answer (to your specific question), yes.

What have you tried so far?

Robin

3

Hello quincyjones,

Could you please try following and let us know if this helps.

awk '{X[$1];Y[$2];Z[$1,$2]=$3}
END{
printf "%20s",""; for(i in X) printf "%10s",i;print "";
for(j in Y) {printf "%10s",j;printf "%10s",""
for(i in X) printf "%10s",Z[i,j];print "";}
}' Input_file

Thanks,
R. Singh

1 Like
4

Yes. It is working great. thanks. is it possible to have tab delimited output ? I noticed at the beginning of the header there are many empty spaces

5

Try

awk     '               {HD[$1]; LN[$2]; VL[$1,$2] = $3}
         END            {                              for (i in HD) printf "\t%s", i;       print "";
                         for (j in LN) {printf "%s",j; for (i in HD) printf "\t%s", VL[i,j]; print "";}
                        }
        ' file
          abc  def
Name_1    0    0
Name_2    1    9
Name_3    2    78
Name_4    0.4  1