04-tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intermine-Python: Tutorial 4: Query Features - Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the previous tutorials we learnt about the basic backbone of a query - views and constraints. This short tutorial will talk about another feature of an intermine query - Outer and Inner Joins. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we add a path to a query, even if it is in the view, then by default there is a constraint involved. Our query will consist of only those records that have information in the fields or attributes that are described by the path. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's say that we want to get any genes involved in a biosynthetic process and any publications on them. If a particular gene has the publication information available, then we want to view it but if it does not then we still want to view the general information about the Gene. By default, Intermine is designed to give you an Inner Join, which basically means that no partial matches will be part of the result. However, for the example that we are discussing over here, we would require something known as an Outer Join. An Outer Join on Gene.publications would help in solving the problem for us. The code for the same is given below. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We begin as usual by importing the Service module and creating a Query object. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from intermine.webservice import Service"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "service = Service(\"https://www.flymine.org/flymine/service\")\n",
    "query=service.new_query(\"Gene\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then select the columns that we want in our final output and add the constraint. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<intermine.query.Query at 0x7f1a76794b70>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.select(\"primaryIdentifier\",\"symbol\",\"publications.year\",\"publications.firstAuthor\",\"publications.title\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<BinaryConstraint: Gene.ontologyAnnotations.ontologyTerm.name = *biosynthetic process*>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.add_constraint(\"ontologyAnnotations.ontologyTerm.name\",\"=\",\"*biosynthetic process*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And finally, we add an outer join. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<intermine.query.Query at 0x7f1a76794b70>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.outerjoin(\"publications\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=2010 publications.firstAuthor='Schnorrer Frank' publications.title='Systematic genetic analysis of muscle morphogenesis and function in Drosophila.'\n",
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=2010 publications.firstAuthor='Neely G Gregory' publications.title='A genome-wide Drosophila screen for heat nociception identifies α2δ3 as an evolutionarily conserved pain gene.'\n",
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=2012 publications.firstAuthor='Comeron Josep M' publications.title='The many landscapes of recombination in Drosophila melanogaster.'\n",
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=2015 publications.firstAuthor='Nitta Kazuhiro R' publications.title='Conservation of transcription factor binding specificities across 600 million years of bilateria evolution.'\n",
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=2005 publications.firstAuthor='Hoskins Roger A' publications.title='Rapid and efficient cDNA library screening by self-ligation of inverse PCR products (SLIP).'\n",
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=2010 publications.firstAuthor='Neely G Gregory' publications.title='A global in vivo Drosophila RNAi screen identifies NOT3 as a conserved regulator of heart function.'\n",
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=2001 publications.firstAuthor='Benos P V' publications.title='From first base: the sequence of the tip of the X chromosome of Drosophila melanogaster, a comparison of two sequencing strategies.'\n",
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=2009 publications.firstAuthor='Wheeler Scott R' publications.title='MidExDB: a database of Drosophila CNS midline cell gene expression.'\n",
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=None publications.firstAuthor='Lefevre G' publications.title='The distribution of randomly recovered X-ray-induced sex-linked genetic effects in Drosophila melanogaster.'\n",
      "Gene: primaryIdentifier='FBgn0000022' symbol='ac' publications.year=2002 publications.firstAuthor='Ernstrom Glen G' publications.title='Genetics of sensory mechanotransduction.'\n"
     ]
    }
   ],
   "source": [
    "for row in query.rows(size=10):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another query feature that Intermine has is the ability to define shorter column names. This can be done using the add_path_description method. I'll show you a short example. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<intermine.query.Query at 0x7f1a76794b70>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.add_view(\"ontologyAnnotations.ontologyTerm.name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<PathDescription: Gene.ontologyAnnotations.ontologyTerm>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.add_path_description(\"ontologyAnnotations.ontologyTerm\",\"Ontology Term\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<PathDescription: Gene.publications>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.add_path_description(\"publications\",\"Pub.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This helps us when we want to print our tables into a file and want the column names to be in a more readable format. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the next tutorial we will look at dealing with the results that are generated. "
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}