Saturday 10 September 2016

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:

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

18 comments:

  1. Don't forget tabix, yes tabix was designed for vcf files, but vcf files are pre-formatted tsv files, tabix does much of the things miller does, however tabix is designed to be lightning fast. It can fetch specific columns (from a ~2600 column file) and specific rows (from the same file ~1000000 rows) as a remote comand in less than a second.

    ReplyDelete
    Replies
    1. Can tabix get specific rows and columns of arbitrary TSV files?

      I know it indexes a "seq" and "start,end" columns but I didn't think it could do other types of extractions?

      Can you give some examples of how it could be used with the file example in this blog post?

      Delete
    2. I'd also be curious to see this kind of usage, @Victor. I've only ever used tabix for vcf files.

      Delete
    3. I want to thank Dr Emu a very powerful spell caster who help me to bring my husband back to me, few month ago i have a serious problem with my husband, to the extend that he left the house, and he started dating another woman and he stayed with the woman, i tried all i can to bring him back, but all my effort was useless until the day my friend came to my house and i told her every thing that had happened between me and my husband, then she told me of a powerful spell caster who help her when she was in the same problem I then contact Dr Emu and told him every thing and he told me not to worry my self again that my husband will come back to me after he has cast a spell on him, i thought it was a joke, after he had finish casting the spell, he told me that he had just finish casting the spell, to my greatest surprise within 48 hours, my husband really came back begging me to forgive him, if you need his help you can contact him with via email: Emutemple@gmail.com or add him up on his whatsapp +2347012841542 is willing to help any body that need his help.

      Delete
    4. Hello, I know a genuine spell caster who helped me when my Husband divorced me, he made my husband come back to me and beg me to forgive him for the pains he has caused me, if you are in need of help to solve your relationship or marriage problem, contact DR. KALA, he is the right choice. He is a great man that has been casting spells with years of experience and he will help you to get your love back and cancel your divorce.

      Contact DR. KALA on email: kalalovespell@gmail.com or WhatsApp +2347051705853

      Delete
  2. I've been telling everyone for years to use column -t! Thanks for writing this up.

    I've never heard of nl but that's a good one to know. I've always used cat -n if I want line numbers but it doesn't have as many cli options.

    ReplyDelete
  3. How about q ?

    http://harelba.github.io/q/

    Not sure how it scales up for big files, but it's worth considering

    ReplyDelete
    Replies
    1. Did not know about "q" - thanks for the post, it looks good!

      Delete
  4. I just know the "column -t" today! I wrote a CSV/TSV tool kit, csvtk, which has a similar subcommand "csvtk pretty" to convert data in a readable table format.

    http://bioinf.shenwei.me/csvtk/


    csvtk provides lots of CSV/TSV manipulations by 20 subcommands. csvtk is convenient for rapid investigation and also easy to integrated into analysis pipelines. Here's a comprehensive example. http://bioinf.shenwei.me/csvtk/tutorial/

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. This comment has been removed by a blog administrator.

    ReplyDelete
  8. This comment has been removed by a blog administrator.

    ReplyDelete
  9. This comment has been removed by a blog administrator.

    ReplyDelete
  10. This comment has been removed by a blog administrator.

    ReplyDelete
  11. This comment has been removed by a blog administrator.

    ReplyDelete
  12. Hello, I know a genuine spell caster who helped me when my Husband divorced me, he made my husband come back to me and beg me to forgive him for the pains he has caused me, if you are in need of help to solve your relationship or marriage problem, contact DR. KALA, he is the right choice. He is a great man that has been casting spells with years of experience and he will help you to get your love back and cancel your divorce.

    Contact DR. KALA on email: kalalovespell@gmail.com or WhatsApp +2347051705853

    ReplyDelete
  13. I was diagnosed of herpes 3 years, and ever since then i have been taking treatment to prevent outbreaks, burning and blisters, but there was no improvement until i came across testimonies of Dr. VOODOO on how he has been curing different people from different diseases all over the world, then i contacted him. After our conversation he sent me the medicine which i took according to his instructions for up to 2 weeks. After completing the medication i went back to my doctor for another test and the virus was all gone and i was completely cured, since then i have not had any signs of outbreak. I'm so filled with joy. With herbal medication Herpes Virus is 100% curable. I refer Dr VOODOO to everyone out there with the virus Email: voodoospelltemple66@gmail.com Add Dr voodoo on whatsApp +2348140120719

    ReplyDelete