Split column using awk in a text file

Shell Programming and Scripting
1 
chr1    412573  .       A       C       2758.77 .      AC=2;AF=1.00;AN=2;DP=71;Dels=0.00;FS=0.000;HaplotypeScore=2.8822;MLEAC=2;MLEAF=1.00;MQ=58.36;MQ0=0;QD=38.86;resource.EFF=INTERGENIC(MODIFIER||||||||) GT:AD:DP:GQ:PL 1/1:0,71:71:99:2787,214,0       GATKSAM

chr1    602567  rs21953190      A       G       5481.77 .   AC=2;AF=1.00;AN=2;DB;DP=152;Dels=0.00;FS=0.000;HaplotypeScore=6.8385;MLEAC=2;MLEAF=1.00;MQ=59.09;MQ0=0;QD=36.06;resource.EFF=SYNONYMOUS_CODING(LOW|SILENT|gaT/gaC|D1034|ADNP2|protein_coding|CODING|ENSCAFT00000000008|5) GT:AD:DP:GQ:PL 1/1:0,151:151:99:5510,430,0     GATKSAM

I have text file with lines as shown here. Each row has 11 columns separated by tab. In each row, i want to split the 8th column such that the output should look like shown below. Here value in the 9th column is DP value and in the 10th column is MQ value followed by the values after resource.EFF=.

chr1    412573  .       A       C       2758.77 .           71     58.36    INTERGENIC    MODIFIER GT:AD:DP:GQ:PL 1/1:0,71:71:99:2787,214,0       GATKSAM

chr1    602567  rs21953190      A       G       5481.77 .         152       59.09  SYNONYMOUS_CODING
LOW    SILENT    gaT/gaC    D1034    ADNP2    protein_coding    CODING    ENSCAFT00000000008    5  GT:AD:DP:GQ:PL 1/1:0,151:151:99:5510,430,0     GATKSAM

Which means the 8th column has to be cleaned up such that, it has only DP value, MQ value and the information after SNPEFF_*= separated by tabs.

Could anyone help?

2

Try

awk -F "\t" '{n=split($8,P,";");for(i=1;i<=n;i++){if(P ~ /^SNPEFF/){split(P,K,"=");S=S?S" "K[2]:K[2]}};$8=S;S=""}1' file
3

Hi,

Thanks it is working. But it is not doing one thing. If you observe the output lines the 9th column is DP value and 10th is MQ value and then the values after SNPEFF_=.

Could you modify to achieve this?

4

Please use code tags, not quote tags.
Try this:

awk     '       {n  = split ($8,TMP,";")
                 $8 = ""
                 for (i=1; i<=n; i++)
                        if (match (TMP, /^DP=|^MQ=|^SNPEFF/)) {sub (/^.*=/,"",TMP); $8 = $8 ($8?"\t":"") TMP}
                }
         1
        ' FS="\t" file
chr1 403111 . G A 42 . 34    53    INTERGENIC    NONE    MODIFIER GT:GQ:PL 0/1:75:72,0,118 SAM
       
chr1 412573 . A C 2758.77 . 71    58.36    INTERGENIC    NONE    MODIFIER GT:ADP:GQ:PL1/1:0,71:71:99:2787,214,0 GATKSAM
5

Thanks it works. Could you explain the code if possible?

6 
awk     '       {n  = split ($8,TMP,";")                     # split the 8th field into TMP array using separator ";", keep no. of elements in n
                 $8 = ""                                     # clear 8th field
                 for (i=1; i<=n; i++)                        # inspect all elements of TMP
                   if (match (TMP, /^DP=|^MQ=|^SNPEFF/))  # if any array element starts with either of the regexs
                      {sub (/^.*=/,"",TMP)                # remove the part before "="
                       $8 = $8 ($8?"\t":"") TMP}          # append the rest to field 8
                } 
         1                                                   # print the modified line
        ' FS="\t" file                                       # use TAB as the separator
7

I tried to put the code as a oneliner as shown below,

awk '{n  = split ($8,TMP,";") $8 = "" for (i=1; i<=n; i++) if (match (TMP, /^DP=|^MQ=|^SNPEFF/)) {sub (/^.*=/,"",TMP); $8 = $8 ($8?"\t":"") TMP} }1' FS="\t" file

But it says syntax error at comma in {sub (/^.*=/,"",TMP[i]). What could be wrong here?

---------- Post updated at 05:25 AM ---------- Previous update was at 03:52 AM ----------

Hi,

The original question is slightly modified with slightly different input in the 8th column. Could you help? Thanks in advance.

8 
awk '{n=split ($8,TMP,";"); $8=""; for (i=1; i<=n; i++) if (match (TMP, /^DP=|^MQ=|^SNPEFF/)) {sub (/^.*=/,"",TMP); $8=$8 ($8?"\t":"") TMP}}1' FS="\t"
9

Can't see what has been modified. What's going wrong?

BTW - why do you need a one liner?

10

In the initial question, 8th column is of type

chr1	   602567	rs21953190	A	G	5481.77	.
AC=2;AF=1.00;AN=2;DB;DP=152;Dels=0.00;FS=0.000;HaplotypeScore=6.8385;MLEAC=2;MLEAF=1.00;MQ=59.09;MQ0=0;QD=36.06;SNPEFF_AMINO_ACID_CHANGE=D1034;SNPEFF_CODON_CHANGE=gaT/gaC;SNPEFF_EFFECT=SYNONYMOUS_CODING;SNPEFF_EXON_ID=5;SNPEFF_FUNCTIONAL_CLASS=SILENT;SNPEFF_GENE_BIOTYPE=protein_coding;SNPEFF_GENE_NAME=ADNP2;SNPEFF_IMPACT=LOW;SNPEFF_TRANSCRIPT_ID=ENSCAFT00000000008 1/1:0,151:151:99:5510,430,0

In the modified question, the data is like,

---------- Post updated at 01:01 PM ---------- Previous update was at 12:27 PM ----------

Anyways i have modified the code and it works now. But i have a new problem,

chr1	    901534	rs21932296	   T	G	34.77	0/1:3,2:5:63:63,0,64	GATKSAM	5	55.21	INTRON	MODIFIER				CTDP1	protein_coding	CODING	ENSCAFT00000000012	11

If we observe after MODIFIER i have a series of empty tabs. When i am piping this input to awk to perform s0me other action with the command,

awk 'BEGIN{OFS="\t"}{split ($7,TMP,":"); $7= TMP[1]}1'

it is replacing multiple empty tabs into a single tab and gives the output like below:

chr1	    901534	rs21932296	  T	G	34.77	 0/1	GATKSAM	5	55.21	INTRON	MODIFIER	CTDP1	protein_coding	CODING	ENSCAFT00000000012	11

I don't want the multiple tabs to be replaced by single tab. Could you help where im going wrong?