<p>Here's one way using <a href="/questions/tagged/awk" class="post-tag" title="show questions tagged &#39;awk&#39;" aria-label="show questions tagged &#39;awk&#39;" rel="tag" aria-labelledby="tag-awk-tooltip-container">awk</a>, that I think fulfills the requirements. It adds each of the column names (on the first line) to an array to make accessing each of the fields a bit easier. This approach isn't strictly necessary, but it does make for a more readable solution in my opinion. With the following in a file called <code>tsv2gff3.awk</code>:</p>
<pre><code>BEGIN {
    FS=OFS=&quot;\t&quot;
    c=1
}

NR==1 &amp;&amp; sub(/^#/, &quot;&quot;) {
    for(i=1; i&lt;=NF; i++) {
        a[$i]=i
    }

    print &quot;##gff-version 3&quot;
    next
}

{
    sub(/\.fna<span class="math-container">$/, "", $</span>a[&quot;FILE&quot;])
    gsub(/[\(\)\[\]]/, &quot;&quot;, $a[&quot;PRODUCT&quot;])

    id = &quot;ID=&quot; <span class="math-container">$a["FILE"] "_" c++
    product = "product=" $</span>a[&quot;PRODUCT&quot;]

    print \
        <span class="math-container">$a["FILE"],
        $</span>a[&quot;DATABASE&quot;],
        &quot;CDS&quot;,
        <span class="math-container">$a["START"],
        $</span>a[&quot;END&quot;],
        &quot;.&quot;,
        &quot;+&quot;,
        &quot;0&quot;,
        id &quot;;&quot; product
}
</code></pre>
<p>And the following in <code>file.tsv</code>:</p>
<pre><code>#FILE   SEQUENCE    START   END GENE    COVERAGE    COVERAGE_MAP    GAPS    %COVERAGE   %IDENTITY   DATABASE    ACCESSION   PRODUCT
UBird_Cyou_D3.fna   BJCZ01000001.1  1866608 1867417 cdtB    1-810/810   =============== 0/0 100 90  vfdb    CAD48850    (cdtB) cytolethal distending toxin B [CDT (VF0185)] [Escherichia coli O157:H str. 493/89]
UBird_Cyou_D3.fna   BJCZ01000001.1  1867414 1868190 cdtA    1-777/777   =============== 0/0 100 90.61   vfdb    CAD48849    (cdtA) cytolethal distending toxin A [CDT (VF0185)] [Escherichia coli O157:H str. 493/89]
UBird_Cyou_D3.fna   BJCZ01000001.1  2245186 2246238 ompA    1-1041/1041 ========/====== 1/12    100 94.11   vfdb    AAF37887    (ompA) outer membrane protein A [OmpA (VF0236)] [Escherichia coli O18:K1:H7 str. RS218]
</code></pre>
<p>Run using <code>awk -f tsv2gff3.awk file.tsv</code>, results:</p>
<pre><code>##gff-version 3
UBird_Cyou_D3   vfdb    CDS 1866608 1867417 .   +   0   ID=UBird_Cyou_D3_1;product=cdtB cytolethal distending toxin B CDT VF0185 Escherichia coli O157:H str. 493/89
UBird_Cyou_D3   vfdb    CDS 1867414 1868190 .   +   0   ID=UBird_Cyou_D3_2;product=cdtA cytolethal distending toxin A CDT VF0185 Escherichia coli O157:H str. 493/89
UBird_Cyou_D3   vfdb    CDS 2245186 2246238 .   +   0   ID=UBird_Cyou_D3_3;product=ompA outer membrane protein A OmpA VF0236 Escherichia coli O18:K1:H7 str. RS218
</code></pre>

