def flow_datasets_all(self, rec_data_code='Primary'):
"""
"""
## Get dataset types
datasets1 = mssql.rd_sql(param['input']['ts_server'], param['input']['ts_database'], param['input']['ts_dataset_table'], where_in={'Feature': ['River'], 'MeasurementType': ['Flow'], 'DataCode': ['Primary', 'RAW']})
man_datasets1 = datasets1[(datasets1['CollectionType'] == 'Manual Field') & (datasets1['DataCode'] == 'Primary')].copy()
rec_datasets1 = datasets1[(datasets1['CollectionType'] == 'Recorder') & (datasets1['DataCode'] == rec_data_code)].copy()
## Get ts summaries
man_summ1 = mssql.rd_sql(param['input']['ts_server'], param['input']['ts_database'], param['input']['ts_summ_table'], ['ExtSiteID', 'DatasetTypeID', 'Min', 'Median', 'Mean', 'Max', 'Count', 'FromDate', 'ToDate'], where_in={'DatasetTypeID': man_datasets1['DatasetTypeID'].tolist()}).sort_values('ToDate')
man_summ2 = man_summ1.drop_duplicates(['ExtSiteID'], keep='last').copy()
man_summ2['CollectionType'] = 'Manual Field'
rec_summ1 = mssql.rd_sql(param['input']['ts_server'], param['input']['ts_database'], param['input']['ts_summ_table'], ['ExtSiteID', 'DatasetTypeID', 'Min', 'Median', 'Mean', 'Max', 'Count', 'FromDate', 'ToDate'], where_in={'DatasetTypeID': rec_datasets1['DatasetTypeID'].tolist()}).sort_values('ToDate')
rec_summ2 = rec_summ1.drop_duplicates(['ExtSiteID'], keep='last').copy()
rec_summ2['CollectionType'] = 'Recorder'
## Combine
summ2 = pd.concat([man_summ2, rec_summ2], sort=False)
summ2['FromDate'] = pd.to_datetime(summ2['FromDate'])
summ2['ToDate'] = pd.to_datetime(summ2['ToDate'])
## Add in site info
sites1 = mssql.rd_sql(param['input']['ts_server'], param['input']['ts_database'], param['input']['sites_table'], ['ExtSiteID', 'NZTMX', 'NZTMY', 'SwazGroupName', 'SwazName'])
summ3 = pd.merge(summ2, sites1, on='ExtSiteID')
## Assign objects
setattr(self, 'sites', sites1)
setattr(self, 'rec_data_code', rec_data_code)
setattr(self, 'summ_all', summ3)
def test_del_rows():
## Write table
mssql.to_mssql(df1, server, database, table)
## Read table
beta1 = mssql.rd_sql(server, database, table)
## Delete parts
mssql.del_mssql_table_rows(server, database, table, pk_df=df2)
## Test
beta2 = mssql.rd_sql(server, database, table)
beta3 = beta1.set_index(['pk1', 'pk2'])
beta3.index.isin(df2.set_index(['pk1', 'pk2']).index)
beta4 = beta3.loc[~beta3.index.isin(df2.set_index(['pk1', 'pk2']).index)].reset_index()
## Remove table
engine = mssql.create_engine('mssql', server, database)
conn = engine.connect()
trans = conn.begin()
conn.execute("IF OBJECT_ID(" + str([str(table)])[1:-1] + ", 'U') IS NOT NULL drop table " + table)
trans.commit()
conn.close()
assert all(beta2 == beta4)
def create_site_mtype(server, database, site, ref_point, new_mtype):
"""
Function to create a new mtype for a specific site. A reference point number of an existing mtype of the same site must be used for creation. Run get_sites_mtypes to find a good reference point.
Parameters
----------
server : str
The server where the Hydrotel database lays.
database : str
The name of the Hydrotel database.
site : str
The site to create the new mtype on.
ref_point : int
The reference point from another mtype on the same site.
new_mtype : str
The new mtype name. Must be unique for the associated site.
Returns
-------
DataFrame
New object and point values extracted by the get_sites_mtypes function.
"""
## Checks
site_mtypes = get_sites_mtypes(server, database, sites=site).reset_index()
if not (site_mtypes.Point == ref_point).any():
raise ValueError('model_point must be a Point that exists within the mtypes of the site')
if (site_mtypes.MType == new_mtype.lower()).any():
raise ValueError('new_name already exists as an mtype, please use a different name')
## Import object/point data
point_val = rd_sql(server, database, points_tab, where_in={'Point': [ref_point]})
obj_val = rd_sql(server, database, objects_tab, where_in={'Object': point_val.Object.tolist()})
treeindex1 = int(rd_sql(server, database, stmt='select max(TreeIndex) from {tab} where Site = {site}'.format(tab=objects_tab, site=int(obj_val.Site))).iloc[0])
## Assign new object data
obj_val2 = obj_val.drop('Object', axis=1).copy()
obj_val2['Name'] = new_mtype
obj_val2['TreeIndex'] = treeindex1 + 1
to_mssql(obj_val2, server, database, objects_tab)
## Find out what the new object value is
new_obj = int(rd_sql(server, database, objects_tab, where_in={'Site': obj_val.Site.tolist(), 'Name': [new_mtype]}).Object)
## Assign new point data
point_val2 = point_val.drop('Point', axis=1).copy()
point_val2['Name'] = new_mtype
point_val2['Object'] = new_obj
to_mssql(point_val2, server, database, points_tab)
## Return new values
site_mtypes = get_sites_mtypes(server, database, sites=site, mtypes=new_mtype)
return site_mtypes
def telem_corr_sites(site_num=None, username=None, password=None):
"""
Function to determine if sites are telemetered or are correlated from telemetered sites in Hydrotel. Output is a list of correlated sites.
Parameters
----------
site_num: list of str
Site numbers for the selection.
Returns
-------
List of str
List of site numbers that are correlated sites.
"""
### Parameters
sites_tab = 'Sites'
obj_tab = 'Objects'
sites_fields = ['Site', 'ExtSysID']
obj_fields = ['Site', 'Name']
where_dict = {'Name': ['calculated flow']}
### Read in data
if isinstance(site_num, list):
sites = rd_sql(hydrotel_server,
hydrotel_db,
sites_tab,
sites_fields, {'ExtSysID': site_num},
username=username,
password=password)
sites['ExtSysID'] = pd.to_numeric(sites['ExtSysID'], 'coerce')
else:
sites = rd_sql(hydrotel_server,
hydrotel_db,
sites_tab,
sites_fields,
username=username,
password=password)
sites['ExtSysID'] = pd.to_numeric(sites['ExtSysID'], 'coerce')
sites = sites[sites.ExtSysID.notnull()]
sites['Site'] = sites['Site'].astype('int32')
where_dict.update({'Site': sites.Site.tolist()})
obj = rd_sql(hydrotel_server,
hydrotel_db,
obj_tab,
obj_fields,
where_dict,
username=username,
password=password)
corr_sites = sites[sites.Site.isin(obj.Site)]
return corr_sites.ExtSysID.astype('int32').astype(str).tolist()
def download_tsdata(sites, bands, start_date, end_date):
if not sites or bands is None:
return ''
sites1 = [str(s) for s in sites]
if isinstance(bands, int):
bands = [bands]
ts1 = mssql.rd_sql(server,
db,
lf_site_band_table,
where_col={
'site': sites1,
'band_num': bands
},
from_date=start_date,
to_date=end_date,
date_col='date')
csv_string = ts1.to_csv(index=False, encoding='utf-8')
csv_string = "data:text/csv;charset=utf-8," + urllib.parse.quote(
csv_string)
return csv_string
def update_band_options(sites, clickdata, end_date, select1):
if not sites:
options1 = []
elif select1 == 'band':
sites1 = [str(s) for s in sites]
site_bands = mssql.rd_sql(
server,
db,
lf_site_band_table, ['band_num', 'band_name', 'site_type'],
where_col={
'site': sites1
},
from_date=end_date,
to_date=end_date,
date_col='date').drop_duplicates(['band_name'])
site_bands['label'] = site_bands['band_name'] + ' - ' + site_bands[
'site_type']
site_bands1 = site_bands.rename(columns={
'band_num': 'value'
}).drop(['band_name', 'site_type'], axis=1)
options1 = site_bands1.to_dict('records')
# elif select1 == 'consent':
# print(options1)
return options1
def app_ts_summ(server, database, features, mtypes, ctypes, data_codes,
data_providers):
"""
"""
## Get TS summary
ecan_summ = ecan_ts_summ(server, database, features, mtypes, ctypes,
data_codes, data_providers)
## Dataset name
ecan_summ[
'Dataset Name'] = ecan_summ.Feature + ' - ' + ecan_summ.MeasurementType + ' - ' + ecan_summ.CollectionType + ' - ' + ecan_summ.DataCode + ' - ' + ecan_summ.DataProvider + ' (' + ecan_summ.Units + ')'
## Get site info
sites = mssql.rd_sql(server, database, sites_table, sites_cols)
sites['NZTMX'] = sites['NZTMX'].astype(int)
sites['NZTMY'] = sites['NZTMY'].astype(int)
# Hover text
sites.loc[sites.ExtSiteName.isnull(), 'ExtSiteName'] = ''
sites['hover'] = sites.ExtSiteID + '<br>' + sites.ExtSiteName.str.strip()
# Convert projections
xy1 = list(zip(sites['NZTMX'], sites['NZTMY']))
x, y = list(zip(*[transform(from_crs, to_crs, x, y) for x, y in xy1]))
sites['lon'] = x
sites['lat'] = y
## Combine with everything
ts_summ = pd.merge(sites, ecan_summ, on='ExtSiteID')
return ts_summ
def rd_lf_crc(SiteID=None,
BandNumber=None,
RecordNumber=None,
username=None,
password=None):
"""
tagLowFlow table.
"""
where_in1 = util.where_gen(SiteID, 'SiteID')
where_in2 = util.where_gen(BandNumber, 'BandNo', where_in1)
where_in = util.where_gen(RecordNumber, 'RecordNo', where_in2)
crc = rd_sql(lf_server,
lf_db,
crc_table,
crc_fields,
where_in=where_in,
rename_cols=crc_names,
username=username,
password=password)
## clean
crc['RecordNumber'] = crc['RecordNumber'].str.strip().str.upper()
crc1 = crc.drop_duplicates()
## Return
return crc1
def get_mtypes(server, database):
"""
Function to return a Series of measurement types that can be passed to get_sites_mtypes and get_ts_data. Returns with a count of the frequency the values exist in the database and is sorted by the count.
Remember, SQL is not case sensitive. The MTypes returned will have different cases, but these differences do not matter for the other functions.
Parameters
----------
server : str
The server where the Hydrotel database lays.
database : str
The name of the Hydrotel database.
mtypes : str or list of str
The measurement type(s) of the sites that should be returned.
sites : list of str or None
The list of sites that should be returned. None returns all sites.
Returns
-------
Series
MType (index), count
"""
objects1 = rd_sql(server, database, objects_tab, objects_col)
objects2 = objects1.groupby('Name').Site.count().sort_values(ascending=False)
objects2.name = 'count'
objects2.index.name = 'MType'
return objects2
def rd_lf_db_log(SiteID=None,
from_date=None,
to_date=None,
LogResult=None,
username=None,
password=None):
"""
LowFlowSiteRefDBaseReadSite table.
"""
if to_date is None:
to_date = str(date.today())
where_in1 = util.where_gen(SiteID, 'SiteID')
where_in = util.where_gen(LogResult, 'Result', where_in1)
db_log = rd_sql(lf_server,
lf_db,
db_log_table,
db_log_fields,
where_in=where_in,
from_date=from_date,
to_date=to_date,
date_col='forDate',
rename_cols=db_log_names,
username=username,
password=password).drop_duplicates(['SiteID', 'RestrDate'])
## Return
return db_log.set_index(['SiteID', 'RestrDate']).sort_index()
def rd_lf_site_type(SiteID=None,
BandNumber=None,
SiteType=None,
only_active=None,
username=None,
password=None):
"""
LowFlowSiteBand table.
"""
where_in1 = util.where_gen(SiteID, 'SiteID')
where_in2 = util.where_gen(BandNumber, 'BandNo', where_in1)
where_in3 = util.where_gen(only_active, 'isActive', where_in2)
where_in = util.where_gen(SiteType, 'RestrictionType', where_in3)
site_type = rd_sql(lf_server,
lf_db,
site_type_table,
site_type_fields,
where_in=where_in,
rename_cols=site_type_names,
username=username,
password=password)
## clean
site_type['BandName'] = site_type['BandName'].str.strip()
site_type['SiteType'] = site_type['SiteType'].str.strip().str.title()
## Return
return site_type.set_index(['SiteID', 'BandNumber']).sort_index()
def rd_lf_restr_ts(SiteID=None,
BandNumber=None,
from_date=None,
to_date=None,
username=None,
password=None):
"""
LowFlowSiteRestrictionDaily table.
"""
# where_in1 = util.where_gen('Live', 'SnapshotType')
where_in2 = util.where_gen(SiteID, 'SiteID')
where_in = util.where_gen(BandNumber, 'BandNo', where_in2)
restr_ts = rd_sql(lf_server,
lf_db,
restr_table,
restr_fields,
where_in=where_in,
rename_cols=restr_names,
from_date=from_date,
to_date=to_date,
date_col='RestrictionDate',
username=username,
password=password).sort_values('SnapshotType')
## clean
restr_ts.drop_duplicates(['SiteID', 'BandNumber', 'RestrDate'],
inplace=True)
## Return
return restr_ts.drop('SnapshotType', axis=1).set_index(
['SiteID', 'BandNumber', 'RestrDate']).sort_index()
def _lowflow_data(self):
"""
"""
if hasattr(self, 'lf_restr_daily'):
lf_crc2 = self.lf_restr_daily
else:
## Pull out the lowflows data
lf_crc1 = mssql.rd_sql(
self.crc_server,
self.crc_db,
param.lf_table,
['RecordNumber', 'AllocationBlock', 'RestrDate', 'Allocation'],
where_in={
'RecordNumber':
self.allo.index.levels[0].unique().tolist()
},
from_date=self.from_date,
to_date=self.to_date,
date_col='RestrDate')
lf_crc1.rename(columns={'RestrDate': 'Date'}, inplace=True)
lf_crc1.Date = pd.to_datetime(lf_crc1.Date)
## Aggregate to the crc and date - min restr ratio
lf_crc2 = util.grp_ts_agg(lf_crc1, 'RecordNumber', 'Date',
'D')['Allocation'].min() * 0.01
lf_crc2.name = 'restr_ratio'
setattr(self, 'lf_restr_daily', lf_crc2)
### Aggregate to the appropriate freq
lf_crc3 = util.grp_ts_agg(lf_crc2.reset_index(), 'RecordNumber',
'Date', self.freq)['restr_ratio'].mean()
setattr(self, 'lf_restr', lf_crc3)
def load_geo_data(data):
"""
Function to load or pass geometry data as a GeoDataFrame.
Parameters
----------
data : str, dict, or GeoDataFrame
Must be either a shapefile path (as str), a GeoDataFrame, or a dict of parameters to be passed to pdsql.mssql.rd_sql.
Returns
-------
GeoDataFrame
"""
if isinstance(data, gpd.GeoDataFrame):
gpd1 = data.copy()
elif isinstance(data, str):
if data.endswith('shp'):
gpd1 = gpd.read_file(data)
else:
raise ValueError(
'If input is a str, then it must be a path to a shapefile.')
elif isinstance(data, dict):
data1 = data.copy()
data1.update({'geo_col': True})
gpd1 = mssql.rd_sql(**data1)
return gpd1
def get_limit_data(pl_server, pl_db, limit_table, username, password):
"""
"""
lim1 = mssql.rd_sql(pl_server, pl_db, limit_table, ['ManagementGroupID', 'PlanName', 'PlanSection', 'PlanTable', 'LimitUnits', 'LimitParameterName', 'LimitParameterType', 'FromMonth', 'ToMonth', 'Limit'], username=username, password=password)
return lim1
def load_rec(self):
"""
"""
if not hasattr(self, 'rec_rivers'):
sql1 = sql_arg()
rec_rivers_dict = sql1.get_dict(param['input']['rec_rivers_sql'])
rec_catch_dict = sql1.get_dict(param['input']['rec_catch_sql'])
rec_rivers = mssql.rd_sql(**rec_rivers_dict)
rec_catch = mssql.rd_sql(**rec_catch_dict)
setattr(self, 'rec_rivers', rec_rivers)
setattr(self, 'rec_catch', rec_catch)
pass
def lf_site_summ(server, database, from_date, to_date):
site_summ = mssql.rd_sql(
server,
database,
lf_site_table, [
'site', 'date', 'site_type', 'flow_method', 'days_since_flow_est',
'flow', 'crc_count', 'min_trig', 'max_trig', 'restr_category'
],
from_date=from_date,
to_date=to_date,
date_col='date',
rename_cols=[
'ExtSite', 'Date', 'Site type', 'Data source',
'Days since last estimate', 'Flow or water level', 'Crc count',
'Min trigger', 'Max trigger', 'Restriction category'
])
sites1 = site_summ.ExtSiteID.unique().tolist()
## Get site info
sites = mssql.rd_sql(server,
database,
sites_table,
['ExtSiteID', 'ExtSiteName', 'NZTMX', 'NZTMY'],
where_in={'ExtSiteID': sites1})
sites['NZTMX'] = sites['NZTMX'].astype(int)
sites['NZTMY'] = sites['NZTMY'].astype(int)
sites.loc[sites.ExtSiteName.isnull(), 'ExtSiteName'] = ''
# Convert projections
xy1 = list(zip(sites['NZTMX'], sites['NZTMY']))
x, y = list(zip(*[transform(from_crs, to_crs, x, y) for x, y in xy1]))
sites['lon'] = x
sites['lat'] = y
combo = pd.merge(sites, site_summ, on='ExtSiteID')
# Hover text
combo['hover'] = combo.ExtSiteID + '<br>' + combo.ExtSiteName.str.strip(
) + '<br>' + combo['Data source'] + ' ' + combo[
'Days since last estimate'].astype(str) + ' day(s) ago'
return combo
def _process_usage(self):
"""
"""
### Get the ts summary tables
if not hasattr(self, 'ts_usage_summ'):
self._usage_summ()
ts_usage_summ = self.ts_usage_summ.copy()
## Get the ts data and aggregate
if hasattr(self, 'usage_ts_daily'):
tsdata1 = self.usage_ts_daily
else:
tsdata1 = mssql.rd_sql(
self.ts_server,
self.ts_db,
param.ts_table, ['ExtSiteID', 'DateTime', 'Value'],
where_in={
'ExtSiteID': ts_usage_summ.Wap.unique().tolist(),
'DatasetTypeID':
ts_usage_summ.DatasetTypeID.unique().tolist()
},
from_date=self.from_date,
to_date=self.to_date,
date_col='DateTime')
tsdata1['DateTime'] = pd.to_datetime(tsdata1['DateTime'])
tsdata1.rename(columns={
'DateTime': 'Date',
'ExtSiteID': 'Wap',
'Value': 'TotalUsage'
},
inplace=True)
### filter - remove individual spikes and negative values
tsdata1.loc[tsdata1['TotalUsage'] < 0, 'TotalUsage'] = 0
def remove_spikes(x):
val1 = bool(x[1] > (x[0] + x[2] + 2))
if val1:
return (x[0] + x[2]) / 2
else:
return x[1]
tsdata1.iloc[1:-1, 2] = tsdata1['TotalUsage'].rolling(
3, center=True).apply(remove_spikes, raw=True).iloc[1:-1]
setattr(self, 'usage_ts_daily', tsdata1)
### Aggregate
tsdata2 = util.grp_ts_agg(tsdata1, 'Wap', 'Date', self.freq).sum()
setattr(self, 'usage_ts', tsdata2)
def rd_sites(where_in=None):
"""
where_in : dict
The keys should be the column names and the values should be a list of values on those columns.
"""
### Site and attributes
sites = mssql.rd_sql(param.hydro_server,
param.hydro_database,
param.site_table,
param.site_cols,
where_in=where_in)
sites1 = sites[sites.ExtSiteID.str.contains('[A-Z]+\d\d/\d+')].copy()
return sites1.set_index('ExtSiteID')
def get_consent_conditions(site_id):
"""This function extracts consent conditions for a site of interest"""
# Read table of consent conditions and convert to a pandas dataframe (Ecan)
server = 'edwprod01'
database = 'ConsentsReporting'
table = 'reporting.CrcActSiteSumm'
crc_table = sq.rd_sql(server, database, table)
# Extract the active consents for the specified site
consents = crc_table.loc[(crc_table['ExtSiteID'] == site_id) & (crc_table['ConsentStatus'] == 'Issued - Active')]
# If there are no active consents, extract the most recent consent for the specified site
if len(consents) == 0:
all_consents = crc_table.loc[(crc_table['ExtSiteID'] == site_id)]
most_recent = all_consents['ToDate'].max()
consents = all_consents.loc[all_consents['ToDate'] == most_recent]
return consents
def rd_allo(from_date='1900-07-01',
to_date='2020-06-30',
where_in=None,
include_hydroelectric=False):
"""
Function to filter consents..
Parameters
----------
from_date : str
The start date for the time series.
to_date: str
The end date for the time series.
where_in : dict
The keys should be the column names and the values should be a list of values on those columns.
Returns
-------
DataFrame
Allocation
"""
### allocation
allo1 = mssql.rd_sql(param.crc_server,
param.crc_database,
param.allo_table,
param.allo_cols,
where_in=where_in)
allo1 = allo1[allo1.ConsentStatus.isin(param.status_codes)].copy()
if not include_hydroelectric:
allo1 = allo1[allo1.WaterUse != 'hydroelectric']
### Time series filtering
allo1.loc[:, 'ToDate'] = pd.to_datetime(allo1.loc[:, 'ToDate'],
errors='coerce')
allo1.loc[:, 'FromDate'] = pd.to_datetime(allo1.loc[:, 'FromDate'],
errors='coerce')
# allo1 = allo1[(allo1['ToDate'] - pd.Timestamp(from_date)).dt.days > 31]
# allo1 = allo1[(pd.Timestamp(to_date) - allo2['FromDate']).dt.days > 31]
allo1 = allo1[(allo1['ToDate'] - allo1['FromDate']).dt.days > 10]
allo2 = allo1[(allo1.FromDate < to_date)
& (allo1.ToDate > from_date)].copy()
### Index the DataFrame
allo2.set_index(
['RecordNumber', 'HydroFeature', 'AllocationBlock', 'ExtSiteID'],
inplace=True)
return allo2
def upstream_takes(self):
"""
Function to determine the upstream water abstraction sites from the catchment delineation.
Returns
-------
DataFrame
allocation data
"""
if not hasattr(self, 'catch_gdf'):
catch_gdf = self.catch_del()
### WAP selection
wap1 = mssql.rd_sql(self.permit_server, param['input']['permit_database'], param['input']['crc_wap_table'], ['ExtSiteID'], where_in={'ConsentStatus': param['input']['crc_status']}).ExtSiteID.unique()
sites3 = self.sites[self.sites.ExtSiteID.isin(wap1)].copy()
sites3.rename(columns={'ExtSiteID': 'Wap'}, inplace=True)
sites4 = vector.xy_to_gpd('Wap', 'NZTMX', 'NZTMY', sites3)
sites4 = sites4.merge(sites3.drop(['NZTMX', 'NZTMY'], axis=1), on='Wap')
waps_gdf, poly1 = vector.pts_poly_join(sites4, catch_gdf, 'ExtSiteID')
waps_gdf.dropna(subset=['SwazName', 'SwazGroupName'], inplace=True)
### Get crc data
if waps_gdf.empty:
print('No WAPs were found in the polygon')
allo_wap = pd.DataFrame()
else:
allo1 = AlloUsage(crc_filter={'ExtSiteID': waps_gdf.Wap.unique().tolist(), 'ConsentStatus': param['input']['crc_status']}, from_date=self.from_date, to_date=self.to_date)
allo_wap1 = allo1.allo.copy()
allo_wap = pd.merge(allo_wap1.reset_index(), waps_gdf[['Wap', 'ExtSiteID']], on='Wap')
## Save if required
if hasattr(self, 'output_path'):
run_time = pd.Timestamp.today().strftime('%Y-%m-%dT%H%M')
waps_shp = param['output']['waps_shp'].format(run_date=run_time)
waps_gdf.to_file(os.path.join(self.output_path, waps_shp))
allo_data_csv = param['output']['allo_data_csv'].format(run_date=run_time)
allo_wap.to_csv(os.path.join(self.output_path, allo_data_csv), index=False)
## Return
setattr(self, 'waps_gdf', waps_gdf)
setattr(self, 'allo_wap', allo_wap)
return allo_wap
def usm_sites(ExtSiteID=None, username=None, password=None):
"""
USM Site table.
"""
where_in = util.where_gen(ExtSiteID, 'UpstreamSiteID')
usm_sites1 = rd_sql(usm_server,
usm_db,
usm_sites_table,
usm_fields,
where_in=where_in,
rename_cols=usm_names,
username=username,
password=password).round()
return usm_sites1
def rd_sites(where_in=None):
"""
where_in : dict
The keys should be the column names and the values should be a list of values on those columns.
"""
### Site and attributes
cols = ['ExtSiteID', 'NZTMX', 'NZTMY']
if isinstance(where_in, dict):
for k in where_in:
if not k in cols:
cols.extend([k])
elif where_in is not None:
raise ValueError('where_in should either be None or a dict')
sites = mssql.rd_sql(server, hydro_db, site_table, cols, where_in=where_in)
sites1 = sites[sites.ExtSiteID.str.contains('[A-Z]+\d\d/\d+')].copy()
return sites1
def rd_lf_periods(SiteID=None, BandNumber=None, username=None, password=None):
"""
LowFlowSiteBandPeriod table.
"""
where_in1 = util.where_gen(SiteID, 'SiteID')
where_in = util.where_gen(BandNumber, 'BandNo', where_in1)
periods = rd_sql(lf_server,
lf_db,
period_table,
period_fields,
where_in=where_in,
rename_cols=period_names,
username=username,
password=password)
## Return
return periods
def get_limit_data(pl_server, pl_db, limit_table):
"""
"""
lim1 = mssql.rd_sql(
pl_server,
pl_db,
limit_table, [
'ManagementGroupID', 'PlanName', 'PlanSection', 'PlanTable',
'LimitUnits', 'LimitParameterName', 'LimitParameterType',
'FromMonth', 'ToMonth', 'Limit'
],
username='******',
password='******')
# gw_lim = lim1[lim1.Groundwater].drop(['Groundwater', 'SurfaceWater'], axis=1).copy()
# sw_lim = lim1[lim1.SurfaceWater].drop(['Groundwater', 'SurfaceWater'], axis=1).copy()
return lim1
def rd_lf_sites(SiteID=None, ExtSiteID=None, username=None, password=None):
"""
LowFlowSite table.
"""
where_in1 = util.where_gen(SiteID, 'SiteID')
where_in = util.where_gen(ExtSiteID, 'RefDBaseKey', where_in1)
sites = rd_sql(lf_server,
lf_db,
lf_sites_table,
lf_sites_fields,
where_in=where_in,
rename_cols=lf_sites_names,
username=username,
password=password)
## Clean
sites['ExtSiteID'] = sites['ExtSiteID'].str.upper()
## Return
return sites
def _usage_summ(self):
"""
"""
### Get the ts summary tables
ts_summ1 = mssql.rd_sql(
self.ts_server, self.ts_db, param.ts_summ_table,
['ExtSiteID', 'DatasetTypeID', 'FromDate', 'ToDate'],
{'DatasetTypeID': list(param.dataset_dict.keys())})
ts_summ2 = ts_summ1[ts_summ1.ExtSiteID.isin(self.waps)].copy()
# ts_summ2['HydroFeature'] = ts_summ2['DatasetTypeID']
# ts_summ2.replace({'HydroFeature': param.dataset_dict}, inplace=True)
ts_summ2.rename(columns={'ExtSiteID': 'Wap'}, inplace=True)
ts_summ2['FromDate'] = pd.to_datetime(ts_summ2['FromDate'])
ts_summ2['ToDate'] = pd.to_datetime(ts_summ2['ToDate'])
ts_summ3 = ts_summ2[(ts_summ2.FromDate < self.to_date)
& (ts_summ2.ToDate > self.from_date)].copy()
setattr(self, 'ts_usage_summ', ts_summ3)
def rd_crc(where_in=None,
from_date='1900-07-01',
to_date='2100-06-30',
include_hydroelectric=False):
"""
Function to filter consents..
Parameters
----------
from_date : str
The start date for the time series.
to_date: str
The end date for the time series.
where_in : dict
The keys should be the column names and the values should be a list of values on those columns.
Returns
-------
DataFrame
Allocation
"""
### allocation
allo1 = mssql.rd_sql(server, crc_db, crc_table, where_in=where_in)
if not include_hydroelectric:
allo1 = allo1[allo1.WaterUse != 'hydroelectric']
### Time series filtering
allo1.loc[:, 'ToDate'] = pd.to_datetime(allo1.loc[:, 'ToDate'],
errors='coerce')
allo1.loc[:, 'FromDate'] = pd.to_datetime(allo1.loc[:, 'FromDate'],
errors='coerce')
allo1 = allo1[(allo1['ToDate'] - allo1['FromDate']).dt.days > 10]
allo2 = allo1[(allo1.FromDate < to_date)
& (allo1.ToDate > from_date)].copy()
# ### Index the DataFrame
# allo2.set_index(['RecordNumber', 'HydroGroup', 'AllocationBlock', 'ExtSiteID'], inplace=True)
return allo2
def rd_lf_last_reading_from_date(from_date,
SiteID=None,
username=None,
password=None):
"""
"""
if SiteID is None:
site_str = ''
elif isinstance(SiteID, (str, int)):
site_str = ' and SiteID = ' + str(SiteID)
elif isinstance(SiteID, list):
site_str = ' and SiteID in ({})'.format(', '.join(
[str(i) for i in SiteID]))
stmt1 = ass_stmt.format(date=from_date, site=site_str)
df1 = rd_sql(lf_server,
lf_db,
stmt=stmt1,
username=username,
password=password)
return df1
