Help needed editing text file using the terminal

4galaxy7 · December 3, 2015, 7:04am

Hi,

I have text file with the header like this

tracking_id condition replicate FPKM
XLOC_000001 alpha 1 10.3199
XLOC_000001 alpha 0 10.3686
XLOC_000001 alpha 2 15.5619
...

With the first column being genes, the second being the condition, the third being the replicate number and the fourth being the relative gene expression. So I have about 15 000 genes tested, with three conditions (alpha, beta and low) and about between 11-13 replicates per condition (13 alpha, 11 beta and 11 low).

I want to change the format so along the top I have the 35 replicates along the top (i.e. the 13 alpha, 11 beta and 11 low), the genes down the side as they already are a FPKM value for each individual for each gene, something as follows

tracking_id 1 2 3 4 ..... 35
XLOC_00001 15 11 22 11 28
XLOC_00002 16 18 34 11 12
XLOC_00003 14 19 62 12 22
....
XLOC_15205

My knowledge of coding is v limited, so if someone could help me, either with a Perl script or a simple UNIX command line script, that would be really great.

Thanks!

Edit: I've added in a screen shot of the input (on the right) and what I want the output to look like. I did it in excel to make it a bit easier to see the columns, but I have them in text files too.

input

tracking_id condition replicate FPKM
XLOC_000001    alpha    1    10.3199
XLOC_000001    alpha    0    10.3686
XLOC_000001    alpha    2    15.5619
XLOC_000001    alpha    3    17.4901
XLOC_000001    alpha    4    5.73344
XLOC_000001    alpha    5    6.38582
XLOC_000001    alpha    6    13.2137
XLOC_000001    alpha    7    12.2629
XLOC_000001    alpha    8    9.99926
XLOC_000001    alpha    9    12.1221
XLOC_000001    alpha    10    10.5121
XLOC_000001    alpha    11    10.75
XLOC_000001    alpha    12    7.43817
XLOC_000001    alpha    13    7.36243
XLOC_000001    beta    0    12.5359
XLOC_000001    beta    1    11.2568
XLOC_000001    beta    2    10.0826
XLOC_000001    beta    3    9.53999
XLOC_000001    beta    4    7.37831
XLOC_000001    beta    5    5.46127
XLOC_000001    beta    6    7.86049
XLOC_000001    beta    7    7.86049
XLOC_000001    beta    8    11.8768
XLOC_000001    beta    9    13.6802
XLOC_000001    beta    10    9.68721
XLOC_000001    beta    11    9.03584
XLOC_000001    low    0    9.45804
XLOC_000001    low    1    9.54959
XLOC_000001    low    2    6.54958
XLOC_000001    low    3    3.23738
XLOC_000001    low    4    8.2976
XLOC_000001    low    5    5.60313
XLOC_000001    low    6    5.16253
XLOC_000001    low    7    6.5954
XLOC_000001    low    8    14.9347
XLOC_000001    low    9    9.66479
XLOC_000001    low    10    4.43241
XLOC_000001    low    11    5.78727
XLOC_000002    alpha    1    0
XLOC_000002    alpha    0    0
XLOC_000002    alpha    2    0
XLOC_000002    alpha    3    0
XLOC_000002    alpha    4    0
XLOC_000002    alpha    5    0
XLOC_000002    alpha    6    0
XLOC_000002    alpha    7    0.198545
XLOC_000002    alpha    8    0
XLOC_000002    alpha    9    0
XLOC_000002    alpha    10    1.50002
XLOC_000002    alpha    11    1.18404
XLOC_000002    alpha    12    0
XLOC_000002    alpha    13    0
XLOC_000002    beta    0    0
XLOC_000002    beta    1    0
XLOC_000002    beta    2    0.191399
XLOC_000002    beta    3    0.377084
XLOC_000002    beta    4    0
XLOC_000002    beta    5    0
XLOC_000002    beta    6    0
XLOC_000002    beta    7    0
XLOC_000002    beta    8    0
XLOC_000002    beta    9    0
XLOC_000002    beta    10    2.5607
XLOC_000002    beta    11    2.2376
XLOC_000002    low    0    0
XLOC_000002    low    1    0
XLOC_000002    low    2    1.37854
XLOC_000002    low    3    1.8105
XLOC_000002    low    4    2.57191
XLOC_000002    low    5    2.43143
XLOC_000002    low    6    0.777916
XLOC_000002    low    7    0.391041
XLOC_000002    low    8    0.545685
XLOC_000002    low    9    0.551643
XLOC_000002    low    10    0
XLOC_000002    low    11    0

output example. all the rows should look like the first two - i just had to add in a few spaces so they lined up correctly. and i just randomly added in the values into the table, but in the ouput they need to correspond to the FPKM values on the input.

