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()