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

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?

unix awk python excel

• 1,907 views • link

written 5.7 years ago by S AR

2 answers

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

• 0 views • link

written 5.7 years ago by cpad0112

Thanks that is great it worked:)

• 0 views • link

written 5.7 years ago by S AR

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

• 0 views • link

written 5.7 years ago by Jean-Karim Heriche

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

• 0 views • link

written 5.7 years ago by Jorge Amigo