tracking_id1(alpha) 2(alpha) 3(alpha) 4(alpha) 5(alpha) 6(alpha) 7(alpha) 8(alpha) 9(alpha) 10(alpha) 11(alpha) 1(beta) 2(beta
XLOC_000001     2    4    6    2    4    6    2    4    6    2    4    6 
XLOC_000002     2    4    6    2    4    6    2    4    6    2    4    6         
XLOC_000003    2    4    6    2    4    6    2    4    6    2    4    6    
XLOC_000004    2    5    4    2    5    4    2    5    4    2    5    4    
XLOC_000005    3    4    54    3    4    54    3    4    54    3    4    54    
XLOC_000006    2    1    5    2    1    5    2    1    5    2    1    5    
XLOC_000007    2    4    4    2    4    4    2    4    4    2    4    4    
XLOC_000008    2    4    6    2    4    6    2    4    6    2    4    6    
XLOC_000009    2    5    4    2    5    4    2    5    4    2    5    4    
XLOC_000010    3    4    54    3    4    54    3    4    54    3    4    54    
XLOC_000011    2    1    5    2    1    5    2    1    5    2    1    5    
XLOC_000012    2    4    4    2    4    4    2    4    4    2    4    4    
XLOC_000013    2    4    6    2    4    6    2    4    6    2    4    6    
XLOC_000014    2    5    4    2    5    4    2    5    4    2    5    4    
XLOC_000015    3    4    54    3    4    54    3    4    54    3    4    54    
XLOC_000016    2    1    5    2    1    5    2    1    5    2    1    5    
XLOC_000017    2    4    4    2    4    4    2    4    4    2    4    4    
XLOC_000018    2    4    6    2    4    6    2    4    6    2    4    6    
XLOC_000019    2    5    4    2    5    4    2    5    4    2    5    4    
XLOC_000020    3    4    54    3    4    54    3    4    54    3    4    54

RudiC · December 3, 2015, 8:01am

As there are "0" replicates, you've got 14 + 12 + 12 = 38 replicates per ID. Should the "0" be included?

Btw, why dont you do it entirely in a spread sheet?

Howsoever, would this come close to what you need?

awk     '
NR > 1  {LN[$1]; HD[$3,$2]; MX[$1,$3,$2] = $4}
END     {               printf "%10s", ""; for (i in HD) printf "%10s", i; print "";
         for (j in LN) {printf "%10s",j;   for (i in HD) printf "%10s", MX[j,i]; print ""}
        }
' SUBSEP=, file
              11,low     8,low   9,alpha     6,low   8,alpha   7,alpha     4,low    9,beta   6,alpha    8,beta   5,alpha     2,low    7,beta   4,alpha    6,beta   3,alpha     0,low    5,beta   2,alpha    4,beta   1,alpha    3,beta   0,alpha    2,beta    1,beta    0,beta  13,alpha    10,low  12,alpha  11,alpha  10,alpha   11,beta   10,beta     9,low     7,low     5,low     3,low     1,low
XLOC_000002         0  0.545685         0  0.777916         0  0.198545   2.57191         0         0         0         0   1.37854         0         0         0         0         0         0         0         0         0  0.377084         0  0.191399         0         0         0         0         0   1.18404   1.50002    2.2376    2.5607  0.551643  0.391041   2.43143    1.8105         0
XLOC_000001   5.78727   14.9347   12.1221   5.16253   9.99926   12.2629    8.2976   13.6802   13.2137   11.8768   6.38582   6.54958   7.86049   5.73344   7.86049   17.4901   9.45804   5.46127   15.5619   7.37831   10.3199   9.53999   10.3686   10.0826   11.2568   12.5359   7.36243   4.43241   7.43817     10.75   10.5121   9.03584   9.68721   9.66479    6.5954   5.60313   3.23738   9.54959

4galaxy7 · December 3, 2015, 8:04am

Yeah you're right, I didn't include the zero.

Yeah that looks great, thanks so much. How do I run the script? Is it a perl script or should I just put it into the linux terminal?

RavinderSingh13 · December 3, 2015, 8:18am

Hello 4galaxy7,

Welcome to forums, thank you for using code tags for Inputs you have shown us in your 1st post. Also for RudiC's script yes you could run it as a command line command as well as you could make a script .ksh OR .sh OR .awk with proper permissions and can run it too. Also as always on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk .

Thanks,
R. Singh

4galaxy7 · December 3, 2015, 9:11am

Thanks that's worked I think. Would it be possible to maybe make it so that it copies the results into a text file, because i can't seem to copy and paste the terminal output into a word file because it's so many lines long. Thanks!

RavinderSingh13 · December 3, 2015, 9:18am

Hello 4galaxy7,

You could use as following for same.

awk     '
NR > 1  {LN[$1]; HD[$3,$2]; MX[$1,$3,$2] = $4}
END     {               printf "%10s", ""; for (i in HD) printf "%10s", i; print "";
         for (j in LN) {printf "%10s",j;   for (i in HD) printf "%10s", MX[j,i]; print ""}
        }
' SUBSEP=, file > Output_file

This Output_file can be up to you and you could put it's name as your wish.

Thanks,
R. Singh