Import Dependencies
In [1]:
%matplotlib inline
import os
import sys
import platform
import matplotlib
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.dates as dates
import matplotlib.ticker as tick
from matplotlib.backends.backend_pdf import PdfPages
from datetime import datetime,timedelta
from pylab import rcParams
rcParams['figure.figsize'] = 15, 10
In [2]:
import ipywidgets as widgets
widgets.Widget.widget_types.values()
from IPython.display import display
import traitlets
In [3]:
# custom functions for transducer data import
#import Snake_Valley_Data_Import as svdi
import wellapplication.transport as svdi
Declare software versions
In [4]:
print("Operating System " + platform.system() + " " + platform.release())
print("Python Version " + str(sys.version))
print("Pandas Version " + str(pd.__version__))
print("Numpy Version " + str(np.__version__))
print("Matplotlib Version " + str(matplotlib.__version__))
Designate root environments
In [5]:
drive = 'M:'
raw_archive_folder = drive + '/PROJECTS/Snake Valley Water/Transducer Data/Raw_data_archive'
folder = raw_archive_folder + '/2016/2016 q2/'
enteredFolder = folder + '/entered/'
checkFolder = folder + '/toCheck/'
wellinfofile = drive + raw_archive_folder + '/well table 2015-03-23.csv'
Import Connection Info For Database
In [6]:
sys.path.append(raw_archive_folder)
import engineGetter
In [7]:
if not os.path.exists(enteredFolder):
print('Creating Output Folder')
os.makedirs(enteredFolder)
else:
print('Output Folder Exists')
In [8]:
if not os.path.exists(checkFolder):
print('Creating Check Folder')
os.makedirs(checkFolder)
else:
print('Check Folder Exists')
In [9]:
wellinf = pd.read_csv(raw_archive_folder + '/well table 2015-03-23.csv',index_col=0)
wellinf["G_Elev_m"] = wellinf["GroundElevation"]/3.2808
wellinf["Well"] = wellinf['Well'].apply(lambda x: str(x).lower().strip())
wellinf['WellID'] = wellinf.index.values
#wellinfo = svdi.barodistance(wellinfo)
wellinfo = svdi.make_files_table(folder, wellinf)
wellinfo.to_csv(folder+"/wellinfo4.csv")
In [10]:
manualwls = raw_archive_folder + "/All tape measurements.csv"
manual = pd.read_csv(manualwls, index_col="DateTime", engine="python")
In [11]:
pw03baro_append = folder + "\\pw03 baro 2016-08-03.xle"
pw10baro_append = folder + "\\pw10 baro 2016-08-03.xle"
pw19baro_append = folder + "\\pw19 baro 2016-08-04.xle"
Skip this section if you have already compiled the data to save on processing time.
In [12]:
pw03baro = raw_archive_folder + "\\pw03baro.csv"
pw10baro = raw_archive_folder + "\\pw10baro.csv"
pw19baro = raw_archive_folder + "\\pw19baro.csv"
In [13]:
svdi.appendomatic(pw03baro_append,pw03baro)
svdi.appendomatic(pw10baro_append,pw10baro)
svdi.appendomatic(pw19baro_append,pw19baro)
In [14]:
# duplicated to update changes made by appendomatic
pw03baro = pd.read_csv(raw_archive_folder + "\\pw03baro.csv",index_col='DateTime',parse_dates=True)
pw10baro = pd.read_csv(raw_archive_folder + "\\pw10baro.csv",index_col='DateTime',parse_dates=True)
pw19baro = pd.read_csv(raw_archive_folder + "\\pw19baro.csv",index_col='DateTime',parse_dates=True)
In [15]:
pw03baro['pw03'] = pw03baro['Level']
pw03baro = svdi.hourly_resample(pw03baro['pw03'].to_frame())
pw10baro['pw10'] = pw10baro['Level']
pw10baro = svdi.hourly_resample(pw10baro['pw10'].to_frame())
pw19baro['pw19'] = pw19baro['Level']
pw19baro = svdi.hourly_resample(pw19baro['pw19'].to_frame())
In [16]:
baro = pd.merge(pw03baro,pw10baro,how="outer",left_index=True,right_index=True)
baro = pd.merge(baro,pw19baro,how="outer",left_index=True,right_index=True)
baro.dropna(axis=0,inplace=True)
baro['integr'] = 0 #for vented transducers
In [30]:
baro[baro.index.to_datetime()==pd.datetime(2015,11,15,10)]
Out[30]:
In [17]:
baro[['pw03','pw10','pw19']].plot()
Out[17]:
In [18]:
baro.to_csv(raw_archive_folder + '\\baro.csv')
In [19]:
manualwls = raw_archive_folder + "\\All tape measurements.csv"
manual = pd.read_csv(manualwls, index_col="DateTime", engine="python")
manualrecent = manual[manual.index.to_datetime() > pd.datetime(2015,6,1)]
manualrecent.dropna(inplace=True)
manualrecent.reset_index(inplace=True)
print manualrecent.dtypes
manualrecent = pd.merge(manualrecent, wellinfo, how='left',left_on='WellID', right_index=True)
manualrecent.loc[:,"MeasuredLevel"] = np.nan
manualrecent.loc[:,"Temp"] = np.nan
manualrecent.loc[:,"BaroEfficiencyCorrected"] = np.nan
manualrecent.loc[:,"DeltaLevel"] = np.nan
manualrecent.loc[:,"DriftCorrection"] = np.nan
manualrecent.loc[:,"MeasuredBy"] = np.nan
manualrecent.loc[:,"Tape"] = 1
manualrecent.loc[:,"DTWBelowGroundSurface"] = np.nan
manualrecent.loc[:,"WaterElevation"] = np.nan
#manualrecent["DTWBelowGroundSurface"] = np.nan
manualrecent.loc[:,"DTWBelowCasing"] = manualrecent.loc[:,"MeasuredDTW"]
manualrecent.loc[:,"DTWBelowGroundSurface"] = manualrecent.loc[:,"MeasuredDTW"] - manualrecent.loc[:,"Offset"]
manualrecent.loc[:,"WaterElevation"] = manualrecent.loc[:,'GroundElevation'] - manualrecent.loc[:,"DTWBelowGroundSurface"]
print manualrecent
#outpath = pathlist[0] + '\\' + pathlist[1] + '\\' + pathlist[2] + '\\' + pathlist[3] + '\\' + pathlist[4] + '\\' + 'Manual' + '.csv'
manualrecent.to_csv(raw_archive_folder+ 'Manual' + '.csv', index=True, columns= ["WellID","DateTime","MeasuredLevel","Temp","BaroEfficiencyCorrected","DeltaLevel",
"MeasuredDTW","DriftCorrection","DTWBelowCasing","DTWBelowGroundSurface",
"WaterElevation","Tape","MeasuredBy"])
In [20]:
manual['DateTime'] = manual.index.to_datetime()
manual.to_csv(raw_archive_folder+ 'Manual' + '.csv', index=False, columns= ["WellID","DateTime","MeasuredLevel","Temp","BaroEfficiencyCorrected","DeltaLevel",
"MeasuredDTW","DriftCorrection","DTWBelowCasing","DTWBelowGroundSurface",
"WaterElevation","Tape","MeasuredBy"])
In [21]:
print wellinfo.loc[:,'full_file_name']
In [22]:
manualwls = raw_archive_folder + '/All tape measurements.csv'
manual = pd.read_csv(manualwls, index_col="DateTime", engine="python")
barofile = raw_archive_folder + '/baro.csv'
baro = pd.read_csv(barofile,index_col=0, parse_dates=True)
wellinfo = pd.read_csv(folder + '/wellinfo4.csv')
In [23]:
engine = engineGetter.getEngine()
In [26]:
form = widgets.VBox()
welllist = ["pw07mx 2015-11-30.csv","pw02a 11-30-2015.xle","pw02a 2016-07-07.xle","sg23b 2016-05-02.xle"]
welllist = welllist+ list(wellinfo.loc[:,'full_file_name'].values)
#print welllist
wells = widgets.SelectMultiple(description="Well", options=welllist,padding=4)
pdfName = widgets.Text(description="PDF:",padding=4)
driftTol = widgets.FloatSlider(value=0.05, min=0.00, max=10.0, step=0.05, description='Drift Tolerance:')
form.children = [wells, pdfName, driftTol]
display(form)
In [27]:
for i in wells.value:
print folder+'/'+i
inputfile = folder +'/'+i
g, drift, wellname = svdi.imp_new_well(inputfile, wellinfo, manual, baro)
glist = g.columns.tolist()
y1 = g['WaterElevation'].values
y2 = baro['pw03'].values
x1 = g.index.values
x2 = baro.index.values
wellname, wellid = svdi.getwellid(folder+'\\'+i,wellinfo)
ylast = wellinfo[wellinfo['WellID']==wellid]['GroundElevation'].values[0] + wellinfo[wellinfo['WellID']==wellid]['Offset'].values[0] - svdi.fcl(manual[manual['WellID']== wellid],max(g.index.to_datetime()))[1]
yfirst = wellinfo[wellinfo['WellID']==wellid]['GroundElevation'].values[0] + wellinfo[wellinfo['WellID']==wellid]['Offset'].values[0] - svdi.fcl(manual[manual['WellID']== wellid],min(g.index.to_datetime()))[1]
xlast = pd.to_datetime((svdi.fcl(manual[manual['WellID']== wellid],max(pd.to_datetime(g.index)))).name)
xfirst = pd.to_datetime((svdi.fcl(manual[manual['WellID']== wellid],min(pd.to_datetime(g.index)))).name)
x4 = [xfirst,xlast]
y4 = [yfirst,ylast]
fig, ax1 = plt.subplots()
ax1.scatter(x4,y4,color='purple')
ax1.plot(x1,y1,color='blue',label='Water Level Elevation')
ax1.set_ylabel('Water Level Elevation',color='blue')
y_formatter = tick.ScalarFormatter(useOffset=False)
ax1.yaxis.set_major_formatter(y_formatter)
ax2 = ax1.twinx()
ax2.set_ylabel('Barometric Pressure (ft)', color='red')
ax2.plot(x2,y2,color='red',label='Barometric pressure (ft)')
h1, l1 = ax1.get_legend_handles_labels()
h2, l2 = ax2.get_legend_handles_labels()
ax1.legend(h1+h2, l1+l2, loc=3)
plt.xlim(xfirst-timedelta(days=3),xlast+timedelta(days=3))
plt.title('Well: ' + wellname.title() + ' ' + 'Total Drift = ' + str(g['DriftCorrection'][-1]))
In [65]:
pdf_pages = PdfPages(folder + '/' + pdfName.value + '.pdf')
for i in wells.value:
print folder+'/'+i
inputfile = folder +'/'+i
g, drift, wellname = svdi.imp_new_well(inputfile, wellinfo, manual, baro)
quer = "SELECT * FROM groundwater.reading where WellID = " + str(g['WellID'].values[0]) + " and DateTime > \'" + str(g.index.values[-1])[0:10] + " "+ str(g.index.values[-1])[11:19] + "\'"
if abs(float(drift)) < driftTol.value:
if len(pd.read_sql_query(sql=quer,con=engine))<1:
g.to_csv(enteredFolder+wellname+".csv", index=False)
tablename = 'reading'
g.to_sql(con=engine, name = tablename, if_exists='append', flavor='mysql', index=False)
print("Added to DB table " + tablename)
else:
print("Already Entered")
print(len(pd.read_sql_query(sql=quer,con=engine)))
else:
g.to_csv(checkFolder+wellname+".csv", index=False, columns= ["WellID","DateTime","MeasuredLevel","Temp","BaroEfficiencyCorrected","DeltaLevel",
"MeasuredDTW","DriftCorrection","DTWBelowCasing","DTWBelowGroundSurface",
"WaterElevation","Tape","MeasuredBy"])
print("Check File")
glist = g.columns.tolist()
for j in range(len(glist)):
if 'pw' in glist[j]:
h = glist[j]
y1 = g['WaterElevation'].values
y2 = baro['pw03'].values
x1 = g.index.values
x2 = baro.index.values
wellname, wellid = svdi.getwellid(folder+'\\'+i,wellinfo)
ylast = wellinfo[wellinfo['WellID']==wellid]['GroundElevation'].values[0] + wellinfo[wellinfo['WellID']==wellid]['Offset'].values[0] - svdi.fcl(manual[manual['WellID']== wellid],max(pd.to_datetime(g.index)))[1]
yfirst = wellinfo[wellinfo['WellID']==wellid]['GroundElevation'].values[0] + wellinfo[wellinfo['WellID']==wellid]['Offset'].values[0] - svdi.fcl(manual[manual['WellID']== wellid],min(pd.to_datetime(g.index)))[1]
xlast = pd.to_datetime(svdi.fcl(manual[manual['WellID']== wellid],max(pd.to_datetime(g.index))).name)
xfirst = pd.to_datetime(svdi.fcl(manual[manual['WellID']== wellid],min(pd.to_datetime(g.index))).name)
x4 = [xfirst,xlast]
y4 = [yfirst,ylast]
fig, ax1 = plt.subplots()
ax1.scatter(x4,y4,color='purple')
ax1.plot(x1,y1,color='blue',label='Water Level Elevation')
ax1.set_ylabel('Water Level Elevation',color='blue')
y_formatter = tick.ScalarFormatter(useOffset=False)
ax1.yaxis.set_major_formatter(y_formatter)
ax2 = ax1.twinx()
ax2.set_ylabel('Barometric Pressure (ft)', color='red')
ax2.plot(x2,y2,color='red',label='Barometric pressure (ft)')
h1, l1 = ax1.get_legend_handles_labels()
h2, l2 = ax2.get_legend_handles_labels()
ax1.legend(h1+h2, l1+l2, loc=3)
plt.xlim(xfirst-timedelta(days=3),xlast+timedelta(days=3))
plt.title('Well: ' + wellname.title() + ' ' + 'Total Drift = ' + str(g['DriftCorrection'][-1]))
pdf_pages.savefig(fig)
plt.close()
pdf_pages.close()
print("DONE!")
print("Files in "+ folder+'\\wells.pdf')
print(datetime.now())
In [24]:
form = widgets.VBox()
driftTol = widgets.FloatSlider(value=0.05, min=0.00, max=10.0, step=0.05, description='Drift Tolerance:')
form.children = [driftTol]
display(form)
In [28]:
pdf_pages = PdfPages(folder + '/wells.pdf')
for i in wellinfo.loc[:,'full_file_name']:
print folder+'/'+i
inputfile = folder +'/'+i
g, drift, wellname = svdi.imp_new_well(inputfile, wellinfo, manual, baro)
quer = "SELECT * FROM groundwater.reading where WellID = " + str(g['WellID'].values[0]) + " and (DateTime >= \'" + str(g.index.values[-1])[0:10] + "\')"
if abs(float(drift)) < driftTol.value:
if len(pd.read_sql_query(sql=quer,con=engine))<1:
g.to_csv(enteredFolder + wellname+".csv", index=False)
g.to_sql(con=engine, name='reading', if_exists='append', flavor='mysql', index=False)
print("Added to DB")
else:
print("Already Entered")
else:
g.to_csv(checkFolder + wellname+".csv", index=False, columns= ["WellID","DateTime","MeasuredLevel","Temp","BaroEfficiencyCorrected","DeltaLevel",
"MeasuredDTW","DriftCorrection","DTWBelowCasing","DTWBelowGroundSurface",
"WaterElevation","Tape","MeasuredBy"])
print("Check File")
glist = g.columns.tolist()
for j in range(len(glist)):
if 'pw' in glist[j]:
h = glist[j]
y1 = g['WaterElevation'].values
y2 = baro['pw03'].values
x1 = g.index.values
x2 = baro.index.values
wellname, wellid = svdi.getwellid(folder+'\\'+i,wellinfo)
ylast = wellinfo[wellinfo['WellID']==wellid]['GroundElevation'].values[0] + wellinfo[wellinfo['WellID']==wellid]['Offset'].values[0] - svdi.fcl(manual[manual['WellID']== wellid],max(pd.to_datetime(g.index)))[1]
yfirst = wellinfo[wellinfo['WellID']==wellid]['GroundElevation'].values[0] + wellinfo[wellinfo['WellID']==wellid]['Offset'].values[0] - svdi.fcl(manual[manual['WellID']== wellid],min(pd.to_datetime(g.index)))[1]
xlast = pd.to_datetime(svdi.fcl(manual[manual['WellID']== wellid],max(pd.to_datetime(g.index))).name)
xfirst = pd.to_datetime(svdi.fcl(manual[manual['WellID']== wellid],min(pd.to_datetime(g.index))).name)
x4 = [xfirst,xlast]
y4 = [yfirst,ylast]
fig, ax1 = plt.subplots()
ax1.scatter(x4,y4,color='purple')
ax1.plot(x1,y1,color='blue',label='Water Level Elevation')
ax1.set_ylabel('Water Level Elevation',color='blue')
y_formatter = tick.ScalarFormatter(useOffset=False)
ax1.yaxis.set_major_formatter(y_formatter)
ax2 = ax1.twinx()
ax2.set_ylabel('Barometric Pressure (ft)', color='red')
ax2.plot(x2,y2,color='red',label='Barometric pressure (ft)')
h1, l1 = ax1.get_legend_handles_labels()
h2, l2 = ax2.get_legend_handles_labels()
ax1.legend(h1+h2, l1+l2, loc=3)
plt.xlim(min(pd.to_datetime(g.index))-timedelta(days=3),max(pd.to_datetime(g.index))+timedelta(days=3))
plt.title('Well: ' + wellname.title() + ' ' + 'Total Drift = ' + str(g['DriftCorrection'][-1]))
pdf_pages.savefig(fig)
plt.close()
pdf_pages.close()
print("DONE!")
print("Files in "+ folder+'\\wells.pdf')
In [16]:
inputfile = 'M:/PROJECTS/Snake Valley Water/Transducer Data/Raw_data_archive/2016/pw06a 2016-05-03.csv'
dd = pd.read_csv(inputfile, skiprows=1, parse_dates=[[0,1]])
dd.reset_index(inplace=True)
dd.set_index('Date_ Time', inplace=True)
In [23]:
dd['date'] = pd.to_datetime(dd.index).values
In [24]:
dd['datediff'] = dd['date'].diff()
In [ ]:
dd = svdi.hourly_resample(dd)
In [ ]:
dd
In [ ]:
q = {}
for i in wellinfo.loc[:,'full_file_name']:
wellname, wellid = getwellid(folder+'\\'+i)
q[wellname] = imp_new_well(folder+'\\'+i, wellinfo, manual)
In [ ]:
q.names.to_list()
In [ ]:
alldf = ag13a.append(q)
outpath = pathlist[0] + '\\' + pathlist[1] + '\\' + pathlist[2] + '\\' + pathlist[3] + '\\' + pathlist[4] + '\\' + 'all' + '.csv'
alldf.to_csv(outpath, index=False, columns= ["WellID","DateTime","MeasuredLevel","Temp","BaroEfficiencyCorrected","DeltaLevel",
"MeasuredDTW","DriftCorrection","DTWBelowCasing","DTWBelowGroundSurface",
"WaterElevation","Tape","MeasuredBy"])
In [ ]:
dailyavgfiles = r"U:\GWP\Snake Valley Water\Transducer Data\Individual Sites\Daily Averages"
In [ ]:
well = r"U:\GWP\Snake Valley Water\Transducer Data\Individual Sites\Daily Averages\ag13b.xlsx"
In [ ]:
hourly = pd.read_excel(well,'input',parse_dates=True,index_col='DateTime')
hwl = hourly["WaterElevation"].resample('D',how='mean')
hwl = hwl.interpolate(how='Time')
hwl.plot()
In [ ]: