Explore-NSERC-Open-Data-Plotly.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\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>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "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": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1995\n",
      "1996\n",
      "1997\n",
      "1998\n",
      "1999\n",
      "2000\n",
      "2001\n",
      "2002\n",
      "2003\n",
      "2004\n",
      "2005\n",
      "2006\n",
      "2007\n",
      "2008\n",
      "2009\n",
      "2010\n",
      "2011\n",
      "2012\n",
      "2013\n",
      "2014\n",
      "2015\n",
      "2016\n"
     ]
    }
   ],
   "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   = 2017  # 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": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.1.0\n"
     ]
    },
    {
     "ename": "ImportError",
     "evalue": "\nThe plotly.plotly module is deprecated,\nplease install the chart-studio package and use the\nchart_studio.plotly module instead. \n",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mImportError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-3-9afb9ca911d7>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mplotly\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0moffline\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mdownload_plotlyjs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0minit_notebook_mode\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mplot\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0miplot\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mprint\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0m__version__\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;31m# requires version >= 1.9.0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0;32mimport\u001b[0m \u001b[0mcufflinks\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mcf\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/.local/lib/python3.7/site-packages/cufflinks/__init__.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     13\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0;34m.\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mdatagen\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     14\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0;34m.\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mhelper\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 15\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0;34m.\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mtools\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     16\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0;34m.\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mcolors\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     17\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0;34m.\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpandastools\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.local/lib/python3.7/site-packages/cufflinks/tools.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpandas\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mplotly\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0moffline\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpy_offline\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0;32mimport\u001b[0m \u001b[0mplotly\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mplotly\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpy\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      7\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mplotly\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgraph_objs\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mFigure\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mScatter\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mLine\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[0;31m# from plotly.graph_objs.layout import XAxis, YAxis\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/plotly/plotly/__init__.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0m_plotly_future_\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0m_chart_studio_error\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m \u001b[0m_chart_studio_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"plotly\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/_plotly_future_/__init__.py\u001b[0m in \u001b[0;36m_chart_studio_error\u001b[0;34m(submodule)\u001b[0m\n\u001b[1;32m     47\u001b[0m \u001b[0mchart_studio\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0msubmodule\u001b[0m\u001b[0;34m}\u001b[0m \u001b[0mmodule\u001b[0m \u001b[0minstead\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     48\u001b[0m \"\"\".format(\n\u001b[0;32m---> 49\u001b[0;31m             \u001b[0msubmodule\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msubmodule\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     50\u001b[0m         )\n\u001b[1;32m     51\u001b[0m     )\n",
      "\u001b[0;31mImportError\u001b[0m: \nThe plotly.plotly module is deprecated,\nplease install the chart-studio package and use the\nchart_studio.plotly module instead. \n"
     ]
    }
   ],
   "source": [
    "import plotly.tools as tls\n",
    "from plotly import __version__\n",
    "from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\n",
    "print (__version__) # requires version >= 1.9.0\n",
    "import cufflinks as cf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## run plotly offline\n",
    "cf.go_offline()"
   ]
  },
  {
   "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=['ProgramNameEN'], 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=10)"
   ]
  },
  {
   "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": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.groupby(['FiscalYear']).sum()['AwardAmount'].iplot(kind=\"bar\")"
   ]
  },
  {
   "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'].iplot(\n",
    "    kind=\"bar\", title=\"Not containing 'iscovery' vs. Time\")"
   ]
  },
  {
   "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(\n",
    "    ['FiscalYear']).sum()['AwardAmount'].iplot(kind=\"bar\", title=\"Containing 'iscovery' vs. Time\")"
   ]
  },
  {
   "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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(disc.groupby(['ProgramNameEN','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).iplot(\n",
    "    kind=\"bar\")"
   ]
  },
  {
   "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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "notdisc.groupby(['ProgramNameEN','FiscalYear']).sum()['AwardAmount'].unstack().iplot(\n",
    "    kind=\"bar\", \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": "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": "markdown",
   "metadata": {},
   "source": [
    "### Postdocs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "viewAvailableSearch('ProgramNameEN', 'ostdo')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pdf = df.loc[df['ProgramNameEN']=='Postdoctoral Fellowships']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(pdf.groupby(['ProgramID','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=False, cmap=\"viridis\", alpha=0.7, figsize=(16, 8), width=1, \n",
    "    title=\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(pdf.groupby(['ProgramNameEN','FiscalYear']).count()\n",
    " ['AwardAmount'].unstack()).plot(\n",
    "    kind=\"bar\", stacked=False, cmap=\"viridis\", alpha=0.7, figsize=(16, 8), width=1, \n",
    "    title=\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Percentage Growth Across Evaluation Groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(EG.groupby(['Committee','FiscalYear']).sum()\n",
    " ['AwardAmount'].unstack())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "awards = pd.read_csv(\"./eg-vs-time.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "awards"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "awards.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "aw = awards[['FiscalYear', 'EGTotal', '1501', '1502', '1503', '1504',\n",
    "       '1505', '1506', '1507', '1508', '1509', '1510', '1511', '1512']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "aw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "aw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "norm = aw.divide(aw.iloc[3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "norm.plot(cmap=\"viridis\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "norm[['EGTotal','1508']].plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "norm[['EGTotal','1508']].plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "norm.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "norm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctrms = df.loc[df['ProgramID']=='CTRMS']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(ctrms.groupby('FiscalYear')['AwardAmount'].sum()/2656500).plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(ctrms.groupby('FiscalYear')['AwardAmount'].sum()).plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctrms"
   ]
  },
  {
   "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
}