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_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 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 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_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 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 rd_lf_min_flows(SiteID=None,
BandNumber=None,
username=None,
password=None):
"""
LowFlowSiteBandPeriodAllocation table.
"""
where_in1 = util.where_gen(SiteID, 'SiteID')
where_in = util.where_gen(BandNumber, 'BandNo', where_in1)
restr_val = rd_sql(lf_server,
lf_db,
min_flow_table,
min_flow_fields,
where_in=where_in,
rename_cols=min_flow_names,
username=username,
password=password)
## clean - Fix duplicate zero allocations at zero flow
grp1 = restr_val.groupby(['SiteID', 'BandNumber', 'Period'])
zeros1 = grp1.min()
zeros2 = zeros1[zeros1.trig_level == 0]['Allocation']
zeros3 = pd.merge(restr_val,
zeros2.reset_index(),
on=['SiteID', 'BandNumber', 'Period', 'Allocation'])
max_zero = zeros3.groupby(['SiteID', 'BandNumber', 'Period',
'Allocation'])['trig_level'].max()
all_trig = restr_val.groupby(
['SiteID', 'BandNumber', 'Period', 'Allocation'])['trig_level'].min()
all_trig[max_zero.index] = max_zero
## Return
return all_trig
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_lf_last_readings_ts(from_date,
to_date=None,
SiteID=None,
username=None,
password=None):
"""
LowFlowSiteAssessment table
"""
if to_date is None:
to_date = str(date.today())
dates1 = pd.date_range(from_date, to_date)
list1 = []
for d in dates1:
df1 = rd_lf_last_reading_from_date(d,
SiteID,
username=username,
password=password)
df1['RestrDate'] = d
list1.append(df1)
df2 = pd.concat(list1)
if df2.empty:
return None
dates2 = df2.AppliesFromDate.astype(str).unique().tolist()
where_in1 = util.where_gen(SiteID, 'SiteID')
where_in = util.where_gen(dates2, 'AppliesFromDate', where_in1)
df3 = rd_sql(lf_server,
lf_db,
ass_table,
ass_fields,
where_in=where_in,
username=username,
password=password)
df4 = pd.merge(df2, df3, on=['SiteID', 'AppliesFromDate'], how='left')
# Rename
ass1 = df4.rename(columns=ass_names)
# Clean
ass1.loc[:,
'SourceReadLog'] = ass1.loc[:,
'SourceReadLog'].str.strip().str[:150]
ass1['MeasurementDate'] = pd.to_datetime(ass1['MeasurementDate'].dt.date)
ass1['OPFlag'] = ass1['OPFlag'].str.strip().str.upper()
## Add in how it was measured and when
sites = rd_lf_sites(SiteID, username=username, password=password)
tel_sites1 = ass1[ass1.MeasurementMethod == 3].SiteID
if not tel_sites1.empty:
tel_sites2 = sites.loc[sites.SiteID.isin(tel_sites1), 'ExtSiteID']
corr_sites1 = telem_corr_sites(tel_sites2.tolist(),
username=username,
password=password)
corr_sites2 = sites.loc[sites.ExtSiteID.isin(corr_sites1), 'SiteID']
ass1.loc[ass1.SiteID.isin(corr_sites2), 'MeasurementMethod'] = 5
site_type2 = ass1.replace({
'MeasurementMethod': method_dict
}).set_index(['SiteID', 'RestrDate']).sort_index()
return site_type2