Shell Programming and Scripting

CPLX9PC-4943    CPLX9PC-4943    1
CPLX9PC-4943    CpxID123        0
CPLX9PC-4943    CpxID126        0
CPLX9PC-4943    CPLX9PC-5763    0.5
CPLX9PC-4943    CpxID13 0
CPLX9PC-4943    CPLX9PC-6163    0
CPLX9PC-4943    CPLX9PC-6164    0.04
CPLX9PC-4943    CPLX9PC-6165    0.027027
::::::::::::::::::::::::::::::::::::::::::::::
::::::::::::::::::::::::::::::::::::::::::::::::

a       a       1
a       b       2
a       c       4
b       a       2
b       b       1
b       c       7
c       a       4
c       b       7
c       c       1

        a       b       c
a       1       2       4
b       2       1       7
c       4       7       1

awk '
{a[$1,$2]=$3; b[$1];}
END{
  for(i in b)   #print header
    printf("\t%s",i);  
  printf("\n");
  for(i in b) { #each row
     printf("%s\t",i); 
     for(j in b)  #each column
        printf("%d\t", a[i,j]);
     printf("\n");
   } 
}' SUBSEP="\t" input.txt

 d = read.table("mydata.txt")
 library(reshape)
 cast(d, V1 ~ V2 )

awk '
NF == 3 {
        if(!($1 in rh)) {
                # Add a new row heading.
                row[++nr] = $1  
                rh[$1]
                if(length($1) > rw) rw = length($1)
        }
        if(!($2 in ch)) {
                # Add a new column heading.
                col[++nc] = $2
                ch[$2]
                cw[nc] = length($2)
        }
        # Add a datapoint.
        d[$1, $2] = $3
}
END {   printf("%*s", rw, "") 
        for(i = 1; i <= nc; i++) {
                if(cw[i] < 11) cw[i] = 12
                printf(" %*.*s", cw[i], cw[i], col[i])
        }
        printf("\n")
        for(i = 1; i <= nr; i++) {
                printf("%*.*s", -rw, rw, row[i])
                for(j = 1; j <= nc; j++)
                        if((row[i], col[j]) in d)
                                printf(" %*.6f", cw[i], d[row[i], col[j]])
                        else    printf(" %*s", cw[i], "")
                printf("\n")
        }
}' input

             CPLX9PC-4943     CpxID123     CpxID126 CPLX9PC-5763      CpxID13 CPLX9PC-6163 CPLX9PC-6164 CPLX9PC-6165
CPLX9PC-4943     1.000000     0.000000     0.000000     0.500000     0.000000     0.000000     0.040000     0.027027

             a            b            c
a     1.000000     2.000000     4.000000
b     2.000000     1.000000     7.000000
c     4.000000     7.000000     1.000000

$ awk '{A[$1,$2]=$3;B[$1]++;C[$2]++}END{for(i in C){s=s?s"\t"i:"\t"i}
print s;
for(i in B){s=i;
for(j in C){s=s"\t"A[j,i]}
print s}}' file

        a       b       c
a       1       2       4
b       2       1       7
c       4       7       1

              CPLX9PC-4943     CpxID123     CpxID126 CPLX9PC-5763      CpxID13 CPLX9PC-6163 CPLX9PC-6164 CPLX9PC-6165
 CPLX9PC-4943            1            0            0          0.5            0            0         0.04     0.027027