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)
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,
database,
sites_table,
['ExtSiteID', 'SwazGroupName', 'SwazName'],
where_in={'ExtSiteID': usage2.Wap.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)
combo_ts1.resample('A-JUN').sum().round(-4)
combo_ts2.resample('A-JUN').sum().round(-4)
### Test 2
a1 = AlloUsage(from_date, to_date)
combo_ts = a1.get_ts(datasets, freq, ['date'])
a1 = AlloUsage(from_date, to_date, crc_filter={'crc': ['CRC157373']})
a1 = AlloUsage(from_date, to_date, crc_filter={'crc': ['CRC182351']})
combo_ts = a1.get_combo_ts(datasets, freq, ['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',
export_path=os.path.join(export_dir, plot_dir),
irr_season=True)
## broken up
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)
########################################
### 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')
self = AlloUsage()
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')