CC BY-SA 4.0
2023-03-22T12:00:09.260Z
Thank you Steve (https://bioinformatics.stackexchange.com/users/10775/steve) this does work.
com
CC BY-SA 4.0
2023-03-21T00:31:32.203Z
2023-03-21T00:31:32.203Z
Steve
<p>I have a tsv file generated from abricate (<a href="https://github.com/tseemann/abricate" rel="nofollow noreferrer">https://github.com/tseemann/abricate</a>). I need to convert them to gff3 format with certain columns retained, certain columns reordered, while other columns deleted.</p>
<p>We are trying to use these gff3 files for downstream applications and for piping into other applications. However, we could not solve it.</p>
<p>Below are examples of my tsv files, what possibly we may need to do, and desired output files in gff3 format.</p>
<p><strong>Any help will be much appreciated.</strong></p>
<h2>Input tsv file:</h2>
<p>FILE SEQUENCE START END GENE COVERAGE COVERAGE_MAP GAPS %COVERAGE %IDENTITY DATABASE ACCESSION PRODUCT</p>
<p>UBird_Cyou_D3.fna BJCZ01000001.1 1866608 1867417 cdtB 1-810/810 =============== 0/0 100 90 vfdb CAD48850 (cdtB) cytolethal distending toxin B [CDT (VF0185)] [Escherichia coli O157:H str. 493/89] UBird_Cyou_D3.fna BJCZ01000001.1 1867414 1868190 cdtA 1-777/777 =============== 0/0 100 90.61 vfdb CAD48849 (cdtA) cytolethal distending toxin A [CDT (VF0185)] [Escherichia coli O157:H str. 493/89] UBird_Cyou_D3.fna BJCZ01000001.1 2245186 2246238 ompA 1-1041/1041 ========/====== 1/12 100 94.11 vfdb AAF37887 (ompA) outer membrane protein A [OmpA (VF0236)] [Escherichia coli O18:K1:H7 str. RS218]</p>
<h2>What we may need to do (there may be other ways too, I am not sure):</h2>
<pre><code>Row 1 (always starts with &quot;#&quot;) - Need to replace with the string &quot;##gff-version 3&quot;
Col 1 - get rid of &quot;.fna&quot; and retain other data
Insert new Col - print the string from Col 11 for all rows
Col 2 - get rid of entire column
Insert new Col - print &quot;CDS&quot; for all rows
Col 3 - retain data
Col 4 - retain data
Insert new Col and print &quot;.&quot; for all rows
Insert new Col and print &quot;+&quot; for all rows
Insert new Col and print &quot;0&quot; for all rows
Col 5 to Col 10 - get rid of all these columns and data
Col 11 - delete column
Col 13 - retain data except &quot;(&quot;, &quot;)&quot;, &quot;[&quot;, &quot;]&quot;
Add new Col - Starting with &quot;ID=&quot; followed by the string taken from Col 1 and a underscore added (for the example data &quot;UBird_Cyou_D3_&quot;) alongwith numerals starting from 1 and incrementing by 1. This column data needs to be appended by &quot;product=&quot; followed by data from the corresponding row of the modified Col 13. The separator between ID string and product string should be &quot;;&quot;. After completion this column should be like &quot;ID=UBird_Cyou_D3_1;product=cdtB cytolethal distending toxin B CDT VF0185 Escherichia coli O157:H str. 493/89&quot;
</code></pre>
<h2>Desired final output (*.gff3) considering the example data:</h2>
<p>##gff-version 3</p>
<p>UBird_Cyou_D3 vfdb CDS 187 756 . + 0 ID=UBird_Cyou_D3_1;product=cdtB cytolethal distending toxin B CDT VF0185 Escherichia coli O157:H str. 493/89</p>

CC BY-SA 4.0
2023-03-20T17:20:56.167Z
Hi and welcome to the site! Please [edit] your post and ensure only the actual data are formated as code. We need this in order to be sure we have your data correctly. The rest, your proposed algorithm should be in normal formatting, but your input file and desired gff need to be code formatted.
terdon
CC BY-SA 4.0
2023-03-21T00:33:35.723Z
Is your expected output incomplete? Do you expect four lines of output (including the header)? Are your START and END positions correct?
Steve
CC BY-SA 4.0
2023-03-22T14:03:06.917Z
The awk code is working. However, we need to add little more to take this to maturity. Posted here (https://bioinformatics.stackexchange.com/questions/20761/improving-conversion-of-abricate-tsv-file-to-gff3-file). Any help?
com
<gff3><tsv>
Convert Abricate output tsv file to gff3 format
# Convert Abricate output tsv file to gff3 format

(Asked by: username_0 on 2023-03-20)

I have a tsv file generated from abricate (https://github.com/tseemann/abricate). I need to convert them to gff3 format with certain columns retained, certain columns reordered, while other columns deleted.
We are trying to use these gff3 files for downstream applications and for piping into other applications. However, we could not solve it.
Below are examples of my tsv files, what possibly we may need to do, and desired output files in gff3 format.
Any help will be much appreciated.
Input tsv file:
FILE SEQUENCE START END GENE COVERAGE COVERAGE_MAP GAPS %COVERAGE %IDENTITY DATABASE ACCESSION PRODUCT
UBird_Cyou_D3.fna BJCZ01000001.1 1866608 1867417 cdtB 1-810/810 =============== 0/0 100 90 vfdb CAD48850 (cdtB) cytolethal distending toxin B [CDT (VF0185)] [Escherichia coli O157:H str. 493/89] UBird_Cyou_D3.fna BJCZ01000001.1 1867414 1868190 cdtA 1-777/777 =============== 0/0 100 90.61 vfdb CAD48849 (cdtA) cytolethal distending toxin A [CDT (VF0185)] [Escherichia coli O157:H str. 493/89] UBird_Cyou_D3.fna BJCZ01000001.1 2245186 2246238 ompA 1-1041/1041 ========/====== 1/12 100 94.11 vfdb AAF37887 (ompA) outer membrane protein A [OmpA (VF0236)] [Escherichia coli O18:K1:H7 str. RS218]
What we may need to do (there may be other ways too, I am not sure):
Row 1 (always starts with "#") - Need to replace with the string "##gff-version 3"
Col 1 - get rid of ".fna" and retain other data
Insert new Col - print the string from Col 11 for all rows
Col 2 - get rid of entire column
Insert new Col - print "CDS" for all rows
Col 3 - retain data
Col 4 - retain data
Insert new Col and print "." for all rows
Insert new Col and print "+" for all rows
Insert new Col and print "0" for all rows
Col 5 to Col 10 - get rid of all these columns and data
Col 11 - delete column
Col 13 - retain data except "(", ")", "[", "]"
Add new Col - Starting with "ID=" followed by the string taken from Col 1 and a underscore added (for the example data "UBird_Cyou_D3_") alongwith numerals starting from 1 and incrementing by 1. This column data needs to be appended by "product=" followed by data from the corresponding row of the modified Col 13. The separator between ID string and product string should be ";". After completion this column should be like "ID=UBird_Cyou_D3_1;product=cdtB cytolethal distending toxin B CDT VF0185 Escherichia coli O157:H str. 493/89"

Desired final output (*.gff3) considering the example data:
##gff-version 3
UBird_Cyou_D3 vfdb CDS 187 756 . + 0 ID=UBird_Cyou_D3_1;product=cdtB cytolethal distending toxin B CDT VF0185 Escherichia coli O157:H str. 493/89

**Tags:** gff3, tsv
> **Comments:**
> **username_1**: Hi and welcome to the site! Please [edit] your post and ensure only the actual data are formated as code. We need this in order to be sure we have your data correctly. The rest, your proposed algorithm should be in normal formatting, but your input file and desired gff need to be code formatted.  
> **username_2**: Is your expected output incomplete? Do you expect four lines of output (including the header)? Are your START and END positions correct?  
> **username_0**: The awk code is working. However, we need to add little more to take this to maturity. Posted here (https://bioinformatics.stackexchange.com/questions/20761/improving-conversion-of-abricate-tsv-file-to-gff3-file). Any help?  

---

## 1 answers

### Answer by username_2 on 2023-03-21. Score: 2

Here's one way using awk, that I think fulfills the requirements. It adds each of the column names (on the first line) to an array to make accessing each of the fields a bit easier. This approach isn't strictly necessary, but it does make for a more readable solution in my opinion. With the following in a file called tsv2gff3.awk:
BEGIN {
    FS=OFS="\t"
    c=1
}

NR==1 && sub(/^#/, "") {
    for(i=1; i<=NF; i++) {
        a[$i]=i
    }

    print "##gff-version 3"
    next
}

{
    sub(/\.fna$/, "", $a["FILE"])
    gsub(/[\(\)\[\]]/, "", $a["PRODUCT"])

    id = "ID=" $a["FILE"] "_" c++
    product = "product=" $a["PRODUCT"]

    print \
        $a["FILE"],
        $a["DATABASE"],
        "CDS",
        $a["START"],
        $a["END"],
        ".",
        "+",
        "0",
        id ";" product
}

And the following in file.tsv:
#FILE   SEQUENCE    START   END GENE    COVERAGE    COVERAGE_MAP    GAPS    %COVERAGE   %IDENTITY   DATABASE    ACCESSION   PRODUCT
UBird_Cyou_D3.fna   BJCZ01000001.1  1866608 1867417 cdtB    1-810/810   =============== 0/0 100 90  vfdb    CAD48850    (cdtB) cytolethal distending toxin B [CDT (VF0185)] [Escherichia coli O157:H str. 493/89]
UBird_Cyou_D3.fna   BJCZ01000001.1  1867414 1868190 cdtA    1-777/777   =============== 0/0 100 90.61   vfdb    CAD48849    (cdtA) cytolethal distending toxin A [CDT (VF0185)] [Escherichia coli O157:H str. 493/89]
UBird_Cyou_D3.fna   BJCZ01000001.1  2245186 2246238 ompA    1-1041/1041 ========/====== 1/12    100 94.11   vfdb    AAF37887    (ompA) outer membrane protein A [OmpA (VF0236)] [Escherichia coli O18:K1:H7 str. RS218]

Run using awk -f tsv2gff3.awk file.tsv, results:
##gff-version 3
UBird_Cyou_D3   vfdb    CDS 1866608 1867417 .   +   0   ID=UBird_Cyou_D3_1;product=cdtB cytolethal distending toxin B CDT VF0185 Escherichia coli O157:H str. 493/89
UBird_Cyou_D3   vfdb    CDS 1867414 1868190 .   +   0   ID=UBird_Cyou_D3_2;product=cdtA cytolethal distending toxin A CDT VF0185 Escherichia coli O157:H str. 493/89
UBird_Cyou_D3   vfdb    CDS 2245186 2246238 .   +   0   ID=UBird_Cyou_D3_3;product=ompA outer membrane protein A OmpA VF0236 Escherichia coli O18:K1:H7 str. RS218

> **Comments:**
> **username_0**: Thank you Steve (https://bioinformatics.stackexchange.com/users/10775/steve) this does work.  

---

