Notebook

In [1]:

# imports
from sqlalchemy import create_engine, Column, String, Integer, Float, Date, MetaData, Table, type_coerce
from sqlalchemy.orm import mapper, create_session
import csv
from sqlalchemy import case
import numpy as np
from sqlalchemy.ext.automap import automap_base
import matplotlib.pyplot as plt
import sqlalchemy.types as types
import numbers
from sqlalchemy.sql import and_, or_, not_, func
from sqlalchemy.sql import select
import datetime as dt
import os
import re
from mpl_toolkits.basemap import Basemap
import pandas as pd
%matplotlib inline

In [2]:

# definitions
basepath='/ocean/eolson/MEOPAR/obs/'
hdir='ECBuoy'
dbname='ECBuoy'
dbpath=os.path.join(basepath,hdir,dbname)
print(dbpath)

/ocean/eolson/MEOPAR/obs/ECBuoy/ECBuoy

In [3]:

engine = create_engine('sqlite:///'+dbpath+'.sqlite')

In [4]:

connection=engine.connect()
if engine.dialect.has_table(connection,'FlowTBL'):
    # delete existing 
    connection.execute('DROP TABLE FlowTBL')
connection.close()

In [5]:

Base = automap_base()
# reflect the tables in salish.sqlite:
Base.prepare(engine, reflect=True)
# mapped classes have been created
FBuoyTBL=Base.classes.FBuoyTBL

In [6]:

session = create_session(bind = engine, autocommit = False, autoflush = True)

In [7]:

# create JDFLocsTBL to identify rows in Strait of Juan de Fuca
class Flow(Base):
        __table__=Table('FlowTBL', Base.metadata,
                    Column('ID', Integer, primary_key=True),
                    Column('Year', Integer),
                    Column('Month', Integer),
                    Column('Day', Integer),
                    Column('DecDay',Integer),
                    Column('RateHope', Float),# m^3/s
                    Column('MeanTurb', Float))
Base.metadata.create_all(engine)
session = create_session(bind = engine, autocommit = False, autoflush = True)

Add flow data to table

In [8]:

df=pd.read_csv('/ocean/eolson/MEOPAR/obs/ECRivers/Flow/FraserHopeDaily__Apr-12-2018_11_07_27PM.csv',
              sep=',',header=1)
dts=[dt.datetime(yr,1,1)+dt.timedelta(days=(float(dd)-1)) for yr,dd in df[['YEAR','DD']].values]
df['Month']=[idt.month for idt in dts]
df['Day']=[idt.day for idt in dts]
df['RateHope']=df['Value']
df['Year']=df['YEAR']
df2=pd.DataFrame(df.loc[:,['Year','Month','Day','RateHope']][df.PARAM == 1])# select flow data (not height data)

In [9]:

for ind, row in df2.iterrows():
    #print(dict(row))
    #idict={'Year':row['Year'],'Month':row['Month']}
    idict=dict(row)
    idict['DecDay']=(dt.date(int(row['Year']),int(row['Month']),int(row['Day']))-dt.date(1900,1,1)).days
    idict['MeanTurb']=np.nanmean([i for i in session.query(FBuoyTBL.Turb_NTU).filter(
        func.round(FBuoyTBL.DecDay-.5,0)==idict['DecDay'])]) if session.query(FBuoyTBL.Turb_NTU).filter(
        func.round(FBuoyTBL.DecDay-.5,0)==idict['DecDay']).count()>0 else np.nan
    if ind<20 or ind%365==0:
        print(idict)
    session.execute(Flow.__table__.insert().values(idict))
#    print(row['Year'], row['Month'])

