<p>Author here - this was a bug which was fixed in August:</p>

<p>See <a href="https://github.com/tseemann/prokka/issues/250" rel="noreferrer">the issue</a> in Github. </p>

<p>You will need to use the latest Github HEAD version until the next release.</p>

CC BY-SA 3.0
2017-12-18T03:25:03.440Z
2017-12-18T09:15:47.397Z
2017-12-18T09:15:47.397Z
Torst
<p>As Devon said, <code>read.delim</code> can deal with this perfectly well as long as the file is properly formatted (so missing fields still end with the field separator, like <code>\t\t</code>). For example, with this input:</p>

<pre><code>Field1  Field2  Field3
Row1field1  Row1field2  Row1field3
Row2field1      Row2field3
</code></pre>

<p>Or, to show the fields more clearly:</p>

<pre><code>Field1\tField2\tField3
Row1field1\tRow1field2\tRow1field3
Row2field1\t\tRow2field3
</code></pre>

<p>R can import:</p>

<pre><code>&gt; df&lt;-read.delim("file",header=TRUE, sep="\t")
&gt; df
      Field1     Field2     Field3
1 Row1field1 Row1field2 Row1field3
2 Row2field1            Row2field3
</code></pre>

<p>Alternatively, if you really want to add something to the missing columns, you can use:</p>

<pre><code><span class="math-container">$ awk -F"\t" '($</span>3==""){$3="none" }1;' file
locus_tag   ftype   gene    EC_number   product
BOFHCHGE_00001  CDS hypothetical    protein
BOFHCHGE_00002  CDS rdgB_1  3.6.1.9 dITP/XTP    pyrophosphatase
BOFHCHGE_00003  CDS hypothetical    protein
BOFHCHGE_00004  CDS hypothetical    protein
BOFHCHGE_00005  CDS hypothetical    protein
</code></pre>

<p>Or, to add <code>none</code> to any empty field, not just the 3rd one:</p>

<pre><code>awk -F"\t" '{for(i=1;i&lt;=NF;i++){if(<span class="math-container">$i==""){$</span>i="none"; }}}1;' file &gt; newfile
</code></pre>

CC BY-SA 3.0
2017-12-14T15:34:55.807Z
I fear that the .tsv file is mal-formatted then. `df<-read.delim("file",header=TRUE, sep="\t")` will misimport and EC column will end up having product names, gene having hypothetical protein, etc.
Biomagician
CC BY-SA 3.0
2017-12-14T15:39:40.493Z
@charlesdarwin if it is indeed malformatted, yes. However, I would be very surprised if that were the case. Using empty fields as described above is standard practice. You can check easily enough though. FInd one of the lines with a missing field (using `12` here as an example), and then run `awk 'NR==12' | od -c` and make sure you see the `\t\t` where the empty field is. If you don't see that, this becomes a text parsing problem. I suggest you post a new question with some example lines and we'll see what we can do.
terdon
CC BY-SA 3.0
2017-12-14T15:46:30.410Z
my 6th line has a missing EC number. My file is file.txt and my data frame is called df, what is the command I should run in the terminal with awk?
Biomagician
CC BY-SA 3.0
2017-12-15T10:45:57.440Z
@charlesdarwin (sorry, I just saw this for some reason) it depends. If there is a `\t\t` for the empty field, you can do `awk -F"\t" '($4==""){$4="none" }1;' file.txt > newfile.txt`. If that doesn't work, you might need to ask a new question, or come into the [site chatroom](https://chat.stackexchange.com/rooms/58858/bioinformatics) and ping me and I'll see if I can help.
terdon
CC BY-SA 3.0
2017-12-15T12:07:19.573Z
newfile.txt is a mess. The element 1,1 is now `BOFHCHGE_00001 CDS hypothetical protein none`. I've written to the developer. I don't think it should be that difficult. I am using the GFF file instead. It is well formatted and contains all the information, too. Thanks for your help.
Biomagician
CC BY-SA 3.0
2017-12-15T12:22:28.437Z
@charlesdarwin that's probably for the best. Sorry, but it's really hard to parse something without having the original file to be able to see exactly what's needed.
terdon
CC BY-SA 3.0
2017-12-15T15:17:15.570Z
very easy to reproduce, download the E. coli genome from here: `ftp://ftp.ensemblgenomes.org/pub/release-32/bacteria//fasta/bacteria_91_collection/escherichia_coli/dna/Escherichia_coli.HUSEC2011CHR1.dna.toplevel.fa.gz`, run prokka on it (conda install prokka) and you'll get a malformed .tsv file.
Biomagician
CC BY-SA 3.0
2017-12-15T15:21:05.413Z
@charlesdarwin OK. Sorry, but I don't use prokka and I don't want to install it for this. If you want to paste a few lines of the output file in chat or in your question, I can try to help.
terdon
CC BY-SA 3.0
2017-12-17T18:01:25.140Z
Here is a link to the offending file: http://cristian-riccio.ch/wp-content/uploads/2017/12/PROKKA_12142017.tsv
Biomagician
CC BY-SA 3.0
2017-12-18T11:29:12.600Z
@charlesdarwin oh wow. Yeah, that's definitely a bug, the fields are wrong and since different lines are missing different fields, I dont' see how you can change it unless you do so manually. That's really impressively bad design.
terdon
CC BY-SA 4.0
2017-12-14T15:12:32.487Z
2019-01-13T15:03:33.560Z
2019-01-13T15:03:33.560Z
terdon
<p>You don't really need to get around anything, your example can be loaded correctly with <code>read.delim()</code>. The rows with missing values are filled in with blanks. You may, however, prefer the <code>readr</code> package, which handles this a bit more elegantly (it'll tell you which values were missing and not do the annoying string->factor conversion).</p>

