# imports
# sqlalchemy imports
from sqlalchemy import create_engine, Column, String, Integer, Numeric, MetaData, Table, type_coerce, ForeignKey, case
from sqlalchemy.orm import create_session 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.sql import select, and_, or_, not_, func

# other imports
import matplotlib.pyplot as plt
import numpy as np
import datetime as dt
import pandas as pd

%matplotlib inline

# definitions
dbpath='/ocean/eolson/MEOPAR/obs/WODSelect_46_52_m130_m122_nitrate/WOD2.sqlite'

Base = automap_base()
engine = create_engine('sqlite:///' + dbpath, echo = False)
# reflect the tables in salish.sqlite:
Base.prepare(engine, reflect=True)
# mapped classes have been created

Sta=Base.classes.stationTBL
Prof=Base.classes.profileTBL

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

qry=session.query(Sta.Year, Sta.Month, Sta.Day, Sta.Latitude, Sta.Longitude, 
                   Prof.Depth, Prof.Temperatur, Prof.Salinity,Prof.Nitrate, Prof.Chlorophyl, 
                   Prof.Phosphate).select_from(Prof).join(Sta,Sta.CastID==Prof.CastID).\
                    filter(and_(
                            Prof.Depth != None,
                            Prof.Chlorophyl != None,
                            Prof.Nitrate != None
                            ))

#for row in data.all():
#    print(row)
qrydf=pd.DataFrame(qry.all())

session.close()
engine.dispose()

qrydf

# alternative: load csv

qrydf=pd.read_csv('/ocean/eolson/MEOPAR/obs/WODSelect_46_52_m130_m122_nitrate/WOD2.csv', index_col=0, parse_dates=True)

qrydf

# Using Pandas:

qrydf.dtypes

qrydf.head()

qrydf.tail()

qrydf.index

qrydf.columns

qrydf.values

qrydf.describe()

np.nanmean(qrydf['Salinity'])

qrydf.sort_values(by='Month').head(10)

# both endpoints are included
qrydf.loc[2:5,['Year','Month']]

# endpoint not included
qrydf.iloc[2:5,0:2]

qrydf[qrydf.Day == 1]

qrydf[qrydf.Chlorophyl > 35.0]

qrydf[qrydf['Month'].isin([1,4])]

binD=np.round(qrydf['Depth']/5.0)*5.0
qrydf['binDepth']=binD

qrydf.loc[10:15,['Depth','binDepth']]

qrydf[pd.isnull(qrydf['Salinity'])].head()

qrydf.mean()

qrydf.apply(lambda x: x.mean())

qrydf.apply(lambda x: x.max() - x.min())

# merge:
mondf = pd.DataFrame({'Month': [1,2,3,4,5,6,7,8,9,10,11,12], 
                      'strMon': ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']})

qrydf2=pd.merge(qrydf, mondf, on='Month')
qrydf2.head(30)

# grouping
qrydf.groupby(['Year','Month','binDepth']).mean()

# grouping
qrydf.groupby(['Year','Month']).count()

# plots

qrydf.hist(figsize=(10,10))