def rd_waps_geo(sites=None):
if sites is not None:
site_geo = rd_sql('SQL2012PROD05',
'Wells',
'WELL_DETAILS', ['WELL_NO', 'NZTMX', 'NZTMY'],
where_col='WELL_NO',
where_val=sites)
else:
site_geo = rd_sql('SQL2012PROD05', 'Wells', 'WELL_DETAILS',
['WELL_NO', 'NZTMX', 'NZTMY'])
site_geo.rename(columns={'WELL_NO': 'site'}, inplace=True)
index1 = (site_geo.NZTMX > 1300000) & (site_geo.NZTMX < 1700000) & (
site_geo.NZTMY > 5000000) & (site_geo.NZTMY < 5400000)
site_geo0 = site_geo[index1]
site_geo2 = xy_to_gpd(df=site_geo0,
id_col='site',
x_col='NZTMX',
y_col='NZTMY')
# site_geo2.loc[:, 'site'] = site_geo2.loc[:, 'site'].str.upper().str.replace(' ', '')
# site_geo2 = site_geo2.drop_duplicates()
site_geo2.loc[:, 'site'] = to_numeric(site_geo2.loc[:, 'site'],
errors='ignore')
return (site_geo2.set_index('site'))
def rd_sw_rain_geo(sites=None):
if sites is not None:
site_geo = rd_sql('SQL2012PROD05',
'Bgauging',
'RSITES',
col_names=['SiteNumber', 'NZTMX', 'NZTMY'],
where_col='SiteNumber',
where_val=sites)
else:
site_geo = rd_sql('SQL2012PROD05',
'Bgauging',
'RSITES',
col_names=['SiteNumber', 'NZTMX', 'NZTMY'])
site_geo.columns = ['site', 'NZTMX', 'NZTMY']
site_geo.loc[:, 'site'] = to_numeric(site_geo.loc[:, 'site'],
errors='ignore')
site_geo2 = xy_to_gpd(df=site_geo,
id_col='site',
x_col='NZTMX',
y_col='NZTMY')
site_geo3 = site_geo2.loc[site_geo2.site > 0, :]
site_geo3.loc[:, 'site'] = site_geo3.loc[:, 'site'].astype('int32')
return (site_geo3.set_index('site'))
def metconnect_id_loc(sites=None,
mc_server='SQL2012PROD03',
mc_db='MetConnect',
mc_site_table='RainFallPredictionSites',
mc_cols=['MetConnectID', 'SiteString', 'TidedaID'],
gis_server='SQL2012PROD05'):
"""
Function to extract the metconnect id table with geometry location.
Parameters
----------
sites : list of int or None
The site numbers to extract from the table, or None for all.
Returns
-------
GeoDataFrame
"""
### Input parameters
# hy_server = 'SQL2012PROD05'
# hy_db = 'Hydrotel'
# pts_table = 'Points'
# objs_table = 'Objects'
# sites_table = 'Sites'
#
# pts_cols = ['Point', 'Object']
# objs_cols = ['Object', 'Site']
# sites_cols = ['Site', 'ExtSysId']
loc_db = 'Bgauging'
loc_table = 'RSITES'
loc_cols = ['SiteNumber', 'NZTMX', 'NZTMY']
## Import tables
mc1 = rd_sql(mc_server, mc_db, mc_site_table, mc_cols)
mc2 = mc1[~mc1.SiteString.str.startswith('M')]
mc2.columns = ['MetConnectID', 'site_name', 'ExtSysId']
mc2 = mc2[(mc2.MetConnectID != 7) & mc2.ExtSysId.notnull()]
mc2.loc[:, 'ExtSysId'] = mc2.loc[:, 'ExtSysId'].astype(int)
# hy_pts = rd_sql(hy_server, hy_db, pts_table, pts_cols, 'Point', mc2.Point.tolist())
# hy_objs = rd_sql(hy_server, hy_db, objs_table, objs_cols, 'Object', hy_pts.Object.tolist())
# hy_sites = rd_sql(hy_server, hy_db, sites_table, sites_cols, 'Site', hy_objs.Site.tolist())
# hy_sites['ExtSysId'] = to_numeric(hy_sites['ExtSysId'])
hy_loc = rd_sql(gis_server, loc_db, loc_table, loc_cols, 'SiteNumber',
mc2.ExtSysId.tolist())
hy_loc.columns = ['ExtSysId', 'x', 'y']
# t1 = merge(mc2, hy_pts, on='Point')
# t2 = merge(t1, hy_objs, on='Object')
# t3 = merge(t2, hy_sites, on='Site')
t4 = merge(mc2, hy_loc, on='ExtSysId')
hy_xy = xy_to_gpd('MetConnectID', 'x', 'y', t4)
return (hy_xy)
def flow_sites_to_shp(sites='All',
min_flow_only=False,
export=False,
export_path='sites.shp'):
"""
Function to create a geopandas/shapefile from flow sites.
"""
### Import from databases
if min_flow_only:
min_flow_sites = rd_sql('SQL2012PROD05',
'Wells',
'"vMinimumFlowSites+Consent+Well_classes"',
col_names=[
'RefDbase', 'RefDbaseKey',
'restrictionType', 'RecordNo', 'WellNo'
],
where_col='RefDbase',
where_val=['Gauging', 'Hydrotel'])
min_flow_sites.columns = ['type', 'site', 'restr', 'crc', 'wap']
min_flow_sites['site'] = min_flow_sites['site'].astype(int)
min_flow_sites = min_flow_sites[min_flow_sites.restr == 'LowFlow']
site_geo = rd_sql('SQL2012PROD05',
'GIS',
'vGAUGING_NZTM',
col_names=['SiteNumber', 'RIVER', 'SITENAME'],
geo_col=True)
site_geo.columns = ['site', 'river', 'site_name', 'geometry']
site_geo['river'] = site_geo.river.apply(lambda x: x.title())
site_geo['site_name'] = site_geo.site_name.apply(lambda x: x.title())
site_geo['site_name'] = site_geo.site_name.apply(
lambda x: x.replace(' (Recorder)', ''))
site_geo['site_name'] = site_geo.site_name.apply(
lambda x: x.replace('Sh', 'SH'))
site_geo['site_name'] = site_geo.site_name.apply(
lambda x: x.replace('Ecs', 'ECS'))
### Select sites
if type(sites) is str:
if sites is 'All':
sites_sel_geo = site_geo
elif sites.endswith('.shp'):
poly = read_file(sites)
sites_sel_geo = sel_sites_poly(poly, site_geo)
else:
raise ValueError('If sites is a str, then it must be a shapefile.')
else:
sites_sel = select_sites(sites).astype('int32')
sites_sel_geo = site_geo[in1d(site_geo.site, sites_sel)]
if min_flow_only:
sites_sel_geo = sites_sel_geo[in1d(sites_sel_geo.site,
min_flow_sites.site.values)]
### Export and return
if export:
sites_sel_geo.to_file(export_path)
return (sites_sel_geo)
def rd_site_geo_attr(sites):
geo_attr = rd_sql('SQL2012DEV01',
'Hydro',
'site_geo_attr',
where_col='site',
where_val=sites)
return (geo_attr)
def _rd_hydro_geo_mssql(self, server, database, table, geo_dict):
"""
Function to select sites based on the geo attributes.
"""
sites1 = rd_sql(server, database, table, 'site', geo_dict)
sites2 = sites1.site.astype(str).values.tolist()
return(sites2)
def poly_import(irr_type_dict, paw_dict, paw_ratio=0.67):
"""
Function to import polygon input data. At the moment, these include irrigation type and PAW. Inputs are dictionaries that reference either an MSSQL table with a geometry column or a shapefile. If the dictionary references an sql table then the keys should be 'server', 'database', 'table', and 'column'. If the dictionary references a shapefile, then the keys should be 'shp' and 'column'. All values should be strings.
"""
if not all([isinstance(irr_type_dict, dict), isinstance(paw_dict, dict)]):
raise TypeError("'irr_type_dict' and 'paw_dict' must be dictionaries.")
if 'column' in irr_type_dict.keys():
if not isinstance(irr_type_dict['column'], str):
raise TypeError("The key 'column' must be a string.")
else:
raise TypeError("The key 'column' must be in the dictionaries.")
if 'shp' in irr_type_dict.keys():
if not isinstance(irr_type_dict['shp'], str):
raise TypeError(
"If 'shp' is in the dict, then it must be a string path to a shapefile."
)
irr1 = read_file(
irr_type_dict['shp'])[[irr_type_dict['column'], 'geometry']]
elif isinstance(irr_type_dict, dict):
irr1 = rd_sql(irr_type_dict['server'],
irr_type_dict['database'],
irr_type_dict['table'], [irr_type_dict['column']],
geo_col=True)
irr1.rename(columns={irr_type_dict['column']: 'irr_type'}, inplace=True)
if 'shp' in paw_dict.keys():
if not isinstance(paw_dict['shp'], str):
raise TypeError(
"If 'shp' is in the dict, then it must be a string path to a shapefile."
)
paw1 = read_file(paw_dict['shp'])[[paw_dict['column'], 'geometry']]
elif isinstance(paw_dict, dict):
paw1 = rd_sql(paw_dict['server'],
paw_dict['database'],
paw_dict['table'], [paw_dict['column']],
geo_col=True)
paw1.rename(columns={paw_dict['column']: 'paw'}, inplace=True)
paw1.loc[:, 'paw'] = paw1.loc[:, 'paw'] * paw_ratio
return (irr1, paw1)
def _proc_hydro_sql(self, sites_sql_fun, mtype_dict, mtype, sites=None, from_date=None, to_date=None, qual_codes=None, min_count=None, buffer_dis=0, resample_code=None, period=1, fun='mean'):
"""
Convenience function for reading in mssql data from standardized hydro tables.
"""
if isinstance(sites, GeoDataFrame):
loc1 = sites_sql_fun()
sites1 = sel_sites_poly(loc1, sites, buffer_dis).index.astype(str)
else:
sites1 = Series(sites).astype(str)
h1 = self.copy()
if isinstance(mtype_dict, (list, tuple)):
for i in range(len(mtype_dict)):
site1 = mtype_dict[i]['site_col']
sites_stmt = 'select distinct ' + site1 + ' from ' + mtype_dict[i]['table']
sites2 = rd_sql(mtype_dict[i]['server'], mtype_dict[i]['database'], stmt=sites_stmt).astype(str)[site1]
sites3 = sites2[sites2.isin(sites1)].astype(str).tolist()
if not sites3:
raise ValueError('No sites in database')
if mtype_dict[i]['qual_col'] is None:
qual_codes = None
h1 = h1._rd_hydro_mssql(sites=sites3, mtype=mtype, from_date=from_date, to_date=to_date, qual_codes=qual_codes, min_count=min_count, resample_code=resample_code, period=period, fun=fun, **mtype_dict[i])
elif isinstance(mtype_dict, dict):
site1 = mtype_dict['site_col']
sites_stmt = 'select distinct ' + site1 + ' from ' + mtype_dict['table']
sites2 = rd_sql(mtype_dict['server'], mtype_dict['database'], stmt=sites_stmt).astype(str)[site1]
sites3 = sites2[sites2.isin(sites1)].astype(str).tolist()
if not sites3:
raise ValueError('No sites in database')
if mtype_dict['qual_col'] is None:
qual_codes = None
h1 = h1._rd_hydro_mssql(sites=sites3, mtype=mtype, from_date=from_date, to_date=to_date, qual_codes=qual_codes, min_count=min_count, resample_code=resample_code, period=period, fun=fun, **mtype_dict)
elif callable(mtype_dict):
h1 = mtype_dict(h1, sites=sites1, mtype=mtype, from_date=from_date, to_date=to_date, min_count=min_count)
return(h1)
def rd_niwa_geo():
site_geo = rd_sql('SQL2012PROD05',
'GIS',
'NIWA_NZTM_NIWA_STATIONS',
col_names=['gml_id'],
geo_col=True)
site_geo.loc[:, 'gml_id'] = site_geo.loc[:, 'gml_id'].str.replace(
'stations.', '')
site_geo.loc[:, 'gml_id'] = to_numeric(site_geo.loc[:, 'gml_id'],
errors='coerse')
site_geo.columns = ['site', 'geometry']
return (site_geo.set_index('site'))
def pts_sql_join(pts, sql_codes):
"""
Function to perform spatial joins on sql tables that are polygon layers.
"""
sql1 = sql_arg()
for i in sql_codes:
poly = rd_sql(**sql1.get_dict(i))
pts = sjoin(pts, poly, how='left', op='within').drop('index_right',
axis=1)
return (pts)
def rd_henry(sites, from_date=None, to_date=None, agg_day=True, sites_by_col=False, min_filter=None, export=False,
export_path='gauge_flows.csv'):
"""
Function to read in gaugings data from the "Henry DB". Hopefully, they keep this around for a while longer.
Arguments:\n
sites -- Either a list of site names or a file path string that contains a column of site names.\n
sites_col -- If 'sites' is a path string, then the column that contains site names.\n
from_date -- A date string for the start of the data (e.g. '2010-01-01').\n
to_date -- A date string for the end of the data.\n
agg_day -- Should the gauging dates be aggregated down to the day as opposed to having the hour and minute. Gaugings are aggregated by the mean.\n
sites_by_col -- 'False' does not make a time series, rather it is organized by site, date, and gauging. 'True' creates a time series with the columns as gauging sites (will create many NAs).\n
min_filter -- Minimum number of days required for the gaugings output.
"""
def resample1(df):
df.index = df.date
df2 = df.resample('D').mean()
return (df2)
#### Fields and names for databases
## Query fields - Be sure to use single quotes for the names!!!
fields = ['SiteNo', 'SampleDate', 'Flow']
## Equivelant short names for analyses - Use these names!!!
names = ['site', 'date', 'flow']
#### Databases
### Gaugings data
server = 'SQL2012PROD03'
database = 'DataWarehouse'
table = 'DataWarehouse.dbo.F_SG_BGauging'
where_col = 'SiteNo'
## Will change to the following!!! Or stay as a duplicate...
# database1 = 'Hydstra'
# table1 = 'Hydstra.dbo.GAUGINGS'
########################################
### Read in data
sites1 = select_sites(sites).tolist()
data = rd_sql(server=server, database=database, table=table, col_names=fields, where_col=where_col,
where_val=sites1).dropna()
data.columns = names
### Aggregate duplicates
data2 = data.groupby(['site', 'date']).mean().reset_index()
### Aggregate by day
if agg_day:
data3 = data2.groupby(['site']).apply(resample1).reset_index().dropna()
else:
data3 = data2
### Filter out sites with less than min_filter
if min_filter is not None:
count1 = data3.groupby('site')['flow'].count()
count_index = count1[count1 >= min_filter].index
data3 = data3[in1d(data3.site.values, count_index)]
### Select within date range
if from_date is not None:
data3 = data3[data3.date >= from_date]
if to_date is not None:
data3 = data3[data3.date <= to_date]
### reorganize data with sites as columns and dates as index
if sites_by_col:
data4 = data3.pivot(index='date', columns='site').xs('flow', axis=1).round(4)
else:
data4 = data3.round(4)
if export:
if sites_by_col:
data4.to_csv(export_path)
else:
data4.to_csv(export_path, index=False)
return (data4)
def rd_hydrotel(sites, mtype='river_flow_cont_raw', from_date=None, to_date=None, resample_code='D', period=1,
fun='mean', val_round=3, min_count=None, pivot=False, export_path=None):
"""
Function to extract time series data from the hydrotel database.
Parameters
----------
sites : list, array, dataframe, or str
Site list or a str path to a single column csv file of site names/numbers.
mtype : str
'flow_tel', 'gwl_tel', 'precip_tel', 'swl_tel', or 'wtemp_tel'.
from_date : str or None
The start date in the format '2000-01-01'.
to_date : str or None
The end date in the format '2000-01-01'.
resample_code : str
The Pandas time series resampling code. e.g. 'D' for day, 'W' for week, 'M' for month, etc.
period : int
The number of resampling periods. e.g. period = 2 and resample = 'D' would be to resample the values over a 2 day period.
fun : str
The resampling function. i.e. mean, sum, count, min, or max. No median yet...
val_round : int
The number of decimals to round the values.
pivot : bool
Should the output be pivotted into wide format?
export_path : str or None
The path and file name to be saved.
Returns
-------
Series or DataFrame
A MultiIndex Pandas Series if pivot is False and a DataFrame if True
"""
#### mtypes dict
mtypes_dict = {'river_flow_cont_raw': 'Flow Rate', 'aq_wl_cont_raw': 'Water Level',
'atmos_precip_cont_raw': 'Rainfall Depth', 'river_wl_cont_raw': 'Water Level',
'river_wtemp_cont_raw': 'Water Temperature'}
#### Database parameters
server = 'SQL2012PROD05'
database = 'Hydrotel'
data_tab = 'Hydrotel.dbo.Samples'
points_tab = 'Hydrotel.dbo.Points'
objects_tab = 'Hydrotel.dbo.Objects'
mtypes_tab = 'Hydrotel.dbo.ObjectVariants'
sites_tab = 'Hydrotel.dbo.Sites'
data_col = ['Point', 'DT', 'SampleValue']
points_col = ['Point', 'Object']
objects_col = ['Object', 'Site', 'Name', 'ObjectVariant']
mtypes_col = ['ObjectVariant', 'Name']
sites_col = ['Site', 'Name', 'ExtSysId']
#### Import data and select the correct sites
sites = select_sites(sites)
if mtype == 'atmos_precip_cont_raw':
site_ob1 = rd_sql(server, database, objects_tab, ['Site', 'ExtSysId'], 'ExtSysId',
sites.astype('int32').tolist())
site_val0 = rd_sql(server, database, sites_tab, ['Site', 'Name'], 'Site', site_ob1.Site.tolist())
site_val1 = merge(site_val0, site_ob1, on='Site')
elif mtype == 'aq_wl_cont_raw':
site_val0 = rd_sql(server, database, sites_tab, ['Site', 'Name'])
site_val0.loc[:, 'Name'] = site_val0.apply(lambda x: x.Name.split(' ')[0], axis=1)
site_val1 = site_val0[site_val0.Name.isin(sites)]
site_val1.loc[:, 'ExtSysId'] = site_val1.loc[:, 'Name']
else:
site_val1 = rd_sql(server, database, sites_tab, sites_col, 'ExtSysId', sites.astype('int32').tolist())
if site_val1.empty:
raise ValueError('No site(s) in database')
site_val1.loc[:, 'ExtSysId'] = to_numeric(site_val1.loc[:, 'ExtSysId'], errors='ignore')
site_val = site_val1.Site.astype('int32').tolist()
if isinstance(mtype, (list, ndarray, Series)):
mtypes = [mtypes_dict[i] for i in mtype]
elif isinstance(mtype, str):
mtypes = [mtypes_dict[mtype]]
else:
raise ValueError('mtype must be a str, list, ndarray, or Series.')
mtypes_val = rd_sql(server, database, mtypes_tab, mtypes_col, 'Name', mtypes)
where_col = {'Site': site_val, 'ObjectVariant': mtypes_val.ObjectVariant.astype('int32').tolist()}
object_val1 = rd_sql(server, database, objects_tab, objects_col, where_col)
if mtype == 'gwl_tel':
object_val1 = object_val1[object_val1.Name == 'Water Level']
if mtype == 'precip_tel':
object_val1 = object_val1[object_val1.Name == 'Rainfall']
object_val = object_val1.Object.values.astype(int).tolist()
#### Rearrange data
point_val1 = rd_sql(server, database, points_tab, points_col, where_col='Object', where_val=object_val)
point_val = point_val1.Point.values.astype(int).tolist()
#### Big merge
comp_tab1 = merge(site_val1, object_val1[['Object', 'Site']], on='Site')
comp_tab2 = merge(comp_tab1, point_val1, on='Object')
comp_tab2.set_index('Point', inplace=True)
#### Pull out the data
### Make SQL statement
data1 = rd_sql_ts(server, database, data_tab, 'Point', 'DT', 'SampleValue', resample_code, period, fun, val_round,
{'Point': point_val}, from_date=from_date, to_date=to_date, min_count=min_count)['SampleValue']
data1.index.names = ['site', 'time']
data1.name = 'value'
site_numbers = [comp_tab2.loc[i, 'ExtSysId'] for i in data1.index.levels[0]]
data1.index.set_levels(site_numbers, level='site', inplace=True)
if pivot:
data3 = data1.unstack(0)
else:
data3 = data1
#### Export and return
if export_path is not None:
save_df(data3, export_path)
return data3
def rd_squalarc(sites,
mtypes=None,
from_date=None,
to_date=None,
convert_dtl=False,
dtl_method=None,
export_path=None):
"""
Function to read in "squalarc" data. Which is atually stored in the mssql db.
Arguments:\n
sites -- The site names as a list, array, csv with the first column as the site names, or a polygon shapefile of the area of interest.\n
mtypes -- A list of measurement type names to be in the output. Leaving it empty returns all mtypes.\n
from_date -- A start date string in of '2010-01-01'.\n
to_date -- A end date string in of '2011-01-01'.\n
convert_dtl -- Should values under the detection limit be converted to numeric?\n
dtl_method -- The method to use to convert values under a detection limit to numeric. None or 'standard' takes half of the detection limit. 'trend' is meant as an output for trend analysis with includes an additional column dtl_ratio referring to the ratio of values under the detection limit.
"""
#### Read in sites
sites1 = select_sites(sites)
#### Extract by polygon
if isinstance(sites1, GeoDataFrame):
## Surface water sites
sw_sites_tab = rd_sql('SQL2012PROD05',
'Squalarc',
'SITES',
col_names=['SITE_ID', 'NZTMX', 'NZTMY'])
sw_sites_tab.columns = ['site', 'NZTMX', 'NZTMY']
gdf_sw_sites = xy_to_gpd('site', 'NZTMX', 'NZTMY', sw_sites_tab)
sites1a = sites1.to_crs(gdf_sw_sites.crs)
sw_sites2 = sel_sites_poly(gdf_sw_sites, sites1a).drop('geometry',
axis=1)
## Groundwater sites
gw_sites_tab = rd_sql('SQL2012PROD05',
'Wells',
'WELL_DETAILS',
col_names=['WELL_NO', 'NZTMX', 'NZTMY'])
gw_sites_tab.columns = ['site', 'NZTMX', 'NZTMY']
gdf_gw_sites = xy_to_gpd('site', 'NZTMX', 'NZTMY', gw_sites_tab)
gw_sites2 = sel_sites_poly(gdf_gw_sites, sites1a).drop('geometry',
axis=1)
sites2 = sw_sites2.site.append(gw_sites2.site).astype(str).tolist()
else:
sites2 = Series(sites1, name='site').astype(str).tolist()
#### Extract the rest of the data
if len(sites2) > 10000:
n_chunks = int(ceil(len(sites2) * 0.0001))
sites3 = [sites2[i::n_chunks] for i in xrange(n_chunks)]
samples_tab = DataFrame()
for i in sites3:
samples_tab1 = rd_sql('SQL2012PROD05',
'Squalarc',
'"SQL_SAMPLE_METHODS+"',
col_names=[
'Site_ID', 'SAMPLE_NO', 'ME_TYP',
'Collect_Date', 'Collect_Time',
'PA_NAME', 'PARAM_UNITS', 'SRESULT'
],
where_col='Site_ID',
where_val=i)
samples_tab1.columns = [
'site', 'sample_id', 'source', 'date', 'time', 'parameter',
'units', 'val'
]
samples_tab1.loc[:,
'source'] = samples_tab1.loc[:,
'source'].str.lower(
)
samples_tab = concat([samples_tab, samples_tab1])
else:
samples_tab = rd_sql('SQL2012PROD05',
'Squalarc',
'"SQL_SAMPLE_METHODS+"',
col_names=[
'Site_ID', 'SAMPLE_NO', 'ME_TYP',
'Collect_Date', 'Collect_Time', 'PA_NAME',
'PARAM_UNITS', 'SRESULT'
],
where_col='Site_ID',
where_val=sites2)
samples_tab.columns = [
'site', 'sample_id', 'source', 'date', 'time', 'parameter',
'units', 'val'
]
samples_tab.loc[:, 'source'] = samples_tab.loc[:, 'source'].str.lower()
samples_tab2 = samples_tab.copy()
num_test = to_numeric(samples_tab2.loc[:, 'time'], 'coerce')
samples_tab2.loc[num_test.isnull(), 'time'] = '0000'
samples_tab2.loc[:,
'time'] = samples_tab2.loc[:,
'time'].str.replace('.', '')
samples_tab2 = samples_tab2[samples_tab2.date.notnull()]
# samples_tab2.loc[:, 'time'] = samples_tab2.loc[:, 'time'].str.replace('9999', '0000')
time1 = to_datetime(samples_tab2.time, format='%H%M', errors='coerce')
time1[time1.isnull()] = Timestamp('2000-01-01 00:00:00')
datetime1 = to_datetime(
samples_tab2.date.dt.date.astype(str) + ' ' +
time1.dt.time.astype(str))
samples_tab2.loc[:, 'date'] = datetime1
samples_tab2 = samples_tab2.drop('time', axis=1)
samples_tab2.loc[samples_tab2.val.isnull(), 'val'] = nan
samples_tab2.loc[samples_tab2.val == 'N/A', 'val'] = nan
#### Select within time range
if isinstance(from_date, str):
samples_tab2 = samples_tab2[samples_tab2['date'] >= from_date]
if isinstance(to_date, str):
samples_tab2 = samples_tab2[samples_tab2['date'] <= to_date]
if mtypes is not None:
mtypes1 = select_sites(mtypes)
data = samples_tab2[samples_tab2.parameter.isin(mtypes1)].reset_index(
drop=True)
else:
data = samples_tab2.reset_index(drop=True)
#### Correct poorly typed in site names
data.loc[:, 'site'] = data.loc[:, 'site'].str.upper().str.replace(' ', '')
#### Convert detection limit values
if convert_dtl:
less1 = data['val'].str.match('<')
if less1.sum() > 0:
less1.loc[less1.isnull()] = False
data2 = data.copy()
data2.loc[less1,
'val'] = to_numeric(data.loc[less1, 'val'].str.replace(
'<', ''),
errors='coerce') * 0.5
if dtl_method in (None, 'standard'):
data3 = data2
if dtl_method == 'trend':
df1 = data2.loc[less1]
count1 = data.groupby('parameter')['val'].count()
count1.name = 'tot_count'
count_dtl = df1.groupby('parameter')['val'].count()
count_dtl.name = 'dtl_count'
count_dtl_val = df1.groupby('parameter')['val'].nunique()
count_dtl_val.name = 'dtl_val_count'
combo1 = concat([count1, count_dtl, count_dtl_val],
axis=1,
join='inner')
combo1['dtl_ratio'] = (combo1['dtl_count'] /
combo1['tot_count']).round(2)
## conditionals
# param1 = combo1[(combo1['dtl_ratio'] <= 0.4) | (combo1['dtl_ratio'] == 1)]
# under_40 = data['parameter'].isin(param1.index)
param2 = combo1[(combo1['dtl_ratio'] > 0.4)
& (combo1['dtl_val_count'] != 1)]
over_40 = data['parameter'].isin(param2.index)
## Calc detection limit values
data3 = merge(data,
combo1['dtl_ratio'].reset_index(),
on='parameter',
how='left')
data3.loc[:, 'val_dtl'] = data2['val']
max_dtl_val = data2[over_40 & less1].groupby(
'parameter')['val'].transform('max')
max_dtl_val.name = 'dtl_val_max'
data3.loc[over_40 & less1, 'val_dtl'] = max_dtl_val
else:
data3 = data
else:
data3 = data
#### Return and export
if isinstance(export_path, str):
data3.to_csv(export_path, encoding='utf-8', index=False)
return (data3)
def rd_ht_wq_data(hts,
sites=None,
mtypes=None,
start=None,
end=None,
dtl_method=None,
output_site_data=False,
mtype_params=None,
sample_params=None):
"""
Function to read data from an hts file and optionally select specific sites and aggregate the data.
Parameters
----------
hts : str
Path to the hts file.
sites : list
A list of site names within the hts file.
mtypes : list
A list of measurement types that should be returned.
start : str
The start date to retreive from the data in ISO format (e.g. '2011-11-30 00:00').
end : str
The end date to retreive from the data in ISO format (e.g. '2011-11-30 00:00').
dtl_method : None, 'standard', 'trend'
The method to use to convert values under a detection limit to numeric. None does no conversion. 'standard' takes half of the detection limit. 'trend' is meant as an output for trend analysis with includes an additional column dtl_ratio referring to the ratio of values under the detection limit.
output_site_data : bool
Should the site data be output?
Returns
-------
DataFrame
"""
# agg_unit_dict = {'l/s': 1, 'm3/s': 1, 'm3/hour': 1, 'mm': 1, 'm3': 4}
# unit_convert = {'l/s': 0.001, 'm3/s': 1, 'm3/hour': 1, 'mm': 1, 'm3': 4}
sites1 = select_sites(sites)
#### Extract by polygon
if isinstance(sites1, GeoDataFrame):
## Surface water sites
sw_sites_tab = rd_sql('SQL2012PROD05',
'Squalarc',
'SITES',
col_names=['SITE_ID', 'NZTMX', 'NZTMY'])
sw_sites_tab.columns = ['site', 'NZTMX', 'NZTMY']
gdf_sw_sites = xy_to_gpd('site', 'NZTMX', 'NZTMY', sw_sites_tab)
sites1a = sites1.to_crs(gdf_sw_sites.crs)
sw_sites2 = sel_sites_poly(gdf_sw_sites, sites1a).drop('geometry',
axis=1)
## Groundwater sites
gw_sites_tab = rd_sql('SQL2012PROD05',
'Wells',
'WELL_DETAILS',
col_names=['WELL_NO', 'NZTMX', 'NZTMY'])
gw_sites_tab.columns = ['site', 'NZTMX', 'NZTMY']
gdf_gw_sites = xy_to_gpd('site', 'NZTMX', 'NZTMY', gw_sites_tab)
gw_sites2 = sel_sites_poly(gdf_gw_sites, sites1a).drop('geometry',
axis=1)
sites2 = sw_sites2.site.append(gw_sites2.site).astype(str).tolist()
else:
sites2 = sites1
### First read all of the sites in the hts file and select the ones to be read
sites_df = rd_hilltop_sites(hts,
sites=sites2,
mtypes=mtypes,
rem_wq_sample=False)
### Open the hts file
wqr = Dispatch("Hilltop.WQRetrieval")
dfile = Dispatch("Hilltop.DataFile")
try:
dfile.Open(hts)
except ValueError:
print(dfile.errmsg)
### Iterate through he hts file
df_lst = []
for i in sites_df.index:
site = sites_df.loc[i, 'site']
mtype = sites_df.loc[i, 'mtype']
if mtype == 'WQ Sample':
continue
wqr = dfile.FromWQSite(site, mtype)
## Set up start and end times and aggregation initiation
if (start is None):
start1 = wqr.DataStartTime
else:
start1 = start
if end is None:
end1 = wqr.DataEndTime
else:
end1 = end
wqr.FromTimeRange(start1, end1)
## Extract data
data = []
time = []
test_params = sites_df[sites_df.site == site].mtype.unique()
if ('WQ Sample' in test_params) & (isinstance(mtype_params, list)
| isinstance(sample_params, list)):
sample_p = []
mtype_p = []
while wqr.GetNext:
data.append(wqr.value)
time.append(str(pytime_to_datetime(wqr.time)))
sample_p.append({
sp: wqr.params(sp).encode('ascii', 'ignore')
for sp in sample_params
})
mtype_p.append({
mp: wqr.params(mp).encode('ascii', 'ignore')
for mp in mtype_params
})
else:
while wqr.GetNext:
data.append(wqr.value)
time.append(str(pytime_to_datetime(wqr.time)))
if data:
df_temp = DataFrame({
'time': time,
'data': data,
'site': site,
'mtype': mtype
})
if sample_p:
df_temp = concat(
[df_temp, DataFrame(sample_p),
DataFrame(mtype_p)], axis=1)
df_lst.append(df_temp)
dfile.Close()
wqr.close()
if df_lst:
data = concat(df_lst)
data.loc[:, 'time'] = to_datetime(data.loc[:, 'time'])
data1 = to_numeric(data.loc[:, 'data'], errors='coerce')
data.loc[data1.notnull(), 'data'] = data1[data1.notnull()]
# data.loc[:, 'data'].str.replace('*', '')
data = data.reset_index(drop=True)
#### Convert detection limit values
if dtl_method is not None:
less1 = data['data'].str.match('<')
if less1.sum() > 0:
less1.loc[less1.isnull()] = False
data2 = data.copy()
data2.loc[less1, 'data'] = to_numeric(
data.loc[less1, 'data'].str.replace('<', ''),
errors='coerce') * 0.5
if dtl_method == 'standard':
data3 = data2
if dtl_method == 'trend':
df1 = data2.loc[less1]
count1 = data.groupby('mtype')['data'].count()
count1.name = 'tot_count'
count_dtl = df1.groupby('mtype')['data'].count()
count_dtl.name = 'dtl_count'
count_dtl_val = df1.groupby('mtype')['data'].nunique()
count_dtl_val.name = 'dtl_val_count'
combo1 = concat([count1, count_dtl, count_dtl_val],
axis=1,
join='inner')
combo1['dtl_ratio'] = (combo1['dtl_count'] /
combo1['tot_count']).round(2)
## conditionals
param2 = combo1[(combo1['dtl_ratio'] > 0.4)
& (combo1['dtl_val_count'] != 1)]
over_40 = data['mtype'].isin(param2.index)
## Calc detection limit values
data3 = merge(data,
combo1['dtl_ratio'].reset_index(),
on='mtype',
how='left')
data3.loc[:, 'data_dtl'] = data2['data']
max_dtl_val = data2[over_40 & less1].groupby(
'mtype')['data'].transform('max')
max_dtl_val.name = 'dtl_data_max'
data3.loc[over_40 & less1, 'data_dtl'] = max_dtl_val
else:
data3 = data
else:
data3 = data
if output_site_data:
sites_df = sites_df[~(sites_df.mtype == 'WQ Sample')]
return (data3, sites_df)
else:
return (data3)