CC BY-SA 3.0
2017-12-14T15:01:27.637Z
Thanks, useful but it puts 'hypothetical protein' under the gene column when it actually belongs in the product column. The gene column should be empty for hypothetical proteins.
Biomagician
CC BY-SA 3.0
2017-12-14T15:02:44.870Z
Oh, I thought that was intentional. In that case prokka has a bug and you'll need to test for "" as the product and just assign accordingly.
Devon Ryan
CC BY-SA 3.0
2017-12-14T16:05:41.687Z
And I get 'Sec-independent protein translocase protein TatA' for EC number, so that's definitely wrong. I never see an empty element before a non-empty element.
Biomagician
CC BY-SA 3.0
2017-12-14T21:09:55.710Z
You can use the `stringsAsFactors = FALSE` to prevent converting strings to factors in `read.delim/read.csv...`
llrs
CC BY-SA 3.0
2017-12-14T21:12:02.697Z
@Llopis Quite true, but the readr package has that as the default :)
Devon Ryan
CC BY-SA 3.0
2017-12-14T14:44:28.380Z
2017-12-14T14:44:28.380Z
Devon Ryan
<p>all. I know this question was asked 2 years ago, but I wrote a script in R, with a little bash, to parse the tsv file in the old version of prokka. In the new version of prokka this problem is fixed.</p>
<p>First use bash to get the rows where ftype is CDS.</p>
<pre><code>grep &quot;CDS&quot; all_prokka_annotations.tsv &gt; all_prokka_annotations_CDS.tsv
</code></pre>
<p>Next we can parse the TSV with the CDS lines in R.</p>
<pre><code>prokka_annotations = readLines(&quot;all_prokka_annotations_CDS.tsv&quot;)
prokka_annotations_split = strsplit(prokka_annotations,split=&quot;\t&quot;)
# first get the protein product name. 
prokka_annotations_product = sapply(prokka_annotations_split, function(x) x[length(x)])
# now the I have all protein product names remove them
prokka_annotations_split = sapply(prokka_annotations_split, function(x) x[-length(x)])
#next get locus tag and ftype
locus_tag_cds = sapply(prokka_annotations_split, function(x) x[c(1,2)])
locus_tag_cds = t(locus_tag_cds)
# now remove locus tag and ftype
prokka_annotations_split = sapply(prokka_annotations_split, function(x) x[-c(1,2)])
# next get EC number
ec_number = lapply(prokka_annotations_split, function(x) x[grep(&quot;.&quot;,x,fixed=T)])
ec_number = as.character(ec_number)
ec_number[ec_number==&quot;character(0)&quot;] &lt;- NA
## now get the gene names if available. to do this remove the EC numbers. whats left are gene names
prokka_gene_names = lapply(prokka_annotations_split, function(x) x[!grepl(&quot;.&quot;,x,fixed=T)])
prokka_gene_names = as.character(prokka_gene_names)
prokka_gene_names[prokka_gene_names==&quot;character(0)&quot;] &lt;- NA
prokka_parsed_tsv_correct = cbind(locus_tag_cds,product=prokka_annotations_product,gene=prokka_gene_names,EC=ec_number)

