unsolved-exercises/05-tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intermine-Python: Tutorial 5: Query Results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This tutorial will talk about dealing with the results of our query. You can either store the results into a file (using a library like csv) or you can process the results immediately after you extract them. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will write a short query and will then explore the Results class of InterMine. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from intermine.webservice import Service\n",
    "service = Service(\"https://www.flymine.org/flymine/service\")\n",
    "query=service.new_query(\"Gene\")\n",
    "query.select(\"publications.*\")\n",
    "query.add_constraint(\"Gene\",\"LOOKUP\",\"zen\",extra_value=\"D. melanogaster\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have added our constraints and views, we are ready to look at the results. The results can be accessed in either a dictionary form, or a list, a ResultRow object (the most common one), or even as a list of strings (CSV or TSV).\n",
    "\n",
    "##### Valid row types are: \n",
    "\n",
    "- dict\n",
    "- list\n",
    "- rr\n",
    "- csv\n",
    "- tsv "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This syntax is probably familiar from previous tutorials:\n",
    "for gene in query.rows():\n",
    "    print(gene)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# There's also an alternate syntax: \n",
    "# for x in x.results(row_type):\n",
    "#     # code to output results here, \n",
    "#     # e.g. maybe print(x).    \n",
    "# try writing a for loop that uses the \"rr\" (resultrow) type.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Iterating through `query.results(row=\"rr\")` and iterating through `query.rows()` are equivalent. Feel free to use whichever you feel more comfortable with. If you want to extract only specific columns, it may be easier to use `\"list\"` instead of `\"rr\"`. Let's say you want to extract column 2 & 3, i.e. `publications.doi` and `publications.firstAuthor`, then it can be done by running `query.results(\"list\")`, then printing columns by index. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Try writing a for loop that iterates over query.results(\"list\")\n",
    "# and prints the column at index 1 (doi) and column 2 (author) of each row.  \n",
    "# the syntax to access a single column from a list is list[index_in_list]\n",
    "# e.g for the second item in a list, access list[1] \n",
    "# since lists start counting at 0\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Maybe we want to only print  those rows where publications.doi is not `None`. We can add an `if` condition to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Same as before, but you probably want to use if row[1] != None: before you print.. \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can pass two more parameters while passing `query.results()`. These are `start` and `size`. `Start` represents the row number that you want to start processing from. By default this is set to 0 (first row). `Size` represents the number of rows that you want to print. Lets say we want to print rows 10 and 11 only. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use a for loop to print rows, \n",
    "# - starting at 10, \n",
    "# - with a size of 2, \n",
    "# - with the row type \"rr\".\n",
    "# Syntax to limit the query: query.results(row=\"row_type\", size=some_size, start=start_index)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with CSVs\n",
    "\n",
    "If you prefer dealing with lists of strings, i.e. csv and tsv objects you can use them too. First, we need to import the csv library. If you want to read your results in a CSV format you create a `csv.reader` object. Let's try below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's create a csv reader object assigned to the variable csv_reader\n",
    "# The syntax to create a reader is csv.reader(input_data, delimiter=\",\", quotechar='\"')\n",
    "# \n",
    "# In this case the input data is a query.results() iterator with \n",
    "# - row type set to csv,\n",
    "# - start at result 10\n",
    "# - select 10 results\n",
    "# e.g. query.results(row=\"csv\", size=10, start=10)\n",
    "# Now we just need to glue those elements together:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in csv_reader:\n",
    "    print(row[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you have used the csv library before try writing your results into a csv file using the writer class. If you have not used it before, trying going through the documentation first and then writing code on your own. It is pretty self explanatory. The documentation can be found at: https://docs.python.org/2/library/csv.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summarized results\n",
    "\n",
    "The last thing that we will look at, in this tutorial, is the summarize method. This method proves to be particularly useful when we want some basic statistics regarding a particular column. We will look at the statistics of the length of genes present in the list : List of the most enriched genes in the adult fly brain. We begin by creating a query. This is followed by adding views and the list constraint. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# query setup\n",
    "query2=service.new_query()\n",
    "query2.select(\"Gene.*\",\"organism.*\")\n",
    "query2.add_constraint(\"Gene\",\"IN\",\"PL FlyAtlas_brain_top\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then print out the first 10 rows of results. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for row in query2.rows(size=10):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can look at the summary of the length of each gene. This contains some useful information such as the average length and the maximum and minimum length. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's print the summary of the Gene's length\n",
    "# Syntax: for summary: query2.summarise(field_name)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This brings us to the end of the fifth tutorial. The next tutorial will be about further management of results. "
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}