Splitting a semi colon value in new line and copying its neighboring cell values

Question

[S AR]

I have an array file having gene names and Fold change values. Its a three column file in which the gene column has semi colon separated values. I want to split those values but while splitting also want that the fold change is copied in the new line with the split values like below:

ID                 GENE              FoldChange
TC0100007038.hg.1  NECAP2            1.17
TC0100007063.hg.1  FAM231C; FAM231A  -1.04
TC0100007206.hg.1  CDA               -1.15
TC0100007207.hg.1  PINK1; MIR6084    1.1

And I want the conversion to be like this:

ID                 GENE        FoldChange
TC0100007038.hg.1  NECAP2      1.17
TC0100007063.hg.1  FAM231C     -1.04
TC0100007063.hg.1  FAM231A     -1.04
TC0100007206.hg.1  CDA         -1.15
TC0100007207.hg.1  PINK1       1.1
TC0100007207.hg.1  MIR6084     1.1

I used awk to split the semi colon separated values into rows but unable to copy the next column value in the split line. Can anyone help please?

Answer 1

[cpad0112]

Try

$ awk -v OFS="\t" '{split($2,a,";"); for(i in a)print $1,a[i],$3}' test.txt 

ID  GENE     FoldChange
TC0100007038.hg.1   NECAP2   1.17
TC0100007063.hg.1   FAM231C      -1.04
TC0100007063.hg.1   FAM231A      -1.04
TC0100007206.hg.1   CDA      -1.15
TC0100007207.hg.1   PINK1    1.1
TC0100007207.hg.1   MIR6084      1.1

[S AR]

Thanks that is great it worked:)

Answer 2

[Jean-Karim Heriche]

Try a perl one-liner:

perl -ne '@a=split/\t/; @b=split(/;/,$a[1]); foreach $b(@b){$a[1] = $b; print join("\t",@a);}'  test.txt

[Jorge Amigo]

Playing around with this idea, here's a similar proposal to remove empty spaces too from gene names:

perl -F"\t" -lane 'print "$F[0]\t$1\t$F[2]" while $F[1] =~ /([^; ]+)/g' test.txt