site = '66401'
mtypes = ['water level', 'water level detided']
from_date = '2018-01-01'
to_date = '2018-03-01'
quantile = 0.3
output_path = r'E:\ecan\shared\projects\de-tide\de-tide_2018-10-16.html'
######################################
### Get data
mtypes1 = get_sites_mtypes(server, database, sites=site)
tsdata = get_ts_data(server, database, mtypes, site, from_date, to_date, None)
tsdata1 = dtl.util.tsreg(tsdata.unstack(1).reset_index().drop(['ExtSiteID'], axis=1).set_index('DateTime')).interpolate('time')
roll1 = tsdata1[['water level']].rolling(12, center=True).mean().dropna()
roll1.columns = ['smoothed original']
######################################
### Run detide
det1 = dtl.detide(roll1, quantile)
det2 = dtl.plot.plot_detide(roll1, quantile, output_path=output_path)
start_date = now1 - DateOffset(months=param.n_previous_months) - DateOffset(
days=now1.day - 1)
end_date = now1 - DateOffset(days=now1.day - 1)
### SW
print('Getting HydroTel Flow Data:')
sites2 = sites1.copy()
sites2.loc[sites2.site.isin([64610, 65104, 68526]),
'site'] = [164610, 165104, 168526]
hy_sites = sites2.site.astype(str).tolist()
hy1 = get_ts_data(param.hydrotel_server,
param.hydrotel_database,
'flow',
hy_sites,
from_date=start_date.strftime('%Y-%m-%d'),
to_date=end_date.strftime('%Y-%m-%d'),
resample_code='D')
hy2 = hy1.reset_index().drop('MType', axis=1)
hy2.rename(columns={
'ExtSiteID': 'site',
'DateTime': 'time',
'Value': 'data'
},
inplace=True)
if len(hy2.site.unique()) != len(hy_sites):
print(
str(len(hy_sites) - len(hy2.site.unique())) +
" sites are not in Hydrotel")
from pyhydrotel import get_sites_mtypes, get_ts_data, get_mtypes
server = 'sql2012prod04'
database = 'hydrotel'
workdir = r'C:\Active\Projects\Rakaia\Data\Lake_Coleridge_Trustpower\IMS\Example_SWR_CSVs'
# #-Coleridge site id
# id = ['6852602']
# mtypes = ['non-hydro release lc', 'Rakaia FH modified']
# mtypes = ['Rakaia FH modified']
# #tsdata = get_ts_data(server, database, mtypes, id, resample_code='T', period=15)#, from_date='2018-01-01', to_date='2018-01-10')
# tsdata = get_ts_data(server, database, mtypes, id, resample_code='D')
# print tsdata
# exit(0)
#-Fighting Hill site id
sites = ['168526']
mtypes = ['Flow'] #, 'Wilcos Flow']
tsdata = get_ts_data(server,
database,
mtypes,
sites,
resample_code='T',
period=15)
print(tsdata)
else:
to_date = run_time_start.floor('D') + pd.DateOffset(hours=12)
from_date = (to_date - pd.DateOffset(days=3)).floor('D')
allo_csv = 'above_66401_allo_2020-08-12.csv'
#from_date = pd.Timestamp('2019-07-01 00:30:00')
#to_date = pd.Timestamp('2019-02-03')
try:
######################################
### Get detided data
tsdata = get_ts_data(param['Output']['hydrotel_server'], 'hydrotel',
param['Input']['detided_mtype'],
str(param['Input']['site']), str(from_date),
str(to_date), None).droplevel([0, 1])
tsdata.name = 'detided'
to_date = tsdata.index.max()
try:
#####################################
### Determine the Wap usage ratios
up_takes1 = pd.read_csv(os.path.join(base_dir, allo_csv))
up_takes2 = up_takes1[up_takes1.AllocatedRate > 0].copy()
up_takes2['AllocatedRateSum'] = up_takes2.groupby(
'Wap')['AllocatedRate'].transform('sum')
up_takes2['AllocatedRateRatio'] = up_takes2[
'AllocatedRate'] / up_takes2['AllocatedRateSum']
ts_table = 'Samples'
point_val = 7229
base_dir = os.path.realpath(os.path.dirname(__file__))
with open(os.path.join(base_dir, 'parameters.yml')) as param:
param = yaml.safe_load(param)
######################################
### Determine last saved values
######################################
### Get data
tsdata = get_ts_data(param['Input']['server'], param['Input']['database'],
param['Input']['mtype'], str(param['Input']['site']),
str(from_date), str(to_date), None)
tsdata1 = dtl.util.tsreg(
tsdata.unstack(1).reset_index().drop(
['ExtSiteID'], axis=1).set_index('DateTime')).interpolate('time')
roll1 = tsdata1[[param['Input']['mtype']
]].rolling(12, center=True).mean().dropna()
roll1.columns = ['smoothed original']
######################################
### Run detide
#det1 = dtl.detide(roll1, float(param.quantile))
sites_mtypes = {'66401': ['flow', 'flow detided', 'flow detided + browns rock'], '66450': ['flow']}
from_date = '2019-10-08'
to_date = '2019-10-11'
##########################################
### Extract and load
for k, v in sites_mtypes.items():
print(k, v)
for m in v:
print(m)
## Get the data out
data1 = get_ts_data(input_server, 'Hydrotel', m, k, from_date, to_date, resample_code=None).reset_index()
ids = get_sites_mtypes(output_server, 'Hydrotel', m, k).iloc[0]
## Prepare the data
data2 = data1.drop(['ExtSiteID', 'MType'], axis=1).rename(columns={'DateTime': 'DT', 'Value': 'SampleValue'})
data2['Point'] = ids['Point']
data2['Quality'] = param['Output']['quality_code']
## Save the data
new_data = mssql.update_from_difference(data2, output_server, 'Hydrotel', 'Samples', on=['Point', 'DT'])
### Determine last generated unmod flow date
last_val1 = mssql.rd_sql(
param['Output']['hydrotel_server'],
'hydrotel',
stmt='select max(DT) from Samples where Point = {point}'.format(
point=param['Output']['unmod_point'])).iloc[0][0]
if last_val1 is None:
last_val1 = pd.Timestamp('1900-01-01')
######################################
### Get data
## Detided data
tsdata = get_ts_data(param['Output']['hydrotel_server'], 'hydrotel',
param['Input']['detided_mtype'],
str(param['Input']['site']), str(last_val1), None,
None)[1:]
## Determine the Wap usage ratios
fn1 = FlowNat(from_date=from_date,
to_date=to_date,
rec_data_code='RAW',
input_sites=str(param['Input']['site']))
up_takes1 = fn1.upstream_takes()
up_takes2 = up_takes1[up_takes1.AllocatedRate > 0].copy()
up_takes2['AllocatedRateSum'] = up_takes2.groupby(
'Wap')['AllocatedRate'].transform('sum')
up_takes2['AllocatedRateRatio'] = up_takes2['AllocatedRate'] / up_takes2[
'AllocatedRateSum']
### Parameters
base_dir = os.path.realpath(os.path.dirname(__file__))
with open(os.path.join(base_dir, 'parameters.yml')) as param:
param = yaml.safe_load(param)
to_date = run_time_start.floor('H')
from_date = (to_date - pd.DateOffset(days=7)).round('D')
try:
######################################
### Get data
tsdata = get_ts_data(param['Input']['hydrotel_server'], 'hydrotel',
param['Input']['mtype'], str(param['Input']['site']),
str(from_date), str(to_date), None)
tsdata1 = dtl.util.tsreg(
tsdata.unstack(1).reset_index().drop(
['ExtSiteID'], axis=1).set_index('DateTime')).interpolate('time')
roll1 = tsdata1[[param['Input']['mtype']
]].rolling(12, center=True).mean().dropna()
roll1.columns = ['smoothed original']
######################################
### Run detide
det1 = dtl.detide(roll1, float(
param['Input']['quantile'])).round(3).reset_index()
