Manipulating big TSV files in the Unix terminal

Introduction

Bioinformatics is full of plain text, machine readable file formats. One of my favourites is the "tab separated values" format (TSV). It's like the popular "comma separated values" format (CSV) but uses a tab instead of a comma. The main advantage of TSV over CSV is that you don't need to escape or quote your values when they have a comma in them, and many Unix tools for manipulating columnar data default to using tab as the field separator.

Some TSV files are huge - not just in rows, but also in columns. An important new TSV file for those who work in comparative genomics is the new NCBI assembly_summary.txt file. Here is the first 3 lines from the archaea summary file:

% head -n 3 assembly_summary.txt

# See ftp://ftp.ncbi.nlm.nih.gov/genomes/README_assembly_summary.txt for a description of the columns in this file.
# assembly_accession bioproject biosample wgs_master refseq_category taxid species_taxid organism_name infraspecific_name isolate version_status assembly_level release_type genome_rep seq_rel_date asm_name submitter gbrs_paired_asm paired_asm_comp ftp_path excluded_from_refseq
GCF_000302455.1 PRJNA224116 SAMN02471940 AMPO00000000.1 representative genome 1204725 2162 Methanobacterium formicicum DSM 3637 strain=DSM 3637 latest Contig Major Full 2012/10/02 ASM30245v1 Deparment of Genetics, University of Seville, Spain GCA_000302455.1 identical ftp://ftp.ncbi.nlm.nih.gov/genomes/all/GCF_000302455.1_ASM30245v1

It is not very clear what is what. At this point some people would load this into Excel, R or Pandas, and those are perfectly fine. But if you need to write a portable pipeline script or are on an unfamiliar server you will want to stick with common Unix tools.

The first problem is that the first line is not the column headers, but a pointer to some documentation. Let's get rid of the first line:

% tail -n +2 assembly_summary.txt | head -n 3

# assembly_accession bioproject biosample wgs_master refseq_category taxid species_taxid organism_name infraspecific_name isolate version_status assembly_level release_type genome_rep seq_rel_date asm_name submitter gbrs_paired_asm paired_asm_comp ftp_path excluded_from_refseq
GCF_000302455.1 PRJNA224116 SAMN02471940 AMPO00000000.1 representative genome 1204725 2162 Methanobacterium formicicum DSM 3637 strain=DSM 3637 latest Contig Major Full 2012/10/02 ASM30245v1 Deparment of Genetics, University of Seville, Spain GCA_000302455.1 identical ftp://ftp.ncbi.nlm.nih.gov/genomes/all/GCF_000302455.1_ASM30245v1
GCF_000762265.1 PRJNA224116 SAMN03085433 representative genome 2162 2162 Methanobacterium formicicum strain=BRM9 latest Complete Genome Major Full 2014/10/02 ASM76226v1 PGgRc GCA_000762265.1 identical ftp://ftp.ncbi.nlm.nih.gov/genomes/all/GCF_000762265.1_ASM76226v1

Viewing

It's still confusing because the rows are wider than the terminal and are wrapping. We can turn wrapping off in the less file viewer woth the -S option, and even use the left and right cursor keys to pan left and right!

% tail -n +2 assembly_summary.txt | less -S

# assembly_accession bioproject biosample wgs_master refseq_category t
GCF_000302455.1 PRJNA224116 SAMN02471940 AMPO00000000.1 representative
GCF_000762265.1 PRJNA224116 SAMN03085433 representative genome 2162 2
...

The horizontal scrolling within the terminal is very useful, but the columns still don't line up. Of course, there is already a Unix tool called column to help! This will examine all the data and convert each tab into a different number of spaces to make everything line up like a spreadsheet.

% tail -n +2 assembly_summary.txt | column -t | less -S

# assembly_accession bioproject biosample
GCF_000302455.1 PRJNA224116 SAMN02471940 AMPO00000000.1
GCF_000762265.1 PRJNA224116 SAMN03085433 representative
...

Manipulating

Okay, so this is very useful for looking at wide TSV files, but what if I want to select particular columns with cut and sort? How do I know which column to cut? Well, we can use some standard Unix tools to do this magic:

% tail -n +2 assembly_summary.txt | head -n 1 | tr "\t" "\n" | nl

     1  # assembly_accession
     2  bioproject
     3  biosample
     4  wgs_master
     5  refseq_category
     6  taxid
     7  species_taxid
     8  organism_name
     9  infraspecific_name
    10  isolate
    11  version_status
    12  assembly_level
    13  release_type
    14  genome_rep
    15  seq_rel_date
    16  asm_name
    17  submitter
    18  gbrs_paired_asm
    19  paired_asm_comp
    20  ftp_path
    21  excluded_from_refseq

This is how it works:

1. tail -n +2 skips over the first junk line
2. head -n 1 keeps the first line only (which is the TSV header line)
3. tr converts the tab characters to newline characters to turn columns into rows
4. nl is a little known Unix command which adds line numbers

if you have zero-based columns, you can use nl -v 0 instead - type man nl for more details.

You can then cut and sort with ease! For example, this one-liner lists the top 10 Archaeal genome assemblies available in Genbank:

% tail -n +3 assembly_summary.txt | cut -f8 | sort | uniq -c | sort -nr | head -n 10

     56 Methanosarcina mazei
     46 Sulfolobus acidocaldarius
      6 Metallosphaera sedula
      4 Sulfolobus solfataricus
      3 Methanobacterium formicicum
      3 Haloferax mediterranei ATCC 33500
      3 Halalkalicoccus jeotgali B3
      2 Sulfolobus solfataricus 98/2
      2 Natronorubrum tibetense GA33
      2 Natronobacterium gregoryi SP2

Conclusion

If you need to do more advanced things in the shell, here are 3 excellent command line tools specifically designed to manipulate TSV files:

• Datamash
• tabtk
• Miller

If this post has stopped at least one person resorting to Excel unnecessarily, then it was worth it.