formatting problem (awk/bash)

Question

[lessismore]

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

[cpad0112]

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

[venu]

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

[Pierre Lindenbaum]

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

[lessismore]

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.

[glihm]

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?

Answer 1

[Pierre Lindenbaum]

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