Explore-NSERC-Math-Stats.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%html\n",
    "\n",
    "<script>\n",
    "  function code_toggle() {\n",
    "    if (code_shown){\n",
    "      $('div.input').hide('500');\n",
    "      $('#toggleButton').val('Show Code')\n",
    "    } else {\n",
    "      $('div.input').show('500');\n",
    "      $('#toggleButton').val('Hide Code')\n",
    "    }\n",
    "    code_shown = !code_shown\n",
    "  }\n",
    "  \n",
    "  $( document ).ready(function(){\n",
    "    code_shown=false;\n",
    "    $('div.input').hide()\n",
    "  });\n",
    "</script>\n",
    "<form action=\"javascript:code_toggle()\"><input type=\"submit\" id=\"toggleButton\" value=\"Show Code\"></form>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "<img src=\"http://www.nserc-crsng.gc.ca/_gui/wmms.gif\" alt=\"Canada logo\" align=\"right\">\n",
    "\n",
    "<br>\n",
    "\n",
    "<img src=\"http://www.triumf.ca/sites/default/files/styles/gallery_large/public/images/nserc_crsng.gif?itok=H7AhTN_F\" alt=\"NSERC logo\" align=\"right\" width = 90>\n",
    "\n",
    "\n",
    "\n",
    "# Exploring NSERC Awards Data\n",
    "\n",
    "\n",
    "Canada's [Open Government Portal](http://open.canada.ca/en) includes [NSERC Awards Data](http://open.canada.ca/data/en/dataset/c1b0f627-8c29-427c-ab73-33968ad9176e) from 1995 through 2016.\n",
    "\n",
    "The awards data (in .csv format) were copied to an [Amazon Web Services S3 bucket](http://docs.aws.amazon.com/AmazonS3/latest/dev/UsingBucket.html).\n",
    "\n",
    "> **Acknowledgement:** This notebook was constructed using JupyterHub service from [syzygy.ca](http://syzygy.ca) with assistance from [Ian Allison](https://github.com/ianabc) and India Heisz. -- [James Colliander](http://colliand.com)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the data from AWS S3 into Jupyter\n",
    "\n",
    "This process is loading NSERC Awards data starting with 1995 and moving to 2016..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "ConnectionResetError",
     "evalue": "[Errno 54] Connection reset by peer",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mConnectionResetError\u001b[0m                      Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-9374474e2968>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     18\u001b[0m                                \u001b[0mcompression\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'gzip'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     19\u001b[0m                                \u001b[0musecols\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m2\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m3\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m4\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m5\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m7\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m9\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m11\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m12\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m13\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m17\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m28\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 20\u001b[0;31m                                \u001b[0mencoding\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'latin-1'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     21\u001b[0m                               )\n\u001b[1;32m     22\u001b[0m                   )  \n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36mparser_f\u001b[0;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, dialect, tupleize_cols, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision)\u001b[0m\n\u001b[1;32m    700\u001b[0m                     skip_blank_lines=skip_blank_lines)\n\u001b[1;32m    701\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 702\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    703\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    704\u001b[0m     \u001b[0mparser_f\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36m_read\u001b[0;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[1;32m    411\u001b[0m     \u001b[0mcompression\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_infer_compression\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcompression\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    412\u001b[0m     filepath_or_buffer, _, compression, should_close = get_filepath_or_buffer(\n\u001b[0;32m--> 413\u001b[0;31m         filepath_or_buffer, encoding, compression)\n\u001b[0m\u001b[1;32m    414\u001b[0m     \u001b[0mkwds\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'compression'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcompression\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    415\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/io/common.py\u001b[0m in \u001b[0;36mget_filepath_or_buffer\u001b[0;34m(filepath_or_buffer, encoding, compression, mode)\u001b[0m\n\u001b[1;32m    205\u001b[0m             \u001b[0;31m# Override compression based on Content-Encoding header\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    206\u001b[0m             \u001b[0mcompression\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'gzip'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 207\u001b[0;31m         \u001b[0mreader\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mBytesIO\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mreq\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    208\u001b[0m         \u001b[0mreq\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    209\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mreader\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mencoding\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcompression\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/http/client.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self, amt)\u001b[0m\n\u001b[1;32m    458\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    459\u001b[0m                 \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 460\u001b[0;31m                     \u001b[0ms\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_safe_read\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlength\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    461\u001b[0m                 \u001b[0;32mexcept\u001b[0m \u001b[0mIncompleteRead\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    462\u001b[0m                     \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_close_conn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/http/client.py\u001b[0m in \u001b[0;36m_safe_read\u001b[0;34m(self, amt)\u001b[0m\n\u001b[1;32m    608\u001b[0m         \u001b[0ms\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    609\u001b[0m         \u001b[0;32mwhile\u001b[0m \u001b[0mamt\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 610\u001b[0;31m             \u001b[0mchunk\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mamt\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mMAXAMOUNT\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    611\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mchunk\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    612\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mIncompleteRead\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mb''\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjoin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mamt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/socket.py\u001b[0m in \u001b[0;36mreadinto\u001b[0;34m(self, b)\u001b[0m\n\u001b[1;32m    587\u001b[0m         \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    588\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 589\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sock\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrecv_into\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    590\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    591\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_timeout_occurred\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/ssl.py\u001b[0m in \u001b[0;36mrecv_into\u001b[0;34m(self, buffer, nbytes, flags)\u001b[0m\n\u001b[1;32m   1050\u001b[0m                   \u001b[0;34m\"non-zero flags not allowed in calls to recv_into() on %s\"\u001b[0m \u001b[0;34m%\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1051\u001b[0m                   self.__class__)\n\u001b[0;32m-> 1052\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnbytes\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbuffer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1053\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1054\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrecv_into\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbuffer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnbytes\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mflags\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/ssl.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self, len, buffer)\u001b[0m\n\u001b[1;32m    909\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    910\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mbuffer\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 911\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sslobj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbuffer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    912\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    913\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sslobj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mConnectionResetError\u001b[0m: [Errno 54] Connection reset by peer"
     ]
    }
   ],
   "source": [
    "## Thanks Ian Allison.\n",
    "## Install some python libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sys\n",
    "\n",
    "## Build a data frame with NSERC Awards Data by iteratively reading CSV files hosted on AWS S3.\n",
    "## It would improve reproducibility if we imported from a definitive NSERC Awards Data.\n",
    "df = pd.DataFrame()\n",
    "\n",
    "startYear = 1995\n",
    "endYear   = 2019  # The last year is not included, so 2017 means we include the 2016 collection but not 2017.\n",
    "\n",
    "## some columns dropped here for convenience\n",
    "for year in range(startYear, endYear):\n",
    "    file = 'https://s3.ca-central-1.amazonaws.com/open-data-ro/NSERC/NSERC_GRT_FYR' + str(year) + '_AWARD.csv.gz'\n",
    "    df = df.append(pd.read_csv(file, \n",
    "                               compression='gzip', \n",
    "                               usecols = [1, 2, 3, 4, 5, 7, 9, 11, 12, 13, 17, 28], \n",
    "                               encoding='latin-1'\n",
    "                              )\n",
    "                  )  \n",
    "    print(year)\n",
    " \n",
    "\n",
    "## Rename columns for better readability.\n",
    "df.columns = ['Name', 'Department', 'OrganizationID',\n",
    "                 'Institution', 'ProvinceEN', 'CountryEN',\n",
    "                 'FiscalYear', 'AwardAmount', 'ProgramID',\n",
    "                 'ProgramNameEN', 'Committee', 'ResearchSubjectEN']\n",
    "\n",
    "## Strip out any leading or trailing whitespace in the ProgramID column\n",
    "df['ProgramID'] = df['ProgramID'].str.strip();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## via India Heisz\n",
    "## Define some methods\n",
    "## Plotly is not installed by default\n",
    "## TODO Remove plotly dependence or arrange for plotly installed as default on the hub\n",
    "import matplotlib.ticker as mtick\n",
    "# import plotly.graph_objs as go\n",
    "# import plotly.offline as py\n",
    "#  from plotly.offline import init_notebook_mode, iplot\n",
    "# import plotly.tools as tls\n",
    "import matplotlib.pylab as plt\n",
    "from matplotlib.backends.backend_agg import FigureCanvasAgg as FigureCanvas\n",
    "\n",
    "def nsercPlot (data):\n",
    "    fig, axes = plt.subplots()\n",
    "\n",
    "    xAxis = 'FiscalYear'\n",
    "    yAxis = 'AwardAmount'\n",
    "    \n",
    "    y = data.groupby(xAxis).sum()[yAxis]\n",
    "    x = y.index\n",
    "    \n",
    "    plt.xlabel(xAxis, fontsize=14)\n",
    "    plt.ylabel(yAxis, fontsize=14)\n",
    "    plt.title(title)\n",
    "      \n",
    "    plt.plot(x,y)\n",
    "    \n",
    "    init_notebook_mode(connected=True)\n",
    "    \n",
    "    axes.scatter(x,y,s=plotPointSizes,alpha=opacity)\n",
    "    canvas = FigureCanvas(fig)\n",
    "    plotly_fig = tls.mpl_to_plotly(fig)\n",
    "    py.iplot(plotly_fig)\n",
    "    \n",
    "    return;\n",
    "\n",
    "def viewAvailableSearch(column, searchString):\n",
    "    available = df.drop_duplicates(subset = column)\n",
    "    available = available[available[column].str.contains(searchString, na=False)]\n",
    "    sorted = available.sort_values(by=[column], ascending=[True])\n",
    "    print(sorted.to_string(columns= [column], index=False))\n",
    "    return;\n",
    "\n",
    "def overview(column, data):\n",
    "    mean = data[columnYouWantInformationOn].mean()\n",
    "    print('The mean of ' + str(columnYouWantInformationOn) + ' is ' + str(mean))\n",
    "\n",
    "    median = data[columnYouWantInformationOn].median()\n",
    "    print('The median of ' + str(columnYouWantInformationOn) + ' is ' + str(median))\n",
    "\n",
    "    standardDeviation = data[columnYouWantInformationOn].std()\n",
    "    print('The standard deviation of ' + str(columnYouWantInformationOn) + ' is ' + str(standardDeviation))\n",
    "\n",
    "    awardCount = data.AwardAmount.count()\n",
    "    print('The total number of awards for your selection is ' + str(awardCount))\n",
    "    return;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First Look at the Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An exploration of the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df\n",
    "## Display the dataframe on the screen as a table. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of rows in Awards Data Frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Number of rows representing all award reports from NSERC during 1995-2016\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Columns of Data Frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Money flowed out of NSERC? We have a data set that reports NSERC disbursements with data to answer:\n",
    "> When? Who received it? Which university? Province? How much? Which NSERC program?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Values in Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Department'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of unique departments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Number of unique departments\n",
    "len(df['Department'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['ProvinceEN'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# How many unique ProgramID values?\n",
    "len(df['ProgramID'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Mismatch in program labels?\n",
    "## TODO: Match ProgramNameEN to ProgramID; Find outliers\n",
    "len(df['ProgramNameEN'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Programs by AwardAmount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Identify programs that have received the most funding\n",
    "pd.pivot_table(df, index=['ProgramNameEN','FiscalYear'], aggfunc=sum,\n",
    "               values = ['AwardAmount']\n",
    "              ).sort_values(by=['AwardAmount'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Identify programs that have received the most funding\n",
    "pd.pivot_table(df, index=['FiscalYear', 'ProgramNameEN'], aggfunc=sum,\n",
    "               values = ['AwardAmount']\n",
    "              ).sort_values(by=['AwardAmount'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "programYear = pd.pivot_table(df, index=['ProgramNameEN','FiscalYear'], aggfunc=sum,\n",
    "               values = ['AwardAmount']\n",
    "              ).sort_values(by=['AwardAmount'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Export this table as a CSV file.\n",
    "programYear.to_csv('program-year.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Big Winners"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Historical Program recipents of most NSERC funds: 1995 -- 2016\n",
    "df.groupby(['ProgramNameEN']).sum()['AwardAmount'].sort_values(ascending=False).head(n=50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Historical PI recipents of most NSERC funds: 1995 -- 2016\n",
    "df.groupby(['Name']).sum()['AwardAmount'].sort_values(ascending=False).head(n=50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Discovery and Not Discovery Budgets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total Budget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ax = (df.groupby(['FiscalYear']).sum()['AwardAmount'].plot(kind=\"bar\", title=\"Total Expenditures vs. Time\"))\n",
    "ax.set_ylabel(\"Reported Dollars\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Total Annual Budget\n",
    "df.groupby(['FiscalYear']).sum()['AwardAmount']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Not Discovery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## The ~ operator negates the condition.\n",
    "ax = (df.loc[~(df['ProgramNameEN'].str.contains('iscovery'))].groupby(['FiscalYear']).sum()['AwardAmount'].plot(kind=\"bar\", title=\"Not containing 'iscovery' vs. Time\"))\n",
    "ax.set_ylabel(\"Reported Dollars\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[~(df['ProgramNameEN'].str.contains('iscovery'))].groupby(['FiscalYear']).sum()['AwardAmount']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Discovery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "ax = (df.loc[(df['ProgramNameEN'].str.contains('iscovery'))].groupby(['FiscalYear']).sum()['AwardAmount']\n",
    "      .plot(kind=\"bar\", title=\"Containing 'iscovery' vs. Time\"))\n",
    "ax.set_ylabel(\"Reported Dollars\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[(df['ProgramNameEN'].str.contains('iscovery'))].groupby(['FiscalYear']).sum()['AwardAmount']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc = df.loc[(df['ProgramNameEN'].str.contains('iscovery'))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "disc['ProgramNameEN'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Distribution of Discovery Funding Across Programs\n",
    "(disc.groupby(['ProgramNameEN','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=False, cmap=\"viridis\", alpha=0.7, figsize=(16, 8), width=1, \n",
    "    title=\"Total dollars invested by Evaluation Group Committee\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The bulk of the historical expenses in Discovyer took place in \"Discovery Grants Program - Individual\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Not Discovery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "notdisc = df.loc[~(df['ProgramNameEN'].str.contains('iscovery'))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "notdisc['ProgramNameEN'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Distribution of Discovery Funding Across Programs\n",
    "(notdisc.groupby(['ProgramNameEN','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=True, cmap=\"viridis\", alpha=0.7, figsize=(16, 8), width=1, \n",
    "    title=\"Reported Dollars invested across programs outside of Discovery\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Evaluation Group (EG) Committees"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://wwejubwfy.s3.amazonaws.com/NSERC_-_Selection_Committees_and_Evaluation_Groups_2018-04-12_14-37-32.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "committee = 1504\n",
    "ax = df.loc[df['Committee'] == committee].groupby(['FiscalYear']).sum()['AwardAmount'].plot(\n",
    "    kind=\"bar\", title=\"committee vs. Time\")\n",
    "ax.set_ylabel(\"Reported Dollars\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "EvaluationGroups = df.loc[(df['Committee']==1501) \n",
    "                                   | (df['Committee']==1502)\n",
    "                                   | (df['Committee']==1503)\n",
    "                                   | (df['Committee']==1504) \n",
    "                                   | (df['Committee']==1505)\n",
    "                                   | (df['Committee']==1506)\n",
    "                                   | (df['Committee']==1507)\n",
    "                                   | (df['Committee']==1508)\n",
    "                                   | (df['Committee']==1509)\n",
    "                                   | (df['Committee']==1510)\n",
    "                                   | (df['Committee']==1511)\n",
    "                                   | (df['Committee']==1512)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## 2009 is an outlier so drop it. 2010 is also a bit weird.\n",
    "EG = EvaluationGroups.loc[~(EvaluationGroups['FiscalYear']==2009)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "EG['FiscalYear'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Total Reported Dollars vs Fiscal Year separated by EG Committee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Total investment dollars vs Fiscal Year separated by Evaluation Group Committee\n",
    "(EG.groupby(['Committee','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=False, cmap=\"viridis\", alpha=0.7, figsize=(16, 8), width=1, \n",
    "    title=\"Total dollars invested by Evaluation Group Committee\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Discipline Dynamics? The number and age of PIs served by each committee and each fiscal year changes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Number of Awards vs Fiscal Year separated by EG Committee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Number of Awards vs Fiscal Year separated by Evaluation Group Committee\n",
    "(EG.groupby(['Committee','FiscalYear']).count()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=False, cmap=\"viridis\", alpha=0.7, figsize=(16, 8), width=1, \n",
    "    title=\"Number of Awards by Evaluation Group Committee\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculuate an average? Divide the total dollars spent on a committee during a fiscal year \n",
    "by the number of awards given in that committee during that fiscal year."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Average Award Size vs. Fiscal Year separated by EG Committee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Total Dollars invested divided by number of awards vs. Fiscal Year separated by Evaluation Group Committee\n",
    "((EG.groupby(['Committee','FiscalYear']).sum()\n",
    " ['AwardAmount'])/(EvaluationGroups.groupby(['Committee','FiscalYear']).count()\n",
    " ['AwardAmount'])).unstack().plot(\n",
    "    kind=\"bar\", stacked=False, cmap=\"viridis\", alpha=0.7, figsize=(16, 8), width=1, \n",
    "    title=\"Average Award Amount by Evaluation Group Committee\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate using .mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Mean vs Fiscal Year separated by Evaluation Group Committee\n",
    "(EG.groupby(['Committee','FiscalYear']).mean()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=False, cmap=\"viridis\", alpha=0.7, figsize=(16, 8), width=1, \n",
    "    title=\"Number of Awards by Evaluation Group Committee\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## (Stacked) Sum vs Fiscal Year separated by Evaluation Group Committee\n",
    "(EG.groupby(['Committee','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=True, cmap=\"viridis\", figsize=(16, 8), \n",
    "    title=\"Sum of Awards by Evaluation Group Committee\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore Funding of Major Programs by Institution"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Canada Research Chairs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "crc = df.loc[df['ProgramNameEN']=='Canada Research Chairs']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## CRCs by Instititution\n",
    "(crc.groupby(['Institution','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=True, cmap=\"viridis\", alpha=0.7, figsize=(24, 8), width=1, title=\"CRCs by Institution\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CRD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "crd = df.loc[df['ProgramNameEN'] == 'Collaborative Research and Development Grants']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Number of Awards vs Fiscal Year separated by Evaluation Group Committee\n",
    "(crd.groupby(['Institution','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=True, cmap=\"viridis\", alpha=0.7, figsize=(24, 8), width=1, title = \"CRDs by Institution\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Strategic Projects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "strat = df.loc[df['ProgramNameEN'] == 'Strategic Projects - Group']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Strategic Project Awards vs Fiscal Year separated by Institution\n",
    "(strat.groupby(['Institution','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=True, cmap=\"viridis\", alpha=0.7, figsize=(24, 8), width=1, \n",
    "    title=\"Strategic Projects -- Group by Institution\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Discovery Grants - Individual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "discov = df.loc[df['ProgramNameEN'] == 'Discovery Grants Program - Individual']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Strategic Project Awards vs Fiscal Year separated by Institution\n",
    "(discov.groupby(['Institution','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=True, cmap=\"viridis\", alpha=0.7, figsize=(24, 8), width=1,\n",
    "    title=\"Discover Grants -- Individual by Institution\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}