Cris Cris - 1 month ago 7
Bash Question

Transpose from long file to wide file using linux commands (awk)

I have a long file and I need a wide file, I know do that in R, but I want to do using Linux because is faster. The field separator is tab.

Input file

1 C C
1 G G
1 C G
2 G G
2 C C
2 C G
3 G G
3 C C
3 C C


Output file:

1 2 3
CC GG GG
GG GG CC
CG CG CC


Thank you

Answer

awk to the rescue!

assuming consistent data (same number of elements for each key)

$ awk '{k[$1]; a[$1,++c[$1]]=$2$3} 
    END{for(x in k) printf "%s",x OFS; 
        print ""; 
        for(i=1;i<=c[$1];i++) 
           {for(x in k) printf "%s", a[x,i] OFS; 
            print ""}}' file

1 2 3
CC GG GG
GG CC CC
CG CG CC

the order of columns not guaranteed though...

Comments