{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## DFO Nutrient Comparison" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/home/eolson/anaconda3/lib/python3.5/site-packages/matplotlib/font_manager.py:273: UserWarning: Matplotlib is building the font cache using fc-list. This may take a moment.\n", " warnings.warn('Matplotlib is building the font cache using fc-list. This may take a moment.')\n", "/home/eolson/anaconda3/lib/python3.5/site-packages/matplotlib/font_manager.py:273: UserWarning: Matplotlib is building the font cache using fc-list. This may take a moment.\n", " warnings.warn('Matplotlib is building the font cache using fc-list. This may take a moment.')\n" ] } ], "source": [ "import sqlalchemy\n", "from sqlalchemy import (create_engine, Column, String, Integer, Float, MetaData, \n", " Table, type_coerce, ForeignKey, case)\n", "from sqlalchemy.orm import mapper, create_session, relationship, aliased, Session\n", "from sqlalchemy.ext.declarative import declarative_base\n", "from sqlalchemy import case\n", "import numpy as np\n", "from sqlalchemy.ext.automap import automap_base\n", "import matplotlib.pyplot as plt\n", "import sqlalchemy.types as types\n", "from sqlalchemy.sql import and_, or_, not_, func\n", "from sqlalchemy.sql import select\n", "import os\n", "from os.path import isfile\n", "import pandas as pd\n", "import netCDF4 as nc\n", "import datetime as dt\n", "\n", "%matplotlib inline" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": true }, "outputs": [], "source": [ "PATH= '/data/eolson/MEOPAR/SS36runs/CedarRuns/spring2015_T6/'" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [], "source": [ "# definitions\n", "basepath='/ocean/eolson/MEOPAR/obs/'\n", "basedir=basepath + 'DFOOPDB/'\n", "dbname='DFO_OcProfDB'\n", "# if db does not exist, exit\n", "if not isfile(basedir + dbname + '.sqlite'):\n", " print('ERROR: ' + dbname + '.sqlite does not exist')\n", "engine = create_engine('sqlite:///' + basedir + dbname + '.sqlite', echo = False)\n", "Base = automap_base()\n", "# reflect the tables in salish.sqlite:\n", "Base.prepare(engine, reflect=True)\n", "# mapped classes have been created\n", "\n", "# existing tables:\n", "StationTBL=Base.classes.StationTBL\n", "ObsTBL=Base.classes.ObsTBL\n", "CalcsTBL=Base.classes.CalcsTBL\n", "JDFLocsTBL=Base.classes.JDFLocsTBL\n", "\n", "session = create_session(bind = engine, autocommit = False, autoflush = True)\n" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": true }, "outputs": [], "source": [ "qry0=(session\n", " .query(StationTBL.StartTimeZone.label('TimeZone'))\n", " .filter(StationTBL.StartYear>2014)\n", " .group_by(StationTBL.StartTimeZone))" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "('UTC',)\n" ] } ], "source": [ "for row in qry0.all():\n", " print(row)\n", " # one value means time zone is the same for all data for 2015 on" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false }, "outputs": [], "source": [ "SA=case([(CalcsTBL.Salinity_Bottle_SA!=None, CalcsTBL.Salinity_Bottle_SA)], else_=\n", " case([(CalcsTBL.Salinity_T0_C0_SA!=None, CalcsTBL.Salinity_T0_C0_SA)], else_=\n", " case([(CalcsTBL.Salinity_T1_C1_SA!=None, CalcsTBL.Salinity_T1_C1_SA)], else_=\n", " case([(CalcsTBL.Salinity_SA!=None, CalcsTBL.Salinity_SA)], else_=\n", " case([(CalcsTBL.Salinity__Unknown_SA!=None, CalcsTBL.Salinity__Unknown_SA)], \n", " else_=CalcsTBL.Salinity__Pre1978_SA)\n", " ))))" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false }, "outputs": [], "source": [ "Tem=case([(ObsTBL.Temperature!=None, ObsTBL.Temperature)], else_=\n", " case([(ObsTBL.Temperature_Primary!=None, ObsTBL.Temperature_Primary)], else_=\n", " case([(ObsTBL.Temperature_Secondary!=None, ObsTBL.Temperature_Secondary)], else_=ObsTBL.Temperature_Reversing)))\n", "TemUnits=case([(ObsTBL.Temperature!=None, ObsTBL.Temperature_units)], else_=\n", " case([(ObsTBL.Temperature_Primary!=None, ObsTBL.Temperature_Primary_units)], else_=\n", " case([(ObsTBL.Temperature_Secondary!=None, ObsTBL.Temperature_Secondary_units)], \n", " else_=ObsTBL.Temperature_Reversing_units)))\n", "TemFlag=ObsTBL.Quality_Flag_Temp" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(None,)\n", "('umol/L',)\n" ] } ], "source": [ "qry1=session.query(ObsTBL.Nitrate_plus_Nitrite_units).group_by(ObsTBL.Nitrate_plus_Nitrite_units)\n", "for row in qry1.all():\n", " print(row)" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false }, "outputs": [], "source": [ "qry=session.query(StationTBL.StartYear.label('Year'),StationTBL.StartMonth.label('Month'),\n", " StationTBL.StartDay.label('Day'),StationTBL.StartHour.label('Hour'),\n", " StationTBL.Lat,StationTBL.Lon,\n", " ObsTBL.Pressure,ObsTBL.Depth,ObsTBL.Ammonium,ObsTBL.Ammonium_units,ObsTBL.Chlorophyll_Extracted,\n", " ObsTBL.Chlorophyll_Extracted_units,ObsTBL.Nitrate_plus_Nitrite.label('N'),\n", " ObsTBL.Silicate.label('Si'),ObsTBL.Silicate_units,SA.label('AbsSal'),Tem.label('T'),TemUnits.label('T_units')).\\\n", " select_from(StationTBL).join(ObsTBL,ObsTBL.StationTBLID==StationTBL.ID).\\\n", " join(CalcsTBL,CalcsTBL.ObsID==ObsTBL.ID).filter(and_(StationTBL.StartYear>2014,\n", " StationTBL.Lat>47-3/2.5*(StationTBL.Lon+123.5),\n", " StationTBL.Lat<47-3/2.5*(StationTBL.Lon+121)))" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", " | Year | \n", "Month | \n", "Day | \n", "Hour | \n", "Lat | \n", "Lon | \n", "Pressure | \n", "Depth | \n", "Ammonium | \n", "Ammonium_units | \n", "Chlorophyll_Extracted | \n", "Chlorophyll_Extracted_units | \n", "N | \n", "Si | \n", "Silicate_units | \n", "AbsSal | \n", "T | \n", "T_units | \n", "
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | \n", "2015.0 | \n", "2.0 | \n", "11.0 | \n", "11.068611 | \n", "48.300833 | \n", "-124.000333 | \n", "1.9 | \n", "None | \n", "None | \n", "None | \n", "NaN | \n", "mg/m^3 | \n", "15.31 | \n", "32.14 | \n", "umol/L | \n", "29.227507 | \n", "9.7647 | \n", "'deg_C_(ITS90)' | \n", "
1 | \n", "2015.0 | \n", "2.0 | \n", "11.0 | \n", "11.068611 | \n", "48.300833 | \n", "-124.000333 | \n", "6.6 | \n", "None | \n", "None | \n", "None | \n", "2.57 | \n", "mg/m^3 | \n", "17.13 | \n", "33.90 | \n", "umol/L | \n", "29.484341 | \n", "9.6880 | \n", "'deg_C_(ITS90)' | \n", "
2 | \n", "2015.0 | \n", "2.0 | \n", "11.0 | \n", "11.068611 | \n", "48.300833 | \n", "-124.000333 | \n", "6.7 | \n", "None | \n", "None | \n", "None | \n", "NaN | \n", "mg/m^3 | \n", "NaN | \n", "NaN | \n", "umol/L | \n", "29.484839 | \n", "9.6828 | \n", "'deg_C_(ITS90)' | \n", "
3 | \n", "2015.0 | \n", "2.0 | \n", "11.0 | \n", "11.068611 | \n", "48.300833 | \n", "-124.000333 | \n", "11.0 | \n", "None | \n", "None | \n", "None | \n", "NaN | \n", "mg/m^3 | \n", "NaN | \n", "NaN | \n", "umol/L | \n", "30.144549 | \n", "9.3646 | \n", "'deg_C_(ITS90)' | \n", "
4 | \n", "2015.0 | \n", "2.0 | \n", "11.0 | \n", "11.068611 | \n", "48.300833 | \n", "-124.000333 | \n", "11.0 | \n", "None | \n", "None | \n", "None | \n", "NaN | \n", "mg/m^3 | \n", "20.62 | \n", "37.65 | \n", "umol/L | \n", "30.157913 | \n", "9.3586 | \n", "'deg_C_(ITS90)' | \n", "