{
 "metadata": {
  "name": "",
  "signature": "sha256:3bbbcfb0d62b99e5755186306ff0cf10e99bf3c5a78c08cf76b27d3ad4da5512"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sys\n",
      "sys.version_info"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 1,
       "text": [
        "sys.version_info(major=3, minor=4, micro=2, releaselevel='final', serial=0)"
       ]
      }
     ],
     "prompt_number": 1
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "[petl](http://petl.readthedocs.org) Case Study 1 - Comparing Tables"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "This case study illustrates the use of the [petl](http://petl.readthedocs.org) package for doing some simple profiling and comparison of data from\n",
      "two tables.\n"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Introduction"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The files used in this case study can be downloaded from the following\n",
      "link:\n",
      "\n",
      "* http://aliman.s3.amazonaws.com/petl/petl-case-study-1-files.zip\n",
      "\n",
      "Download and unzip the files:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!wget http://aliman.s3.amazonaws.com/petl/petl-case-study-1-files.zip\n",
      "!unzip -o petl-case-study-1-files.zip"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "--2015-01-19 17:37:39--  http://aliman.s3.amazonaws.com/petl/petl-case-study-1-files.zip\r\n",
        "Resolving aliman.s3.amazonaws.com (aliman.s3.amazonaws.com)... 54.231.9.241\r\n",
        "Connecting to aliman.s3.amazonaws.com (aliman.s3.amazonaws.com)|54.231.9.241|:80... "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "connected.\r\n",
        "HTTP request sent, awaiting response... "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "200 OK\r\n",
        "Length: 3076773 (2.9M) [application/zip]\r\n",
        "Saving to: \u2018petl-case-study-1-files.zip\u2019\r\n",
        "\r\n",
        "\r",
        " 0% [                                       ] 0           --.-K/s              "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r",
        " 2% [                                       ] 75,696       276KB/s             "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r",
        " 8% [==>                                    ] 265,496      484KB/s             "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r",
        "22% [=======>                               ] 688,896      838KB/s             "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r",
        "50% [==================>                    ] 1,567,816   1.39MB/s             "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r",
        "100%[======================================>] 3,076,773   2.34MB/s   in 1.3s   \r\n",
        "\r\n",
        "2015-01-19 17:37:41 (2.34 MB/s) - \u2018petl-case-study-1-files.zip\u2019 saved [3076773/3076773]\r\n",
        "\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Archive:  petl-case-study-1-files.zip\r\n",
        "  inflating: popdata.csv             "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r\n",
        "  inflating: snpdata.csv             "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\r\n"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The first file (`snpdata.csv`) contains a list of locations in the\n",
      "genome of the malaria parasite *P. falciparum*, along with some basic\n",
      "data about genetic variations found at those locations.\n",
      "\n",
      "The second file (`popdata.csv`) is supposed to contain the same list\n",
      "of genome locations, along with some additional data such as allele\n",
      "frequencies in different populations.\n",
      "\n",
      "The main point for this case study is that the first file\n",
      "(`snpdata.csv`) contains the canonical list of genome locations, and\n",
      "the second file (`popdata.csv`) contains some additional data about\n",
      "the same genome locations and therefore should be consistent with the\n",
      "first file. We want to check whether this second file is in fact\n",
      "consistent with the first file."
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Preparing the data"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Start by importing the petl package:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import petl as etl\n",
      "etl.__version__"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 3,
       "text": [
        "'1.0.0'"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "To save some typing, let ***a*** be the table of data extracted from the\n",
      "first file (`snpdata.csv`), and let ***b*** be the table of data extracted\n",
      "from the second file (`popdata.csv`), using the `fromcsv()`\n",
      "function:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a = etl.fromtsv('snpdata.csv')\n",
      "b = etl.fromtsv('popdata.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Examine the header from each file:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a.header()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 5,
       "text": [
        "('Chr',\n",
        " 'Pos',\n",
        " 'Ref',\n",
        " 'Nref',\n",
        " 'Der',\n",
        " 'Mut',\n",
        " 'isTypable',\n",
        " 'GeneId',\n",
        " 'GeneAlias',\n",
        " 'GeneDescr')"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b.header()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 6,
       "text": [
        "('Chromosome',\n",
        " 'Coordinates',\n",
        " 'Ref. Allele',\n",
        " 'Non-Ref. Allele',\n",
        " 'Outgroup Allele',\n",
        " 'Ancestral Allele',\n",
        " 'Derived Allele',\n",
        " 'Ref. Aminoacid',\n",
        " 'Non-Ref. Aminoacid',\n",
        " 'Private Allele',\n",
        " 'Private population',\n",
        " 'maf AFR',\n",
        " 'maf PNG',\n",
        " 'maf SEA',\n",
        " 'daf AFR',\n",
        " 'daf PNG',\n",
        " 'daf SEA',\n",
        " 'nraf AFR',\n",
        " 'nraf PNG',\n",
        " 'nraf SEA',\n",
        " 'Mutation type',\n",
        " 'Gene',\n",
        " 'Gene Aliases',\n",
        " 'Gene Description',\n",
        " 'Gene Information')"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "There is a common set of 9 fields that is present in both tables, and\n",
      "we would like focus on comparing these common fields, however\n",
      "different field names have been used in the two files. To simplify\n",
      "comparison, use `rename()` to rename some fields in the\n",
      "second file:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b_renamed = b.rename({'Chromosome': 'Chr', \n",
      "                      'Coordinates': 'Pos', \n",
      "                      'Ref. Allele': 'Ref', \n",
      "                      'Non-Ref. Allele': 'Nref', \n",
      "                      'Derived Allele': 'Der', \n",
      "                      'Mutation type': 'Mut', \n",
      "                      'Gene': 'GeneId', \n",
      "                      'Gene Aliases': 'GeneAlias', \n",
      "                      'Gene Description': 'GeneDescr'})\n",
      "b_renamed.header()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "('Chr',\n",
        " 'Pos',\n",
        " 'Ref',\n",
        " 'Nref',\n",
        " 'Outgroup Allele',\n",
        " 'Ancestral Allele',\n",
        " 'Der',\n",
        " 'Ref. Aminoacid',\n",
        " 'Non-Ref. Aminoacid',\n",
        " 'Private Allele',\n",
        " 'Private population',\n",
        " 'maf AFR',\n",
        " 'maf PNG',\n",
        " 'maf SEA',\n",
        " 'daf AFR',\n",
        " 'daf PNG',\n",
        " 'daf SEA',\n",
        " 'nraf AFR',\n",
        " 'nraf PNG',\n",
        " 'nraf SEA',\n",
        " 'Mut',\n",
        " 'GeneId',\n",
        " 'GeneAlias',\n",
        " 'GeneDescr',\n",
        " 'Gene Information')"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Use `cut()` to extract only the fields we're interested in\n",
      "from both tables:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "common_fields = ['Chr', 'Pos', 'Ref', 'Nref', 'Der', 'Mut', 'GeneId', 'GeneAlias', 'GeneDescr']\n",
      "a_common = a.cut(common_fields)\n",
      "b_common = b_renamed.cut(common_fields)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Inspect the data:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a_common"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>Chr</th>\n",
        "<th>Pos</th>\n",
        "<th>Ref</th>\n",
        "<th>Nref</th>\n",
        "<th>Der</th>\n",
        "<th>Mut</th>\n",
        "<th>GeneId</th>\n",
        "<th>GeneAlias</th>\n",
        "<th>GeneDescr</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>91099</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>S</td>\n",
        "<td>PFA0095c</td>\n",
        "<td>MAL1P1.10</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>91104</td>\n",
        "<td>A</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0095c</td>\n",
        "<td>MAL1P1.10</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>93363</td>\n",
        "<td>T</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>hypothetical protein, conserved in P. falciparum</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>93382</td>\n",
        "<td>T</td>\n",
        "<td>G</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>hypothetical protein, conserved in P. falciparum</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>93384</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>hypothetical protein, conserved in P. falciparum</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n",
        "<p><strong>...</strong></p>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "+--------+---------+-----+------+-----+-----+------------+-------------+----------------------------------------------------+\n",
        "| Chr    | Pos     | Ref | Nref | Der | Mut | GeneId     | GeneAlias   | GeneDescr                                          |\n",
        "+========+=========+=====+======+=====+=====+============+=============+====================================================+\n",
        "| 'MAL1' | '91099' | 'G' | 'A'  | '-' | 'S' | 'PFA0095c' | 'MAL1P1.10' | 'rifin'                                            |\n",
        "+--------+---------+-----+------+-----+-----+------------+-------------+----------------------------------------------------+\n",
        "| 'MAL1' | '91104' | 'A' | 'T'  | '-' | 'N' | 'PFA0095c' | 'MAL1P1.10' | 'rifin'                                            |\n",
        "+--------+---------+-----+------+-----+-----+------------+-------------+----------------------------------------------------+\n",
        "| 'MAL1' | '93363' | 'T' | 'A'  | '-' | 'N' | 'PFA0100c' | 'MAL1P1.11' | 'hypothetical protein, conserved in P. falciparum' |\n",
        "+--------+---------+-----+------+-----+-----+------------+-------------+----------------------------------------------------+\n",
        "| 'MAL1' | '93382' | 'T' | 'G'  | '-' | 'N' | 'PFA0100c' | 'MAL1P1.11' | 'hypothetical protein, conserved in P. falciparum' |\n",
        "+--------+---------+-----+------+-----+-----+------------+-------------+----------------------------------------------------+\n",
        "| 'MAL1' | '93384' | 'G' | 'A'  | '-' | 'N' | 'PFA0100c' | 'MAL1P1.11' | 'hypothetical protein, conserved in P. falciparum' |\n",
        "+--------+---------+-----+------+-----+-----+------------+-------------+----------------------------------------------------+\n",
        "..."
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b_common"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>Chr</th>\n",
        "<th>Pos</th>\n",
        "<th>Ref</th>\n",
        "<th>Nref</th>\n",
        "<th>Der</th>\n",
        "<th>Mut</th>\n",
        "<th>GeneId</th>\n",
        "<th>GeneAlias</th>\n",
        "<th>GeneDescr</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>91099</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>SYN</td>\n",
        "<td>PFA0095c</td>\n",
        "<td>MAL1P1.10,RIF</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>91104</td>\n",
        "<td>A</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td>NON</td>\n",
        "<td>PFA0095c</td>\n",
        "<td>MAL1P1.10,RIF</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>93363</td>\n",
        "<td>T</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>NON</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>Plasmodium exported protein (PHISTa), unknown function</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>93382</td>\n",
        "<td>T</td>\n",
        "<td>G</td>\n",
        "<td>-</td>\n",
        "<td>NON</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>Plasmodium exported protein (PHISTa), unknown function</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>93384</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>NON</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>Plasmodium exported protein (PHISTa), unknown function</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n",
        "<p><strong>...</strong></p>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "+--------+---------+-----+------+-----+-------+------------+-----------------+----------------------------------------------------------+\n",
        "| Chr    | Pos     | Ref | Nref | Der | Mut   | GeneId     | GeneAlias       | GeneDescr                                                |\n",
        "+========+=========+=====+======+=====+=======+============+=================+==========================================================+\n",
        "| 'MAL1' | '91099' | 'G' | 'A'  | '-' | 'SYN' | 'PFA0095c' | 'MAL1P1.10,RIF' | 'rifin'                                                  |\n",
        "+--------+---------+-----+------+-----+-------+------------+-----------------+----------------------------------------------------------+\n",
        "| 'MAL1' | '91104' | 'A' | 'T'  | '-' | 'NON' | 'PFA0095c' | 'MAL1P1.10,RIF' | 'rifin'                                                  |\n",
        "+--------+---------+-----+------+-----+-------+------------+-----------------+----------------------------------------------------------+\n",
        "| 'MAL1' | '93363' | 'T' | 'A'  | '-' | 'NON' | 'PFA0100c' | 'MAL1P1.11'     | 'Plasmodium exported protein (PHISTa), unknown function' |\n",
        "+--------+---------+-----+------+-----+-------+------------+-----------------+----------------------------------------------------------+\n",
        "| 'MAL1' | '93382' | 'T' | 'G'  | '-' | 'NON' | 'PFA0100c' | 'MAL1P1.11'     | 'Plasmodium exported protein (PHISTa), unknown function' |\n",
        "+--------+---------+-----+------+-----+-------+------------+-----------------+----------------------------------------------------------+\n",
        "| 'MAL1' | '93384' | 'G' | 'A'  | '-' | 'NON' | 'PFA0100c' | 'MAL1P1.11'     | 'Plasmodium exported protein (PHISTa), unknown function' |\n",
        "+--------+---------+-----+------+-----+-------+------------+-----------------+----------------------------------------------------------+\n",
        "..."
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The `fromucsv()` function does not attempt to parse any of the\n",
      "values from the underlying CSV file, so all values are reported as\n",
      "strings:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b_common.display(vrepr=repr)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>Chr</th>\n",
        "<th>Pos</th>\n",
        "<th>Ref</th>\n",
        "<th>Nref</th>\n",
        "<th>Der</th>\n",
        "<th>Mut</th>\n",
        "<th>GeneId</th>\n",
        "<th>GeneAlias</th>\n",
        "<th>GeneDescr</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td>'91099'</td>\n",
        "<td>'G'</td>\n",
        "<td>'A'</td>\n",
        "<td>'-'</td>\n",
        "<td>'SYN'</td>\n",
        "<td>'PFA0095c'</td>\n",
        "<td>'MAL1P1.10,RIF'</td>\n",
        "<td>'rifin'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td>'91104'</td>\n",
        "<td>'A'</td>\n",
        "<td>'T'</td>\n",
        "<td>'-'</td>\n",
        "<td>'NON'</td>\n",
        "<td>'PFA0095c'</td>\n",
        "<td>'MAL1P1.10,RIF'</td>\n",
        "<td>'rifin'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td>'93363'</td>\n",
        "<td>'T'</td>\n",
        "<td>'A'</td>\n",
        "<td>'-'</td>\n",
        "<td>'NON'</td>\n",
        "<td>'PFA0100c'</td>\n",
        "<td>'MAL1P1.11'</td>\n",
        "<td>'Plasmodium exported protein (PHISTa), unknown function'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td>'93382'</td>\n",
        "<td>'T'</td>\n",
        "<td>'G'</td>\n",
        "<td>'-'</td>\n",
        "<td>'NON'</td>\n",
        "<td>'PFA0100c'</td>\n",
        "<td>'MAL1P1.11'</td>\n",
        "<td>'Plasmodium exported protein (PHISTa), unknown function'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td>'93384'</td>\n",
        "<td>'G'</td>\n",
        "<td>'A'</td>\n",
        "<td>'-'</td>\n",
        "<td>'NON'</td>\n",
        "<td>'PFA0100c'</td>\n",
        "<td>'MAL1P1.11'</td>\n",
        "<td>'Plasmodium exported protein (PHISTa), unknown function'</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n",
        "<p><strong>...</strong></p>"
       ],
       "metadata": {},
       "output_type": "display_data"
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "However, the 'Pos' field should be interpreted as an integer.\n",
      "\n",
      "Also, the 'Mut' field has a different representation in the two\n",
      "tables, which needs to be converted before the data can be compared:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a_common.valuecounts('Mut')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>Mut</th>\n",
        "<th>count</th>\n",
        "<th>frequency</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>N</td>\n",
        "<td style='text-align: right'>71162</td>\n",
        "<td style='text-align: right'>0.6865804123611875</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>S</td>\n",
        "<td style='text-align: right'>31535</td>\n",
        "<td style='text-align: right'>0.30425386166507473</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>-</td>\n",
        "<td style='text-align: right'>950</td>\n",
        "<td style='text-align: right'>0.009165725973737783</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 12,
       "text": [
        "+-----+-------+----------------------+\n",
        "| Mut | count | frequency            |\n",
        "+=====+=======+======================+\n",
        "| 'N' | 71162 |   0.6865804123611875 |\n",
        "+-----+-------+----------------------+\n",
        "| 'S' | 31535 |  0.30425386166507473 |\n",
        "+-----+-------+----------------------+\n",
        "| '-' |   950 | 0.009165725973737783 |\n",
        "+-----+-------+----------------------+"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b_common.valuecounts('Mut')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>Mut</th>\n",
        "<th>count</th>\n",
        "<th>frequency</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>NON</td>\n",
        "<td style='text-align: right'>70880</td>\n",
        "<td style='text-align: right'>0.6840510336042</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>SYN</td>\n",
        "<td style='text-align: right'>32738</td>\n",
        "<td style='text-align: right'>0.31594896639579995</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 13,
       "text": [
        "+-------+-------+---------------------+\n",
        "| Mut   | count | frequency           |\n",
        "+=======+=======+=====================+\n",
        "| 'NON' | 70880 |     0.6840510336042 |\n",
        "+-------+-------+---------------------+\n",
        "| 'SYN' | 32738 | 0.31594896639579995 |\n",
        "+-------+-------+---------------------+"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Use the `convert()` function to convert the type of the 'Pos'\n",
      "field in both tables and the representation of the 'Mut' field in\n",
      "table ***b***:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a_conv = a_common.convert('Pos', int)\n",
      "b_conv = (\n",
      "    b_common\n",
      "    .convert('Pos', int)\n",
      "    .convert('Mut', {'SYN': 'S', 'NON': 'N'})\n",
      ")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "highlight = 'background-color: yellow'\n",
      "a_conv.display(caption='a', vrepr=repr, td_styles={'Pos': highlight})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<caption>a</caption>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>Chr</th>\n",
        "<th>Pos</th>\n",
        "<th>Ref</th>\n",
        "<th>Nref</th>\n",
        "<th>Der</th>\n",
        "<th>Mut</th>\n",
        "<th>GeneId</th>\n",
        "<th>GeneAlias</th>\n",
        "<th>GeneDescr</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>91099</td>\n",
        "<td>'G'</td>\n",
        "<td>'A'</td>\n",
        "<td>'-'</td>\n",
        "<td>'S'</td>\n",
        "<td>'PFA0095c'</td>\n",
        "<td>'MAL1P1.10'</td>\n",
        "<td>'rifin'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>91104</td>\n",
        "<td>'A'</td>\n",
        "<td>'T'</td>\n",
        "<td>'-'</td>\n",
        "<td>'N'</td>\n",
        "<td>'PFA0095c'</td>\n",
        "<td>'MAL1P1.10'</td>\n",
        "<td>'rifin'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>93363</td>\n",
        "<td>'T'</td>\n",
        "<td>'A'</td>\n",
        "<td>'-'</td>\n",
        "<td>'N'</td>\n",
        "<td>'PFA0100c'</td>\n",
        "<td>'MAL1P1.11'</td>\n",
        "<td>'hypothetical protein, conserved in P. falciparum'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>93382</td>\n",
        "<td>'T'</td>\n",
        "<td>'G'</td>\n",
        "<td>'-'</td>\n",
        "<td>'N'</td>\n",
        "<td>'PFA0100c'</td>\n",
        "<td>'MAL1P1.11'</td>\n",
        "<td>'hypothetical protein, conserved in P. falciparum'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>93384</td>\n",
        "<td>'G'</td>\n",
        "<td>'A'</td>\n",
        "<td>'-'</td>\n",
        "<td>'N'</td>\n",
        "<td>'PFA0100c'</td>\n",
        "<td>'MAL1P1.11'</td>\n",
        "<td>'hypothetical protein, conserved in P. falciparum'</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n",
        "<p><strong>...</strong></p>"
       ],
       "metadata": {},
       "output_type": "display_data"
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b_conv.display(caption='b', vrepr=repr, td_styles={'Pos': highlight, 'Mut': highlight})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<caption>b</caption>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>Chr</th>\n",
        "<th>Pos</th>\n",
        "<th>Ref</th>\n",
        "<th>Nref</th>\n",
        "<th>Der</th>\n",
        "<th>Mut</th>\n",
        "<th>GeneId</th>\n",
        "<th>GeneAlias</th>\n",
        "<th>GeneDescr</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>91099</td>\n",
        "<td>'G'</td>\n",
        "<td>'A'</td>\n",
        "<td>'-'</td>\n",
        "<td style='background-color: yellow'>'S'</td>\n",
        "<td>'PFA0095c'</td>\n",
        "<td>'MAL1P1.10,RIF'</td>\n",
        "<td>'rifin'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>91104</td>\n",
        "<td>'A'</td>\n",
        "<td>'T'</td>\n",
        "<td>'-'</td>\n",
        "<td style='background-color: yellow'>'N'</td>\n",
        "<td>'PFA0095c'</td>\n",
        "<td>'MAL1P1.10,RIF'</td>\n",
        "<td>'rifin'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>93363</td>\n",
        "<td>'T'</td>\n",
        "<td>'A'</td>\n",
        "<td>'-'</td>\n",
        "<td style='background-color: yellow'>'N'</td>\n",
        "<td>'PFA0100c'</td>\n",
        "<td>'MAL1P1.11'</td>\n",
        "<td>'Plasmodium exported protein (PHISTa), unknown function'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>93382</td>\n",
        "<td>'T'</td>\n",
        "<td>'G'</td>\n",
        "<td>'-'</td>\n",
        "<td style='background-color: yellow'>'N'</td>\n",
        "<td>'PFA0100c'</td>\n",
        "<td>'MAL1P1.11'</td>\n",
        "<td>'Plasmodium exported protein (PHISTa), unknown function'</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>'MAL1'</td>\n",
        "<td style='background-color: yellow'>93384</td>\n",
        "<td>'G'</td>\n",
        "<td>'A'</td>\n",
        "<td>'-'</td>\n",
        "<td style='background-color: yellow'>'N'</td>\n",
        "<td>'PFA0100c'</td>\n",
        "<td>'MAL1P1.11'</td>\n",
        "<td>'Plasmodium exported protein (PHISTa), unknown function'</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n",
        "<p><strong>...</strong></p>"
       ],
       "metadata": {},
       "output_type": "display_data"
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now the tables are ready for comparison."
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Looking for missing or unexpected rows"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Because both tables should contain the same list of genome locations,\n",
      "they should have the same number of rows. Use `nrows()` to\n",
      "compare:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a_conv.nrows()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 17,
       "text": [
        "103647"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "b_conv.nrows()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 18,
       "text": [
        "103618"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "There is some discrepancy. First investigate by comparing just the\n",
      "genomic locations, defined by the 'Chr' and 'Pos' fields, using\n",
      "`complement()`:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "a_locs = a_conv.cut('Chr', 'Pos')\n",
      "b_locs = b_conv.cut('Chr', 'Pos')\n",
      "locs_only_in_a = a_locs.complement(b_locs)\n",
      "locs_only_in_a.nrows()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 19,
       "text": [
        "29"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "locs_only_in_a.displayall(caption='a only')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<caption>a only</caption>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>Chr</th>\n",
        "<th>Pos</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>216961</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL10</td>\n",
        "<td style='text-align: right'>538210</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL10</td>\n",
        "<td style='text-align: right'>548779</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL10</td>\n",
        "<td style='text-align: right'>1432969</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL11</td>\n",
        "<td style='text-align: right'>500289</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL11</td>\n",
        "<td style='text-align: right'>1119809</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL11</td>\n",
        "<td style='text-align: right'>1278859</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL12</td>\n",
        "<td style='text-align: right'>51827</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL13</td>\n",
        "<td style='text-align: right'>183727</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL13</td>\n",
        "<td style='text-align: right'>398404</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL13</td>\n",
        "<td style='text-align: right'>627342</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL13</td>\n",
        "<td style='text-align: right'>1216664</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL13</td>\n",
        "<td style='text-align: right'>2750149</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL14</td>\n",
        "<td style='text-align: right'>1991758</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL14</td>\n",
        "<td style='text-align: right'>2297918</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL14</td>\n",
        "<td style='text-align: right'>2372268</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL14</td>\n",
        "<td style='text-align: right'>2994810</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL2</td>\n",
        "<td style='text-align: right'>38577</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL2</td>\n",
        "<td style='text-align: right'>64017</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL4</td>\n",
        "<td style='text-align: right'>1094258</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL5</td>\n",
        "<td style='text-align: right'>1335335</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL5</td>\n",
        "<td style='text-align: right'>1338718</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL7</td>\n",
        "<td style='text-align: right'>670602</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL7</td>\n",
        "<td style='text-align: right'>690509</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL8</td>\n",
        "<td style='text-align: right'>489937</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL9</td>\n",
        "<td style='text-align: right'>416116</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL9</td>\n",
        "<td style='text-align: right'>868677</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL9</td>\n",
        "<td style='text-align: right'>1201970</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL9</td>\n",
        "<td style='text-align: right'>1475245</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n"
       ],
       "metadata": {},
       "output_type": "display_data"
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "locs_only_in_b = b_locs.complement(a_locs)\n",
      "locs_only_in_b.nrows()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 21,
       "text": [
        "0"
       ]
      }
     ],
     "prompt_number": 21
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "So it appears that 29 locations are missing from table ***b***. Export\n",
      "these missing locations to a CSV file using `toucsv()`:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "locs_only_in_a.tocsv('missing_locations.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 22
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "An alternative method for finding rows in one table where some key\n",
      "value is not present in another table is to use the `antijoin()`\n",
      "function:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "locs_only_in_a = a_conv.antijoin(b_conv, key=('Chr', 'Pos'))\n",
      "locs_only_in_a.nrows()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 23,
       "text": [
        "29"
       ]
      }
     ],
     "prompt_number": 23
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Finding conflicts"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We'd also like to compare the values given in the other fields, to\n",
      "find any discrepancies between the two tables.\n",
      "\n",
      "The simplest way to find conflicts is to `merge()` both tables under a given key:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ab_merge = etl.merge(a_conv, b_conv, key=('Chr', 'Pos'))\n",
      "ab_merge.display(caption='ab_merge', \n",
      "                 td_styles=lambda v: highlight if isinstance(v, etl.Conflict) else '')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<caption>ab_merge</caption>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>Chr</th>\n",
        "<th>Pos</th>\n",
        "<th>Ref</th>\n",
        "<th>Nref</th>\n",
        "<th>Der</th>\n",
        "<th>Mut</th>\n",
        "<th>GeneId</th>\n",
        "<th>GeneAlias</th>\n",
        "<th>GeneDescr</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>91099</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>S</td>\n",
        "<td>PFA0095c</td>\n",
        "<td style='background-color: yellow'>Conflict({'MAL1P1.10', 'MAL1P1.10,RIF'})</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>91104</td>\n",
        "<td>A</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0095c</td>\n",
        "<td style='background-color: yellow'>Conflict({'MAL1P1.10', 'MAL1P1.10,RIF'})</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>93363</td>\n",
        "<td>T</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td style='background-color: yellow'>Conflict({'Plasmodium exported protein (PHISTa), unknown function', 'hypothetical protein, conserved in P. falciparum'})</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>93382</td>\n",
        "<td>T</td>\n",
        "<td>G</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td style='background-color: yellow'>Conflict({'Plasmodium exported protein (PHISTa), unknown function', 'hypothetical protein, conserved in P. falciparum'})</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>MAL1</td>\n",
        "<td>93384</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td style='background-color: yellow'>Conflict({'Plasmodium exported protein (PHISTa), unknown function', 'hypothetical protein, conserved in P. falciparum'})</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n",
        "<p><strong>...</strong></p>"
       ],
       "metadata": {},
       "output_type": "display_data"
      }
     ],
     "prompt_number": 24
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "From a glance at the conflicts above, it appears there are\n",
      "discrepancies in the 'GeneAlias' and 'GeneDescr' fields. There may\n",
      "also be conflicts in other fields, so we need to investigate further.\n",
      "\n",
      "Note that the table ***ab_merge*** will contain all rows, not only those containing conflicts. To find only conflicting rows, use `cat()` then `conflicts()`, e.g.:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ab = etl.cat(a_conv.addfield('source', 'a', index=0), \n",
      "             b_conv.addfield('source', 'b', index=0))\n",
      "ab_conflicts = ab.conflicts(key=('Chr', 'Pos'), exclude='source')\n",
      "ab_conflicts.display(10)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>source</th>\n",
        "<th>Chr</th>\n",
        "<th>Pos</th>\n",
        "<th>Ref</th>\n",
        "<th>Nref</th>\n",
        "<th>Der</th>\n",
        "<th>Mut</th>\n",
        "<th>GeneId</th>\n",
        "<th>GeneAlias</th>\n",
        "<th>GeneDescr</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>91099</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>S</td>\n",
        "<td>PFA0095c</td>\n",
        "<td>MAL1P1.10</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>91099</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>S</td>\n",
        "<td>PFA0095c</td>\n",
        "<td>MAL1P1.10,RIF</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>91104</td>\n",
        "<td>A</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0095c</td>\n",
        "<td>MAL1P1.10</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>91104</td>\n",
        "<td>A</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0095c</td>\n",
        "<td>MAL1P1.10,RIF</td>\n",
        "<td>rifin</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>93363</td>\n",
        "<td>T</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>hypothetical protein, conserved in P. falciparum</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>93363</td>\n",
        "<td>T</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>Plasmodium exported protein (PHISTa), unknown function</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>93382</td>\n",
        "<td>T</td>\n",
        "<td>G</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>hypothetical protein, conserved in P. falciparum</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>93382</td>\n",
        "<td>T</td>\n",
        "<td>G</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>Plasmodium exported protein (PHISTa), unknown function</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>93384</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>hypothetical protein, conserved in P. falciparum</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>93384</td>\n",
        "<td>G</td>\n",
        "<td>A</td>\n",
        "<td>-</td>\n",
        "<td>N</td>\n",
        "<td>PFA0100c</td>\n",
        "<td>MAL1P1.11</td>\n",
        "<td>Plasmodium exported protein (PHISTa), unknown function</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n",
        "<p><strong>...</strong></p>"
       ],
       "metadata": {},
       "output_type": "display_data"
      }
     ],
     "prompt_number": 25
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Finally, let's find conflicts in a specific field:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ab_conflicts_mut = ab.conflicts(key=('Chr', 'Pos'), include='Mut')\n",
      "ab_conflicts_mut.display(10, caption='Mut conflicts', td_styles={'Mut': highlight})"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<table class='petl'>\n",
        "<caption>Mut conflicts</caption>\n",
        "<thead>\n",
        "<tr>\n",
        "<th>source</th>\n",
        "<th>Chr</th>\n",
        "<th>Pos</th>\n",
        "<th>Ref</th>\n",
        "<th>Nref</th>\n",
        "<th>Der</th>\n",
        "<th>Mut</th>\n",
        "<th>GeneId</th>\n",
        "<th>GeneAlias</th>\n",
        "<th>GeneDescr</th>\n",
        "</tr>\n",
        "</thead>\n",
        "<tbody>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>99099</td>\n",
        "<td>G</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td style='background-color: yellow'>-</td>\n",
        "<td>PFA0110w</td>\n",
        "<td>MAL1P1.13,Pf155</td>\n",
        "<td>ring-infected erythrocyte surface antigen</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>99099</td>\n",
        "<td>G</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td style='background-color: yellow'>N</td>\n",
        "<td>PFA0110w</td>\n",
        "<td>MAL1P1.13,Pf155,RESA</td>\n",
        "<td>ring-infected erythrocyte surface antigen</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>99211</td>\n",
        "<td>C</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td style='background-color: yellow'>-</td>\n",
        "<td>PFA0110w</td>\n",
        "<td>MAL1P1.13,Pf155</td>\n",
        "<td>ring-infected erythrocyte surface antigen</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>99211</td>\n",
        "<td>C</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td style='background-color: yellow'>N</td>\n",
        "<td>PFA0110w</td>\n",
        "<td>MAL1P1.13,Pf155,RESA</td>\n",
        "<td>ring-infected erythrocyte surface antigen</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>197903</td>\n",
        "<td>C</td>\n",
        "<td>A</td>\n",
        "<td>A</td>\n",
        "<td style='background-color: yellow'>S</td>\n",
        "<td>PFA0220w</td>\n",
        "<td>MAL1P1.34b</td>\n",
        "<td>ubiquitin carboxyl-terminal hydrolase, putative</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>197903</td>\n",
        "<td>C</td>\n",
        "<td>A</td>\n",
        "<td>A</td>\n",
        "<td style='background-color: yellow'>N</td>\n",
        "<td>PFA0220w</td>\n",
        "<td>PFA0215w,MAL1P1.34b</td>\n",
        "<td>ubiquitin carboxyl-terminal hydrolase, putative</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>384429</td>\n",
        "<td>C</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td style='background-color: yellow'>N</td>\n",
        "<td>PFA0485w</td>\n",
        "<td>MAL1P2.26</td>\n",
        "<td>dolichol kinase</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>384429</td>\n",
        "<td>C</td>\n",
        "<td>T</td>\n",
        "<td>-</td>\n",
        "<td style='background-color: yellow'>S</td>\n",
        "<td>-</td>\n",
        "<td>-</td>\n",
        "<td>-</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>a</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>513268</td>\n",
        "<td>A</td>\n",
        "<td>G</td>\n",
        "<td>-</td>\n",
        "<td style='background-color: yellow'>N</td>\n",
        "<td>PFA0650w</td>\n",
        "<td>MAL1P3.12,MAL1P3.12a,PFA0655w</td>\n",
        "<td>surface-associated interspersed gene pseudogene, (SURFIN) pseudogene</td>\n",
        "</tr>\n",
        "<tr>\n",
        "<td>b</td>\n",
        "<td>MAL1</td>\n",
        "<td style='text-align: right'>513268</td>\n",
        "<td>A</td>\n",
        "<td>G</td>\n",
        "<td>-</td>\n",
        "<td style='background-color: yellow'>S</td>\n",
        "<td>PFA0650w</td>\n",
        "<td>MAL1P3.12,PFA0655,MAL1P3.12a,3D7surf1.2,PFA0655w,MAL1P12a</td>\n",
        "<td>surface-associated interspersed gene (SURFIN), pseudogene</td>\n",
        "</tr>\n",
        "</tbody>\n",
        "</table>\n",
        "<p><strong>...</strong></p>"
       ],
       "metadata": {},
       "output_type": "display_data"
      }
     ],
     "prompt_number": 26
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ab_conflicts_mut.nrows()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 27,
       "text": [
        "3592"
       ]
      }
     ],
     "prompt_number": 27
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "For more information about the `petl` package see the [petl online documentation](http://petl.readthedocs.org)."
     ]
    }
   ],
   "metadata": {}
  }
 ]
}