<span class="math-container">```</span>
</code></pre>

CC BY-SA 4.0
2020-10-12T21:29:34.070Z
2020-10-12T21:29:34.070Z
Samuel Zimmerman
<p>Since the missing values don't have field separators, the easiest option is to rearrange the columns so that the ones with missing values are at the end. They will then be the ones that are filled with missing values when you import them into R. You can use use <code>fill=TRUE</code> with read.table() or use <a href="https://cran.r-project.org/web/packages/readr/vignettes/readr.html" rel="nofollow noreferrer">readr</a>, which will complain about missing columns, but fill them in with missing values automatically:</p>

<pre><code>awk -F'\t' -v OFS='\t' '{print($1,$2,$5,$3,$4)}' file.txt |perl -pe 's/\t+/\t/g' &gt;newfile.txt 
</code></pre>

<p>With readr:</p>

<pre><code>read_tsv("newfile.txt")

# A tibble: 5 x 5
       locus_tag ftype                  product   gene EC_number
           &lt;chr&gt; &lt;chr&gt;                    &lt;chr&gt;  &lt;chr&gt;     &lt;chr&gt;
1 BOFHCHGE_00001   CDS     hypothetical protein   &lt;NA&gt;      &lt;NA&gt;
2 BOFHCHGE_00002   CDS dITP/XTP pyrophosphatase rdgB_1   3.6.1.9
3 BOFHCHGE_00003   CDS     hypothetical protein   &lt;NA&gt;      &lt;NA&gt;
4 BOFHCHGE_00004   CDS     hypothetical protein   &lt;NA&gt;      &lt;NA&gt;
5 BOFHCHGE_00005   CDS     hypothetical protein   &lt;NA&gt;      &lt;NA&gt;
</code></pre>

<p>The perl regex is necessary because awk adds tabs after the missing values, which defeats the point of reordering the columns</p>

CC BY-SA 3.0
2017-12-17T18:12:43.887Z
I did as you said but the data frame is still wrong. On line 6, gene and product are interchanged.
Biomagician
CC BY-SA 3.0
2017-12-18T19:49:51.380Z
Since you only provided 5 lines in the example, I can't comment on the results for the 6th line
heathobrien
CC BY-SA 3.0
2017-12-15T15:25:39.170Z
2017-12-15T15:25:39.170Z
heathobrien
<p>I am using prokka to annotate a bacterial genome:</p>

<p><code>prokka ecoli.fa</code></p>

<p>Prokka is outputting a tab-separated file (called PROKKA_12142017.tsv) with differing numbers of elements in each row:</p>

<pre><code>locus_tag    ftype    gene    EC_number    product
BOFHCHGE_00001    CDS    hypothetical protein
BOFHCHGE_00002    CDS    rdgB_1    3.6.1.9    dITP/XTP pyrophosphatase
BOFHCHGE_00003    CDS    hypothetical protein
BOFHCHGE_00004    CDS    hypothetical protein
BOFHCHGE_00005    CDS    hypothetical protein
</code></pre>

<p>Does anybody know how I can get round this to import into a dataframe in R?</p>

<p>Is there a way to ask prokka to add something in the gene column?</p>

CC BY-SA 3.0
2017-12-14T15:14:07.143Z
Please make sure to send a bug report to the prokka authors, since this should be fixed on their end.
Devon Ryan
<r><sequence-annotation>
Import a tab-separated file with differing numbers of elements in each row; prokka output
# Import a tab-separated file with differing numbers of elements in each row; prokka output

(Asked by: username_0 on 2017-12-14)

I am using prokka to annotate a bacterial genome:
prokka ecoli.fa
Prokka is outputting a tab-separated file (called PROKKA_12142017.tsv) with differing numbers of elements in each row:
locus_tag    ftype    gene    EC_number    product
BOFHCHGE_00001    CDS    hypothetical protein
BOFHCHGE_00002    CDS    rdgB_1    3.6.1.9    dITP/XTP pyrophosphatase
BOFHCHGE_00003    CDS    hypothetical protein
BOFHCHGE_00004    CDS    hypothetical protein
BOFHCHGE_00005    CDS    hypothetical protein

Does anybody know how I can get round this to import into a dataframe in R?
Is there a way to ask prokka to add something in the gene column?

**Tags:** r, sequence-annotation
> **Comments:**
> **username_1**: Please make sure to send a bug report to the prokka authors, since this should be fixed on their end.  

---

## 5 answers

### Answer by username_2 on 2017-12-18. Score: 5 | Accepted answer

Author here - this was a bug which was fixed in August:

See [the issue](https://github.com/tseemann/prokka/issues/250) in Github.

You will need to use the latest Github HEAD version until the next release.


---

### Answer by username_1 on 2017-12-14. Score: 6

You don't really need to get around anything, your example can be loaded correctly with `read.delim()`. The rows with missing values are filled in with blanks. You may, however, prefer the `readr` package, which handles this a bit more elegantly (it'll tell you which values were missing and not do the annoying string->factor conversion).

> **Comments:**
> **username_0**: Thanks, useful but it puts 'hypothetical protein' under the gene column when it actually belongs in the product column. The gene column should be empty for hypothetical proteins.  
> **username_1**: Oh, I thought that was intentional. In that case prokka has a bug and you'll need to test for "" as the product and just assign accordingly.  
> **username_0**: And I get 'Sec-independent protein translocase protein TatA' for EC number, so that's definitely wrong. I never see an empty element before a non-empty element.  
> **username_4**: You can use the `stringsAsFactors = FALSE` to prevent converting strings to factors in `read.delim/read.csv...`  
> **username_1**: @Llopis Quite true, but the readr package has that as the default :)  

---

### Answer by username_3 on 2017-12-14. Score: 3

As Devon said, read.delim can deal with this perfectly well as long as the file is properly formatted (so missing fields still end with the field separator, like \t\t). For example, with this input:
Field1  Field2  Field3
Row1field1  Row1field2  Row1field3
Row2field1      Row2field3

Or, to show the fields more clearly:
Field1\tField2\tField3
Row1field1\tRow1field2\tRow1field3
Row2field1\t\tRow2field3

R can import:
> df<-read.delim("file",header=TRUE, sep="\t")
> df
      Field1     Field2     Field3
1 Row1field1 Row1field2 Row1field3
2 Row2field1            Row2field3

Alternatively, if you really want to add something to the missing columns, you can use:
$ awk -F"\t" '($3==""){$3="none" }1;' file
locus_tag   ftype   gene    EC_number   product
BOFHCHGE_00001  CDS hypothetical    protein
BOFHCHGE_00002  CDS rdgB_1  3.6.1.9 dITP/XTP    pyrophosphatase
BOFHCHGE_00003  CDS hypothetical    protein
BOFHCHGE_00004  CDS hypothetical    protein
BOFHCHGE_00005  CDS hypothetical    protein

Or, to add none to any empty field, not just the 3rd one:
awk -F"\t" '{for(i=1;i<=NF;i++){if($i==""){$i="none"; }}}1;' file > newfile

> **Comments:**
> **username_0**: I fear that the .tsv file is mal-formatted then. `df<-read.delim("file",header=TRUE, sep="\t")` will misimport and EC column will end up having product names, gene having hypothetical protein, etc.  
> **username_3**: @charlesdarwin if it is indeed malformatted, yes. However, I would be very surprised if that were the case. Using empty fields as described above is standard practice. You can check easily enough though. FInd one of the lines with a missing field (using `12` here as an example), and then run `awk 'NR==12' | od -c` and make sure you see the `\t\t` where the empty field is. If you don't see that, this becomes a text parsing problem. I suggest you post a new question with some example lines and we'll see what we can do.  
> **username_0**: my 6th line has a missing EC number. My file is file.txt and my data frame is called df, what is the command I should run in the terminal with awk?  
> **username_3**: @charlesdarwin (sorry, I just saw this for some reason) it depends. If there is a `\t\t` for the empty field, you can do `awk -F"\t" '($4==""){$4="none" }1;' file.txt > newfile.txt`. If that doesn't work, you might need to ask a new question, or come into the [site chatroom](https://chat.stackexchange.com/rooms/58858/bioinformatics) and ping me and I'll see if I can help.  
> **username_0**: newfile.txt is a mess. The element 1,1 is now `BOFHCHGE_00001 CDS hypothetical protein none`. I've written to the developer. I don't think it should be that difficult. I am using the GFF file instead. It is well formatted and contains all the information, too. Thanks for your help.  
> **username_3**: @charlesdarwin that's probably for the best. Sorry, but it's really hard to parse something without having the original file to be able to see exactly what's needed.  
> **username_0**: very easy to reproduce, download the E. coli genome from here: `ftp://ftp.ensemblgenomes.org/pub/release-32/bacteria//fasta/bacteria_91_collection/escherichia_coli/dna/Escherichia_coli.HUSEC2011CHR1.dna.toplevel.fa.gz`, run prokka on it (conda install prokka) and you'll get a malformed .tsv file.  
> **username_3**: @charlesdarwin OK. Sorry, but I don't use prokka and I don't want to install it for this. If you want to paste a few lines of the output file in chat or in your question, I can try to help.  
> **username_0**: Here is a link to the offending file: http://cristian-riccio.ch/wp-content/uploads/2017/12/PROKKA_12142017.tsv  
> **username_3**: @charlesdarwin oh wow. Yeah, that's definitely a bug, the fields are wrong and since different lines are missing different fields, I dont' see how you can change it unless you do so manually. That's really impressively bad design.  

