splitting file with awk

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

splitting file with awk

Josh Henderson
Hi Guru,

I have read your site with interest. I am a complete noob and am trying really hard to learn the basics of shell scripting. I have a particular problem that I am sure there is an elegant solution to but I just cant seem to figure it out. I have a data file structured:

input.csv

FileID geneID Coverage Quality Pass
1011 gene1 1.00 1271 PASS
1011 gene2 1.00 1821 PASS
1011 gene3 0.33 121.73 PASS
1011 gene4 1.00 235.962 PASS
6766 gene1 1.00 872.482 PASS
6766 gene2 1.00 20.098 PASS
6766 gene3 1.00 4 PASS
6766 gene4 1.00 246.965 PASS
9888 gene1 1.00 138.292 PASS
9888 gene2 1.00 236.567 PASS
9888 gene3 0.01 0.30625 PASS

and I want to restructure it so that it looks like:

        1011                6766                9888
geneID Coverage Quality Coverage Quality Coverage Quality
gene1 1.00        1271        1.00        872.482 1.00        138.292
gene2 1.00        1821        1.00        20.098 1.00        236.567
gene3 0.33        121.73 1.00        4        0.01        0.30625
gene4 1.00        235.962 1.00        246.965

I have been able to split the file using awk -F ',' '{if(FILENAME!=$1){FILENAME=$1;print FILENAME > FILENAME}} {print $2 "," $3 "," $4 > FILENAME}'  input.csv

I just dont know how to pipe that output to a join command so that they can be joined in the manner above. There are 95 unique fileIDs and I know that awk has trouble with lines >99 fields.

Any help would be greatly appreciated

Thanks

Josh