def site_alerts(curr_trig, ts, release_data_ts): site_id = curr_trig['site_id'].values[0] query = "SELECT site_id, stat.trigger_id, trigger_source, alert_level FROM " query += " (SELECT * FROM alert_status " query += " WHERE ts_last_retrigger >= '%s' " % (ts - timedelta(1)) query += " ) as stat " query += "INNER JOIN " query += " (SELECT trigger_id, site_id, trigger_source, alert_level FROM " query += " (SELECT * FROM operational_triggers " query += " WHERE site_id = %s " % site_id query += " ) as op " query += " INNER JOIN " query += " (SELECT trigger_sym_id, trigger_source, alert_level FROM " query += " operational_trigger_symbols AS trig_sym " query += " INNER JOIN " query += " trigger_hierarchies AS trig " query += " ON trig.source_id = trig_sym.source_id " query += " ) as sym " query += " ON op.trigger_sym_id = sym.trigger_sym_id " query += " ) as sub " query += "ON stat.trigger_id = sub.trigger_id" sent_alert = qdb.get_db_dataframe(query) query = "SELECT * FROM alert_status" query += " WHERE trigger_id in (%s)" %(','.join(map(lambda x: str(x), \ set(curr_trig['trigger_id'].values)))) written = qdb.get_db_dataframe(query) site_curr_trig = curr_trig[~curr_trig.trigger_id.isin(written.trigger_id)] site_curr_trig = site_curr_trig.sort_values('alert_level', ascending=False) site_curr_trig = site_curr_trig.drop_duplicates('trigger_source') if len(site_curr_trig) == 0: qdb.print_out('no new trigger for site_id %s' % site_id) return if len(sent_alert) == 0: pass elif max(site_curr_trig.alert_level) <= max(sent_alert.alert_level): if max(sent_alert.alert_level) > 1 or \ (max(site_curr_trig.alert_level) == 1 and \ 'surficial' not in site_curr_trig['trigger_source'].values): qdb.print_out('no higher trigger') return site_curr_trig = site_curr_trig[site_curr_trig.trigger_source == 'surficial'] else: site_curr_trig = site_curr_trig[ site_curr_trig.alert_level > max(sent_alert.alert_level)] alert_status = site_curr_trig[['ts_last_retrigger', 'trigger_id']] alert_status = alert_status.rename(columns={'ts': 'ts_last_retrigger'}) alert_status['ts_set'] = datetime.now() qdb.push_db_dataframe(alert_status, 'alert_status', index=False)
def get_monitoring_type(site_id, end): """Type of monitoring: 'event' or 'routine'. Extended monitoring is tagged as 'routine' since it requires surficial data once a day. For simplicity, sites not under monitoring (event, extended, routine) are tagged 'routine'. Args: site_id (int): ID of each site. end (datetime): Current public alert timestamp. Returns: str: 'event' or 'routine'. """ query = "SELECT alert_type FROM " query += " (SELECT * FROM public_alerts " query += " WHERE site_id = %s " % site_id query += " AND ((ts_updated <= '%s' " % end query += " AND ts_updated >= '%s') " % (end - timedelta(hours=0.5)) query += " OR (ts_updated >= '%s' " % end query += " AND ts <= '%s')) " % end query += " ORDER BY ts DESC LIMIT 1 " query += " ) AS pub " query += "INNER JOIN " query += " public_alert_symbols AS sym " query += "ON pub.pub_sym_id = sym.pub_sym_id" monitoring_type = qdb.get_db_dataframe(query)['alert_type'].values[0] return monitoring_type
def get_operational_trigger(site_id, start_monitor, end): """Dataframe containing alert level on each operational trigger from start of monitoring. Args: site_id (dataframe): ID each site. start_monitor (datetime): Timestamp of start of monitoring. end (datetime): Public alert timestamp. Returns: dataframe: Contains timestamp range of alert, three-letter site code, operational trigger, alert level, and alert symbol from start of monitoring """ query = "SELECT op.trigger_id, op.trigger_sym_id, ts, site_id, source_id, alert_level, " query += "alert_symbol, ts_updated FROM" query += " (SELECT * FROM operational_triggers " query += " WHERE site_id = %s" % site_id query += " AND ts_updated >= '%s' AND ts <= '%s' " % (start_monitor, end) query += " ) AS op " query += "INNER JOIN " query += " operational_trigger_symbols AS sym " query += "ON op.trigger_sym_id = sym.trigger_sym_id " query += "ORDER BY ts DESC" op_trigger = qdb.get_db_dataframe(query) return op_trigger
def get_alert_history(current_events): site_id = current_events['site_id'].values[0] start_ts = current_events['ts'].values[0] public_alert_symbols = current_events['alert_symbol'].values[0] query = "SELECT CONCAT(cdb.firstname, ' ', cdb.lastname) as iomp, " query += "sites.site_code, OTS.alert_symbol, ALS.ts_last_retrigger, OTS.alert_level, " query += "ALS.remarks, TH.trigger_source, ALS.alert_status, PAS.alert_symbol as public_alert_symbol " query += "FROM alert_status as ALS " query += " JOIN operational_triggers as OT " query += " ON ALS.trigger_id = OT.trigger_id " # LOUIE - added commons_db. query += " JOIN commons_db.sites " query += " ON sites.site_id = OT.site_id " query += " JOIN operational_trigger_symbols as OTS " query += " ON OT.trigger_sym_id = OTS.trigger_sym_id " query += " JOIN trigger_hierarchies as TH " query += " ON OTS.source_id = TH.source_id " query += " JOIN comms_db.users as cdb " query += " ON ALS.user_id = cdb.user_id " query += " JOIN public_alerts as PA" query += " ON PA.site_id = OT.site_id" query += " JOIN public_alert_symbols as PAS " query += " ON PA.pub_sym_id = PAS.pub_sym_id " query += "WHERE OT.site_id = '%s' " % site_id query += "AND OT.ts >= '%s' " % start_ts query += "AND PAS.alert_symbol = '%s' " % public_alert_symbols query += "ORDER BY OT.ts DESC" current_events_history = qdb.get_db_dataframe(query) return current_events_history
def get_site_moms_alerts(site_id, start, end): """ Returns sorted site moms and highest moms alert level or -1 if no moms is in db Args: TODO """ query = "SELECT * FROM senslopedb.monitoring_moms as moms" query = f"{query} JOIN senslopedb.moms_instances as mi" query = f"{query} ON moms.instance_id = mi.instance_id" query = f"{query} JOIN commons_db.sites as site" query = f"{query} ON mi.site_id = site.site_id" query = f"{query} WHERE site.site_id = {site_id}" query = f"{query} AND moms.observance_ts >= '{start}'" query = f"{query} AND moms.observance_ts <= '{end}'" query = f"{query} ORDER BY moms.observance_ts DESC" site_moms_alerts_df = qdb.get_db_dataframe(query) sorted_df = site_moms_alerts_df.sort_values(['op_trigger'], ascending=[False]) moms_op_trigger = 0 if not sorted_df.empty: moms_op_trigger = sorted_df.iloc[0]["op_trigger"] else: moms_op_trigger = -1 return site_moms_alerts_df, moms_op_trigger
def get_moms_feature_types(): """Dataframe containing moms feature types for use in tech info """ query = "SELECT feature_id, feature_type FROM moms_features" moms_types = qdb.get_db_dataframe(query) return moms_types
def to_DB(df): df = df.rename(columns={'dyna': 'alert', 'sandbox': 'alert_ref'}) if not qdb.does_table_exist('db_comparison', hostdb='sandbox'): create_db_comparison() query = "SELECT EXISTS (SELECT * FROM db_comparison" query += " WHERE ts = '%s' AND site_code = '%s')" % ( df['ts'].values[0], df['site_code'].values[0]) if qdb.get_db_dataframe(query, hostdb='sandbox').values[0][0] == 0: qdb.push_db_dataframe(df, 'db_comparison', index=False)
def event_start(site_id, end): """Timestamp of start of event monitoring. Start of event is computed by checking if event progresses from non A0 to higher alert. Args: site_id (int): ID of each site. end (datetime): Current public alert timestamp. Returns: datetime: Timestamp of start of monitoring. """ query = "SELECT ts, ts_updated FROM " query += " (SELECT * FROM public_alerts " query += " WHERE site_id = %s " % site_id query += " AND (ts_updated <= '%s' " % end query += " OR (ts_updated >= '%s' " % end query += " AND ts <= '%s')) " % end query += " ) AS pub " % site_id query += "INNER JOIN " query += " (SELECT * FROM public_alert_symbols " query += " WHERE alert_type = 'event') AS sym " query += "ON pub.pub_sym_id = sym.pub_sym_id " query += "ORDER BY ts DESC LIMIT 3" # previous positive alert prev_pub_alerts = qdb.get_db_dataframe(query) if len(prev_pub_alerts) == 1: start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[0]) # two previous positive alert elif len(prev_pub_alerts) == 2: # one event with two previous positive alert if pd.to_datetime(prev_pub_alerts['ts'].values[0]) - \ pd.to_datetime(prev_pub_alerts['ts_updated'].values[1]) <= \ timedelta(hours=0.5): start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[1]) else: start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[0]) # three previous positive alert else: if pd.to_datetime(prev_pub_alerts['ts'].values[0]) - \ pd.to_datetime(prev_pub_alerts['ts_updated'].values[1]) <= \ timedelta(hours=0.5): # one event with three previous positive alert if pd.to_datetime(prev_pub_alerts['ts'].values[1]) - \ pd.to_datetime(prev_pub_alerts['ts_updated'].values[2]) \ <= timedelta(hours=0.5): start_monitor = pd.to_datetime(prev_pub_alerts['ts']\ .values[2]) # one event with two previous positive alert else: start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[1]) else: start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[0]) return start_monitor
def query_surficial_alerts(site_id, latest_trigger_ts): query = "SELECT * FROM marker_alerts as ma " query += "JOIN site_markers as sm " query += "ON ma.marker_id = sm.marker_id " query += "WHERE sm.site_id = '%s' and ts = '%s'" % (site_id, latest_trigger_ts) query += "AND alert_level > 0" result = qdb.get_db_dataframe(query) return result
def query_od_alerts(site_id, latest_trigger_ts): query = "SELECT * FROM senslopedb.public_alert_on_demand as paod " + \ f"WHERE site_id = {site_id} and ts = '{latest_trigger_ts}'" result = qdb.get_db_dataframe(query) if not result.empty: result = result.to_dict('records')[0] return result
def get_unprocessed_data(): """ Get dataframe of unprocessed marker alert. """ query = "SELECT * FROM unprocessed" df = qdb.get_db_dataframe(query) return df
def get_surficial_data(): """ Get dataframe of all marker measurements. """ query = "SELECT * FROM marker_meas" df = qdb.get_db_dataframe(query) return df
def query_eq_alerts(site_id, latest_trigger_ts): query = "SELECT * FROM senslopedb.earthquake_events " + \ f"WHERE site_id = {site_id} and ts = '{latest_trigger_ts}'" result = qdb.get_db_dataframe(query) if not result.empty: result = result.to_dict('records')[0] return result
def query_current_events(end): query = "SELECT PA.ts, PA.ts_updated, PA.site_id, PAS.alert_symbol FROM public_alerts as PA " query += " JOIN public_alert_symbols as PAS " query += " ON PA.pub_sym_id = PAS.pub_sym_id " query += " WHERE PAS.alert_level > 0 " query += " AND ts_updated >= '%s' " % end query += " ORDER BY ts DESC " events = qdb.get_db_dataframe(query) current_events = events.groupby('site_id', as_index=False) return current_events
def get_public_symbols(): """Dataframe containing public alert level and its corresponding symbol. """ query = "SELECT * FROM public_alert_symbols" public_symbols = qdb.get_db_dataframe(query) public_symbols = public_symbols.sort_values(['alert_type', 'alert_level'], ascending=[True, False]) return public_symbols
def query_rainfall_alerts(site_id, latest_trigger_ts): query = "SELECT ra.*, rp.* " query += "FROM rainfall_alerts AS ra " query += "JOIN rain_props AS rp " query += "ON ra.rain_id = rp.rain_id " query += "AND ra.site_id = rp.site_id " query += "WHERE ra.site_id = '%s' AND ts = '%s'" % (site_id, latest_trigger_ts) result = qdb.get_db_dataframe(query) return result
def query_moms_alerts(site_id, latest_trigger_ts): query = "SELECT * FROM senslopedb.monitoring_moms as moms " + \ "JOIN senslopedb.moms_instances as mi " + \ "ON moms.instance_id = mi.instance_id " + \ "JOIN commons_db.sites as site " + \ "ON mi.site_id = site.site_id " + \ f"WHERE site.site_id = '{site_id}'" + \ f"AND moms.observance_ts = '{latest_trigger_ts}'" + \ f"AND moms.op_trigger > 0" result = qdb.get_db_dataframe(query) return result
def get_trigger_symbols(): """Dataframe containing operational trigger alert level and its corresponding id/symbol. """ 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 += " trigger_hierarchies AS trig " query += "ON op.source_id = trig.source_id" trig_symbols = qdb.get_db_dataframe(query) return trig_symbols
def query_tsm_alerts(site_id, start_ts, latest_trigger_ts): query = "SELECT ts, t_s.tsm_name, node_id, disp_alert, vel_alert FROM node_alerts " query += "JOIN tsm_sensors AS t_s" query += " ON node_alerts.tsm_id = t_s.tsm_id " query += "JOIN " query += " (SELECT site_code, site_id FROM sites WHERE site_id = '%s') AS sc " % ( site_id) query += " ON t_s.site_id = sc.site_id " query += "WHERE ts >= '%s' " % (start_ts) query += "AND ts <= '%s' " % (latest_trigger_ts) query += "AND t_s.tsm_name LIKE CONCAT(sc.site_code, '%') " query += "ORDER BY ts DESC" result = qdb.get_db_dataframe(query) return result
def get_surficial_data(site_id, use_plot=False): """ Get dataframe of surficial measurement per marker per marker observation. Get only last 10 marker observation if not for plotting. """ query = "select * " query += "from " query += " (select data_id, ts, marker_id, meas, alert_level " query += " from " query += " (select distinct(ts), mo_id from marker_observations " query += " where site_id = %s " % site_id query += " order by ts desc " if not use_plot: query += "limit 10 " query += " ) as mo " query += " inner join " query += " (select marker_id, data_id, mo_id, meas, alert_level " query += " from " query += " marker_data " query += " inner join " query += " markers " query += " using (marker_id) " query += " ) as marker_meas " query += " using (mo_id) " query += " ) as data " query += " inner join " query += " (select marker_id, name " query += " from " query += " marker_histories " query += " inner join " query += " (select max(ts) as ts, marker_id " query += " from marker_histories " query += " where event_id in ( " query += " select event_id " query += " from marker_events " query += " where event_type in ( " query += " 'rename', 'add')) " query += " group by marker_id " query += " ) as hist " query += " using (ts, marker_id) " query += " ) as marker_name " query += " using (marker_id)" df = qdb.get_db_dataframe(query) return df
def replace_rainfall_alert_if_rx(internal_df, internal_symbols, site_id, end, rainfall_id, rain75_id): """Current internal alert sysmbol: includes rainfall symbol if above 75% of threshold Args: internal_df (dataframe): Current internal alert level and sysmbol. internal_symbols (dataframe): Internal alert symbols and id corresponding to its alert level. site_id (dataframe): ID each site. end (datetime): Public alert timestamp. rainfall_id (int): id of rainfall operational trigger Returns: dataframe: alert symbol indicating event triggers, data presence and status of rainfall. """ query = "SELECT * FROM rainfall_alerts " query += "where site_id = '%s' " % site_id query += "and ts = '%s'" % end rainfall_df = qdb.get_db_dataframe(query) is_x = False if len(rainfall_df) != 0: is_x = True if rainfall_id in internal_df['source_id'].values: rain_alert = internal_symbols[internal_symbols.trigger_sym_id == \ rain75_id]['alert_symbol'].values[0] trigger_sym_id = internal_symbols[internal_symbols.trigger_sym_id == \ rain75_id]['trigger_sym_id'].values[0] internal_df.loc[internal_df.source_id == rainfall_id, 'alert_symbol'] = rain_alert internal_df.loc[internal_df.source_id == rainfall_id, 'trigger_sym_id'] = trigger_sym_id else: rain_df = internal_symbols[internal_symbols.trigger_sym_id == \ rain75_id] rain_df['alert_symbol'] = rain_df['alert_symbol'].apply(lambda x: \ x.lower()) internal_df = internal_df.append(rain_df, ignore_index=True) return internal_df, is_x
def get_tsm_alert(site_id, end): """Dataframe containing alert level on each tsm sensor Args: site_id (dataframe): ID each site. end (datetime): Public alert timestamp. Returns: dataframe: Contains tsm name, alert level, and alert symbol for current release """ query = "SELECT tsm_name, sub.alert_level FROM " query += " (SELECT tsm_name, alert_level FROM " query += " (SELECT * FROM tsm_alerts " query += " WHERE ts <= '%s' " % end query += " AND ts_updated >= '%s' " % (end - timedelta(hours=0.5)) query += " ORDER BY ts DESC " query += " ) AS alert " query += " INNER JOIN " query += " (SELECT tsm_id, tsm_name FROM tsm_sensors " query += " WHERE site_id = %s " % site_id query += " ) AS tsm " query += " ON tsm.tsm_id = alert.tsm_id) " query += " AS sub " query += "INNER JOIN " query += " (SELECT sym.source_id, alert_symbol, alert_level FROM " query += " (SELECT source_id, alert_level, alert_symbol FROM " query += " operational_trigger_symbols " query += " ) AS sym " query += " INNER JOIN " query += " (SELECT source_id FROM trigger_hierarchies " query += " WHERE trigger_source = 'subsurface' " query += " ) AS hier " query += " ON hier.source_id = sym.source_id " query += " ) AS sub2 " query += "ON sub.alert_level = sub2.alert_level" subsurface = qdb.get_db_dataframe(query) subsurface = subsurface.drop_duplicates('tsm_name') return subsurface
def get_internal_symbols(): """Dataframe containing trigger alert level, source and, hierarchy in writing its symbol in internal alert """ query = "SELECT trigger_sym_id, trig.source_id, trigger_source, " query += "alert_level, alert_symbol, hierarchy_id FROM ( " query += " SELECT op.trigger_sym_id, source_id, alert_level, " query += " inte.alert_symbol FROM " query += " internal_alert_symbols AS inte " query += " INNER JOIN " query += " operational_trigger_symbols AS op " query += " ON op.trigger_sym_id = inte.trigger_sym_id " query += " ) AS sub " query += "INNER JOIN " query += " trigger_hierarchies AS trig " query += "ON trig.source_id = sub.source_id " query += "ORDER BY hierarchy_id" internal_symbols = qdb.get_db_dataframe(query) return internal_symbols
def main(): start_time = datetime.now() qdb.print_out(start_time) ts = pub.round_data_ts(start_time) release_data_ts = pub.release_time(ts) - timedelta(hours=0.5) if qdb.does_table_exist('operational_triggers') == False: qdb.create_operational_triggers() query = "SELECT trigger_id, ts, site_id, trigger_source, " query += "alert_level, ts_updated FROM " query += " (SELECT * FROM operational_triggers " query += " WHERE ts <= '%s' " % ts query += " AND ts_updated >= '%s' " % (ts - timedelta(1)) query += " ) AS op " query += "INNER JOIN " query += " (SELECT trigger_sym_id, alert_level, trigger_source FROM " query += " (SELECT * FROM operational_trigger_symbols " query += " WHERE alert_level > 0 " query += " ) AS trig_sym " query += " INNER JOIN " query += " trigger_hierarchies AS trig " query += " ON trig_sym.source_id = trig.source_id " query += " ) AS sym " query += "ON op.trigger_sym_id = sym.trigger_sym_id " query += "ORDER BY ts_updated DESC" curr_trig = qdb.get_db_dataframe(query) if len(curr_trig) == 0: qdb.print_out('no new trigger') return if not qdb.does_table_exist('alert_status'): qdb.create_alert_status() curr_trig = curr_trig.rename(columns={"ts_updated": "ts_last_retrigger"}) site_curr_trig = curr_trig.groupby('site_id', as_index=False) site_curr_trig.apply(site_alerts, ts=ts, release_data_ts=release_data_ts)
def writeOperationalTriggers(site_id, end): query = "SELECT sym.alert_level, trigger_sym_id FROM ( " query += " SELECT alert_level FROM " query += " (SELECT * FROM tsm_alerts " query += " where ts <= '%s' " % end query += " and ts_updated >= '%s' " % end query += " ) as ta " query += " INNER JOIN " query += " (SELECT tsm_id FROM tsm_sensors " query += " where site_id = %s " % site_id query += " ) as tsm " query += " on ta.tsm_id = tsm.tsm_id " query += " ) AS sub " query += "INNER JOIN " query += " (SELECT trigger_sym_id, alert_level FROM " query += " operational_trigger_symbols AS op " query += " INNER JOIN " query += " (SELECT source_id FROM trigger_hierarchies " query += " WHERE trigger_source = 'subsurface' " query += " ) AS trig " query += " ON op.source_id = trig.source_id " query += " ) as sym " query += "on sym.alert_level = sub.alert_level" df = qdb.get_db_dataframe(query) trigger_sym_id = df.sort_values( 'alert_level', ascending=False)['trigger_sym_id'].values[0] operational_trigger = pd.DataFrame({ 'ts': [end], 'site_id': [site_id], 'trigger_sym_id': [trigger_sym_id], 'ts_updated': [end] }) qdb.alert_to_db(operational_trigger, 'operational_triggers')
def main(end=datetime.now()): """Compiles all alerts to compute for public alert and internal alert. Writes result to public_alert table and publicalert.json Args: end (datetime): Optional. Public alert timestamp. """ start_time = datetime.now() print(start_time) # LOUIE # qdb.print_out(start_time) end = round_data_ts(pd.to_datetime(end)) # alert symbols # public alert public_symbols = get_public_symbols() pub_map = alert_map(public_symbols) # internal alert symbols internal_symbols = get_internal_symbols() # operational triggers symbols trig_symbols = get_trigger_symbols() # subsurface alert subsurface_map = trig_symbols[trig_symbols.trigger_source == 'subsurface'] subsurface_map = alert_map(subsurface_map) # surficial alert surficial_map = trig_symbols[trig_symbols.trigger_source == 'surficial'] surficial_map = alert_map(surficial_map) # Manifestation Of Movement moms_map = trig_symbols[trig_symbols.trigger_source == 'moms'] moms_map = alert_map(moms_map) # rainfall alert rain_map = trig_symbols[trig_symbols.trigger_source == 'rainfall'] rain_map = alert_map(rain_map) # LOUIE # site id and code query = "SELECT site_id, site_code FROM commons_db.sites WHERE active = 1" props = qdb.get_db_dataframe(query) props = props[props.site_code == 'mar'] site_props = props.groupby('site_id', as_index=False) alerts = site_props.apply(site_public_alert, end=end, public_symbols=public_symbols, internal_symbols=internal_symbols, start_time=start_time).reset_index(drop=True) alerts = alerts.sort_values(['public_alert', 'site_code'], ascending=[False, True]) # map alert level to alert symbol alerts['public_alert'] = alerts['public_alert'].map(pub_map) alerts['rainfall'] = alerts['rainfall'].map(rain_map) alerts['surficial'] = alerts['surficial'].map(surficial_map) alerts['moms'] = alerts['moms'].map(moms_map) site_alerts = alerts.groupby('site_code', as_index=False) alerts = site_alerts.apply(subsurface_sym, sym_map=subsurface_map).reset_index(drop=True) # map invalid alerts current_events = query_current_events(end) current_alerts = current_events.apply(get_alert_history) columns = [ 'iomp', 'site_code', 'alert_symbol', 'ts_last_retrigger', 'alert_level', 'remarks', 'trigger_source', 'alert_status', 'public_alert_symbol' ] invalid_alerts = pd.DataFrame(columns=columns) try: for site in current_alerts.site_code.unique(): site_df = current_alerts[current_alerts.site_code == site] count = len(site_df) for i in range(0, count): if site_df.alert_status.values[i] == -1: alert = pd.Series(site_df.values[i], index=columns) invalid_alerts = invalid_alerts.append(alert, ignore_index=True) else: invalid_alerts = invalid_alerts invalid_alerts = invalid_alerts.drop_duplicates( ['alert_symbol', 'site_code']) invalid_alerts['ts_last_retrigger'] = invalid_alerts[ 'ts_last_retrigger'].apply(lambda x: str(x)) except AttributeError as att_err: print("Empty dataframe") invalid_alerts = pd.DataFrame() except Exception as err: raise (err) all_alerts = pd.DataFrame({ 'invalids': [invalid_alerts], 'alerts': [alerts] }) public_json = all_alerts.to_json(orient="records") output_path = os.path.abspath( os.path.join(os.path.dirname(__file__), '../..')) sc = qdb.memcached() # LOUIE if not os.path.exists(output_path + sc['fileio']['output_path']): os.makedirs(output_path + sc['fileio']['output_path']) print(output_path) with open( output_path + sc['fileio']['output_path'] + 'PublicAlertRefDB.json', 'w') as w: w.write(public_json) # LOUIE print( f"PublicAlertRefDB.json written at {output_path+sc['fileio']['output_path']}" ) print('runtime = %s' % (datetime.now() - start_time)) return alerts
Site surficial plot """ if site_id == '': site_id = sys.argv[1].lower() data = get_surficial_data(site_id, use_plot=True) fig = plt.figure() ax = fig.add_subplot(111) for name in sorted(set(data['name'])): marker_data = data[data.name == name] ax = plotter(marker_data, ax) fmt = md.DateFormatter('%b %Y') ax.xaxis.set_major_formatter(fmt) plt.legend(loc=2) plt.xticks(rotation=45) fig.tight_layout() plt.savefig('surficial' + str(site_id) + '.png', dpi=200, facecolor='w', edgecolor='w', orientation='landscape', mode='w') ############################################################################### if __name__ == "__main__": query = "SELECT site_id FROM sites" sites = qdb.get_db_dataframe(query)['site_id'].values for site_id in sites: surficial_plot(site_id)