---

### Answer by username_5 on 2020-10-12. Score: 1

all. I know this question was asked 2 years ago, but I wrote a script in R, with a little bash, to parse the tsv file in the old version of prokka. In the new version of prokka this problem is fixed.
First use bash to get the rows where ftype is CDS.
grep "CDS" all_prokka_annotations.tsv > all_prokka_annotations_CDS.tsv

Next we can parse the TSV with the CDS lines in R.
prokka_annotations = readLines("all_prokka_annotations_CDS.tsv")
prokka_annotations_split = strsplit(prokka_annotations,split="\t")
# first get the protein product name. 
prokka_annotations_product = sapply(prokka_annotations_split, function(x) x[length(x)])
# now the I have all protein product names remove them
prokka_annotations_split = sapply(prokka_annotations_split, function(x) x[-length(x)])
#next get locus tag and ftype
locus_tag_cds = sapply(prokka_annotations_split, function(x) x[c(1,2)])
locus_tag_cds = t(locus_tag_cds)
# now remove locus tag and ftype
prokka_annotations_split = sapply(prokka_annotations_split, function(x) x[-c(1,2)])
# next get EC number
ec_number = lapply(prokka_annotations_split, function(x) x[grep(".",x,fixed=T)])
ec_number = as.character(ec_number)
ec_number[ec_number=="character(0)"] <- NA
## now get the gene names if available. to do this remove the EC numbers. whats left are gene names
prokka_gene_names = lapply(prokka_annotations_split, function(x) x[!grepl(".",x,fixed=T)])
prokka_gene_names = as.character(prokka_gene_names)
prokka_gene_names[prokka_gene_names=="character(0)"] <- NA
prokka_parsed_tsv_correct = cbind(locus_tag_cds,product=prokka_annotations_product,gene=prokka_gene_names,EC=ec_number)

