reformat a gene association file

Question

[lessismore]

Hey all,

i have a text file with 3 columns tab separated: 1st column: a gene ID
2nd column: a value
3rd column: a list of genes associated to the one in the 1st column comma separated

TMCS09g1008699 6.4 TMCS09g1008677, TMCS09g1008681, TMCS09g1008685
TMCS09g1008690 5.3 TMCS09g1008686, TMCS09g1008680, TMCS09g1008675

etc..

what i want is this:

TMCS09g1008699 6.4 TMCS09g1008677
TMCS09g1008699 6.4 TMCS09g1008681
TMCS09g1008699 6.4 TMCS09g1008685
TMCS09g1008690 5.3 TMCS09g1008686
TMCS09g1008690 5.3 TMCS09g1008680
TMCS09g1008690 5.3 TMCS09g1008675

could someone help me?

Answer 1

[Pierre Lindenbaum]

 awk '{for(i=3;i<=NF;i++) print $1,$2,$i;}' input.txt  | sed 's/,$//'

[lessismore]

dear Pierre, i made a mistake, in the 3rd column theres no space in my input and now i get a wrong output.

Answer 2

[h.mon]

Combine Pierre's answer with Tom Fenech's answer at StackOverflow.

Three hints: 1) pay attention at which column you split; 2) you will have two awk commands, separated by a comma; and 3) you will not need the sed command.

[lessismore]

got it, thanks

awk 'BEGIN{FS=OFS="\t"} {n=split($3,a,",");for(i=1;i<=n;i++) print $1,$2,a[i]}