In [1]:
import pandas as pd
import arcpy
import os
import sys
import numpy as np
arcpy.env.overwriteOutput = True
In [15]:
xlfile = "H:/GWP/Wetland/WaterMonitoring/PiezometerData/PiezometersCorrected_2015_1.xlsx"
In [16]:
xl = pd.ExcelFile(xlfile)
In [17]:
sde_conn = "C:/Users/{:}/AppData/Roaming/ESRI/Desktop10.5/ArcCatalog/UGS_SDE.sde".format(os.environ.get('USERNAME'))
arcpy.env.workspace = sde_conn
In [18]:
def add_data(df):
gw_reading_table="UGGP.UGGPADMIN.UGS_GW_reading"
read_descr = arcpy.Describe(gw_reading_table)
fieldnames = []
for field in read_descr.fields:
fieldnames.append(field.name)
fieldnames.remove('OBJECTID')
fieldnames.remove('DELTALEVEL')
table_names = df.columns
for name in fieldnames:
if name not in table_names:
fieldnames.remove(name)
if len(fieldnames) > 0:
subset = df[fieldnames]
rowlist = subset.values.tolist()
arcpy.env.overwriteOutput = True
edit = arcpy.da.Editor(arcpy.env.workspace)
edit.startEditing(False, False)
edit.startOperation()
cursor = arcpy.da.InsertCursor(gw_reading_table, fieldnames)
for j in range(len(rowlist)):
cursor.insertRow(rowlist[j])
del cursor
edit.stopOperation()
edit.stopEditing(True)
print('Well {:} imported!'.format(df['LOCATIONID'].values[-1]))
else:
print('No data imported!')
In [19]:
xl.sheet_names
Out[19]:
In [14]:
sheet = '1005'
cols = ['READINGDATE','MEASUREDLEVEL','TEMP','BP','MEASUREDDTW','DRIFTCORRECTION','WATERELEVATION','DTWBELOWGROUNDSURFACE']
test = xl.parse(sheet, parse_cols=("A,B,C,D,E,L,M,N"), skiprows=1,header=None)
test.columns = cols
test
test['TAPE'] = 0
test['LOCATIONID'] = int(sheet)
test
Out[14]:
In [ ]:
if test.loc[test.last_valid_index(),'DRIFTCORRECTION'] < 0.3:
add_data(test)
else:
print('Well {:} drift too high'.format(test['LOCATIONID'].values[-1]))
In [7]:
xldict = {}
cols = ['READINGDATE','MEASUREDLEVEL','TEMP','BP','MEASUREDDTW','DRIFTCORRECTION','WATERELEVATION','DTWBELOWGROUNDSURFACE']
for sheet in xl.sheet_names:
#print(sheet)
if str(sheet)[:2] == '10':
if int(str(sheet)[:4]) < 1081:
try:
xldict[sheet] = xl.parse(sheet, parse_cols=("A,B,C,D,E,L,M,N"), skiprows=1,header=None)
xldict[sheet].columns = cols
xldict[sheet]['TAPE'] = 0
xldict[sheet]['LOCATIONID'] = int(sheet)
if xldict[sheet].loc[xldict[sheet].last_valid_index(),'DRIFTCORRECTION'] < 0.3:
add_data(xldict[sheet])
else:
print('Well {:} drift too high'.format(xldict[sheet]['LOCATIONID'].values[-1]))
except (RuntimeError,TypeError):
print('format exception for well {:}'.format(sheet))
pass
date piezo temp baro piezobaro elevation stickup cap measure manual id comp water elev dtw diff max min
In [ ]:
xl.parse('1003', parse_cols=("A,B,C,D,E,L,M,N"), skiprows=1,header=None)
In [12]:
import pandas as pd
df_input = pd.read_csv(r'C:\Users\PAULINKENBRANDT\PycharmProjects\Peeters_Piper\GW20130314-0057-s02.csv')
In [13]:
dat_piper = df_input[['Ca','Mg','Na','K','HCO3','CO3','Cl','SO4']].values