```


---

### Answer by username_6 on 2017-12-15. Score: 1

Since the missing values don't have field separators, the easiest option is to rearrange the columns so that the ones with missing values are at the end. They will then be the ones that are filled with missing values when you import them into R. You can use use fill=TRUE with read.table() or use readr, which will complain about missing columns, but fill them in with missing values automatically:
awk -F'\t' -v OFS='\t' '{print($1,$2,$5,$3,$4)}' file.txt |perl -pe 's/\t+/\t/g' >newfile.txt 

With readr:
read_tsv("newfile.txt")

# A tibble: 5 x 5
       locus_tag ftype                  product   gene EC_number
                                       
1 BOFHCHGE_00001   CDS     hypothetical protein         
2 BOFHCHGE_00002   CDS dITP/XTP pyrophosphatase rdgB_1   3.6.1.9
3 BOFHCHGE_00003   CDS     hypothetical protein         
4 BOFHCHGE_00004   CDS     hypothetical protein         
5 BOFHCHGE_00005   CDS     hypothetical protein         

The perl regex is necessary because awk adds tabs after the missing values, which defeats the point of reordering the columns

> **Comments:**
> **username_0**: I did as you said but the data frame is still wrong. On line 6, gene and product are interchanged.  
> **username_6**: Since you only provided 5 lines in the example, I can't comment on the results for the 6th line  

---

