In [ ]:
%matplotlib inline
import pandas as pd
import unicodedata
import numpy as np
import os
import glob
import re
import xmltodict
import matplotlib.pyplot as plt
import matplotlib.dates as dates
import matplotlib.ticker as tick
from matplotlib.backends.backend_pdf import PdfPages
import snake

from datetime import datetime
from pylab import rcParams
rcParams['figure.figsize'] = 15, 10

In [ ]:
Drive = 'U'

Use g[wellinfo[wellinfo['Well']==wellname]['closest_baro']] instead if you want to match the closest barometer to the data

Well Information


In [ ]:
folder = Drive + ':\\GWP\\Groundwater\\Courthouse_Wash_Transducer_Data'
wellinfofile = Drive + ':\\GWP\\Groundwater\\Courthouse_Wash_Transducer_Data\\wellinfo.csv'

In [ ]:
wellinfo = pd.read_csv(wellinfofile,header=0,index_col=0)
wellinfo["G_Elev_m"] = wellinfo["GroundElevation"]/3.2808
wellinfo["Well"] = wellinfo['Well'].apply(lambda x: str(x).lower().strip())
wellinfo['WellID'] = wellinfo.index.values

In [ ]:
wellinfo

Manual Water Levels


In [ ]:
manualwls = Drive + ':\\GWP\\Groundwater\\Courthouse_Wash_Transducer_Data\\manual_measurements.csv'
manual = pd.read_csv(manualwls, skiprows=0, parse_dates=1, index_col="DateTime", engine="python")

Barometric Pressure Data

Compilation of Barometric Pressure Data


In [ ]:
barofile = folder + '\\*baro*'
barodata = snake.compilation(barofile)

In [ ]:
barodata['Level']
barodata.tail(15)

In [ ]:
barodata = snake.hourly_resample(barodata)

In [ ]:
infile = Drive + ":\\GWP\\Groundwater\\Courthouse_Wash_Transducer_Data\\"
pathlist = os.path.splitext(infile)[0].split('\\')
barodata.to_csv(pathlist[0] + '\\' + pathlist[1] + '\\' + pathlist[2] + '\\' + pathlist[3] + '\\' + pathlist[4] + '\\' + 'baro' + '.csv')

In [ ]:
barodata[['Level']].plot()

In [ ]:
folder = Drive + ":\\GWP\\Groundwater\\Courthouse_Wash_Transducer_Data"
wellinfo = snake.make_files_table(folder, wellinfo)
wellinfo.tail(n=3)

Water Level Tranducer Data

Export and Plot Data

Export Manual Data


In [ ]:
manual.reset_index(inplace=True)
manual.set_index('WellID',inplace=True)

manual["MeasuredLevel"] = np.nan
manual["Temp"] = np.nan
manual["BaroEfficiencyCorrected"] = np.nan 
manual["DeltaLevel"] = np.nan
manual["DriftCorrection"] = np.nan
manual["MeasuredBy"] = np.nan
manual["Tape"] = 1
manual["DTWBelowGroundSurface"] = np.nan
manual["WaterElevation"] = np.nan
#manualrecent["DTWBelowGroundSurface"] = np.nan
manlist= [int(i) for i in manual.index.tolist()]

print manlist
for i in manlist:
    try:
        manual.loc[i,"DTWBelowCasing"] = manual.loc[i,"MeasuredDTW"]
        manual.loc[i,"DTWBelowGroundSurface"] = manual.loc[i,"MeasuredDTW"] - wellinfo.loc[i,"Offset"]
        manual.loc[i,"WaterElevation"] = wellinfo.loc[i,'GroundElevation'] - manual.loc[i,"DTWBelowGroundSurface"]
    except(KeyError):
        pass

outpath = pathlist[0] + '\\' + pathlist[1] + '\\' + pathlist[2] + '\\' + pathlist[3] + '\\' + pathlist[4] + '\\' + 'Manual' + '.csv'  
manual.to_csv(outpath, index=True, columns= ["DateTime","MeasuredLevel","Temp","BaroEfficiencyCorrected","DeltaLevel",
                                             "MeasuredDTW","DriftCorrection","DTWBelowCasing","DTWBelowGroundSurface",
                                             "WaterElevation","Tape","MeasuredBy"])

Import All Files


