def loads(): logger.info(f'ENV {ENV}') # time_=datetime.datetime(2021, 1, 10, 10, 30, 0, 0) # day_before = now_ - datetime.timedelta(days=1) now_ = datetime.datetime.now() neighborhood_df, cells_df = neighborhood(time_=now_) now_ = datetime.datetime.now() load_rets(time_=now_) # ok now_ = datetime.datetime.now() neighborhood_df, cells_df = load_terrains( time_=now_, neighborhood_df=neighborhood_df, cells_df=cells_df, ) now_ = datetime.datetime.now() load_overshooters( time_=now_, neighborhood_df=neighborhood_df, cells_df=cells_df, )
def rets_data(time_=None): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return when_ = time_ period = when_.strftime("%Y-%m-%d") query_ = f''' select x.datetimeid, x.node, x.devicename, x.deviceno, x.tilt, x.subname, x.subunitno, y.localcellid, y.eci, y.cellname from (select ret.dateid as datetimeid, ret.node as node, ret.devicename as devicename, ret.deviceno as deviceno, sub.tilt as tilt, sub.subname as subname, sub.subunitno as subunitno from ret inner join retsubunit sub on date(ret.dateid) = date(sub.dateid) and ret.node = sub.node and ret.deviceno = sub.deviceno where date(ret.dateid) = current_date) as x inner join lcellreference as y on (x.node = y.node and (x.deviceno = y.localcellid or x.deviceno = y.localcellid + 10) and STR_TO_DATE(y.dateid, '%Y-%m-%d') = '{period}'); ''' return pd_sql(time_=time_, query_=query_)
def pd_sql(time_=None, query_=None): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return try: cnx = mysql.connector.connect( user=user_, password=password_, host=host_, database=database_, use_pure=True, ) query = query_ df = pd.read_sql(query,cnx) logger.info(f'df.shape {df.shape}') cnx.close() return df except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: logger.error("Something is wrong with your user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: logger.error("Database does not exist") else: logger.error(err) else: cnx.close()
def overshooting_intensity(neighborhood_df=pd.DataFrame(), ta_df=pd.DataFrame()): global TA_COLUMNS, TA_INDEX logger.info(f'overshooting_intensity:') if neighborhood_df.empty: neighborhood_df = neighborhood() neighborhood_df.reset_index(inplace=True) l = ['CELLNAME', 'distance_'] t = ['CELLNAME'] overshooters_intensity_df = neighborhood_df[l].groupby(t).mean() overshooters_intensity_df.reset_index(inplace=True) overshooters_intensity_df[ 'distance_index'] = overshooters_intensity_df.apply( distance_percentaje_index, axis=1) if ta_df.empty: ta_df = get_ta_df() l = [ 'L_RA_TA_UE_Index0', 'L_RA_TA_UE_Index1', 'L_RA_TA_UE_Index2', 'L_RA_TA_UE_Index3', 'L_RA_TA_UE_Index4', 'L_RA_TA_UE_Index5', 'L_RA_TA_UE_Index6', 'L_RA_TA_UE_Index7', 'L_RA_TA_UE_Index8', 'L_RA_TA_UE_Index9', 'L_RA_TA_UE_Index10', 'L_RA_TA_UE_Index11', ] ra_ta_df = ta_df[l] cumulative_df = ra_ta_df.cumsum(axis=1, skipna=True) l = ['Cell_Name'] ta_df_ = pd.concat([ta_df[l], cumulative_df], axis=1) merged_df = pd.merge(overshooters_intensity_df, ta_df_, how="inner", left_on='CELLNAME', right_on='Cell_Name').drop_duplicates() # merged_df['overshooting'] = merged_df.apply(_overshooting_intensity, axis=1) # --------------------------------------- merged_df['cum_sum_out'] = merged_df.apply(cum_sum_out, axis=1) merged_df['overs_intensity'] = merged_df.apply(_overshooting_intensity, axis=1) # --------------------------------------- new_merged_df = merged_df.drop(['Cell_Name'], axis=1) return neighborhood_df, new_merged_df
def load_rets(time_=None): logger.info(f'ENV {ENV}') if not time_: return list_ = [ { 'datetimeid': time_, 'node': 'MBTS-AIS_3G_003', 'cellname': 'AIS_4G_003_3', 'eci': 2816002, 'devicename': 'RET82', 'deviceno': 2, 'tilt': 30, 'subname': 'RET82', 'subunitno': 1, 'localcellid': 2, }, { 'datetimeid': time_, 'node': 'MBTS-ARA_3G_013', 'cellname': 'ARA_4G_013_3', 'eci': 2304258, 'devicename': 'RET82R_S3', 'deviceno': 2, 'tilt': 40, 'subname': 'RET82R_S3', 'subunitno': 1, 'localcellid': 2, }, { 'datetimeid': time_, 'node': 'MBTS-ARA_3G_013', 'cellname': 'ARA_4G_013_3', 'eci': 2304258, 'devicename': 'RET82L_S3', 'deviceno': 12, 'tilt': 40, 'subname': 'RET82L_S3', 'subunitno': 1, 'localcellid': 2, }, ] if ENV == 'sim': df = pd.DataFrame.from_dict(list_) if ENV == 'prod': df = rets_data(time_=time_) engine = get_engine() session = get_session(engine=engine) df.to_sql('rets', con=engine, if_exists='append', index=False) session.commit() session.close()
def get_period_data(time_=None): logger.info(f'ENV {ENV}') if ENV == 'sim': period = time_.strftime("%Y%m%d") df = pd.read_json(f'./data/_prs_lte_hour_{period}.json') if ENV == 'prod': df = kpi_data(time_=time_) return df
def neighborhood(time_=None): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return cells_df = get_cells_df(time_=time_) l = ['SITE', 'LAT', 'LON'] sites_df = cells_df[l].drop_duplicates() sites_df['key'] = 1 merged_df = pd.merge(sites_df, sites_df, on='key').drop("key", 1) merged_df = merged_df[merged_df['SITE_x'] != merged_df['SITE_y']] merged_df['distance_'] = haversine_distance(merged_df['LAT_x'].values, merged_df['LON_x'].values, merged_df['LAT_y'].values, merged_df['LON_y'].values) merged_df['bearing_'] = bearing(merged_df['LAT_x'].values, merged_df['LON_x'].values, merged_df['LAT_y'].values, merged_df['LON_y'].values) # logger.info(f'neighborhood: merged_df.columns {merged_df.columns}') l = ['SITE_x', 'SITE_y', 'distance_', 'bearing_'] merged_df = merged_df[l] merged_df = merged_df[merged_df['distance_'] <= KM] l = ['SITE', 'CELLNAME', 'AZIMUTH'] merged_df = pd.merge(cells_df[l], merged_df, how="inner", left_on='SITE', right_on='SITE_x') merged_df = merged_df[(merged_df['bearing_'] > merged_df['AZIMUTH'] - D) & (merged_df['bearing_'] < merged_df['AZIMUTH'] + D)] l = ['CELLNAME', 'distance_'] merged_df.sort_values(by=l, inplace=True) l = ['CELLNAME'] neighborhood_df = merged_df.groupby(l).head(N_DISTANCE) l = ['CELLNAME', 'AZIMUTH', 'SITE_x', 'SITE_y', 'distance_', 'bearing_'] logger.info(f'neighborhood_df[l].shape {neighborhood_df[l].shape}') return neighborhood_df[l], cells_df
def haversine_distance(lat1, lon1, lat2, lon2): logger.info(f'haversine_distance:') km_constant = 6372.795477598 lat1, lon1, lat2, lon2 = list(map(np.deg2rad,\ [lat1, lon1, lat2, lon2])) dlat = lat2 - lat1 dlon = lon2 - lon1 a = np.sin(dlat/2)**2 + np.cos(lat1) *\ np.cos(lat2) * np.sin(dlon/2)**2 c = 2 * np.arcsin(np.sqrt(a)) mi = km_constant * c return mi
def overshooting(neighborhood_df=pd.DataFrame(), ta_df=pd.DataFrame()): global TA_COLUMNS, TA_INDEX logger.info(f'overshooting:') if neighborhood_df.empty: neighborhood_df = neighborhood() neighborhood_df.reset_index(inplace=True) # Detecting cells' overshooting l = ['CELLNAME', 'distance_'] t = ['CELLNAME'] overshooters_df = neighborhood_df[l].groupby(t).mean() overshooters_df.reset_index(inplace=True) if ta_df.empty: ta_df = get_ta_df() l = [ 'Cell_Name', 'L_RA_TA_UE_Index0', 'L_RA_TA_UE_Index1', 'L_RA_TA_UE_Index2', 'L_RA_TA_UE_Index3', 'L_RA_TA_UE_Index4', 'L_RA_TA_UE_Index5', 'L_RA_TA_UE_Index6', 'L_RA_TA_UE_Index7', 'L_RA_TA_UE_Index8', 'L_RA_TA_UE_Index9', 'L_RA_TA_UE_Index10', 'L_RA_TA_UE_Index11', ] TA_COLUMNS = l TA_INDEX = {i: f for i, f in enumerate(l)} ta_df = ta_df[l].drop_duplicates().copy() ta_df['ta_'] = ta_df.apply(ta_percentaje_distance, axis=1) l = ['Cell_Name', 'ta_'] t = ['CELLNAME', 'ta_', 'distance_'] overshooters_df = pd.merge(overshooters_df, ta_df[l], how="inner", left_on='CELLNAME', right_on='Cell_Name')[t].drop_duplicates() overshooters_df[ 'overshooter'] = overshooters_df['ta_'] > overshooters_df['distance_'] return neighborhood_df, overshooters_df
def get_cells_df(time_=None): logger.info(f'ENV {ENV}') if ENV == 'sim': df = pd.read_csv("./data/lcellreference_2020_12_30.csv") if ENV == 'prod': if not time_: logger.info(f'time_ {time_}') return df = cells_data(time_=time_) return df
def get_ta_df(time_=None): # logger.info(f'get_ta_df:') # return pd.read_csv("./data/prs_lte_hour_2020_12_30.csv") logger.info(f'ENV {ENV}') if ENV == 'sim': df = pd.read_csv("./data/prs_lte_hour_2020_12_30.csv") if ENV == 'prod': if not time_: logger.info(f'time_ {time_}') return df = ta_data(time_=time_) return df
def all_enabler(time_=None): ''' La finalidad de esta función es habilitar a todas las celdas. Con excepción de aquellas cuyo nombre haga match con el pattern _MM_ ''' logger.debug(f'ENV {ENV} time_ {time_}') if not time_: logger.info(f'time_ {time_}') return df = cells_data(time_=time_) cellnames = df[~df['CELLNAME'].str.contains("_MM_")]['CELLNAME'].drop_duplicates().tolist() logger.debug(f'len(cellnames) {len(cellnames)}') enabler(cellnames=cellnames)
def cells_data(time_=None): logger.debug(f'ENV {ENV} time_ {time_}') if not time_: logger.info(f'time_ {time_}') return when_ = time_ period = when_.strftime("%Y-%m-%d") logger.debug(f'period {period}') query_ = f''' select distinct * from lcellreference as l where STR_TO_DATE(l.dateid, '%Y-%m-%d') = '{period}'; ''' return pd_sql(time_=time_, query_=query_)
def trx_updater(commands=None, sent_=None): ''' Esta función recibe una lista de diccionarios, con las respuestas a los comandos de cambio de tilt ejecutados en el NBI. Si el resultado es exitoso se actualizan las tablas rets y transactions. ''' logger.debug(f"ENV {ENV}") if not commands: return engine = get_engine() session = get_session(engine=engine) for command in commands: result = command['data']['result'] logger.debug(f"result {result}") executed_time_stamp_str = command['data']['executed_time_stamp'] executed_time_stamp = datetime.datetime.strptime( executed_time_stamp_str, '%Y-%m-%d %H:%M:%S') object_id = command['object_id'] trx = session.query(Transaction).filter( Transaction.id == object_id).first() if not trx: session.commit() session.close() return trx.sent = sent_ if result: trx.oldtilt = trx.newtilt trx.success = executed_time_stamp ret_updater(node=trx.node, deviceno=trx.deviceno, tilt=trx.newtilt, session=session) else: logger.info(f"result {result}") trx.failure = executed_time_stamp session.commit() session.close()
def load_overshooters(): logger.info(f'load_overshooters:') neighborhood_df, overshooters_df = overshooting() overshooters_dict = overshooters_df.to_dict('index') engine = get_engine() session = get_session(engine=engine) now_ = datetime.now() for index, dict_ in overshooters_dict.items(): obj_ = Overshooter( date_time = now_, cell_name = dict_['CELLNAME'], time_advanced = int(dict_['ta_']), average_distance = int(dict_['distance_']), is_overshooter = dict_['overshooter'] ) session.add(obj_) session.commit() session.close()
def bearing(lat1, lon1, lat2, lon2): logger.info(f'bearing:') lat1, lon1, lat2, lon2 = list(map(np.deg2rad,\ [lat1, lon1, lat2, lon2])) dlon = lon2 - lon1 x = np.sin(dlon) * np.cos(lat2) y = np.cos(lat1) * np.sin(lat2) - (np.sin(lat1) * np.cos(lat2) * np.cos(dlon)) initial_bearing = np.arctan2(x, y) # Now we have the initial bearing but math.atan2 return values # from -180° to + 180° which is not what we want for a compass bearing # The solution is to normalize the initial bearing as shown below initial_bearing = np.degrees(initial_bearing) bearing = (initial_bearing + 360) % 360 return bearing
def load_overshooters(time_=None, neighborhood_df=pd.DataFrame(), cells_df=pd.DataFrame()): logger.info(f'load_overshooters:') if not time_: return list_ = [ { 'datetimeid': time_, 'cellname': 'AIS_4G_003_3', 'ta_calculated': 14.4, 'average_distance': 2.43982546537283, 'overshooter': True, 'intensity': 'High', }, { 'datetimeid': time_, 'cellname': 'ARA_4G_013_3', 'ta_calculated': 14.4, 'average_distance': 6.14587256200947, 'overshooter': True, 'intensity': 'High', }, ] if ENV == 'sim': df = pd.DataFrame.from_dict(list_) if ENV == 'prod': df = overshooters(time_=time_, neighborhood_df=neighborhood_df, cells_df=cells_df) engine = get_engine() session = get_session(engine=engine) df.to_sql('overshooters', con=engine, if_exists='append', index=False) session.commit() session.close()
def ta_data(time_=None): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return when_ = time_ period = when_.strftime("%Y-%m-%d") query_ = f''' select distinct * from prs_lte_hour p where STR_TO_DATE(p.dateid_date, '%Y-%m-%d') between '{period}' and '{period}'; ''' query_ = f''' select distinct * from prs_lte_hour p where STR_TO_DATE(p.dateid_date, '%Y-%m-%d') between '{period}' and '{period}' and p.dateid_hour = '20'; ''' return pd_sql(time_=time_, query_=query_)
def load_terrains(time_=None, neighborhood_df=pd.DataFrame(), cells_df=pd.DataFrame()): logger.info(f'ENV {ENV}') if not time_: return list_ = [ { 'datetimeid': time_, 'cellname': 'AIS_4G_003_3', 'is_plain': True, 'slope': 0, }, { 'datetimeid': time_, 'cellname': 'ARA_4G_013_3', 'is_plain': True, 'slope': 0, }, ] if ENV == 'sim': terrain_df = pd.DataFrame.from_dict(list_) if ENV == 'prod': terrain_df, neighborhood_df, cells_df = check_terrain( time_=time_, neighborhood_df=neighborhood_df, cells_df=cells_df) logger.info(f'terrain_df.shape {terrain_df.shape}') logger.info(f'terrain_df.columns {terrain_df.columns}') terrains_dict = terrain_df.to_dict('index') engine = get_engine() session = get_session(engine=engine) now_ = datetime.datetime.now() for index, dict_ in terrains_dict.items(): is_plain_ = not dict_['HILL'] if is_plain_: slope_ = 0 else: slope_ = 1 if int(dict_['HEIGHT_DIFF']) > 0 else -1 obj_ = Terrain(datetimeid=now_, cellname=dict_['CELLNAME'], slope=slope_, is_plain=is_plain_) session.add(obj_) session.commit() session.close() return neighborhood_df, cells_df
def nbi_simulator(time_=None, session_=None, trx_=None): logger.debug(f"time_ {time_}") if not time_ or not session_ or not trx_: pass logger.info(f"trx_ \n{trx_}") # logger.info(f"ENV {ENV}") # if ENV == 'sim': logger.info(f"ENV {ENV}") trx_.sent = datetime.now() nbi_response = failure_percentage() logger.info(f"nbi_response {nbi_response}") if nbi_response: trx_.success = datetime.now() else: trx_.failure = datetime.now() session_.commit()
def overshooters(time_=None, neighborhood_df=pd.DataFrame(), cells_df=pd.DataFrame()): logger.debug(f'ENV {ENV}') if not time_: logger.info(f'time_ {time_}') return # ------------------------------------------ # fue necesario setear tiempos para avanzar con las pruebas .. # now_ = datetime.datetime.now() if neighborhood_df.empty: neighborhood_df, cells_df = neighborhood(time_) day_before = time_ - datetime.timedelta(days=1) ta_df = get_ta_df(time_=day_before) # neighborhood_df.reset_index(inplace=True) # cells_df.reset_index(inplace=True) # ta_df.reset_index(inplace=True) # ------------------------------------------ neighborhood_df, overshooters_df = overshooting( neighborhood_df=neighborhood_df, ta_df=ta_df) # neighborhood_df.to_excel(r'data/neighborhood_df.xlsx', index = False) # overshooters_df.to_excel(r'data/overshooters_df.xlsx', index = False) # neighborhood_df.reset_index(inplace=True) # overshooters_df.reset_index(inplace=True) neighborhood_df, overshooters_intensity_df = overshooting_intensity( neighborhood_df=neighborhood_df, ta_df=ta_df) # neighborhood_df.to_excel(r'data/neighborhood_df.xlsx', index = False) overshooters_intensity_df.to_excel(r'data/overshooters_intensity_df.xlsx', index=False) # neighborhood_df.reset_index(inplace=True) # overshooters_intensity_df.reset_index(inplace=True) intensity_df = overshooters_intensity_df.drop(['distance_'], axis=1) # intensity_df.reset_index(inplace=True) merged_df = pd.merge(overshooters_df, intensity_df, how="inner", left_on='CELLNAME', right_on='CELLNAME').drop_duplicates() # merged_df.to_excel(r'data/merged_df.xlsx', index = False) # merged_df.reset_index(inplace=True) # ------------------------------------------------ l = ['CELLNAME', 'ta_', 'distance_', 'overshooter', 'overs_intensity'] overshooters_df = merged_df[l].drop_duplicates() l = [ 'cellname', 'ta_calculated', 'average_distance', 'overshooter', 'intensity' ] overshooters_df.columns = l overshooters_df['datetimeid'] = cells_df.iloc[0]['Dateid'] # ------------------------------------------------ # return overshooters_df, intensity_df, merged_df return overshooters_df
def get_prs_lte_hour_df(): logger.info(f'get_prs_lte_hour_df:') return pd.read_csv("./data/prs_lte_hour_2020_12_30.csv")
def get_ret_join_data(period=None): logger.info(f'get_ret_data:') return pd.read_json(f'./data/ret_join_data_{period}.json')
def check_terrain(time_=None, neighborhood_df=pd.DataFrame(), cells_df=pd.DataFrame()): logger.info(f'check_terrain:') if not time_: return if neighborhood_df.empty: neighborhood_df, cells_df = neighborhood(time_) if cells_df.empty: cells_df = get_cells_df(time_=time_) # Adding GEO_HEIGHT_x to neighborhood l = ['SITE', 'GEO_HEIGHT'] aux = pd.merge(neighborhood_df, cells_df[l], how="inner", left_on='SITE_x', right_on='SITE').drop_duplicates() l = ['SITE_x', 'GEO_HEIGHT'] aux = aux[l][aux['SITE_x'] == aux['SITE']].drop_duplicates() l = ['SITE_x', 'GEO_HEIGHT_x'] aux.columns = l neighborhood_df_ = pd.merge(neighborhood_df, aux, how="inner", left_on='SITE_x', right_on='SITE_x').drop_duplicates() # Adding GEO_HEIGHT_y to neighborhood l = ['SITE', 'GEO_HEIGHT'] aux = pd.merge(neighborhood_df_, cells_df[l], how="inner", left_on='SITE_y', right_on='SITE').drop_duplicates() l = ['SITE_y', 'GEO_HEIGHT'] aux = aux[l][aux['SITE_y'] == aux['SITE']].drop_duplicates() l = ['SITE_y', 'GEO_HEIGHT_y'] aux.columns = l neighborhood_df_ = pd.merge(neighborhood_df_, aux, how="inner", left_on='SITE_y', right_on='SITE_y').drop_duplicates() # Detecting cells' terrain # neighborhood_df_['HEIGHT_DIFF'] = np.abs((neighborhood_df_['GEO_HEIGHT_x'] - neighborhood_df_['GEO_HEIGHT_y']).values) neighborhood_df_['HEIGHT_DIFF'] = (neighborhood_df_['GEO_HEIGHT_x'] - neighborhood_df_['GEO_HEIGHT_y']).values l = ['CELLNAME', 'SITE_x', 'SITE_y', 'HEIGHT_DIFF'] t = ['CELLNAME', 'SITE_x'] terrain_df = neighborhood_df_[l].groupby(t).mean() terrain_df['HILL'] = np.abs(terrain_df['HEIGHT_DIFF']) > TERRAIN_DELTA terrain_df.reset_index(inplace=True) # neighborhood_df.reset_index(inplace=True) return terrain_df, neighborhood_df, cells_df