def site_alerts(curr_trig, ts, release_data_ts, connection): df = curr_trig.drop_duplicates( ['site_id', 'trigger_source', 'alert_level']) site_id = df['site_id'].values[0] query = "SELECT trigger_id, MAX(ts_last_retrigger) ts_last_retrigger FROM alert_status" query += " WHERE trigger_id IN (%s)" %(','.join(map(lambda x: str(x), \ set(df['trigger_id'].values)))) query += " GROUP BY trigger_id" written = db.df_read(query, connection=connection) site_curr_trig = pd.merge(df, written, how='left') site_curr_trig = site_curr_trig.loc[ (site_curr_trig.ts_last_retrigger + timedelta(1) < site_curr_trig.ts_updated) | (site_curr_trig.ts_last_retrigger.isnull()), :] if len(site_curr_trig) == 0: qdb.print_out('no new trigger for site_id %s' % site_id) return alert_status = site_curr_trig[['ts_updated', 'trigger_id']] alert_status = alert_status.rename( columns={'ts_updated': 'ts_last_retrigger'}) alert_status['ts_set'] = datetime.now() data_table = sms.DataTable('alert_status', alert_status) db.df_write(data_table, connection=connection)
def trending_alert_gen(pos_alert, tsm_id, end): if qdb.does_table_exist('node_alerts') == False: #Create a node_alerts table if it doesn't exist yet create_node_alerts() query = "SELECT EXISTS(SELECT * FROM node_alerts" query += " WHERE ts = '%s'" %end query += " and tsm_id = %s and node_id = %s)" %(tsm_id, pos_alert['node_id'].values[0]) if db.df_read(query, connection='local').values[0][0] == 0: node_alert = pos_alert[['disp_alert', 'vel_alert']] node_alert['ts'] = end node_alert['tsm_id'] = tsm_id node_alert['node_id'] = pos_alert['node_id'].values[0] data_table = sms.DataTable('node_alerts', node_alert) db.df_write(data_table, connection='local') query = "SELECT * FROM node_alerts WHERE tsm_id = %s and node_id = %s and ts >= '%s'" %(tsm_id, pos_alert['node_id'].values[0], end-timedelta(hours=3)) node_alert = db.df_read(query, connection='local') node_alert['node_alert'] = np.where(node_alert['vel_alert'].values >= node_alert['disp_alert'].values, #node alert takes the higher perceive risk between vel alert and disp alert node_alert['vel_alert'].values, node_alert['disp_alert'].values) if len(node_alert[node_alert.node_alert > 0]) > 3: trending_alert = pd.DataFrame({'node_id': [pos_alert['node_id'].values[0]], 'TNL': [max(node_alert['node_alert'].values)]}) else: trending_alert = pd.DataFrame({'node_id': [pos_alert['node_id'].values[0]], 'TNL': [0]}) return trending_alert
def earthquake(site_id, ts): """Insert values to earthquake_events, earthquake_alerts, and operational_triggers to (re)trigger subsurface alert. Args: site_id (int): ID of site to compute earthquake analysis for. ts (datetime): Timestamp of alert trigger. """ # writes to earthquake_events; defaults epicenter to site coordinates, depth to 0, and magnitude to 4.3 sites = eq.get_sites() earthquake_events = sites.loc[sites.site_id == site_id, ['latitude', 'longitude', 'province']] earthquake_events.loc[:, 'ts'] = ts earthquake_events.loc[:, 'magnitude'] = 4.3 earthquake_events.loc[:, 'depth'] = 0 earthquake_events.loc[:, 'critical_distance'] = np.round(eq.get_crit_dist(4.3), decimals=2) earthquake_events.loc[:, 'issuer'] = 'TOPSSOFTWAREINFRA' earthquake_events.loc[:, 'processed'] = 1 eq_id = int(db.df_write(data_table = sms.DataTable("earthquake_events", earthquake_events), resource='sensor_data', last_insert=True)[0][0]) # writes to earthquake_alerts earthquake_alerts = pd.DataFrame({'eq_id': [eq_id], 'site_id': [site_id], 'distance': [0]}) db.df_write(data_table = sms.DataTable("earthquake_alerts", earthquake_alerts), resource='sensor_data') # writes to operational_triggers trigger_symbol = mem.get('df_trigger_symbols') trigger_sym_id = trigger_symbol.loc[(trigger_symbol.trigger_source == 'earthquake') & (trigger_symbol.alert_level == 1), 'trigger_sym_id'].values[0] operational_trigger = pd.DataFrame({'site_id': [site_id], 'trigger_sym_id': [trigger_sym_id], 'ts': [ts], 'ts_updated': [ts]}) qdb.alert_to_db(operational_trigger, 'operational_triggers') # details for trigger tech info earthquake_events.loc[:, 'distance'] = 0 return earthquake_events
def send_unsent_notif(df, notif_type, curr_release, validation=False): ts = curr_release.strftime('%I%p %B %d, %Y') if len(df) != 0: site_notif = '\n'.join(list(map(lambda x: ': '.join(x), df.values))) if validation: sms_msg = 'Validate measurements with displacement of 1cm and more:\n\n' + site_notif else: sms_msg = 'Unsent ' + notif_type + ' (' + ts + '):\n\n' + site_notif smsoutbox_user_status = get_recipient(curr_release) else: if notif_type == 'gndmeas': return sms_msg = 'Sent all ' + notif_type + ' (' + ts + ')' smsoutbox_user_status = get_recipient(curr_release, unsent=False) smsoutbox_users = pd.DataFrame({ 'sms_msg': [sms_msg], 'source': ['central'] }) data_table = sms.DataTable('smsoutbox_users', smsoutbox_users) outbox_id = db.df_write(data_table, connection='gsm_pi', last_insert=True)[0][0] smsoutbox_user_status.loc[:, 'outbox_id'] = outbox_id data_table = sms.DataTable('smsoutbox_user_status', smsoutbox_user_status) db.df_write(data_table, connection='gsm_pi')
def dftosql(df): v2df = get_loggers_v2() v3df = get_loggers_v3() logger_active = pd.DataFrame() loggers = v2df.append(v3df).reset_index() logger_active = pd.DataFrame() for i in range(0, len(v2df)): logger_active = logger_active.append(get_data_tsm(v2df.logger_name[i])) for i in range(0, len(v3df)): logger_active = logger_active.append( get_data_rain(v3df.logger_name[i])) logger_active = logger_active.reset_index() timeNow = datetime.today() df['last_data'] = logger_active['max(ts)'] df['last_data'] = pd.to_datetime(df['last_data']) df['ts_updated'] = timeNow df['logger_id'] = loggers.logger_id diff = df['ts_updated'] - df['last_data'] tdta = diff fdta = tdta.astype('timedelta64[D]') df['diff_days'] = fdta df.loc[(df['diff_days'] > -1) & (df['diff_days'] < 3), 'presence'] = 'active' df['presence'] = df['diff_days'].apply(lambda x: '1' if x <= 3 else '0') data_table = sms.DataTable('data_presence_loggers', df) db.df_write(data_table, connection='analysis') return df
def main(): """Writes in rainfall_gauges information on available rain gauges for rainfall alert analysis """ start = datetime.now() qdb.print_out(start) if qdb.does_table_exist('rainfall_gauges') == False: #Create a rainfall_gauges table if it doesn't exist yet qdb.create_rainfall_gauges() senslope = mem.get('df_dyna_rain_gauges') senslope = senslope.loc[senslope.has_rain == 1, :] senslope.loc[:, 'data_source'] = 'senslope' noah = noah_gauges() all_gauges = senslope.append(noah, sort=False) all_gauges.loc[:, 'gauge_name'] = all_gauges.loc[:, 'gauge_name'].apply( lambda x: str(x)) all_gauges.loc[:, 'date_activated'] = pd.to_datetime( all_gauges.loc[:, 'date_activated']) written_gauges = mem.get('df_rain_gauges') not_written = set(all_gauges['gauge_name']) \ - set(written_gauges['gauge_name']) new_gauges = all_gauges.loc[all_gauges.gauge_name.isin(not_written), :] new_gauges = new_gauges.loc[new_gauges.date_deactivated.isnull(), :] new_gauges = new_gauges.loc[:, [ 'gauge_name', 'data_source', 'longitude', 'latitude', 'date_activated' ]] if len(new_gauges) != 0: data_table = sms.DataTable('rainfall_gauges', new_gauges) db.df_write(data_table) deactivated = written_gauges.loc[ ~written_gauges.date_deactivated.isnull(), :] deactivated_gauges = all_gauges.loc[(~all_gauges.date_deactivated.isnull()) \ & (~all_gauges.gauge_name.isin(not_written))\ & (~all_gauges.gauge_name.isin(deactivated.gauge_name)), :] date_deactivated = pd.to_datetime( deactivated_gauges.loc[:, 'date_deactivated']) deactivated_gauges.loc[:, 'date_deactivated'] = date_deactivated deactivated_gauges = deactivated_gauges.loc[:, [ 'gauge_name', 'data_source', 'longitude', 'latitude', 'date_activated' ]] if len(deactivated_gauges) != 0: data_table = sms.DataTable('rainfall_gauges', deactivated_gauges) db.df_write(data_table) qdb.print_out('runtime = %s' % (datetime.now() - start))
def main(key): ts = datetime.now() sheet_name = ts.strftime('%B %Y') shift_sched = get_shift(key, sheet_name) try: sheet_name = (ts+timedelta(weeks=2)).strftime('%B %Y') shift_sched = shift_sched.append(get_shift(key, sheet_name)) except: print("no shift schedule for next month") data_table = sms.DataTable('monshiftsched', shift_sched) db.df_write(data_table)
def main(): query = 'SELECT * FROM markers' markers = db.df_read(query) query = "SELECT * FROM marker_observations" mo = db.df_read(query) query = "SELECT * FROM marker_data" md = db.df_read(query) query = "SELECT ma_id, ts, marker_id FROM marker_alerts" ma = db.df_read(query) marker_alerts = pd.merge(ma, markers, on='marker_id', validate='m:1') marker_alerts = pd.merge(marker_alerts, mo, on=['site_id', 'ts'], validate='m:1') marker_alerts = pd.merge(marker_alerts, md, on=['mo_id', 'marker_id'], validate='m:1') marker_alerts = marker_alerts.drop_duplicates(['ts', 'marker_id'], keep='last') # delete marker_alerts not in marker_observations and duplicated marker_alerts ma_id = set(ma['ma_id']) - set(marker_alerts['ma_id']) if len(ma_id) != 0: query = 'DELETE FROM marker_alerts WHERE ma_id in %s' %str(tuple(ma_id)) qdb.execute_query(query) try: query = 'ALTER TABLE marker_alerts ADD UNIQUE INDEX uq_marker_alerts (marker_id ASC, ts ASC)' qdb.execute_query(query) except: pass try: query = "ALTER TABLE marker_alerts " query += "ADD UNIQUE INDEX uq_marker_alerts1 (data_id ASC); " qdb.execute_query(query) except: pass try: query = "ALTER TABLE marker_alerts " query += "ADD CONSTRAINT fk_marker_data " query += " FOREIGN KEY (data_id) " query += " REFERENCES marker_data (data_id) " query += " ON DELETE CASCADE " query += " ON UPDATE CASCADE; " qdb.execute_query(query) except: pass data_table = sms.DataTable('marker_alerts', marker_alerts[['ts', 'marker_id', 'data_id']]) db.df_write(data_table)
def main(): URL = "https://earthquake.phivolcs.dost.gov.ph/" eq_table = get_eq(URL) eq_data = read_tbl(eq_table, get_eqtbl_headers(eq_table)) query = "SELECT * FROM earthquake_events ORDER BY ts DESC LIMIT 1" start = pd.to_datetime( db.df_read(query, connection='analysis').ts.values[0]) - timedelta(1) eq_data = eq_data.loc[ eq_data.ts >= start, ['ts', 'latitude', 'longitude', 'depth', 'magnitude', 'province']] eq_data.loc[:, 'issuer'] = 'PHIV' data_table = sms.DataTable('earthquake_events', eq_data) db.df_write(data_table, connection='analysis')
def generate_surficial_alert(site_id = None,ts = None): """ Main alert generating function for surificial alert for a site at specified time Parameters ------------------ site_id: int site_id of site of interest ts: timestamp timestamp of alert generation Returns ------------------- Prints the generated alert and writes to marker_alerts database """ #### Obtain system arguments from command prompt if site_id == None and ts == None: site_id, ts = sys.argv[1].lower(),sys.argv[2].lower() #### Config variables num_pts = int(sc['surficial']['surficial_num_pts']) ts_start = pd.to_datetime(ts) - timedelta(sc['surficial']['meas_plot_window']) #### Get latest ground data surficial_data_df = qdb.get_surficial_data(site_id, ts_start, ts, num_pts) #### Generate Marker alerts marker_data_df = surficial_data_df.groupby('marker_id',as_index = False) marker_alerts = marker_data_df.apply(evaluate_marker_alerts, ts) #### Write to marker_alerts table data_table = sms.DataTable('marker_alerts', marker_alerts) db.df_write(data_table) #### Generate surficial alert for site surficial_alert = get_surficial_alert(marker_alerts,site_id) #### Write to db qdb.alert_to_db(surficial_alert,'operational_triggers') #### Plot current ground meas if sc['surficial']['print_meas_plot']: ### Retreive the surficial data to plot surficial_data_to_plot = surficial_data_df.loc[surficial_data_df.ts >= ts_start, :] ### Plot the surficial data plot_site_meas(surficial_data_to_plot, ts) return surficial_data_df
def to_mysql(df): """Writes in rainfall_priorities the distance of 4 active nearby rain gauges from the site. Args: df (dataframe): Record of 4 nearby rain gauges with its distance from the site. """ written_df = mem.get('df_rain_priorities') combined = written_df.append(df, ignore_index=True, sort=False) combined = combined.append(written_df, ignore_index=True, sort=False) combined = combined.drop_duplicates(['site_id', 'rain_id'], keep=False) if len(combined) > 0: data_table = sms.DataTable('rainfall_priorities', combined) db.df_write(data_table)
def subsurface(site_id, ts, alert_level): """Insert values to node_alerts, tsm_alerts, and operational_triggers to (re)trigger subsurface alert. Args: site_id (int): ID of site to compute subsurface analysis for. ts (datetime): Timestamp of alert trigger. alert_level (int: {0, 2, 3}, default None): Subsurface alert level. """ # get tsm_id query = "SELECT tsm_id FROM tsm_sensors " query += "where site_id = {} ".format(site_id) query += "and (date_deactivated is null or date_deactivated > '{}')".format(ts) tsm_id = db.df_read(query, resource='sensor_data').values.flatten() tsm_id = random.choice(tsm_id) # writes to node_alerts; defaults to node 1 and vel_alert ts_list = pd.date_range(end=ts, freq='30min', periods=4) node_alerts = pd.DataFrame({'ts': ts_list, 'node_id': [1]*len(ts_list), 'tsm_id': [tsm_id]*len(ts_list), 'disp_alert': [0]*len(ts_list), 'vel_alert': [alert_level]*len(ts_list)}) db.df_write(data_table = sms.DataTable("node_alerts", node_alerts), resource='sensor_data') # writes to tsm_alerts tsm_alerts = pd.DataFrame({'ts': [ts], 'tsm_id': [tsm_id], 'alert_level': [alert_level], 'ts_updated': [ts]}) db.df_write(data_table = sms.DataTable("tsm_alerts", tsm_alerts), resource='sensor_data') # writes to operational_triggers trigger_symbol = mem.get('df_trigger_symbols') trigger_sym_id = trigger_symbol.loc[(trigger_symbol.trigger_source == 'subsurface') & (trigger_symbol.alert_level == alert_level), 'trigger_sym_id'].values[0] operational_trigger = pd.DataFrame({'site_id': [site_id], 'trigger_sym_id': [trigger_sym_id], 'ts': [ts], 'ts_updated': [ts]}) qdb.alert_to_db(operational_trigger, 'operational_triggers') # details for trigger tech info tsm_alerts.loc[:, 'node_id'] = 1 tsm_alerts.loc[:, 'disp_alert'] = 0 tsm_alerts.loc[:, 'vel_alert'] = alert_level return tsm_alerts
def send_unsent_notif(df, curr_release): ts = curr_release.strftime('%I%p %B %d, %Y') if len(df) != 0: unsent_ewi = '\n'.join(list(map(lambda x: ': '.join(x), df.values))) sms_msg = 'Unsent EWI SMS (' + ts + '):\n\n' + unsent_ewi smsoutbox_user_status = get_recipient(curr_release) else: sms_msg = 'Sent all EWI SMS (' + ts + ')' smsoutbox_user_status = get_recipient(curr_release, unsent=False) smsoutbox_users = pd.DataFrame({ 'sms_msg': [sms_msg], 'source': ['central'] }) data_table = sms.DataTable('smsoutbox_users', smsoutbox_users) outbox_id = db.df_write(data_table, connection='gsm_pi', last_insert=True)[0][0] smsoutbox_user_status.loc[:, 'outbox_id'] = outbox_id data_table = sms.DataTable('smsoutbox_user_status', smsoutbox_user_status) db.df_write(data_table, connection='gsm_pi')
def send_reminder(ts = datetime.now()): shift_ts = release_time(ts)+timedelta(0.5) query = """SELECT * FROM monshiftsched WHERE ts = '{}'""".format(shift_ts) df = db.df_read(query) df = df.rename(columns={'iompmt': 'MT', 'iompct': 'CT'}) sched = (shift_ts-timedelta(hours=0.25)).strftime("%B %d, %Y %I:%M%p") greeting = ts.strftime("%p") if greeting == 'AM': greeting = 'morning' else: greeting = 'evening' query = """SELECT site_code FROM commons_db.sites WHERE active = 1 AND site_id NOT IN (SELECT site_id FROM analysis_db.markers WHERE in_use = 1)""" sites = ', '.join(db.df_read(query, resource= "sensor_analysis").values.flatten()).upper() sites = ', and'.join(sites.rsplit(',', 1)) IOMP_dict = df.loc[:, ['MT', 'CT']].to_dict(orient='records')[0] IOMP_num = get_mobile() for IOMP, name in IOMP_dict.items(): sms_msg = ("Monitoring shift reminder:\n\n" "Good {} {}, you are assigned to be the IOMP-{} for {}.\n\n" "Please be reminded that sites {} currently do not have markers installed. " "Instead of asking for ground measurement, please ask for " "ground observation.").format(greeting, name, IOMP, sched, sites) print(sms_msg, '\n') outbox = pd.DataFrame({'sms_msg': [sms_msg], 'source': ['central']}) mobile_id = IOMP_num.loc[IOMP_num.nickname == name, 'mobile_id'].values gsm_id = IOMP_num.loc[IOMP_num.nickname == name, 'gsm_id'].values if len(mobile_id) != 0 and len(gsm_id) != 0: data_table = sms.DataTable('smsoutbox_users', outbox) outbox_id = db.df_write(data_table, resource='sms_data', last_insert=True)[0][0] status = pd.DataFrame({'outbox_id': [outbox_id]*len(mobile_id), 'mobile_id': mobile_id, 'gsm_id': gsm_id}) data_table = sms.DataTable('smsoutbox_user_status', status) db.df_write(data_table, resource='sms_data') else: print("No mobile number")
def send_notif(ts=datetime.now()): start = lib.release_time(ts) - timedelta(hours=4) if (ts - start).total_seconds() / 3600 < 1.5: notif = olivia.main(ts) sms_msg = '\n'.join( list(filter(lambda x: x.startswith('Un'), notif.split('\nNo ')))) else: sms_msg = gndmeas.main(ts) if sms_msg != '': smsoutbox_user_status = get_recipient(ts) smsoutbox_users = pd.DataFrame({ 'sms_msg': [sms_msg], 'source': ['central'] }) data_table = sms.DataTable('smsoutbox_users', smsoutbox_users) outbox_id = db.df_write(data_table, connection='gsm_pi', last_insert=True)[0][0] smsoutbox_user_status.loc[:, 'outbox_id'] = outbox_id data_table = sms.DataTable('smsoutbox_user_status', smsoutbox_user_status) db.df_write(data_table, connection='gsm_pi')
def update_table_data(noah_id, gauge_name, fdate, tdate, noah_gauges): """Updates data of table gauge_name from fdate to tdate. Args: noah_id (int): Device id of noah data. gauge_name (str): Name of table containing rainfall data of noah_id. fdate (timestamp): Timestamp start of data to be downloaded. tdate (timestamp): Timestamp end of data to be downloaded. noah_gauges (dataframe): Rain gauge properties- id, name, data source. """ noah_data = download_rainfall_noah(noah_id, fdate, tdate) cur_ts = datetime.now() if noah_data.empty: qdb.print_out(" no data...") #Insert an entry with values: [timestamp, -1] as a marker #-1 values should not be included in computation of cml rainfall if pd.to_datetime(tdate) <= cur_ts: place_holder_data = pd.DataFrame({"ts": [tdate], "rain": [-1.0]}) data_table = sms.DataTable(gauge_name, place_holder_data) db.df_write(data_table) else: #Insert the new data on the noahid table data_table = sms.DataTable(gauge_name, noah_data) db.df_write(data_table) #The table is already up to date if pd.to_datetime(tdate) > cur_ts: return else: #call this function again until the maximum recent timestamp is hit update_single_table(noah_gauges)
def main(hours=''): auth_api = get_auth() username = '******' if hours == '': try: hours = int(sys.argv[1]) except: hours = 0.25 end_date = datetime.utcnow() - timedelta(hours=hours) for status in Cursor(auth_api.user_timeline, id=username).items(): text = auth_api.get_status(status.id, tweet_mode="extended").full_text if 'earthquake' in text.lower(): try: print(status.created_at) df = get_eq_events(text) data_table = sms.DataTable('earthquake_events', df) db.df_write(data_table) except: pass if status.created_at < end_date: break
def write_observation(surf_df, site_id): mo_df = surf_df.loc[:, ['site_id', 'ts', 'meas_type', 'observer_name']] mo_df.loc[:, 'data_source'] = 'ops' mo_df.loc[:, 'reliability'] = 1 mo_df.loc[:, 'weather'] = 'maaraw' mo_id = dbio.df_write(data_table=smsclass.DataTable( "marker_observations", mo_df), resource='sensor_data', last_insert=True)[0][0] if mo_id == 0: query = "SELECT marker_observations.mo_id FROM marker_observations " query += "WHERE ts = '{ts}' and site_id = '{site_id}'" query = query.format(ts=surf_df['ts'].values[0], site_id=site_id) mo_id = dbio.read(query, resource='sensor_data')[0][0] surf_df = surf_df.dropna(axis=1) md_df = surf_df.loc[:, surf_df.columns.astype(str).str.isnumeric()].transpose( ) md_df = md_df.reset_index() md_df.columns = ['marker_id', 'measurement'] md_df.loc[:, 'mo_id'] = mo_id dbio.df_write(data_table=smsclass.DataTable("marker_data", md_df), resource='sensor_data') ma.generate_surficial_alert(site_id, ts=mo_df.ts.values[0])
def drift_detection(acc_id="", f_time=pd.to_datetime(dt.now() - td(weeks=12))): accelerometers = memory.get('DF_ACCELEROMETERS') acc_det = accelerometers[accelerometers.accel_id == acc_id].iloc[0] try: df = q.get_raw_accel_data(tsm_id=acc_det.tsm_id, node_id=acc_det.node_id, accel_number=acc_det.accel_number, from_time=f_time) #lagpas yung node_id except ValueError: return 0 #walang table ng tilt_***** sa db except AttributeError: return 0 #walang laman yung df if df.empty: return 0 #Resample 30min df = df.set_index('ts').resample('30min').first() #Integer index N = len(df.index) df['i'] = range(1, N + 1, 1) # Compute accelerometer raw value df.x[df.x < -2048] = df.x[df.x < -2048] + 4096 df.y[df.y < -2048] = df.y[df.y < -2048] + 4096 df.z[df.z < -2048] = df.z[df.z < -2048] + 4096 # Compute accelerometer magnitude df['mag'] = ((df.x / 1024) * (df.x / 1024) + (df.y / 1024) * (df.y / 1024) + (df.z / 1024) * (df.z / 1024)).apply(np.sqrt) #count number of data dfw = pd.DataFrame() dfw['count'] = df.mag.resample('1W').count() # Filter data with very big/small magnitude df[df.mag > 3.0] = np.nan df[df.mag < 0.5] = np.nan # Compute mean and standard deviation in time frame df['ave'] = df.mag.rolling(window=12, center=True).mean() df['stdev'] = df.mag.rolling(window=12, center=True).std() # Filter data with outlier values in time frame df[(df.mag > df.ave + 3 * df.stdev) & (df.stdev != 0)] = np.nan df[(df.mag < df.ave - 3 * df.stdev) & (df.stdev != 0)] = np.nan #interpolate missing data df = df.interpolate() # Resample every six hours df = df.resample('6H').mean() # Recompute standard deviation after resampling df.stdev = df.mag.rolling(window=2, center=False).std() df.stdev = df.stdev.shift(-1) df.stdev = df.stdev.rolling(window=2, center=False).mean() # Filter data with large standard deviation df[df.stdev > 0.05] = np.nan # Compute velocity and acceleration of magnitude df['vel'] = df.mag - df.mag.shift(1) df['acc'] = df.vel - df.vel.shift(1) #Resample 1week dfw['vel_week'] = df.vel.resample('1W').mean() dfw['acc_week'] = df.acc.resample('1W').mean() dfw['corr'] = df.resample('1W').mag.corr(df.i) dfw['corr'] = dfw['corr']**2 # Get the data that exceeds the threshold value dfw = dfw[(abs(dfw['acc_week']) > 0.000003) & (dfw['corr'] > 0.7) & (dfw['count'] >= 84)] #Compute the difference for each threshold data if len(dfw) > 0: dfw = dfw.reset_index() dfw['diff_TS'] = dfw.ts - dfw.ts.shift(1) dfw['sign'] = dfw.vel_week * dfw.vel_week.shift(1) #Check if there are 4 weeks consecutive threshold data week = 1 days = td(days=0) while days < td(days=28) and week < len(dfw.index): if ((dfw.loc[week]['diff_TS'] <= td(days=14)) & (dfw.loc[week]['sign'] > 0)): days = days + dfw.loc[week]['diff_TS'] else: days = td(days=0) week = week + 1 if days >= td(days=28): print(acc_id, dfw.ts[week - 1]) # df['mag'].plot() # plt.savefig(OutputFP+col+nids+a+"-mag") # plt.close() dft = pd.DataFrame(columns=['accel_id', 'ts_identified']) dft.loc[0] = [acc_id, dfw.ts[week - 1]] #save to db db.df_write(smsclass.DataTable("drift_detection", dft)) print("very nice!")
def main(): eq_events = get_unprocessed() sym = get_alert_symbol() sites = get_sites() dfg = sites.groupby('site_id') eq_a = pd.DataFrame(columns=['site_id', 'eq_id', 'distance']) EVENTS_TABLE = 'earthquake_events' for i in eq_events.index: cur = eq_events.loc[i] mag = cur.magnitude eq_lat = cur.latitude eq_lon = cur.longitude ts = cur.ts critdist = get_crit_dist(mag) print(critdist) if False in np.isfinite([mag, eq_lat, eq_lon]): #has NaN value in mag, lat, or lon query = "UPDATE %s SET processed = -1 where eq_id = %s" % ( EVENTS_TABLE, i) dynadb.write(query=query, resource="sensor_data") continue if mag < 4: print("> Magnitude too small: %d" % (mag)) query = "UPDATE %s SET processed = 1 where eq_id = %s" % ( EVENTS_TABLE, i) dynadb.write(query=query, resource="sensor_data") continue else: print("> Magnitude reached threshold: %d" % (mag)) # magnitude is big enough to consider sites = dfg.apply(get_distance_to_eq, eq_lat=eq_lat, eq_lon=eq_lon) print(sites) crits = sites.loc[sites.distance <= critdist, :] if len(crits) == 0: print("> No affected sites. ") query = "UPDATE %s SET processed = 1, critical_distance = %s where eq_id = %s" % ( EVENTS_TABLE, critdist, i) dynadb.write(query=query, resource="sensor_data") continue else: #merong may trigger print(">> Possible sites affected: %d" % (len(crits.site_id.values))) crits.loc[:, 'ts'] = ts crits.loc[:, 'source'] = 'earthquake' crits.loc[:, 'trigger_sym_id'] = sym crits.loc[:, 'ts_updated'] = ts crits.loc[:, 'eq_id'] = i eq_a = crits.loc[:, ['eq_id', 'site_id', 'distance']] op_trig = crits.loc[:, ['ts', 'site_id', 'trigger_sym_id', 'ts_updated']] # write to tables data_table = sms.DataTable("operational_triggers", op_trig) dynadb.df_write(data_table) data_table = sms.DataTable("earthquake_alerts", eq_a) dynadb.df_write(data_table) query = "UPDATE %s SET processed = 1, critical_distance = %s where eq_id = %s " % ( EVENTS_TABLE, critdist, i) dynadb.write(query=query, resource="sensor_data") print(">> Alert iniated.\n")
def surficial(site_id, ts, alert_level): """Insert values to marker_observations, marker_data, marker_alerts, and operational_triggers to (re)trigger surficial alert. Args: site_id (int): ID of site to compute surficial analysis for. ts (datetime): Timestamp of alert trigger. alert_level (int: {0, 1, 2, 3}, default None): Surficial alert level. """ # get last data for site_id conn = mem.get('DICT_DB_CONNECTIONS') query = "SELECT ts, marker_id, marker_name, measurement " query += "FROM {analysis}.marker_observations " query += "INNER JOIN {common}.sites USING (site_id) " query += "INNER JOIN {analysis}.marker_data using (mo_id) " query += "INNER JOIN (SELECT data_id, displacement, time_delta, alert_level, processed FROM {analysis}.marker_alerts) sub1 USING (data_id) " query += "INNER JOIN (SELECT marker_id, marker_name FROM {analysis}.view_marker_history) sub2 USING (marker_id) " query += "WHERE site_id = {site_id} " query += "AND ts IN ( " query += " SELECT MAX(ts) FROM {analysis}.marker_observations " query += " WHERE ts < '{ts}' " query += " AND site_id = {site_id})" query = query.format(analysis=conn['analysis']['schema'], common=conn['common']['schema'], site_id=site_id, ts=ts) df = db.df_read(query, resource='sensor_analysis') # compute diff in measurements to reach threshold if alert_level == 3: rate = 1.8 elif alert_level in (1,2): rate = 0.25 else: rate = 0 meas_diff = np.ceil(rate * (ts-df.ts[0]).total_seconds()/3600) # input measurements in inbox gndmeas = df.loc[:, ['marker_id', 'marker_name', 'measurement']] gndmeas.loc[:, 'ts'] = ts gndmeas.loc[:, 'measurement'] += meas_diff if alert_level == 1: temp_gndmeas = gndmeas.copy() temp_gndmeas.loc[:, 'ts'] -= (ts - df.ts[0])/2 temp_gndmeas.loc[:, 'measurement'] += meas_diff # filler measurement for alert level 1 df_obv = pd.DataFrame({'meas_type': ['ROUTINE'], 'site_id': [site_id], 'weather': ['MAARAW'], 'observer_name':['TOPSSOFTWAREINFRA'], 'reliability': [1], 'data_source': ['SMS'], 'ts': [temp_gndmeas.ts[0]]}) mo_id = int(db.df_write(data_table=sms.DataTable("marker_observations", df_obv), resource='sensor_data', last_insert=True)[0][0]) temp_gndmeas.loc[:, 'mo_id'] = mo_id df_data = temp_gndmeas.loc[:, ['mo_id', 'marker_id', 'measurement']] db.df_write(data_table = sms.DataTable("marker_data", df_data), resource='sensor_data') surf.generate_surficial_alert(site_id = site_id, ts = temp_gndmeas.ts[0]) # measurement for ts given df_obv = pd.DataFrame({'meas_type': ['ROUTINE'], 'site_id': [site_id], 'weather': ['MAARAW'], 'observer_name':['TOPSSOFTWAREINFRA'], 'reliability': [1], 'data_source': ['SMS'], 'ts': [ts]}) mo_id = int(db.df_write(data_table=sms.DataTable("marker_observations", df_obv), resource='sensor_data', last_insert=True)[0][0]) gndmeas.loc[:, 'mo_id'] = mo_id df_data = gndmeas.loc[:, ['mo_id', 'marker_id', 'measurement']] db.df_write(data_table = sms.DataTable("marker_data", df_data), resource='sensor_data') surf.generate_surficial_alert(site_id = site_id, ts = ts) # details for trigger tech info time_delta = np.round((ts - df.ts[0]).total_seconds()/3600, 2) if alert_level == 1: time_delta /= 2 gndmeas.loc[:, 'displacement'] = meas_diff gndmeas.loc[:, 'time_delta'] = time_delta gndmeas.loc[:, 'alert_level'] = alert_level # writes to operational_triggers trigger_symbol = mem.get('df_trigger_symbols') trigger_sym_id = trigger_symbol.loc[(trigger_symbol.trigger_source == 'surficial') & (trigger_symbol.alert_level == alert_level), 'trigger_sym_id'].values[0] operational_trigger = pd.DataFrame({'site_id': [site_id], 'trigger_sym_id': [trigger_sym_id], 'ts': [ts], 'ts_updated': [ts]}) qdb.alert_to_db(operational_trigger, 'operational_triggers') return gndmeas
def alert_to_db(df, table_name): """Summary of cumulative rainfall, threshold, alert and rain gauge used in analysis of rainfall. Args: df (dataframe): Dataframe to be written to database. table_name (str): Name of table df to be written to. """ if does_table_exist(table_name) == False: #Create a tsm_alerts table if it doesn't exist yet if table_name == 'tsm_alerts': create_tsm_alerts() #Create a public_alerts table if it doesn't exist yet elif table_name == 'public_alerts': create_public_alerts() #Create a operational_triggers table if it doesn't exist yet elif table_name == 'operational_triggers': create_operational_triggers() else: print_out('unrecognized table : ' + table_name) return if table_name == 'operational_triggers': # checks trigger source query = "SELECT * FROM " query += " operational_trigger_symbols AS op " query += "INNER JOIN " query += " trigger_hierarchies AS trig " query += "ON op.source_id = trig.source_id " all_trig = db.df_read(query) trigger_source = all_trig[all_trig.trigger_sym_id == \ df['trigger_sym_id'].values[0]]['trigger_source'].values[0] # does not write nd subsurface alerts if trigger_source == 'subsurface': alert_level = all_trig[all_trig.trigger_sym_id == \ df['trigger_sym_id'].values[0]]['alert_level'].values[0] if alert_level == -1: return # if ts does not exist, writes alert; else: updates alert level elif trigger_source == 'surficial': query = "SELECT trigger_id, trig.trigger_sym_id FROM " query += " (SELECT trigger_sym_id, alert_level, alert_symbol, " query += " op.source_id, trigger_source FROM " query += " operational_trigger_symbols AS op " query += " INNER JOIN " query += " (SELECT * FROM trigger_hierarchies " query += " WHERE trigger_source = '%s' " % trigger_source query += " ) AS trig " query += " ON op.source_id = trig.source_id " query += " ) AS sym " query += "INNER JOIN " query += " (SELECT * FROM operational_triggers " query += " WHERE site_id = %s " % df['site_id'].values[0] query += " AND ts = '%s' " % df['ts'].values[0] query += " ) AS trig " query += "ON trig.trigger_sym_id = sym.trigger_sym_id" surficial = db.df_read(query) if len(surficial) == 0: data_table = sms.DataTable(table_name, df) db.df_write(data_table) else: trigger_id = surficial['trigger_id'].values[0] trigger_sym_id = df['trigger_sym_id'].values[0] if trigger_sym_id != surficial['trigger_sym_id'].values[0]: query = "UPDATE %s " % table_name query += "SET trigger_sym_id = '%s' " % trigger_sym_id query += "WHERE trigger_id = %s" % trigger_id db.write(query) return query = "SELECT * FROM " query += " (SELECT trigger_sym_id, alert_level, alert_symbol, " query += " op.source_id, trigger_source FROM " query += " operational_trigger_symbols AS op " query += " INNER JOIN " query += " (SELECT * FROM trigger_hierarchies " query += " WHERE trigger_source = '%s' " % trigger_source query += " ) AS trig " query += " ON op.source_id = trig.source_id " query += " ) AS sym " query += "INNER JOIN " query += " ( " else: query = "" if table_name == 'tsm_alerts': where_id = 'tsm_id' else: where_id = 'site_id' ts_updated = pd.to_datetime( df['ts_updated'].values[0]) - timedelta(hours=0.5) # previous alert query += " SELECT * FROM %s " % table_name query += " WHERE %s = %s " % (where_id, df[where_id].values[0]) query += " AND ((ts <= '%s' " % df['ts_updated'].values[0] query += " AND ts_updated >= '%s') " % df['ts_updated'].values[0] query += " OR (ts_updated <= '%s' " % df['ts_updated'].values[0] query += " AND ts_updated >= '%s')) " % ts_updated if table_name == 'operational_triggers': query += " ) AS trig " query += "ON trig.trigger_sym_id = sym.trigger_sym_id " query += "ORDER BY ts DESC LIMIT 1" df2 = db.df_read(query) if table_name == 'public_alerts': query = "SELECT * FROM %s " % table_name query += "WHERE site_id = %s " % df['site_id'].values[0] query += "AND ts = '%s' " % df['ts'].values[0] query += "AND pub_sym_id = %s" % df['pub_sym_id'].values[0] df2 = df2.append(db.df_read(query)) # writes alert if no alerts within the past 30mins if len(df2) == 0: data_table = sms.DataTable(table_name, df) db.df_write(data_table) # does not update ts_updated if ts in written ts to ts_updated range elif pd.to_datetime(df2['ts_updated'].values[0]) >= \ pd.to_datetime(df['ts_updated'].values[0]): pass # if diff prev alert, writes to db; else: updates ts_updated else: if table_name == 'tsm_alerts': alert_comp = 'alert_level' pk_id = 'ta_id' elif table_name == 'public_alerts': alert_comp = 'pub_sym_id' pk_id = 'public_id' else: alert_comp = 'trigger_sym_id' pk_id = 'trigger_id' same_alert = df2[alert_comp].values[0] == df[alert_comp].values[0] try: same_alert = same_alert[0] except: pass if not same_alert: data_table = sms.DataTable(table_name, df) db.df_write(data_table) else: query = "UPDATE %s " % table_name query += "SET ts_updated = '%s' " % df['ts_updated'].values[0] query += "WHERE %s = %s" % (pk_id, df2[pk_id].values[0]) db.write(query)
def parse_all_messages(args, allmsgs=[]): """ - Processing all messages that came from smsinbox_(loggers/users) and select parsing method dependent to sms message . :param args: arguement list of modes and criteria of sms message. :param allmsgs: list of all messages that being selected from loggers and users table. :type args: obj :type allmsgs: obj Returns: bool: True output for success parsing and return False if fails. """ read_success_list = [] read_fail_list = [] print("table:", args.table) ref_count = 0 if allmsgs == []: print('Error: No message to Parse') sys.exit() # total_msgs = len(all_msgs) # # sc = mem.server_config() # mc = mem.get_handle() # table_sim_nums = mc.get('%s_mobile_sim_nums' % args.table[:-1]) resource = "sensor_data" while allmsgs: is_msg_proc_success = True print('\n\n*******************************************************') sms = allmsgs.pop(0) ref_count += 1 if args.table == 'loggers': # start of sms parsing if re.search("^[A-Z]{3}X[A-Z]{1}\*U\*", sms.msg): df_data = extenso.uts(sms) if df_data: print(df_data.data) dbio.df_write(df_data, resource=resource) else: is_msg_proc_success = False if re.search("^[A-Z]{3}L[A-Z]{1}\*L\*", sms.msg): df_data = lidarparser.lidar(sms) if df_data: print(df_data.data) dbio.df_write(df_data, resource=resource) else: is_msg_proc_success = False elif re.search("\*FF", sms.msg) or re.search("PZ\*", sms.msg): is_msg_proc_success = process_piezometer(sms) # elif re.search("[A-Z]{4}DUE\*[A-F0-9]+\*\d+T?$",sms.msg): elif re.search("[A-Z]{4}DUE\*[A-F0-9]+\*.*", sms.msg): df_data = subsurface.v1(sms) if df_data: print(df_data[0].data, df_data[1].data) dbio.df_write(df_data[0], resource=resource) dbio.df_write(df_data[1], resource=resource) tsm_name = df_data[0].name.split("_") tsm_name = str(tsm_name[1]) timestamp = df_data[0].data.reset_index() timestamp = str(timestamp['ts'][0]) spawn_alert_gen(tsm_name, timestamp) else: print('>> Value Error') is_msg_proc_success = False elif re.search("^[A-Z]{4,5}\*[xyabcdXYABCD]\*[A-F0-9]+\*[0-9]+T?$", sms.msg): try: df_data = subsurface.v2(sms) if df_data: try: print(df_data.data) dbio.df_write(df_data, resource=resource) tsm_name = df_data.name.split("_") tsm_name = str(tsm_name[1]) timestamp = df_data.data.reset_index() timestamp = str(timestamp['ts'][0]) spawn_alert_gen(tsm_name, timestamp) except: print('>> SQL Error') else: print('>> Value Error') is_msg_proc_success = False except IndexError: print("\n\n>> Error: Possible data type error") print(sms.msg) is_msg_proc_success = False except ValueError: print(">> Value error detected") is_msg_proc_success = False except MySQLdb.ProgrammingError: print(">> Error writing data to DB") is_msg_proc_success = False elif re.search( "^[A-Z]{5}\*[A-Za-z0-9/+]{2}\*[A-Za-z0-9/+]+\*[0-9]{12}$", sms.msg): try: df_data = subsurface.b64Parser(sms) if df_data: print(df_data.data) dbio.df_write(df_data, resource=resource) tsm_name = df_data.name.split("_") tsm_name = str(tsm_name[1]) timestamp = df_data.data.reset_index() timestamp = str(timestamp['ts'][0]) spawn_alert_gen(tsm_name, timestamp) else: print('>>b64 Value Error') is_msg_proc_success = False except IndexError: print("\n\n>> Error: Possible data type error") print(sms.msg) is_msg_proc_success = False except ValueError: print(">> Value error detected") is_msg_proc_success = False except MySQLdb.ProgrammingError: print(">> Error writing data to DB") is_msg_proc_success = False elif re.search("[A-Z]{4}\*[A-F0-9]+\*[0-9]+$", sms.msg): df_data = subsurface.v1(sms) if df_data: print(df_data[0].data, df_data[1].data) dbio.df_write(df_data[0], resource=resource) dbio.df_write(df_data[1], resource=resource) tsm_name = df_data[0].name.split("_") tsm_name = str(tsm_name[1]) timestamp = df_data[0].data.reset_index() timestamp = str(timestamp['ts'][0]) spawn_alert_gen(tsm_name, timestamp) else: print('>> Value Error') is_msg_proc_success = False #check if message is from rain gauge elif re.search("^\w{4},[\d\/:,]+", sms.msg): df_data = rain.v3(sms) if df_data: print(df_data.data) dbio.df_write(df_data, resource=resource) else: print('>> Value Error') elif re.search("ARQ\+[0-9\.\+/\- ]+$", sms.msg): try: df_data = rain.rain_arq(sms) if df_data: print(df_data.data) dbio.df_write(df_data, resource=resource) else: print('>> Value Error') except: print("Kennex temp fix") pass elif (sms.msg.split('*')[0] == 'COORDINATOR' or sms.msg.split('*')[0] == 'GATEWAY'): is_msg_proc_success = process_gateway_msg(sms) elif common_logger_sms(sms) > 0: print('inbox_id: ', sms.inbox_id) print('match') else: print('>> Unrecognized message format: ') print('NUM: ', sms.sim_num) print('MSG: ', sms.msg) is_msg_proc_success = False elif args.table == 'users': if re.search("EQINFO", sms.msg.upper()): data_table = parser.earthquake.eq(sms) if data_table: dbio.df_write(data_table, resource=resource) else: is_msg_proc_success = False elif re.search("ACK \d+ .+", sms.msg.upper()): is_msg_proc_success = amsg.process_ack_to_alert(sms) elif re.search("^ *(R(O|0)*U*TI*N*E )|(EVE*NT )", sms.msg.upper()): is_msg_proc_success = process_surficial_observation(sms) else: print("User SMS not in known template.", sms.msg) is_msg_proc_success = True else: raise ValueError("Table value not recognized (%s)" % (args.table)) sys.exit() if is_msg_proc_success: read_success_list.append(sms.inbox_id) else: read_fail_list.append(sms.inbox_id) print(">> SMS count processed:", ref_count) # method for updating the read_status all messages that have been processed # so that they will not be processed again in another run if ref_count % 200 == 0: smstables.set_read_status(read_success_list, read_status=1, table=args.table, host=args.dbhost) smstables.set_read_status(read_fail_list, read_status=-1, table=args.table, host=args.dbhost) read_success_list = [] read_fail_list = [] smstables.set_read_status(sms_id_list=read_success_list, read_status=1, table=args.table, host=args.dbhost) smstables.set_read_status(sms_id_list=read_fail_list, read_status=-1, table=args.table, host=args.dbhost)
def process_surficial_observation(sms): """ - Process the sms message that fits for surficial observation and save paserse message to database. :param sms: list data info of sms message . :type sms: list Returns: bool: True output for success process and return False if fails. """ mc = mem.get_handle() surf_mark = mc.get("DF_SURFICIAL_MARKERS") reply_msgs = mc.get("surficial_parser_reply_messages") sc = mem.server_config() ct_sim_num = str(sc["surficial"]["ct_sim_num"]) enable_analysis = sc["surficial"]["enable_analysis"] SEND_REPLY_TO_COMMUNITY = sc["surficial"]["send_reply_to_community"] SEND_ACK_TO_CT_PHONE = sc["surficial"]["send_ack_to_ct_phone"] resource = "sensor_data" obv = [] try: obv = parser.surficial.observation(sms.msg) except ValueError as err_val: err_val = int(str(err_val)) mc = mem.get_handle() messages = mc.get("surficial_parser_reply_messages") # print messages.iloc[err_val - 1].internal_msg # print messages.iloc[err_val - 1].external_msg sms_msg_for_operations = "{}\n\n{}".format( messages.iloc[err_val - 1].internal_msg, sms.msg) smstables.write_outbox(sms_msg_for_operations, ct_sim_num) return False site_surf_mark = surf_mark[surf_mark["site_id"] == obv["obv"]["site_id"]] df_meas = pd.DataFrame() df_meas = df_meas.from_dict(obv["markers"]["measurements"], orient='index') df_meas.columns = ["measurement"] markers = site_surf_mark.join(df_meas, on="marker_name", how="outer") # send message for unknown marker names markers_unk = markers[~(markers["marker_id"] > 0)] markers_unk = markers_unk[["marker_name", "measurement"]] markers_unk = markers_unk.set_index(["marker_name"]) markers_unk = markers_unk.to_dict() internal_msg = "DEWSL Beta:\n\n%s\n\n" % (sms.msg) if len(markers_unk["measurement"].keys()) > 0: internal_msg += "%s\n%s\n\n" % (reply_msgs.iloc[13]["internal_msg"], "\n".join(["%s = %s" % (key, value) for (key, value) in \ markers_unk["measurement"].items()])) # send message for unreported marker measurements markers_nd = markers[~(markers["measurement"] > 0)] markers_nd = markers_nd[["marker_name", "measurement"]].to_dict() if len(markers_nd["marker_name"].keys()) > 0: internal_msg += "%s\n%s" % (reply_msgs.iloc[14]["internal_msg"], ", ".join(["%s" % name for name in \ markers_nd["marker_name"].values()])) internal_msg += "\n\n" print(">> Updating observations") df_obv = pd.DataFrame(obv["obv"], index=[0]) mo_id = dbio.df_write(data_table=smsclass.DataTable( "marker_observations", df_obv), resource=resource, last_insert=True) try: mo_id = int(mo_id[0][0]) except (ValueError, TypeError): print( "Error: conversion of measurement observation id during last insert" ) internal_msg += "\n\nERROR: Resultset conversion" smstables.write_outbox(internal_msg, ct_sim_num) return False print(">> Updating marker measurements") if mo_id == 0: # Duplicate entry query = ("SELECT marker_observations.mo_id FROM marker_observations " "WHERE ts = '{}' and site_id = '{}'".format( obv["obv"]['ts'], obv["obv"]['site_id'])) mo_id = dbio.read(query, resource=resource)[0][0] markers_ok = markers[markers["marker_id"] > 0] markers_ok = markers_ok[markers_ok["measurement"] > 0] markers_ok_for_report = markers_ok[["marker_name", "measurement"]] markers_ok = markers_ok[["marker_id", "measurement"]] markers_ok["mo_id"] = mo_id markers_ok.columns = ["%s" % (str(col)) for col in markers_ok.columns] dbio.df_write(data_table=smsclass.DataTable("marker_data", markers_ok), resource=resource) # send success messages markers_ok_for_report = markers_ok_for_report.set_index(["marker_name"]) markers_ok_for_report = markers_ok_for_report.to_dict() updated_measurements_str = "\n".join(["%s = %0.2f CM" % (name, meas) \ for name, meas in markers_ok_for_report["measurement"].items()]) success_msg = "%s\n%s\n%s" % ( reply_msgs.iloc[12]["external_msg"], dt.strptime(obv["obv"]["ts"], "%Y-%m-%d %H:%M:%S").strftime("%c"), updated_measurements_str) internal_msg += "Updated measurements:\n%s" % (updated_measurements_str) # for ct phone c/o iomp-ct if SEND_ACK_TO_CT_PHONE: smstables.write_outbox(internal_msg, ct_sim_num) # for community who sent the data if SEND_REPLY_TO_COMMUNITY: smstables.write_outbox(success_msg, sms.sim_num) # spawn surficial measurement analysis if enable_analysis: obv = obv["obv"] surf_cmd_line = "python %s %d '%s' > %s 2>&1" % ( sc['fileio']['gndalert1'], obv['site_id'], obv['ts'], sc['fileio']['surfscriptlogs']) subprocess.Popen(surf_cmd_line, stdout=subprocess.PIPE, shell=True, stderr=subprocess.STDOUT) return True
def summary_writer(site_id, site_code, gauge_name, rain_id, twoyrmax, halfmax, rainfall, end, write_alert): """Summary of cumulative rainfall, threshold, alert and rain gauge used in analysis of rainfall. Args: site_id (int): ID per site. site_code (str): Three-letter code per site. gauge_name (str): Rain gauge used in rainfall analysis. rain_id (int): ID of gauge_name. twoyrmax (float): Threshold for 3-day cumulative rainfall per site. halfmax (float): Threshold for 1-day cumulative rainfall per site. rainfall (str): Data to compute cumulative rainfall from. end (datetime): End timestamp of alert to be computed. write_alert (bool): To write alert in database. Returns: dataframe: Summary of cumulative rainfall, threshold, alert and rain gauge used in analysis of rainfall. """ one, three = one_three_val_writer(rainfall, end) #threshold is reached if one >= halfmax or three >= twoyrmax: ralert = 1 #no data elif one == None or math.isnan(one): ralert = -1 #rainfall below threshold else: ralert = 0 if write_alert or ralert == 1: if qdb.does_table_exist('rainfall_alerts') == False: #Create a site_alerts table if it doesn't exist yet qdb.create_rainfall_alerts() columns = ['rain_alert', 'cumulative', 'threshold'] alerts = pd.DataFrame(columns=columns) if ralert == 0: if one >= halfmax * 0.75: temp_df = pd.Series(['x', one, halfmax], index=columns) elif three >= twoyrmax * 0.75: temp_df = pd.Series(['x', three, twoyrmax], index=columns) else: temp_df = pd.Series([False, np.nan, np.nan], index=columns) alerts = alerts.append(temp_df, ignore_index=True, sort=False) else: if one >= halfmax: temp_df = pd.Series(['a', one, halfmax], index=columns) alerts = alerts.append(temp_df, ignore_index=True, sort=False) if three >= twoyrmax: temp_df = pd.Series(['b', three, twoyrmax], index=columns) alerts = alerts.append(temp_df, ignore_index=True, sort=False) if ralert == -1: temp_df = pd.Series([False, np.nan, np.nan], index=columns) alerts = alerts.append(temp_df, ignore_index=True, sort=False) if alerts['rain_alert'][0] != False: for index, row in alerts.iterrows(): rain_alert = row['rain_alert'] cumulative = row['cumulative'] threshold = row['threshold'] if qdb.does_alert_exists(site_id, end, rain_alert).values[0][0] == 0: df = pd.DataFrame({ 'ts': [end], 'site_id': [site_id], 'rain_id': [rain_id], 'rain_alert': [rain_alert], 'cumulative': [cumulative], 'threshold': [threshold] }) data_table = sms.DataTable('rainfall_alerts', df) db.df_write(data_table) summary = pd.DataFrame({ 'site_id': [site_id], 'site_code': [site_code], '1D cml': [one], 'half of 2yr max': [round(halfmax, 2)], '3D cml': [three], '2yr max': [round(twoyrmax, 2)], 'DataSource': [gauge_name], 'rain_id': [rain_id], 'alert': [ralert] }) return summary
def write_marker_alerts(df, connection='analysis'): data_table = sms.DataTable('marker_alerts', df) db.df_write(data_table, connection=connection)
def write_rain_data(gauge_name, df, connection='analysis'): data_table = sms.DataTable(gauge_name, df) db.df_write(data_table, connection=connection)
def write_rain_gauges(df, connection='analysis'): data_table = sms.DataTable('rainfall_gauges', df) db.df_write(data_table, connection=connection)