def get_faultcount_by_vobcid_loc(start_date, end_date,fault_code, velocity_dropdown = None, apstatus = None ): start_date,end_date = util.date2str2(start_date,end_date) faults = '' veld = '' apstat = '' if (fault_code != -1 ): faults = " and faultCode = {}".format(fault_code) if velocity_dropdown is 0: veld = " and velocity = 0" if velocity_dropdown is 1: veld = " and NOT(velocity = 0)" if apstatus is 1: apstat = " and activePassiveStatus = true" if apstatus is 0: apstat = " and activePassiveStatus = false" query = ("SELECT vobcid, locationName, count(vobcid) as FaultCount from dlr_vobc_fault" " where loggedAt >= '{}' and loggedAt < '{}' and faultCodeSet = true {} {} {}" " group by vobcid, locationName" " order by FaultCount desc" " LIMIT 300").format(start_date, end_date, faults, veld, apstat) df = util.run_query(query) return df
def get_commLoss_by_vobcid_loc(start_date, end_date, velocity_dropdown=None, apstatus=None, commtype=None): start_date, end_date = util.date2str2(start_date, end_date) veld = '' apstat = '' commT = '' if velocity_dropdown is 0: veld = " and velocity = 0" if velocity_dropdown is 1: veld = " and NOT(velocity = 0)" if apstatus is 1: apstat = " and activePassiveStatus = true" if apstatus is 0: apstat = " and activePassiveStatus = false" if commtype is not None and commtype != -1: commT = " and commType = {}".format(commtype) query = ( "SELECT vobcid, locationName, count(commType) as commLossCount from dlr_vobc_comloss" " where commType > 0 and commType < 6 and loggedAt >= '{}' and loggedAt < '{}' {} {} {}" " group by vobcid, locationName" " order by commLossCount desc" " LIMIT 300").format(start_date, end_date, veld, apstat, commT) df = util.run_query(query) if df is None: df = pd.DataFrame() return df
def get_faultcount_by_vobcid_loc_date(start_date, end_date, vobcid, fault_code, location, velocity_dropdown = None, apstatus = None): faults = '' vobcs = '' loc = '' veld = '' apstat = '' if (fault_code != -1 ): faults = " and faultCode = {}".format(fault_code) if (vobcid != -1 ): vobcs = " and vobcid = {}".format(vobcid) if (location is not None ): loc = " and locationName = '{}'".format(location) if velocity_dropdown is 0: veld = " and velocity = 0" if velocity_dropdown is 1: veld = " and NOT(velocity = 0)" if apstatus is 1: apstat = " and activePassiveStatus = true" if apstatus is 0: apstat = " and activePassiveStatus = false" start_date,end_date = util.date2str2(start_date,end_date) query = ("SELECT HISTOGRAM(loggedAt, INTERVAL 1 DAY) as date, count(loggedAt) as FaultCount" " from dlr_vobc_fault" " where loggedAt >= '{}' and loggedAt < '{}' and faultCodeSet = true {} {} {} {} {}" " GROUP BY date").format(start_date, end_date, vobcs, faults, loc, veld, apstat) df = util.run_query(query) return df
def get_commLoss_list(start_date, end_date, vobc_id=None, location=None, velocity=None, apstatus=None, commtype=None): start_date, end_date = util.date2str2(start_date, end_date) query = "SELECT vobcid, parentTrainId, loggedAt, velocity, activePassiveStatus, locationName, commType from dlr_vobc_comloss where commType > 0 and commType < 6 and loggedAt >= '{}' and loggedAt < '{}'".format( start_date, end_date) if vobc_id is not None and vobc_id != -1: query += " and vobcid = {}".format(vobc_id) if (location is not None): query += " and locationName = '{}'".format(location) if velocity is 0: query += " and velocity = 0" if velocity is 1: query += " and NOT(velocity = 0)" if apstatus is 1: query += " and activePassiveStatus = true" if apstatus is 0: query += " and activePassiveStatus = false" if commtype is not None and commtype != -1: query += " and commType = {}".format(commtype) df = util.run_query(query) if df is None: df = pd.DataFrame() return df
def gen_train_mileage_table(vobcid, start_date): start_date = util.str2date1(start_date) end_date = start_date + timedelta(hours=24) start_date, end_date = util.date2str2(start_date, end_date) df = get_train_mileage_by_30min(vobcid, start_date, end_date) df = df.dropna() s = df["Distance"].sum() df2 = get_train_mileage_by_loop(vobcid, start_date, end_date) df2 = df2.dropna() df = df.append(df2, ignore_index=True, sort=False) if df.empty: return df df = df.sort_values(by='time') df["vobcid"] = vobcid df["vobcid"] = df["vobcid"].astype(str) df2 = pd.DataFrame({ "vobcid": ["Total:"], "loopName": [""], "time": [""], "Distance": [s] }) df2 = df2.append(df, ignore_index=True, sort=False) data = df2.to_dict('rows') return data
def get_mileage_by_train(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT vobcid, SUM(distance)/1000 as Distance FROM dlr_train_move " " where loggedAt >= '{}' and loggedAt < '{}' group by vobcid").format( start_date, end_date) L = util.run_query(query) return L
def get_trainId(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT vobcid from dlr_train_move where loggedAt >= '{}' and loggedAt < '{}' " " group by vobcid").format(start_date, end_date) L = util.run_query(query) L = L["vobcid"].tolist() return L
def get_vobc_fault_list(start_date, end_date,faultCode): start_date, end_date = util.date2str2(start_date, end_date) query = ("SELECT loggedAt, faultCodeSet, faultCode,parentTrainId, vehicleName," " vobcid, activePassiveStatus as VobcStatus, locationName, loopName, velocity, " " faultDescription FROM dlr_vobc_fault where loggedAt >= '{}' and loggedAt < '{}' and faultCode = {}" " and faultCodeSet = true").format(start_date, end_date, faultCode) L = util.run_query(query) return L
def get_fleet_daily_mileage(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT loggedDate, SUM(distance)/1000 as daily_distance FROM dlr_train_move " " where loggedAt >= '{}' and loggedAt < '{}' group by loggedDate" ).format(start_date, end_date) L = util.run_query(query) return L
def get_fault_names(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ("SELECT faultName FROM dlr_vobc_fault " " where faultCode > 0 and loggedAt >= '{}' and loggedAt < '{}' group by faultName").format(start_date, end_date) L = util.run_query(query) if L.empty == False: return L["faultName"].tolist() return []
def get_switchId(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT switchId from dlr_switch_move where loggedAt >= '{}' and loggedAt < '{}' " " group by switchId").format(start_date, end_date) L = util.run_query(query) L = L["switchId"].tolist() return L
def get_fault_types_per_day(start_date, end_date, vobcid = None): start_date, end_date = util.date2str2(start_date, end_date) if vobcid is not None: x = ("and vobcid = {}").format(vobcid) else: x = "" query = ("SELECT HISTOGRAM(loggedAt, INTERVAL 1 Day) as loggedDay, faultName, count(*) as faultcount FROM dlr_vobc_fault " " where faultCode > 0 and loggedAt >= '{}' and loggedAt < '{}' {} group by faultName, loggedDay").format(start_date, end_date, x) L = util.run_query(query) return L
def gen_box_df(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) df = gen_bx_df_(start_date, end_date) df2 = get_unlock_count(start_date, end_date) if not df2.empty: df = df.set_index('switchId').join(df2.set_index('switchId')) df = df.fillna(value=0) else: df["count"] = 0 return df
def test_datecheck(): start_date = dt(2014, 1, 1) end_date = dt(2020, 1, 1) start_date, end_date = util.date2str2(start_date, end_date) start_date, end_date = vm.datecheck(start_date, end_date) start_date1, end_date1 = vm.datecheck(end_date, start_date) start_date2, end_date2 = vm.datecheck(None, None) assert start_date == start_date1 assert end_date == end_date1 assert start_date2 is not None assert end_date2 is not None
def get_op_hours(startDate, endDate, trainId): month = [] hours = [] while startDate < endDate: end = startDate + relativedelta(months=+1) start_, end_ = util.date2str2(startDate, end) df = get_operating_hours_by_month(start_, end_, trainId) hours.append(df) month.append(startDate) startDate = end d = {"loggedMonth": month, "op_time": hours} df = pd.DataFrame(data=d) df["vobcid"] = trainId return df
def get_unique_vobcid_list(start_date, end_date, trainId): start_date, end_date = util.date2str2(start_date, end_date) if trainId == None or end_date == None or start_date == None: return [] query = ("SELECT vobcid from dlr_train_move" " where trainId = '{}' and loggedAt >= '{}' and loggedAt < '{}'" " group by vobcid").format(trainId, start_date, end_date) df = util.run_query(query) if df is None or df.empty: df = pd.DataFrame() return df df = df['vobcid'].unique() return df
def gen_box_date_df(switchId, start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) df = gen_bx_date_df_(switchId, start_date, end_date) df2 = get_unlock_count_by_date(switchId, start_date, end_date) df["loggedDate"] = pd.to_datetime(df["loggedDate"]) # df["loggedDate"] = df["loggedDate"].tz_localize(None) df = df.set_index('loggedDate').tz_localize(None) if not df2.empty: df2 = df2.set_index('loggedDate').tz_localize(None) df = df.join(df2) df = df.fillna(value=0) else: df["count"] = 0 df.index = df.index.astype(str) return df
def create_fig_by_trend(fault_code, start_date, end_date, vobc_id, velocity=None, apstatus=None): start_date, end_date = util.date2str2(start_date, end_date) title = 'vobc={}, fault={}'.format(vobc_id, fault_code) fig = go.Figure() # make_subplots(rows=1, cols=2) df = vobcfault_m.get_count_trend(fault_code, start_date, end_date, vobc_id, velocity, apstatus) if (not df.empty): y_max = df.groupby(['LoggedDate']).max().max() * 1.01 for fc_code in sorted(df['faultCode'].unique()): df_fc = df[df['faultCode'] == fc_code] fig.add_trace( go.Scatter(x=df_fc['LoggedDate'], y=df_fc['FaultCount'], showlegend=False, line_color=cfg.vobc_fault_color_dict[fc_code], stackgroup='one')) fig.update_xaxes(title_text=title) #, type='category') fig.update_yaxes(range=[0, y_max], title_text='fault count by date') # df = vobcfault_m.get_count_location(fault_code, start_date, end_date, vobc_id) # if (not df.empty): # y_max = df.groupby(['LocationName']).max().max() * 1.01 # for fc_code in sorted(df['faultCode'].unique()): # df_fc = df[df['faultCode']==fc_code] # fig.add_trace(go.Bar(x=df_fc['LocationName'], y=df_fc['FaultCount'], # showlegend = False, # marker=dict(color=cfg.vobc_fault_color_dict[fault_code]) # ), # row=1,col=2) # #fig.update_xaxes(row = 1, col = 2, title_text=title)#, type='category') # fig.update_yaxes(row = 1, col = 2, range=[0,y_max], title_text='fault count by location') fig.update_layout(barmode='stack') #, row = 2, col = 1) fig.update_layout(height=300, margin=dict(l=2, r=10, t=30, b=2), hovermode='closest') return fig
def get_count_location(fault_code, start_date, end_date, vobcid): fault_condition = '' vobc_condition = '' if (fault_code != -1 ): fault_condition = " and faultCode = {}".format(fault_code) if (vobcid != -1 ): vobc_condition = " and vobcid = {}".format(vobcid) start_date,end_date = util.date2str2(start_date,end_date) query = ("SELECT faultName, faultCode, locationName as LocationName, count(*) as FaultCount" " from dlr_vobc_fault" " where vobcid <=300 and loggedAt >= '{}' and loggedAt < '{}' {} {}" " group by faultName, faultCode, locationName LIMIT 5000").format(start_date, end_date, fault_condition, vobc_condition) df = util.run_query(query) return df
def get_commLoss(start_date, end_date, vobcid): if vobcid == None or end_date == None or start_date == None: return pd.DataFrame() start_date, end_date = util.date2str2(start_date, end_date) query = ("SELECT commLossCount, loggedAt, velocity from dlr_train_move" " where vobcid = '{}' and loggedAt >= '{}' and loggedAt < '{}'" ).format(vobcid, start_date, end_date) df = util.run_query(query) if df is None: df = pd.DataFrame() return df
def create_fig_by_vobc(fault_code, start_date, end_date, velocity=None, apstatus=None): df_res = vobcfault_m.get_count_by(fault_code, start_date, end_date, velocity, apstatus) df_list = [] df_list.append( df_res[(df_res['VOBCID'] <= 150)].sort_values(by=['VOBCID'])) df_list.append(df_res[(df_res['VOBCID'] > 150)].sort_values(by=['VOBCID'])) fig = make_subplots(rows=2, cols=1, shared_yaxes=True) i = 1 for df in df_list: j = 0 for fault_code in sorted(df['faultCode'].unique()): df_fc = df[df['faultCode'] == fault_code] fig.add_trace(go.Bar( name=vobcfault_m.get_fault_name(fault_code), x=df_fc['VOBCID'], y=df_fc['FaultCount'], legendgroup=vobcfault_m.get_fault_name(fault_code), showlegend=i == 1, marker=dict(color=cfg.vobc_fault_color_dict[fault_code])), row=i, col=1) j += 1 i += 1 y_max = df.groupby(['VOBCID']).sum().FaultCount.max() * 1.01 start_date, end_date = util.date2str2(start_date, end_date) fig.update_layout(barmode='stack', height=500, hovermode='closest', margin=dict(l=2, r=2, t=30, b=2)) fig.update_xaxes(row=1, col=1, dtick=4, title_text='vobc id') #, type='category') fig.update_xaxes(row=2, col=1, dtick=4, title_text='vobc id') #, type='category') fig.update_yaxes(range=[0, y_max], title_text='fault count') return fig
def gen_bx_date_df_(switchId, start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) result = query_interval_by_date(switchId, start_date, end_date) Ldate = [] L = [] d0 = [] mean = [] d1 = [] D98 = [] D985 = [] D99 = [] D995 = [] D999 = [] D = [] for i in range(len(result['aggregations']['opDate']['buckets'])): Ldate.append( result['aggregations']['opDate']['buckets'][i]["key_as_string"]) L.append(result['aggregations']['opDate']['buckets'][i]['box_interval'] ['values']['1.0']) d0.append(result['aggregations']['opDate']['buckets'][i] ['box_interval']['values']['25.0']) mean.append(result['aggregations']['opDate']['buckets'][i] ['box_interval']['values']['50.0']) d1.append(result['aggregations']['opDate']['buckets'][i] ['box_interval']['values']['75.0']) D98.append(result['aggregations']['opDate']['buckets'][i] ['box_interval']['values']['98.0']) D985.append(result['aggregations']['opDate']['buckets'][i] ['box_interval']['values']['98.5']) D99.append(result['aggregations']['opDate']['buckets'][i] ['box_interval']['values']['99.0']) D995.append(result['aggregations']['opDate']['buckets'][i] ['box_interval']['values']['99.5']) D999.append(result['aggregations']['opDate']['buckets'][i] ['box_interval']['values']['99.9']) D.append(result['aggregations']['opDate']['buckets'][i]['box_interval'] ['values']['100.0']) df = pd.DataFrame(list( zip(Ldate, L, d0, mean, d1, D98, D985, D99, D995, D999, D)), columns=[ "loggedDate", "min", "d0", "mean", "d1", "98%", "98.5%", "99%", "99.5%", "99.9%", "100%" ]) return df
def gen_bx_df_(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) result = query_interval_by_switch(start_date, end_date) Swid = [] L = [] d0 = [] mean = [] d1 = [] U98 = [] U985 = [] U99 = [] U995 = [] U999 = [] U = [] for i in range(len(result['aggregations']['switchId']['buckets'])): Swid.append(result['aggregations']['switchId']['buckets'][i]['key']) L.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['1.0']) d0.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['25.0']) mean.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['50.0']) d1.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['75.0']) U98.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['98.0']) U985.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['98.5']) U99.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['99.0']) U995.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['99.5']) U999.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['99.9']) U.append(result['aggregations']['switchId']['buckets'][i] ['box_interval']['values']['100.0']) df = pd.DataFrame(list( zip(Swid, L, d0, mean, d1, U98, U985, U99, U995, U999, U)), columns=[ 'switchId', "min", "d0", "mean", "d1", "98%", "98.5%", "99%", "99.5%", "99.9%", "100%" ]) return df
def get_switch_line_df(switchId, start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) df = get_switch_linechart_data(switchId, start_date, end_date) mapping = { 'Left': 1, 'Moving': 2, 'Right': 3, "Locked": 5, "UnLocked": 6, "Move Left": 8, "No Command": 9, "Move Right": 10 } df = df.replace({ 'positionDesc': mapping, 'statusDesc': mapping, 'switchCommandDesc': mapping }) return df
def get_trainmove(parent_id, start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) if (parent_id == None or parent_id == -1): return None query = ( "SELECT activePassiveStatus, loggedAt, loggedDate, loopName, velocity, vobcid, trainId, maximumVelocity, doorCmd, doorStatus" " from dlr_train_move " " where trainId = {} and loggedAt >= '{}' and loggedAt < '{}'" " order by loggedAt LIMIT 10000 ").format(parent_id, start_date, end_date) df = util.run_query(query) if df is not None and not df.empty: df['doorStatus'] = df['doorStatus'].apply(lambda x: x * 10 - 35) df['doorCmd'] = df['doorCmd'].apply(lambda x: x * 10 - 35) df['loggedAt'] = pd.to_datetime(df['loggedAt']) return df
def gen_cmd_to_switch_by_dates(switchId, startDate, endDate, l): mapping = {'Left': 1, 'Right': 2} data = pd.DataFrame() while startDate < endDate: end = startDate + timedelta(days=2) start_, end_ = util.date2str2(startDate, end) l.acquire() try: df = get_switch_data(switchId, start_, end_) finally: l.release() #df = get_switch_data(switchId, start_, end_) if df is not None: if df.empty == False: df = df.replace({'positionDesc': mapping}) data = data.append(getcmd_switch_times(df)) startDate = end data["switchId"] = switchId return data
def get_commLoss_by_vobcid_loc_date(start_date, end_date, vobcid, location, velocity_dropdown=None, apstatus=None, commtype=None): vobcs = '' loc = '' veld = '' apstat = '' commT = '' if (vobcid != -1): vobcs = " and vobcid = {}".format(vobcid) if (location is not None): loc = " and locationName = '{}'".format(location) if velocity_dropdown is 0: veld = " and velocity = 0" if velocity_dropdown is 1: veld = " and NOT(velocity = 0)" if apstatus is 1: apstat = " and activePassiveStatus = true" if apstatus is 0: apstat = " and activePassiveStatus = false" if commtype is not None and commtype != -1: commT = " and commType = {}".format(commtype) start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT HISTOGRAM(loggedAt, INTERVAL 1 DAY) as date, count(*) as commLossCount" " from dlr_vobc_comloss" " where commType > 0 and commType < 6 and loggedAt >= '{}' and loggedAt < '{}' {} {} {} {} {}" " GROUP BY date").format(start_date, end_date, vobcs, loc, veld, apstat, commT) df = util.run_query(query) return df
def get_fault_list(start_date,end_date, vobc_id = None, faultCode = None, location = None, velocity= None, apstatus = None): start_date,end_date = util.date2str2(start_date,end_date) query = "SELECT vobcid, parentTrainId, faultName, faultCode, loggedAt, velocity, faultCodeSet, activePassiveStatus, locationName from dlr_vobc_fault where loggedAt >= '{}' and loggedAt < '{}'".format(start_date,end_date) if vobc_id is not None and vobc_id != -1: query += " and vobcid = {}".format(vobc_id) if faultCode is not None and faultCode != 0 and faultCode != -1: query += " and faultCode = {}".format(faultCode) if (location is not None ): query += " and locationName = '{}'".format(location) if velocity is 0: query += " and velocity = 0" if velocity is 1: query += " and NOT(velocity = 0)" if apstatus is 1: query += " and activePassiveStatus = true" if apstatus is 0: query += " and activePassiveStatus = false" df = util.run_query(query) return df
def get_count_by(fault_code, start_date, end_date, velocity = None, apstatus = None): fault_condition = '' apstat ='' vel = '' if (fault_code != -1): fault_condition = " and faultCode = {}".format(fault_code) if velocity is 0: vel += " and velocity = 0" if velocity is 1: vel += " and NOT(velocity = 0)" if apstatus is 1: apstat += " and activePassiveStatus = true" if apstatus is 0: apstat += " and activePassiveStatus = false" start_date,end_date = util.date2str2(start_date,end_date) query = ("SELECT faultName, faultCode, vobcid as VOBCID, count(*) as FaultCount" " from dlr_vobc_fault " " where vobcid <= 300 and faultCodeSet = True and loggedAt >= '{}' and loggedAt < '{}' {} {} {} " " group by faultName, faultCode, vobcid " " LIMIT 10000 ").format( start_date, end_date, fault_condition, vel, apstat) df = util.run_query(query) return df
from modules import module_switch_self_move as ms import pandas as pd from datetime import datetime as dt from datetime import timedelta import util as util import config as cfg import pytest start_date = dt(2014, 1, 1) end_date = dt(2020, 1, 1) start_date, end_date = util.date2str2(start_date, end_date) def test_get_switch_amts(): df = ms.get_switch_amts(101) assert df is not None assert isinstance(df, pd.DataFrame) assert df.empty == False def test_get_switch_count(): x = ms.get_switch_count() assert x is not None assert x is not 0 def test_gen_3d_df(): df = ms.gen_3d_df() assert df is not None assert isinstance(df, pd.DataFrame)