In [ ]:
pdf_pages = PdfPages(folder+'wells.pdf')
for i in wellinfo.loc[:,'full_file_name']:
    print i
    g = snake.imp_new_well(folder+'\\'+i, wellinfo, manual, barodata)
    glist = g.columns.tolist()
    for j in range(len(glist)):
        if 'pw' in glist[j]:
            h = glist[j]
    y1 = g['WaterElevation'].values
    y2 = g[h].values
    x1 = g['DateTime'].values
    wellname, wellid = snake.getwellid(folder+'\\'+i,wellinfo1)
    ylast = wellinfo[wellinfo['WellID']==wellid]['GroundElevation'].values[0] + wellinfo[wellinfo['WellID']==wellid]['Offset'].values[0] - 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] - fcl(manual[manual['WellID']== wellid],min(g.index.to_datetime()))[1]
    xlast = (fcl(manual[manual['WellID']== wellid],max(g.index.to_datetime()))).name.to_datetime()
    xfirst = (fcl(manual[manual['WellID']== wellid],min(g.index.to_datetime()))).name.to_datetime()
    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(x1,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.title('Well: ' + wellname.title() + '  ' + 'Total Drift = ' + str(g['DriftCorrection'][-1]))
    pdf_pages.savefig(fig)
    fig.close()
pdf_pages.close()

In [ ]:
infile = Drive + ':\\GWP\\Groundwater\\Courthouse_Wash_Transducer_Data\\chwnav 2015_06_12.xle'
with open(infile) as fd:
    # parse xml
    obj = xmltodict.parse(fd.read(),encoding="ISO-8859-1")
# navigate through xml to the data
wellrawdata = obj['Body_xle']['Data']['Log']
# convert xml data to pandas dataframe
f = pd.DataFrame(wellrawdata)
# get header names and apply to the pandas dataframe
f[str(obj['Body_xle']['Ch2_data_header']['Identification']).title()] = f['ch2']

tempunit = (obj['Body_xle']['Ch2_data_header']['Unit'])
if tempunit == 'Deg C' or tempunit == u'\N{DEGREE SIGN}' + u'C':
    f[str(obj['Body_xle']['Ch2_data_header']['Identification']).title()] = f['ch2'].convert_objects(convert_numeric=True)
elif tempunit == 'Deg F' or tempunit == u'\N{DEGREE SIGN}' + u'F': 
    f[str(obj['Body_xle']['Ch2_data_header']['Identification']).title()] = f['ch2'].convert_objects(convert_numeric=True)*0.33456

unit = str(obj['Body_xle']['Ch1_data_header']['Unit']).lower()
if unit == "feet" or unit == "ft":
    f[str(obj['Body_xle']['Ch1_data_header']['Identification']).title()] = f['ch1'].convert_objects(convert_numeric=True)
elif unit == "kpa":
    f[str(obj['Body_xle']['Ch1_data_header']['Identification']).title()] = f['ch1'].convert_objects(convert_numeric=True)*0.33456
elif unit == "mbar":
    f[str(obj['Body_xle']['Ch1_data_header']['Identification']).title()] = f['ch1'].convert_objects(convert_numeric=True)*0.0334552565551
elif unit == "psi":
    f[str(obj['Body_xle']['Ch1_data_header']['Identification']).title()] = f['ch1'].convert_objects(convert_numeric=True)*2.306726
elif unit == "m" or unit == "meters":
    f[str(obj['Body_xle']['Ch1_data_header']['Identification']).title()] = f['ch1'].convert_objects(convert_numeric=True)*3.28084
else:
    f[str(obj['Body_xle']['Ch1_data_header']['Identification']).title()] = f['ch1'].convert_objects(convert_numeric=True)
    print "Unknown Units"
# add extension-free file name to dataframe
f['name'] = snake.getfilename(infile)
# combine Date and Time fields into one field
f['DateTime'] = pd.to_datetime(f.apply(lambda x: x['Date'] + ' ' + x['Time'], 1))
f[str(obj['Body_xle']['Ch1_data_header']['Identification']).title()] = f[str(obj['Body_xle']['Ch1_data_header']['Identification']).title()].convert_objects(convert_numeric=True)
f[str(obj['Body_xle']['Ch2_data_header']['Identification']).title()] = f[str(obj['Body_xle']['Ch2_data_header']['Identification']).title()].convert_objects(convert_numeric=True)
f = f.reset_index()
f = f.set_index('DateTime')
f = f.drop(['Date','Time','@id','ch1','ch2','index','ms'],axis=1)

In [ ]:
f

In [ ]:
import snake
infile = Drive + ':\\GWP\\Groundwater\\Courthouse_Wash_Transducer_Data\\chwnav 2015_06_12.xle'
b = snake.hourly_resample(barodata, 0)
f = snake.new_xle_imp(infile)
f = snake.hourly_resample(f, 0)
f

In [ ]:
f = snake.new_xle_imp(infile)
bse =57
f = f.resample('1Min', how='first', closed='left')
#f
f = f.interpolate(method='time')
f = f.resample('60Min', how='first', closed='left', base=bse)
f

Import One File


In [ ]:
inputfile = r"U:\GWP\Groundwater\Courthouse_Wash_Transducer_Data\chwnav 2015_06_12.xle"

g = snake.imp_new_well(inputfile, wellinfo, manual, barodata)
glist = g.columns.tolist()
for j in range(len(glist)):
        if 'pw' in glist[j]:
            h = glist[j]
y1 = g['WaterElevation'].values
y2 = g[h].values
x1 = g['DateTime'].values

wellname, wellid = getwellid(inputfile)
ylast = wellinfo[wellinfo['WellID']==wellid]['GroundElevation'].values[0] + wellinfo[wellinfo['WellID']==wellid]['Offset'].values[0] - 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] - fcl(manual[manual['WellID']== wellid],min(g.index.to_datetime()))[1]
xlast = (fcl(manual[manual['WellID']== wellid],max(g.index.to_datetime()))).name.to_datetime()
xfirst = (fcl(manual[manual['WellID']== wellid],min(g.index.to_datetime()))).name.to_datetime()
x4 = [xfirst,xlast]
y4 = [yfirst,ylast]

fig, ax1 = plt.subplots()
ax1.scatter(x4,y4,color='purple')
ax1.plot(x1,y1,color='red')
y_formatter = tick.ScalarFormatter(useOffset=False)
ax1.yaxis.set_major_formatter(y_formatter)
ax2 = ax1.twinx()
ax2.plot(x1,y2,color='blue')
plt.title(getfilename(inputfile)+'  '+str(g['DriftCorrection'][-1]))
plt.show()

Combine Output


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 [ ]: