{
 "metadata": {
  "name": "vectors_figures"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "raw",
     "metadata": {},
     "source": [
      "Raw code for VECTORS Kustviewer project. The purpose of the code is to apply a user request (click on an icessquare) and generate timerseries of one of the values."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# preparation --> modules\n",
      "import pandas\n",
      "import psycopg2\n",
      "import matplotlib.pyplot as plt\n",
      "import matplotlib.gridspec\n",
      "import pandas.io.sql"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# check for full list http://ocean.ices.dk/Formats/IOF.aspx\n",
      "def setspecies():\n",
      "    dctspecies = {'sound':['Sound',''],\n",
      "          'sdepth':['Sample depth','meters'],\n",
      "          'temperature':['Temperature','\u00b0C'],\n",
      "          'salinity':['Salinity','PSS-78'],\n",
      "          'doxy':['Dissolved Oxygen','ml/l'],\n",
      "          'phos':['Phosphate Phosphorus','umol/l'],\n",
      "          'tphs':['Total Phosphorus','\u00b5mol/l'],\n",
      "          'slca':['Silicate Silicon','umol/l'],\n",
      "          'ntra':['Nitrate Nitrogen','umol/l'],\n",
      "          'ntri':['Nitrite Nitrogen','umol/l'],\n",
      "          'amon':['Ammonium Nitrogen ','umol/l'],\n",
      "          'ntot':['Total Nitrogen','umol/l'],\n",
      "          'hs':['Hydrogen Sulphide Sulphur','\u00b5mol/l'],\n",
      "          'phph':['pH','-log[H+]'],\n",
      "          'alky':['Alkalinity','meq/l'],\n",
      "          'chpl':['Chlorophyll a','mg/m^3']}\n",
      "    return dctspecies    \n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# credentials for VECTORS database (i.e. readonly)\n",
      "def setcredentials():\n",
      "    uname = 'dbices'\n",
      "    pwd = 'vectors'\n",
      "    host = 'postgresx03.infra.xtr.deltares.nl'\n",
      "    dbname = 'ICES'\n",
      "    return [uname,pwd,host,dbname]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def executesqlfetch(dbname,host,uname,pwd,strSql):\n",
      "    conn = psycopg2.connect(\"dbname=\"+dbname+\" host=\"+host+\" user=\"+uname+\" password=\"+pwd)\n",
      "    try:\n",
      "        df = pandas.io.sql.read_frame(strSql,conn)        \n",
      "        return df\n",
      "    except Exception,e:\n",
      "        print e.message\n",
      "    finally:\n",
      "        conn.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# the Query\n",
      "def queryICES(dbname,host,uname,pwd,aspecies,statsq):\n",
      "    strSql = \"\"\"\n",
      "    select to_date(to_char(year,'9999')||'-'||to_char(month,'00'),'YYYY-MM') as date,year, avg(\"\"\"+aspecies+\"\"\"),stddev_samp(\"\"\"+aspecies+\"\"\") \n",
      "    from ocean\n",
      "    where sdepth < 30 and statsq = '\"\"\"+statsq+\"\"\"'\n",
      "    group by date,year\n",
      "    having avg(\"\"\"+aspecies+\"\"\") > 0 and year > 2002\n",
      "    order by date,year\n",
      "    \"\"\"\n",
      "\n",
      "    df = executesqlfetch(dbname,host,uname,pwd,strSql)\n",
      "    return df\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# create plot\n",
      "def plotdf(df,statsq,aspecies,dctspecies):\n",
      "    # Plot the results.\n",
      "    fig, ax = plt.subplots(1)\n",
      "    ax.errorbar(df['date'], df['avg'],yerr=df['stddev_samp'],fmt='r',label='stddev_samp')\n",
      "    ax.plot(df['date'], df['avg'],color='b',label='avg '+aspecies)\n",
      "    fig.autofmt_xdate()\n",
      "    ax.set_title('Timeseries of %s (%s) for ICES Square %s' % (dctspecies[aspecies][0], dctspecies[aspecies][1], statsq))\n",
      "    ax.fmt_xdata = mdates.DateFormatter('%Y-%M-%d')\n",
      "    ax.legend(loc='upper left')\n",
      "    ax.set_xlabel('date')\n",
      "    ax.set_ylabel(aspecies+' in '+dctspecies[aspecies][1])\n",
      "    ax.grid()\n",
      "    #plt.show()\n",
      "    fig.savefig(r'd:\\temp\\test_'+aspecies+'_'+statsq+'.png')\n",
      "\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 16
    }
   ],
   "metadata": {}
  }
 ]
}