This is a test version of Biostars. For the public version, visit https://www.biostars.org.
formatting problem (awk/bash)

Hello everybody,

really simple question i think.

text1 5 r1
text2 4 r1
text3 6 r1
text1 6 r2
text2 44 r2
text3 5 r2

i would like to have this
          r1 r2
text1     5  6
text2     4  44
text3     6  5

thanks in advance

awk bash
• 2,162 views • link
updated by Pierre Lindenbaum Pierre Lindenbaum • written by lessismore lessismore

May be this is not a right place to ask this question. (You can check datamash utility)

• 0 views • link
written by venu venu

what is the logic ? how is it related to bioinformatics ? put the real data.

• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Sorry i simplified the problem. Text are gene ID and numbers are expression values. The idea is to generate a matrix for plotting the data. "r" stands for replicate.

• 0 views • link
written by lessismore lessismore

Real data are always better as some special cases must be handled depending on your expression values and ID. ;)

edit: Do you always have r1 lines, and then r2 lines and so on? Or it can be unordered?

• 0 views • link
written by glihm glihm

datamash output:

$ datamash crosstab 1,3 sum 2 < test.txt  
        r1  r2
text1   5   6
text2   4   44
text3   6   5

input:

$ cat test.txt 
text1   5   r1
text2   4   r1
text3   6   r1
text1   6   r2
text2   44  r2
text3   5   r2
• 0 views • link
written by cpad0112 cpad0112

1 answer 

rm -f tmp.sqlite3 && cat input.txt | awk 'BEGIN{printf("create table T(gene unique,r1,r2);\n");}{printf("insert or ignore into T(gene) values(\"%s\");\nupdate T set %s=%s where gene=\"%s\";\n",$1,$3,$2,$1);}END{printf("select * from T; drop table T;\n");}'  | sqlite3 

text1|5|6
text2|4|44
text3|6|5
• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Log in to answer this question.

More posts like this