{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "import datetime as dt\n", "import os\n", "import re\n", "from matplotlib import pyplot as plt\n", "from pandas.plotting import register_matplotlib_converters\n", "register_matplotlib_converters()\n", "import netCDF4 as nc\n", "\n", "from sqlalchemy.sql import select, and_, or_, not_, func\n", "from sqlalchemy import create_engine, Column, String, Integer, Boolean, MetaData, Table, case, between, ForeignKey, desc\n", "from sqlalchemy.orm import mapper, create_session, relationship\n", "from sqlalchemy.ext.declarative import declarative_base\n", "from sqlalchemy.ext.automap import automap_base\n", "import sqlalchemy.types as types\n", "from sqlalchemy.sql import select, and_, or_, not_, func\n", "from time import strptime\n", "import string\n", "import pandas as pd\n", "from dateutil.parser import parse as dutparse\n", "\n", "%matplotlib inline" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [], "source": [ "basepath='/ocean/eolson/MEOPAR/obs/'\n", "basedir=basepath + 'ECBuoy/'\n", "dbname='ECBuoy'\n", "Base = automap_base()\n", "engine = create_engine('sqlite:///' + basedir + dbname + '.sqlite', echo = False)\n", "# reflect the tables in salish.sqlite:\n", "Base.prepare(engine, reflect=True)\n", "# mapped classes have been created\n", "FBuoyTBL=Base.classes.FBuoyTBL\n", "FlowTBL=Base.classes.FlowTBL\n", "session = create_session(bind = engine, autocommit = False, autoflush = True)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(39812,)\n" ] } ], "source": [ "turbstart=session.query(FlowTBL.DecDay).filter(FlowTBL.MeanTurb>0).order_by(FlowTBL.DecDay).first()\n", "print(turbstart)" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [ { "data": { "text/plain": [ "datetime.datetime(2009, 1, 1, 0, 0)" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dt.datetime(1900,1,1)+dt.timedelta(days=turbstart[0])" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "tags": [] }, "outputs": [], "source": [ "df=pd.DataFrame(session.query(FlowTBL.DecDay,FlowTBL.RateHope,FlowTBL.MeanTurb).filter(FlowTBL.DecDay>39800).all())\n", "df.columns = ['DecDay', 'RateHope', 'MeanTurb']" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "tags": [] }, "outputs": [], "source": [ "dts=[dt.datetime(1900,1,1)+dt.timedelta(days=ii) for ii in df['DecDay']]" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [ { "data": { "text/html": [ "
| \n", " | DecDay | \n", "RateHope | \n", "MeanTurb | \n", "
|---|---|---|---|
| 0 | \n", "39801 | \n", "688.0 | \n", "NaN | \n", "
| 1 | \n", "39802 | \n", "729.0 | \n", "NaN | \n", "
| 2 | \n", "39803 | \n", "930.0 | \n", "NaN | \n", "
| 3 | \n", "39804 | \n", "818.0 | \n", "NaN | \n", "
| 4 | \n", "39805 | \n", "750.0 | \n", "NaN | \n", "
| 5 | \n", "39806 | \n", "770.0 | \n", "NaN | \n", "
| 6 | \n", "39807 | \n", "810.0 | \n", "NaN | \n", "
| 7 | \n", "39808 | \n", "845.0 | \n", "NaN | \n", "
| 8 | \n", "39809 | \n", "900.0 | \n", "NaN | \n", "
| 9 | \n", "39810 | \n", "933.0 | \n", "NaN | \n", "
| 10 | \n", "39811 | \n", "969.0 | \n", "NaN | \n", "
| 11 | \n", "39812 | \n", "1040.0 | \n", "5.300000 | \n", "
| 12 | \n", "39813 | \n", "1040.0 | \n", "4.552174 | \n", "
| 13 | \n", "39814 | \n", "1040.0 | \n", "4.039130 | \n", "
| 14 | \n", "39815 | \n", "1000.0 | \n", "3.612500 | \n", "
| 15 | \n", "39816 | \n", "968.0 | \n", "3.900000 | \n", "
| 16 | \n", "39817 | \n", "1000.0 | \n", "4.312500 | \n", "
| 17 | \n", "39818 | \n", "1240.0 | \n", "5.786364 | \n", "
| 18 | \n", "39819 | \n", "1270.0 | \n", "15.336364 | \n", "
| 19 | \n", "39820 | \n", "1190.0 | \n", "42.970833 | \n", "