text: user1075: 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?
  
  user581: 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
             <chr> <chr>                    <chr>  <chr>     <chr>
  1 BOFHCHGE_00001   CDS     hypothetical protein   <NA>      <NA>
  2 BOFHCHGE_00002   CDS dITP/XTP pyrophosphatase rdgB_1   3.6.1.9
  3 BOFHCHGE_00003   CDS     hypothetical protein   <NA>      <NA>
  4 BOFHCHGE_00004   CDS     hypothetical protein   <NA>      <NA>
  5 BOFHCHGE_00005   CDS     hypothetical protein   <NA>      <NA>
  
  The perl regex is necessary because awk adds tabs after the missing values, which defeats the point of reordering the columns
  
  user9665: 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)
  
  ```
  
  
  user298: 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
  
  
  user550: Author here - this was a bug which was fixed in August:
  See the issue in Github. 
  You will need to use the latest Github HEAD version until the next release.
  
  user77: 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).
  
original_text: 1075: <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>
   581: <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>
   9665: <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>
   298: <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>
   550: <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>
   77: <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>
  