# Split column using awk in a text file

**URL:** <https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069>\
**Category:** Shell Programming and Scripting\
**Created:** [May 23, 2013, 4:08am UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069 "2013-05-23T04:08:41Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![mehar](https://community.unix.com/letter_avatar/mehar/32/6_22734026db12803ebb3e059622ac3534.png) [@mehar](https://community.unix.com/u/mehar)\
**Post date:** [May 23, 2013, 4:08am UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/1 "2013-05-23T04:08:41Z")

</div>

```nohighlight
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=.

```nohighlight
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?

---

<div class="post-metadata">

**Author:** ![pamu](https://community.unix.com/letter_avatar/pamu/32/6_22734026db12803ebb3e059622ac3534.png) [@pamu](https://community.unix.com/u/pamu)\
**Post date:** [May 23, 2013, 4:19am UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/2 "2013-05-23T04:19:56Z")

</div>

Try

```nohighlight
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

```

---

<div class="post-metadata">

**Author:** ![mehar](https://community.unix.com/letter_avatar/mehar/32/6_22734026db12803ebb3e059622ac3534.png) [@mehar](https://community.unix.com/u/mehar)\
**Post date:** [May 23, 2013, 4:28am UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/3 "2013-05-23T04:28:55Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![RudiC](https://community.unix.com/letter_avatar/rudic/32/6_22734026db12803ebb3e059622ac3534.png) [@RudiC](https://community.unix.com/u/RudiC)\
**Post date:** [May 23, 2013, 4:34am UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/4 "2013-05-23T04:34:06Z")

</div>

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

```nohighlight
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

```

---

<div class="post-metadata">

**Author:** ![mehar](https://community.unix.com/letter_avatar/mehar/32/6_22734026db12803ebb3e059622ac3534.png) [@mehar](https://community.unix.com/u/mehar)\
**Post date:** [May 23, 2013, 4:38am UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/5 "2013-05-23T04:38:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![RudiC](https://community.unix.com/letter_avatar/rudic/32/6_22734026db12803ebb3e059622ac3534.png) [@RudiC](https://community.unix.com/u/RudiC)\
**Post date:** [May 23, 2013, 4:42am UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/6 "2013-05-23T04:42:46Z")

</div>

```nohighlight
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

```

---

<div class="post-metadata">

**Author:** ![mehar](https://community.unix.com/letter_avatar/mehar/32/6_22734026db12803ebb3e059622ac3534.png) [@mehar](https://community.unix.com/u/mehar)\
**Post date:** [May 23, 2013, 6:26am UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/7 "2013-05-23T06:26:02Z")

</div>

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

```nohighlight
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.

---

<div class="post-metadata">

**Author:** ![Jotne](https://community.unix.com/user_avatar/community.unix.com/jotne/32/2148_2.png) [@Jotne](https://community.unix.com/u/Jotne)\
**Post date:** [May 23, 2013, 6:44am UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/8 "2013-05-23T06:44:25Z")

</div>

```nohighlight
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"

```

---

<div class="post-metadata">

**Author:** ![RudiC](https://community.unix.com/letter_avatar/rudic/32/6_22734026db12803ebb3e059622ac3534.png) [@RudiC](https://community.unix.com/u/RudiC)\
**Post date:** [May 23, 2013, 1:08pm UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/9 "2013-05-23T13:08:05Z")

</div>

> [@mehar](#):
>
> . . .  
> Hi,
> 
> The original question is slightly modified with slightly different input in the 8th column. Could you help? Thanks in advance.

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

BTW - why do you need a one liner?

---

<div class="post-metadata">

**Author:** ![mehar](https://community.unix.com/letter_avatar/mehar/32/6_22734026db12803ebb3e059622ac3534.png) [@mehar](https://community.unix.com/u/mehar)\
**Post date:** [May 23, 2013, 2:01pm UTC](https://community.unix.com/t/split-column-using-awk-in-a-text-file/329069/10 "2013-05-23T14:01:35Z")

</div>

In the initial question, 8th column is of type

```plaintext
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,

> [@](#):
>
> 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

---------- 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,

```plaintext
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,

```plaintext
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:

```plaintext
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?