def test_AlloUsage1():
a1 = AlloUsage(from_date, to_date, site_filter=site_filter)
combo_ts1 = a1.get_ts(datasets, 'A-JUN', cols)
combo_ts2 = a1.get_ts(datasets, 'M', cols)
a1.plot_group(freq, export_path=base_dir)
a1.plot_stacked(freq, export_path=base_dir)
assert (len(combo_ts1) == 73) & (len(combo_ts2) == 869)
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
usage4 = pd.read_csv(os.path.join(results_path, allo_usage_wap_swaz_csv),
parse_dates=['Date'],
infer_datetime_format=True)
usage_rate = pd.read_csv(os.path.join(results_path, wap_sw_mon_usage_csv),
parse_dates=['Date'],
infer_datetime_format=True)
print('-> loaded from local files')
except:
print('-> Processing usage data from the databases')
allo1 = AlloUsage(param['from_date'],
param['to_date'],
site_filter={
'SwazGroupName':
takes.waps_gdf.SwazGroupName.unique().tolist()
})
usage1 = allo1.get_ts(datasets, 'M', grp)
usage2 = usage1.loc[usage1.SwRestrAllo > 0,
['SwRestrAllo', 'SwUsage']].reset_index().copy()
usage2.replace({'WaterUse': use_type_dict}, inplace=True)
usage2[['SwRestrAlloYr', 'SwUsageYr']] = usage2.groupby(
['Wap', 'WaterUse',
pd.Grouper(key='Date', freq='A-JUN')]).transform('sum')
sites1 = mssql.rd_sql(server,
cols = ['SwazName', 'WaterUse', 'Date']
export_path = r'E:\ecan\local\Projects\requests\Ilja\2019-02-27'
########################################
### Test 1
sites1 = mssql.rd_sql(server,
database,
sites_table,
['ExtSiteID', 'CatchmentGroupName', summ_col],
where_in={'CatchmentGroupName': catch_group})
site_filter = {'SwazName': sites1.SwazName.unique().tolist()}
a1 = AlloUsage(from_date,
to_date,
site_filter=site_filter,
crc_filter=crc_filter)
allo_ts1 = a1._get_allo_ts(freq, groupby=['crc', 'date'])
metered_allo_ts1 = a1._get_metered_allo_ts(freq, ['crc', 'date'])
usage1 = a1._get_usage_ts(freq, ['crc', 'date'])
a1._lowflow_data(freq)
restr_allo = a1._get_restr_allo_ts(freq, ['crc', 'wap', 'date'])
combo_ts1 = a1.get_ts(datasets, freq, ['date'])
combo_ts2 = a1.get_ts(datasets, freq, ['date'], irr_season=True)
crc_wap_table, ['ExtSiteID'],
where_in={
'ConsentStatus': param['crc_status']
}).ExtSiteID.unique()
sites3 = sites1[sites1.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, 'FlowSite')
waps_gdf.to_file(os.path.join(results_path, waps_shp))
##################################
### Get crc data
allo1 = AlloUsage(crc_filter={
'ExtSiteID': waps_gdf.Wap.unique().tolist(),
'ConsentStatus': param['crc_status']
},
from_date=param['from_date'],
to_date=param['to_date'])
allo_wap1 = allo1.allo.copy()
allo_wap = pd.merge(allo_wap1.reset_index(),
waps_gdf[['Wap', 'FlowSite']],
on='Wap')
allo_wap.to_csv(os.path.join(results_path, allo_csv), index=False)
shp_dir = 'input_files'
output_dir = 'output_files'
catch_del_shp = 'pareora_catchments.shp'
catch_del_shp_path = os.path.join(base_path, shp_dir, catch_del_shp)
rec_site = 70105
usage_output = 'pareora_huts_usage_mon.csv'
############################################
### Extract data
site_filter = {'CatchmentGroupName': catch_group}
a1 = AlloUsage(from_date, to_date, site_filter=site_filter)
sites0 = a1.sites.reset_index().copy()
sites = vector.xy_to_gpd('wap', 'NZTMX', 'NZTMY', sites0)
catch_del = gpd.read_file(catch_del_shp_path)
catch_del.rename(columns={'SITENUMBER': 'site'}, inplace=True)
catch_del1 = catch_del[catch_del.site == rec_site]
sites1 = vector.sel_sites_poly(sites, catch_del1)
## Usage data
usage1 = pd.read_hdf(os.path.join(source_path, usage_hdf))
rec_rivers_dict = sql1.get_dict(rec_rivers_sql)
rec_catch_dict = sql1.get_dict(rec_catch_sql)
###################################
### Catchment delineation and WAPs
catch1 = rec.catch_delineate(sites2, rec_rivers_dict, rec_catch_dict)
catch1.to_file(os.path.join(results_path, catch_del_shp))
wap1 = mssql.rd_sql(server, database, crc_wap_table, ['wap']).wap.unique()
sites3 = sites1[sites1.ExtSiteID.isin(wap1)].copy()
sites4 = vector.xy_to_gpd('ExtSiteID', 'NZTMX', 'NZTMY', sites3)
sites5 = vector.sel_sites_poly(sites4, catch1)
sites5.to_file(os.path.join(results_path, wap_shp))
##################################
### Get crc data
allo1 = AlloUsage(crc_wap_filter={'wap': sites5.ExtSiteID.tolist()},
from_date=from_date,
to_date=to_date)
#allo1.allo[allo1.allo.crc_status == 'Terminated - Replaced']
allo1.allo_wap.to_csv(os.path.join(results_path, allo_wap_csv))
allo1.allo.to_csv(os.path.join(results_path, allo_csv))
export3 = 'swaz_allo_usage_pivot_2019-03-25.csv'
now1 = str(datetime.now().date())
############################################
### Extract data
sites1 = mssql.rd_sql(server,
database,
sites_table, ['ExtSiteID', 'SwazGroupName', summ_col],
where_in={'SwazGroupName': catch_group})
site_filter = {'SwazName': sites1.SwazName.unique().tolist()}
a1 = AlloUsage(from_date,
to_date,
site_filter=site_filter,
crc_filter=crc_filter)
combo_ts = a1.get_ts(datasets,
freq, ['SwazName', 'use_type', 'date'],
irr_season=True)
combo_ts.to_csv(os.path.join(export_dir, export2))
#########################################
### Plotting
### Grouped
## Lumped
a1.plot_group('A-JUN',
group='SwazGroupName',
def usage_est(self, daily_usage_allo_ratio=2, yr_usage_allo_ratio=2, mon_usage_allo_ratio=3):
"""
Function to estimate abstraction. Uses measured abstraction with the associated allocation to estimate mean monthly ratios in the SWAZs and SWAZ groups and applies them to abstraction locations that are missing measured abstractions.
Returns
-------
DataFrame
of the usage rate
"""
if not hasattr(self, 'waps_gdf'):
allo_wap = self.upstream_takes()
waps_gdf = self.waps_gdf.copy()
if waps_gdf.empty:
usage_daily_rate = pd.DataFrame()
else:
## Get allo and usage data
allo1 = AlloUsage(self.from_date, self.to_date, site_filter={'SwazGroupName': waps_gdf.SwazGroupName.unique().tolist()})
usage1 = allo1.get_ts(['Allo', 'RestrAllo', 'Usage'], 'M', ['Wap', 'WaterUse'], daily_usage_allo_ratio=daily_usage_allo_ratio)
usage2 = usage1.loc[usage1.SwRestrAllo > 0, ['SwRestrAllo', 'SwUsage']].reset_index().copy()
usage2.replace({'WaterUse': {'industry': 'other'}}, inplace=True)
usage2[['SwRestrAlloYr', 'SwUsageYr']] = usage2.groupby(['Wap', 'WaterUse', pd.Grouper(key='Date', freq='A-JUN')]).transform('sum')
sites1 = mssql.rd_sql(self.ts_server, param['input']['ts_database'], param['input']['sites_table'], ['ExtSiteID', 'SwazGroupName', 'SwazName'], where_in={'ExtSiteID': usage2.Wap.unique().tolist()})
sites1.rename(columns={'ExtSiteID': 'Wap'}, inplace=True)
usage0 = pd.merge(sites1, usage2, on='Wap')
usage0['Mon'] = usage0.Date.dt.month
usage0['MonRatio'] = usage0.SwUsage/usage0.SwRestrAllo
usage0['YrRatio'] = usage0.SwUsageYr/usage0.SwRestrAlloYr
usage0.set_index(['Wap', 'Date', 'WaterUse'], inplace=True)
### Create the filters and ratios
filter1 = (usage0['YrRatio'] >= 0.04) & (usage0['YrRatio'] <= yr_usage_allo_ratio) & (usage0['MonRatio'] <= mon_usage_allo_ratio)
filter1.name = 'filter'
usage3 = usage0[filter1].reset_index().copy()
res_swaz1 = usage3.groupby(['SwazGroupName', 'SwazName', 'WaterUse', 'Mon']).MonRatio.mean()
res_grp1 = usage3.groupby(['SwazGroupName', 'WaterUse', 'Mon']).MonRatio.mean()
res_grp1.name = 'GrpRatio'
res_grp2 = usage3.groupby(['WaterUse', 'Mon']).MonRatio.mean()
res_grp2.name = 'GrossRatio'
all1 = usage0.groupby(['SwazGroupName', 'SwazName', 'WaterUse', 'Mon']).Mon.first()
res_swaz2 = pd.concat([res_swaz1, all1], axis=1).drop('Mon', axis=1)
res_swaz3 = pd.merge(res_swaz2.reset_index(), res_grp1.reset_index(), on=['SwazGroupName', 'WaterUse', 'Mon'], how='left')
res_swaz4 = pd.merge(res_swaz3, res_grp2.reset_index(), on=['WaterUse', 'Mon'], how='left')
res_swaz4.loc[res_swaz4.MonRatio.isnull(), 'MonRatio'] = res_swaz4.loc[res_swaz4.MonRatio.isnull(), 'GrpRatio']
res_swaz4.loc[res_swaz4.MonRatio.isnull(), 'MonRatio'] = res_swaz4.loc[res_swaz4.MonRatio.isnull(), 'GrossRatio']
res_swaz5 = res_swaz4.drop(['GrpRatio', 'GrossRatio'], axis=1).copy()
### Estimate monthly usage by WAP
usage4 = pd.merge(usage0.drop(['MonRatio', 'YrRatio', 'SwRestrAlloYr', 'SwUsageYr'], axis=1).reset_index(), res_swaz5, on=['SwazGroupName', 'SwazName', 'WaterUse', 'Mon'], how='left').set_index(['Wap', 'Date', 'WaterUse'])
usage4.loc[~filter1, 'SwUsage'] = usage4.loc[~filter1, 'SwRestrAllo'] * usage4.loc[~filter1, 'MonRatio']
usage5 = usage4.groupby(level=['Wap', 'Date'])[['SwUsage']].sum()
usage_rate = usage5.reset_index().copy()
usage_rate.rename(columns={'SwUsage': 'SwUsageRate'}, inplace=True)
days1 = usage_rate.Date.dt.daysinmonth
usage_rate['SwUsageRate'] = usage_rate['SwUsageRate'] / days1 /24/60/60
usage4.reset_index(inplace=True)
### Remove bad values from the daily usage data and find the proportion of daily usage
filter2 = filter1.groupby(level=['Wap', 'Date']).max()
filter3 = filter2[filter2].reset_index().drop('filter', axis=1)
filter3['year'] = filter3.Date.dt.year
filter3['month'] = filter3.Date.dt.month
daily1 = allo1.usage_ts_daily.drop('AllocatedRate', axis=1).copy()
daily1['year'] = daily1.Date.dt.year
daily1['month'] = daily1.Date.dt.month
daily2 = pd.merge(daily1, filter3.drop('Date', axis=1), on=['Wap', 'year', 'month'])
d2 = daily2.groupby(['Wap', pd.Grouper(key='Date', freq='M')])[['TotalUsage']].sum().round()
u3 = pd.concat([usage5, d2], axis=1, join='inner').reset_index()
u3['ratio'] = u3['SwUsage']/u3['TotalUsage']
u3.loc[u3.ratio.isnull(), 'ratio'] = 1
u3['year'] = u3.Date.dt.year
u3['month'] = u3.Date.dt.month
daily3 = pd.merge(daily2, u3.drop(['Date', 'SwUsage', 'TotalUsage'], axis=1), on=['Wap', 'year', 'month']).drop(['year', 'month'], axis=1)
daily3['TotalUsage'] = (daily3['TotalUsage'] * daily3['ratio']).round()
daily3['TotalUsage'] = daily3['TotalUsage'] /24/60/60
### Create daily usage for all Waps
usage_rate = usage_rate[usage_rate.Wap.isin(waps_gdf.Wap.unique())].copy()
days1 = usage_rate.Date.dt.daysinmonth
days2 = pd.to_timedelta((days1/2).round().astype('int32'), unit='D')
usage_rate0 = usage_rate.copy()
usage_rate0['Date'] = usage_rate0['Date'] - days2
grp1 = usage_rate.groupby('Wap')
first1 = grp1.first()
last1 = grp1.last()
first1.loc[:, 'Date'] = pd.to_datetime(first1.loc[:, 'Date'].dt.strftime('%Y-%m') + '-01')
usage_rate1 = pd.concat([first1, usage_rate0.set_index('Wap'), last1], sort=True).reset_index()
usage_rate1.set_index('Date', inplace=True)
usage_daily_rate1 = usage_rate1.groupby(['Wap']).apply(lambda x: x.resample('D').interpolate(method='pchip')['SwUsageRate'])
if isinstance(usage_daily_rate1, pd.DataFrame):
usage_daily_rate1 = usage_daily_rate1.stack()
usage_daily_rate1.name = 'SwUsageRate'
## Imbed the actual usage
usage_daily_rate2 = pd.merge(usage_daily_rate1.reset_index(), daily3.drop('ratio', axis=1), on=['Wap', 'Date'], how='left')
usage_daily_rate2.loc[usage_daily_rate2.TotalUsage.notnull(), 'SwUsageRate'] = usage_daily_rate2.loc[usage_daily_rate2.TotalUsage.notnull(), 'TotalUsage']
usage_daily_rate = usage_daily_rate2.drop('TotalUsage', axis=1).copy()
## Save results
if hasattr(self, 'output_path'):
run_time = pd.Timestamp.today().strftime('%Y-%m-%dT%H%M')
swaz_mon_ratio_csv = param['output']['swaz_mon_ratio_csv'].format(run_date=run_time)
res_swaz5.to_csv(os.path.join(self.output_path, swaz_mon_ratio_csv), index=False)
allo_usage_wap_swaz_csv = param['output']['allo_usage_wap_swaz_csv'].format(run_date=run_time)
usage4.to_csv(os.path.join(self.output_path, allo_usage_wap_swaz_csv), index=False)
usage_rate_wap_csv = param['output']['usage_rate_wap_csv'].format(run_date=run_time)
usage_daily_rate.to_csv(os.path.join(self.output_path, usage_rate_wap_csv), index=False)
setattr(self, 'mon_swaz_usage_ratio', res_swaz5)
setattr(self, 'allo_usage_wap_swaz', usage4)
setattr(self, 'usage_rate', usage_daily_rate)
return usage_daily_rate
def test_AlloUsage2():
a2 = AlloUsage(from_date, to_date, crc_filter=crc_filter)
combo_ts3 = a2.get_ts(datasets, freq, cols)
assert (combo_ts3.SwAllo.sum() == 1480908) & (combo_ts3.SwUsage.sum()
== 1017923)
export_path = r'E:\ecan\local\Projects\requests\Ilja\2019-02-26'
export1 = 'test_crcs_{}.csv'.format(date2)
########################################
### Read in sites
test_crc = pd.read_csv(test_sites_csv).crc.unique().tolist()
########################################
### generate!
#sites1 = mssql.rd_sql(server, database, sites_table, ['ExtSiteID', 'CatchmentGroupName', summ_col], where_in={'CatchmentGroupName': catch_group})
#
#site_filter = {'SwazName': sites1.SwazName.unique().tolist()}
a1 = AlloUsage()
a1 = AlloUsage(from_date, to_date, crc_filter={'crc': test_crc})
ts1 = a1.get_ts(datasets, freq, cols, usage_allo_ratio=2)
ts1.to_csv(os.path.join(export_path, export1))
a1.plot_group('A-JUN',
val='total',
group='crc',
with_restr=False,
export_path=export_path)
# a1.plot_stacked('A-JUN', val='total', export_path=r'E:\ecan\local\Projects\requests\suz\2018-12-17\plots')
export_path = r'E:\ecan\local\Projects\requests\Ilja\2019-02-26'
export1 = 'test_crcs_{}.csv'.format(date2)
########################################
### Read in sites
test_crc = pd.read_csv(test_sites_csv).crc.unique().tolist()
########################################
### generate!
#sites1 = mssql.rd_sql(server, database, sites_table, ['ExtSiteID', 'CatchmentGroupName', summ_col], where_in={'CatchmentGroupName': catch_group})
#
#site_filter = {'SwazName': sites1.SwazName.unique().tolist()}
a1 = AlloUsage(from_date, to_date, crc_filter={'crc': test_crc})
ts1 = a1.get_ts(datasets, freq, cols, usage_allo_ratio=10).round()
ts1.to_csv(os.path.join(export_path, export1))
a1.plot_group('A-JUN',
val='total',
group='crc',
with_restr=False,
export_path=export_path)
a1.plot_stacked(
'A-JUN',
val='total',
export_path=r'E:\ecan\local\Projects\requests\suz\2018-12-17\plots')