{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 1.0, 'DecDay': 4442, 'RateHope': 538.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 2.0, 'DecDay': 4443, 'RateHope': 538.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 3.0, 'DecDay': 4444, 'RateHope': 538.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 4.0, 'DecDay': 4445, 'RateHope': 538.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 5.0, 'DecDay': 4446, 'RateHope': 538.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 6.0, 'DecDay': 4447, 'RateHope': 538.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 7.0, 'DecDay': 4448, 'RateHope': 479.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 8.0, 'DecDay': 4449, 'RateHope': 479.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 9.0, 'DecDay': 4450, 'RateHope': 459.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 10.0, 'DecDay': 4451, 'RateHope': 459.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 11.0, 'DecDay': 4452, 'RateHope': 459.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 12.0, 'DecDay': 4453, 'RateHope': 459.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 13.0, 'DecDay': 4454, 'RateHope': 459.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 14.0, 'DecDay': 4455, 'RateHope': 439.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 15.0, 'DecDay': 4456, 'RateHope': 419.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 16.0, 'DecDay': 4457, 'RateHope': 439.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 17.0, 'DecDay': 4458, 'RateHope': 459.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 18.0, 'DecDay': 4459, 'RateHope': 479.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 19.0, 'DecDay': 4460, 'RateHope': 479.0}
{'Year': 1912.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 20.0, 'DecDay': 4461, 'RateHope': 459.0}
{'Year': 1913.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 1.0, 'DecDay': 4807, 'RateHope': 538.0}
{'Year': 1914.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 1.0, 'DecDay': 5172, 'RateHope': 940.0}
{'Year': 1915.0, 'MeanTurb': nan, 'Month': 3.0, 'Day': 1.0, 'DecDay': 5537, 'RateHope': 629.0}
{'Year': 1916.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 29.0, 'DecDay': 5902, 'RateHope': 991.0}
{'Year': 1917.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 28.0, 'DecDay': 6267, 'RateHope': 538.0}
{'Year': 1918.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 28.0, 'DecDay': 6632, 'RateHope': 1040.0}
{'Year': 1919.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 28.0, 'DecDay': 6997, 'RateHope': 490.0}
{'Year': 1920.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 28.0, 'DecDay': 7362, 'RateHope': 702.0}
{'Year': 1921.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 27.0, 'DecDay': 7727, 'RateHope': 923.0}
{'Year': 1922.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 27.0, 'DecDay': 8092, 'RateHope': 566.0}
{'Year': 1923.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 27.0, 'DecDay': 8457, 'RateHope': 634.0}
{'Year': 1924.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 27.0, 'DecDay': 8822, 'RateHope': 748.0}
{'Year': 1925.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 26.0, 'DecDay': 9187, 'RateHope': 711.0}
{'Year': 1926.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 26.0, 'DecDay': 9552, 'RateHope': 753.0}
{'Year': 1927.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 26.0, 'DecDay': 9917, 'RateHope': 612.0}
{'Year': 1928.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 26.0, 'DecDay': 10282, 'RateHope': 691.0}
{'Year': 1929.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 25.0, 'DecDay': 10647, 'RateHope': 456.0}
{'Year': 1930.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 25.0, 'DecDay': 11012, 'RateHope': 731.0}
{'Year': 1931.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 25.0, 'DecDay': 11377, 'RateHope': 671.0}
{'Year': 1932.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 25.0, 'DecDay': 11742, 'RateHope': 631.0}
{'Year': 1933.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 24.0, 'DecDay': 12107, 'RateHope': 714.0}
{'Year': 1934.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 24.0, 'DecDay': 12472, 'RateHope': 1110.0}
{'Year': 1935.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 24.0, 'DecDay': 12837, 'RateHope': 1330.0}
{'Year': 1936.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 24.0, 'DecDay': 13202, 'RateHope': 609.0}
{'Year': 1937.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 23.0, 'DecDay': 13567, 'RateHope': 552.0}
{'Year': 1938.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 23.0, 'DecDay': 13932, 'RateHope': 646.0}
{'Year': 1939.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 23.0, 'DecDay': 14297, 'RateHope': 544.0}
{'Year': 1940.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 23.0, 'DecDay': 14662, 'RateHope': 827.0}
{'Year': 1941.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 22.0, 'DecDay': 15027, 'RateHope': 702.0}
{'Year': 1942.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 22.0, 'DecDay': 15392, 'RateHope': 731.0}
{'Year': 1943.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 22.0, 'DecDay': 15757, 'RateHope': 753.0}
{'Year': 1944.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 22.0, 'DecDay': 16122, 'RateHope': 600.0}
{'Year': 1945.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 21.0, 'DecDay': 16487, 'RateHope': 654.0}
{'Year': 1946.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 21.0, 'DecDay': 16852, 'RateHope': 580.0}
{'Year': 1947.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 21.0, 'DecDay': 17217, 'RateHope': 597.0}
{'Year': 1948.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 21.0, 'DecDay': 17582, 'RateHope': 623.0}
{'Year': 1949.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 20.0, 'DecDay': 17947, 'RateHope': 643.0}
{'Year': 1950.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 20.0, 'DecDay': 18312, 'RateHope': 682.0}
{'Year': 1951.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 20.0, 'DecDay': 18677, 'RateHope': 796.0}
{'Year': 1952.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 20.0, 'DecDay': 19042, 'RateHope': 626.0}
{'Year': 1953.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 19.0, 'DecDay': 19407, 'RateHope': 634.0}
{'Year': 1954.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 19.0, 'DecDay': 19772, 'RateHope': 954.0}
{'Year': 1955.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 19.0, 'DecDay': 20137, 'RateHope': 872.0}
{'Year': 1956.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 19.0, 'DecDay': 20502, 'RateHope': 626.0}
{'Year': 1957.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 18.0, 'DecDay': 20867, 'RateHope': 1080.0}
{'Year': 1958.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 18.0, 'DecDay': 21232, 'RateHope': 1070.0}
{'Year': 1959.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 18.0, 'DecDay': 21597, 'RateHope': 776.0}
{'Year': 1960.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 18.0, 'DecDay': 21962, 'RateHope': 1040.0}
{'Year': 1961.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 17.0, 'DecDay': 22327, 'RateHope': 994.0}
{'Year': 1962.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 17.0, 'DecDay': 22692, 'RateHope': 1350.0}
{'Year': 1963.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 17.0, 'DecDay': 23057, 'RateHope': 1560.0}
{'Year': 1964.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 17.0, 'DecDay': 23422, 'RateHope': 1030.0}
{'Year': 1965.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 16.0, 'DecDay': 23787, 'RateHope': 997.0}
{'Year': 1966.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 16.0, 'DecDay': 24152, 'RateHope': 821.0}
{'Year': 1967.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 16.0, 'DecDay': 24517, 'RateHope': 1100.0}
{'Year': 1968.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 16.0, 'DecDay': 24882, 'RateHope': 1230.0}
{'Year': 1969.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 15.0, 'DecDay': 25247, 'RateHope': 1020.0}
{'Year': 1970.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 15.0, 'DecDay': 25612, 'RateHope': 1050.0}
{'Year': 1971.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 15.0, 'DecDay': 25977, 'RateHope': 1260.0}
{'Year': 1972.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 15.0, 'DecDay': 26342, 'RateHope': 855.0}
{'Year': 1973.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 14.0, 'DecDay': 26707, 'RateHope': 799.0}
{'Year': 1974.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 14.0, 'DecDay': 27072, 'RateHope': 934.0}
{'Year': 1975.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 14.0, 'DecDay': 27437, 'RateHope': 719.0}
{'Year': 1976.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 14.0, 'DecDay': 27802, 'RateHope': 1030.0}
{'Year': 1977.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 13.0, 'DecDay': 28167, 'RateHope': 1320.0}
{'Year': 1978.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 13.0, 'DecDay': 28532, 'RateHope': 796.0}
{'Year': 1979.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 13.0, 'DecDay': 28897, 'RateHope': 711.0}
{'Year': 1980.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 13.0, 'DecDay': 29262, 'RateHope': 600.0}
{'Year': 1981.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 12.0, 'DecDay': 29627, 'RateHope': 1130.0}
{'Year': 1982.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 12.0, 'DecDay': 29992, 'RateHope': 810.0}
{'Year': 1983.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 12.0, 'DecDay': 30357, 'RateHope': 1010.0}
{'Year': 1984.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 12.0, 'DecDay': 30722, 'RateHope': 1030.0}
{'Year': 1985.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 11.0, 'DecDay': 31087, 'RateHope': 658.0}
{'Year': 1986.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 11.0, 'DecDay': 31452, 'RateHope': 703.0}
{'Year': 1987.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 11.0, 'DecDay': 31817, 'RateHope': 894.0}
{'Year': 1988.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 11.0, 'DecDay': 32182, 'RateHope': 617.0}
{'Year': 1989.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 10.0, 'DecDay': 32547, 'RateHope': 678.0}
{'Year': 1990.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 10.0, 'DecDay': 32912, 'RateHope': 960.0}
{'Year': 1991.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 10.0, 'DecDay': 33277, 'RateHope': 1350.0}
{'Year': 1992.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 10.0, 'DecDay': 33642, 'RateHope': 1320.0}
{'Year': 1993.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 9.0, 'DecDay': 34007, 'RateHope': 709.0}
{'Year': 1994.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 9.0, 'DecDay': 34372, 'RateHope': 630.0}
{'Year': 1995.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 9.0, 'DecDay': 34737, 'RateHope': 794.0}
{'Year': 1996.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 9.0, 'DecDay': 35102, 'RateHope': 983.0}
{'Year': 1997.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 8.0, 'DecDay': 35467, 'RateHope': 888.0}
{'Year': 1998.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 8.0, 'DecDay': 35832, 'RateHope': 907.0}
{'Year': 1999.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 8.0, 'DecDay': 36197, 'RateHope': 784.0}
{'Year': 2000.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 8.0, 'DecDay': 36562, 'RateHope': 775.0}
{'Year': 2001.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 7.0, 'DecDay': 36927, 'RateHope': 648.0}
{'Year': 2002.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 7.0, 'DecDay': 37292, 'RateHope': 771.0}
{'Year': 2003.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 7.0, 'DecDay': 37657, 'RateHope': 735.0}
{'Year': 2004.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 7.0, 'DecDay': 38022, 'RateHope': 680.0}
{'Year': 2005.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 6.0, 'DecDay': 38387, 'RateHope': 3170.0}
{'Year': 2006.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 6.0, 'DecDay': 38752, 'RateHope': 1150.0}
{'Year': 2007.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 6.0, 'DecDay': 39117, 'RateHope': 685.0}
{'Year': 2008.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 6.0, 'DecDay': 39482, 'RateHope': 814.0}
{'Year': 2009.0, 'MeanTurb': 4.5826086956521745, 'Month': 2.0, 'Day': 5.0, 'DecDay': 39847, 'RateHope': 895.0}
{'Year': 2010.0, 'MeanTurb': 5.3500000000000005, 'Month': 2.0, 'Day': 5.0, 'DecDay': 40212, 'RateHope': 851.0}
{'Year': 2011.0, 'MeanTurb': 7.3208333333333329, 'Month': 2.0, 'Day': 5.0, 'DecDay': 40577, 'RateHope': 1140.0}
{'Year': 2012.0, 'MeanTurb': 6.4666666666666659, 'Month': 2.0, 'Day': 5.0, 'DecDay': 40942, 'RateHope': 1110.0}
{'Year': 2013.0, 'MeanTurb': 4.5916666666666668, 'Month': 2.0, 'Day': 4.0, 'DecDay': 41307, 'RateHope': 841.0}
{'Year': 2014.0, 'MeanTurb': nan, 'Month': 2.0, 'Day': 4.0, 'DecDay': 41672, 'RateHope': 744.0}
{'Year': 2015.0, 'MeanTurb': 17.232083333333332, 'Month': 2.0, 'Day': 4.0, 'DecDay': 42037, 'RateHope': 1770.0}
{'Year': 2016.0, 'MeanTurb': 7.0291666666666659, 'Month': 2.0, 'Day': 4.0, 'DecDay': 42402, 'RateHope': 1210.0}

In [10]:

session.commit()

In [11]:

session.close()
engine.dispose()

In [ ]: