def sites(SiteID=None, ExtSiteID=None, username=None, password=None): """ Function to get the site info for the lowflows sites that correspond in USM. Parameters ---------- SiteID: int, str, or list LowFlow internal site IDs. ExtSiteID: int, str, or list ECan site IDs Returns ------- DataFrame 'ExtSiteID' """ lf_sites = rd.rd_lf_sites(SiteID, ExtSiteID, username=username, password=password) usm_sites1 = rd.usm_sites(lf_sites.ExtSiteID.tolist(), username=username, password=password) return usm_sites1.set_index('ExtSiteID')
def site_log_ts(from_date, to_date=None, SiteID=None, ExtSiteID=None, username=None, password=None): """ Function to return a time series log of site measurements read by Lowflows to the source systems. Parameters ---------- from_date: str The start date for the log. to_date: str or None The end date for the log. None returns today's date. SiteID: int, str, or list LowFlow internal site IDs. ExtSiteID: int, str, or list ECan site IDs Returns ------- DataFrame ['ExtSiteID', 'RestrDate'] """ ### Read in tables site_log1 = rd.rd_lf_db_log(SiteID=SiteID, from_date=from_date, to_date=to_date, username=username, password=password) sites = rd.rd_lf_sites(SiteID=SiteID, ExtSiteID=ExtSiteID, username=username, password=password) method1 = rd.rd_lf_last_readings_ts(from_date, to_date, SiteID, username=username, password=password) ### Combine tables method2 = pd.concat([method1, site_log1.drop('LogResult', axis=1)], axis=1, join='inner').reset_index() site_ts = pd.merge(sites, method2, on='SiteID').drop('SiteID', axis=1) ### Return return site_ts.set_index(['ExtSiteID', 'RestrDate'])
def allocation_ts(from_date, to_date=None, ExtSiteID=None, BandNumber=None, RecordNumber=None, username=None, password=None): """ Function to return a time series of allocation restrictions by 'RecordNumber', 'BandNumber', 'ExtSiteID', and 'RestrDate'. Parameters ---------- from_date: str The start date for the log. to_date: str or None The end date for the log. None returns today's date. SiteID: int, str, or list LowFlow internal site IDs. ExtSiteID: int, str, or list ECan site IDs BandNumber: int or list of int The Lowflow internal band numbers. RecordNumber: str or list of str The ECan record numbers. Returns ------- DataFrame ['RecordNumber', 'BandNumber', 'ExtSiteID', 'RestrDate'] """ ## Read tables sites1 = rd.rd_lf_sites(ExtSiteID=ExtSiteID, username=username, password=password) crc1 = rd.rd_lf_crc(BandNumber=BandNumber, RecordNumber=RecordNumber, username=username, password=password) if ExtSiteID is not None: SiteID = sites1.SiteID.unique().tolist() else: SiteID = None restr_ts = rd.rd_lf_restr_ts(SiteID, BandNumber=BandNumber, from_date=from_date, to_date=to_date, username=username, password=password).drop('Measurement', axis=1).reset_index() ## Combine tables restr_crc1 = pd.merge(crc1, restr_ts, on=[ 'SiteID', 'BandNumber' ]).drop_duplicates(['SiteID', 'BandNumber', 'RecordNumber', 'RestrDate'], keep='last') restr_crc2 = pd.merge(sites1, restr_crc1, on='SiteID').drop('SiteID', axis=1) ## Return return restr_crc2.set_index( ['RecordNumber', 'BandNumber', 'ExtSiteID', 'RestrDate'])
def min_max_trigs(ExtSiteID=None, only_active=None, username=None, password=None): """ Function to determine the min/max triggers. Parameters ---------- ExtSiteID: list of str ECan site IDs. only_active: bool Should the output only return active sites/bands? Returns ------- DataFrames Outputs two DataFrames. The first includes the min and max triggger levels for all bands per site, while the second has the min and max trigger levels for each site and band. """ ######################################## ### Read in data sites1 = rd.rd_lf_sites(ExtSiteID=ExtSiteID, username=username, password=password) periods0 = rd.rd_lf_periods(username=username, password=password) all_trig = rd.rd_lf_min_flows(username=username, password=password) site_type = rd.rd_lf_site_type(only_active=only_active, username=username, password=password) ####################################### ### Process data ## Periods by month periods = pd.merge(periods0, site_type, on=['SiteID', 'BandNumber']) periods['from_mon'] = periods['from_date'].dt.month periods['to_mon'] = periods['to_date'].dt.month ## filter by ExtSiteIDs periods = periods[periods.SiteID.isin(sites1.SiteID)] ## Process dates new1_list = [] for group in periods.itertuples(): if group.from_mon > group.to_mon: first1 = np.arange(group.from_mon, 13).tolist() sec1 = np.arange(1, group.to_mon + 1).tolist() first1.extend(sec1) else: first1 = np.arange(group.from_mon, group.to_mon + 1).tolist() index1 = [[group.SiteID, group.BandNumber, group.Period]] * len(first1) new1 = pd.DataFrame(index1, columns=['SiteID', 'BandNumber', 'Period']) new1['Month'] = first1 new1_list.append(new1) periods1 = pd.concat(new1_list).drop_duplicates( ['SiteID', 'BandNumber', 'Month']) periods1a = pd.merge(periods1, all_trig.reset_index(), on=['SiteID', 'BandNumber', 'Period']).drop('Period', axis=1) periods2 = pd.merge(periods1a, sites1, on='SiteID').drop('SiteID', axis=1) p_min = periods2[~periods2.Allocation. isin([103, 105, 106, 107, 108, 109])].groupby( ['ExtSiteID', 'BandNumber', 'Month']).min() p_min.columns = ['MinAllocation', 'MinTrigger'] p_max = periods2.groupby(['ExtSiteID', 'BandNumber', 'Month']).max() p_max.columns = ['MaxAllocation', 'MaxTrigger'] # p_min_site = p_min.reset_index().groupby(['ExtSiteID', 'mon'])['min_trig'].min() # p_max_site = p_max.reset_index().groupby(['ExtSiteID', 'mon'])['max_trig'].max() # p_set_site = pd.concat([p_min_site, p_max_site], axis=1).reset_index() p_set = pd.concat([p_min, p_max], axis=1) return p_set
def crc_trigs(SiteID=None, ExtSiteID=None, BandNumber=None, RecordNumber=None, SiteType=None, only_active=None, username=None, password=None): """ Function to Determine the min and max trigger and allocations by the RecordNumber, BandNumber, and ExtSiteID. Parameters ---------- SiteID: int, str, or list Lowflow internal site IDs. ExtSiteID: int, str, or list ECan site IDs BandNumber: int or list of int The Lowflow internal band numbers. RecordNumber: str or list of str The ECan record numbers. SiteType: str or list of str Options are 'Lowflow' or 'Residual' only_active: bool or None Should only the active bands be returned? None will contain all. Returns ------- DataFrame """ ### Read in tables crc = rd.rd_lf_crc(SiteID=SiteID, BandNumber=BandNumber, RecordNumber=RecordNumber, username=username, password=password) min_max = min_max_trigs(ExtSiteID=ExtSiteID, only_active=only_active, username=username, password=password).reset_index() sites = rd.rd_lf_sites(SiteID=SiteID, ExtSiteID=ExtSiteID, username=username, password=password) site_types = rd.rd_lf_site_type(SiteID=SiteID, BandNumber=BandNumber, SiteType=SiteType, only_active=only_active, username=username, password=password).reset_index() ### process min-max min_max2 = min_max.groupby(['ExtSiteID', 'BandNumber']) min1 = min_max2[['MinAllocation', 'MinTrigger']].min() max1 = min_max2[['MaxAllocation', 'MaxTrigger']].max() min_max3 = pd.concat([min1, max1], axis=1).reset_index() ## clean min_max3.loc[min_max3.MaxAllocation < 100, 'MaxAllocation'] = 100 min_max3.loc[min_max3.MinAllocation == 100, 'MinAllocation'] = min_max3.loc[min_max3.MinAllocation == 100, 'MaxAllocation'] min_max3.loc[ (min_max3.MaxAllocation > 100) & (min_max3.MinAllocation < 100), 'MinAllocation'] = min_max3.loc[(min_max3.MaxAllocation > 100) & (min_max3.MinAllocation < 100), 'MaxAllocation'] min_max3['MinTrigger'] = min_max3['MinTrigger'].round(2) min_max3['MaxTrigger'] = min_max3['MaxTrigger'].round(2) # min_max3[min_max3.MaxAllocation < 100].to_csv('max_allo_under_100.csv', index=False) # min_max3[min_max3.MinAllocation == 100].to_csv('min_allo_equals_100.csv', index=False) # min_max3.loc[(min_max3.MaxAllocation > 100) & (min_max3.MinAllocation < 100)].to_csv('min_allo_less_than_100.csv', index=False) ### Merges crc_sites = pd.merge(sites, crc, on='SiteID').drop('SiteID', axis=1) site_types2 = pd.merge(sites, site_types, on='SiteID').drop('SiteID', axis=1) min_max4 = pd.merge(crc_sites, min_max3, on=['ExtSiteID', 'BandNumber']) min_max5 = pd.merge(min_max4, site_types2, on=['ExtSiteID', 'BandNumber']) ### Return return min_max5.set_index(['RecordNumber', 'BandNumber', 'ExtSiteID'])
def site_summary_ts(from_date, to_date=None, ExtSiteID=None, SiteType=None, only_active=True, username=None, password=None): """ Function to provide a site summary time series. Parameters ---------- from_date: str The start date for the log. to_date: str or None The end date for the log. None returns today's date. ExtSiteID: int, str, or list ECan site IDs SiteType: str or list of str Options are 'Lowflow' or 'Residual' only_active: bool or None Should only the active bands be returned? None will contain all. Returns ------- DataFrame ['ExtSiteID', 'RestrDate'] """ ## Read data sites = core.sites(ExtSiteID=ExtSiteID, username=username, password=password).reset_index() site_link = rd.rd_lf_sites(ExtSiteID=ExtSiteID, username=username, password=password) min_max1 = core.min_max_trigs(ExtSiteID=ExtSiteID, only_active=only_active, username=username, password=password).reset_index() site_log1 = core.site_log_ts(from_date, to_date=to_date, ExtSiteID=ExtSiteID, username=username, password=password).reset_index() site_types = rd.rd_lf_site_type(SiteType=SiteType, only_active=only_active, username=username, password=password).reset_index() ## Determine site type by site site_types1 = site_types.sort_values('SiteType').drop_duplicates('SiteID')[['SiteID', 'SiteType']] band_count = site_types.groupby('SiteID')['BandNumber'].count() band_count.name = 'BandCount' site_types2 = pd.merge(site_types1, band_count.reset_index(), on='SiteID') # Convert SiteIDs site_types3 = pd.merge(site_link, site_types2, on='SiteID').drop('SiteID', axis=1) ## find min and max triggers grp1 = min_max1.groupby('ExtSiteID') min_loc = grp1['MinTrigger'].idxmin() max_loc = grp1['MaxTrigger'].idxmax() min_trig = min_max1.loc[min_loc].drop(['Month', 'MaxAllocation', 'MaxTrigger'], axis=1).copy() min_trig.rename(columns={'BandNumber': 'MinBandNumber'}, inplace=True) max_trig = min_max1.loc[max_loc].drop(['Month', 'MinAllocation', 'MinTrigger'], axis=1).copy() max_trig.rename(columns={'BandNumber': 'MaxBandNumber'}, inplace=True) min_max2 = pd.merge(min_trig, max_trig, on='ExtSiteID') ## Combine other tables min_max3 = pd.merge(sites, min_max2, on='ExtSiteID') min_max3a = pd.merge(min_max3, site_types3, on='ExtSiteID') min_max4 = pd.merge(min_max3a, site_log1.drop('SourceReadLog', axis=1), on='ExtSiteID') ## Assign restriction categories min_max4['RestrCategory'] = 'No' min_max4.loc[(min_max4['Measurement'] <= min_max4['MinTrigger']), 'RestrCategory'] = 'Full' min_max4.loc[(min_max4['Measurement'] < min_max4['MaxTrigger']) & (min_max4['Measurement'] > min_max4['MinTrigger']), 'RestrCategory'] = 'Partial' min_max4.loc[min_max4.OPFlag == 'NA', 'RestrCategory'] = 'Deactivated' min_max4.drop('OPFlag', axis=1, inplace=True) ### Return return min_max4.set_index(['ExtSiteID', 'RestrDate']).sort_index()