def prb_process(filepath):
# hour based statistics
cellkpi = CellKPI(filepath)
if not cellkpi.readfile(', skiprows=7, na_values=["NIL"]'):
print('Read Excel file failed')
return False
timed_df = cellkpi.df.groupby('开始时间').mean()
celled_df = cellkpi.df.groupby('小区名称').mean()
overall_df = cellkpi.df.mean(numeric_only=True)
print('hour based statistics processed successfully')
# structure summary page for handover KPI
if not hasattr(cellkpi, 'bh_df'):
cellkpi.busyhour()
bh_df = cellkpi.df[cellkpi.df['Busy Hour'] == True].mean(numeric_only=True)
pfm_dfs = list()
summary_df = pd.DataFrame()
summary_df['PRB KPI metrics'] = ['Mean DL PRB Utilization','Mean UL PRB Utilization']
for col, nw_df in zip(['Overall', 'Busy Hour'], [overall_df, bh_df]):
dlPRB = nw_df.ix['下行Physical Resource Block被使用的平均个数 (无)']
ulPRB = nw_df.ix['上行Physical Resource Block被使用的平均个数 (无)']
summary_df[col] = [float(dlPRB),float(ulPRB)]
pfm_dfs.append(summary_df)
print('summary statistics precessed successfully')
sheet_list = [pfm_dfs, timed_df, celled_df]
name_list = ['performance overview', 'hour based', 'cell based']
cellkpi.writefile(sheet_list, name_list)
print('write Excel file successfully')
def csfb_process(filepath):
# hour based statistics
cellkpi = CellKPI(filepath)
if not cellkpi.readfile(', skiprows=7, na_values=["NIL"]'):
print('Read Excel file failed')
return False
timed_df = cellkpi.timed_df
timed_df = cellkpi.timed_df
timed_df['CSFB成功率'] = timed_df['E-UTRAN向GERAN执行的CSFB重定向次数 (无)']/timed_df['eNodeB收到的CSFB触发次数 (无)']
#wanted_cols = ['CQI总数', 'CQI总和', '平均CQI','RANK总数', 'RANK总和', '平均RANK']
#timedoutput_df = pd.DataFrame(timed_df.ix[:, wanted_cols])
print('hour based statistics precessed successfully')
# structure summary page for DL Throughput KPI
if not hasattr(cellkpi, 'bh_df'):
cellkpi.busyhour()
pfm_dfs = list()
summary_df = pd.DataFrame()
summary_df['CSFB KPI metrics'] = ['CSFB attempt', 'CSFB execution','CSFB successful rate']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
nw_df['CSFB成功率'] = nw_df['E-UTRAN向GERAN执行的CSFB重定向次数 (无)']/nw_df['eNodeB收到的CSFB触发次数 (无)']
summary_df[col] = [nw_df['eNodeB收到的CSFB触发次数 (无)'],nw_df['E-UTRAN向GERAN执行的CSFB重定向次数 (无)'],
nw_df['CSFB成功率']]
pfm_dfs.append(summary_df)
print('summary statistics precessed successfully')
# calculate Cell KPI
cellkpi_df = cellkpi.celled_df.ix[:, 1:]
cellkpi_df['CSFB成功率'] = cellkpi_df['E-UTRAN向GERAN执行的CSFB重定向次数 (无)']/cellkpi_df['eNodeB收到的CSFB触发次数 (无)']
cellkpi_df['CSFB失败次数'] = cellkpi_df['eNodeB收到的CSFB触发次数 (无)']-cellkpi_df['E-UTRAN向GERAN执行的CSFB重定向次数 (无)']
#wanted_cols = ['下行平均速率', '上行平均速率']
#cellkpi_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
print('Cell KPI precessed successfully')
# calculate Top List
wanted_cols = ['CSFB失败次数','CSFB成功率','E-UTRAN向GERAN执行的CSFB重定向次数 (无)','eNodeB收到的CSFB触发次数 (无)']
csfbfailTOP_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
csfbfailTOP_df = csfbfailTOP_df.sort_values(by='CSFB失败次数', ascending=False)
print('TopN low Throughput precessed successfully')
# write excel file
sheet_list = [pfm_dfs, timed_df, cellkpi.celled_df, cellkpi_df, csfbfailTOP_df]
name_list = ['performance overview', 'hour based', 'cell based', 'Cell KPI', 'CSFB Fail TOP List']
cellkpi.writefile(sheet_list, name_list)
print('write Excel file successfully')
def interference_process(filepath):
# hour based statistics
cellkpi = CellKPI(filepath)
if not cellkpi.readfile(', skiprows=7, na_values=["NIL"]'):
print('Read Excel file failed')
return False
timed_df = cellkpi.timed_df
timed_df = cellkpi.df.groupby('开始时间').mean()
celled_df = cellkpi.df.groupby('小区名称').mean()
overall_df = cellkpi.df.mean(numeric_only=True)
print('hour based statistics precessed successfully')
# structure summary page for handover KPI
if not hasattr(cellkpi, 'bh_df'):
cellkpi.busyhour()
bh_df = cellkpi.df[cellkpi.df['Busy Hour'] == True].mean(numeric_only=True)
pfm_dfs = list()
summary_df = pd.DataFrame()
summary_df['UL Interference KPI metrics'] = ['Mean UL Interference']
for col, nw_df in zip(['Overall', 'Busy Hour'], [overall_df, bh_df]):
mean_interference = nw_df.ix['系统上行每个PRB上检测到的干扰噪声的平均值 (毫瓦分贝)']
summary_df[col] = [float(mean_interference)]
pfm_dfs.append(summary_df)
print('summary statistics precessed successfully')
# calculate interference Top List
wanted_cols = ['系统上行每个PRB上检测到的干扰噪声的平均值 (毫瓦分贝)']
interference_df = pd.DataFrame(celled_df.ix[:, wanted_cols])
interference_df = interference_df.sort_values(by='系统上行每个PRB上检测到的干扰噪声的平均值 (毫瓦分贝)', ascending=False)
print('TopN UL interference precessed successfully')
sheet_list = [pfm_dfs, timed_df, celled_df, interference_df]
name_list = ['performance overview', 'hour based', 'cell based', 'TopN Interference']
cellkpi.writefile(sheet_list, name_list)
print('write Excel file successfully')
def tput_process(filepath):
cellkpi = CellKPI(filepath)
if not cellkpi.readfile(', skiprows=7, na_values=["NIL"]'):
print('Read Excel file failed')
return False
timed_df = cellkpi.timed_df
# hour based statistics
timed_df['下行平均速率'] = (timed_df['小区PDCP层所发送的下行数据的总吞吐量 (比特)']/1048576)/(timed_df['小区PDCP发送下行数据的总时长 (毫秒)']/1000)
timed_df['扣除小包后下行平均速率'] = ((timed_df['小区PDCP层所发送的下行数据的总吞吐量 (比特)']-timed_df['使缓存为空的最后一个TTI所传的下行PDCP吞吐量 (比特)'])/1048576) \
/(timed_df['扣除使下行缓存为空的最后一个TTI之后的数传时长 (毫秒)']/1000)
timed_df['上行平均速率'] = (timed_df['小区PDCP层所接收到的上行数据的总吞吐量 (比特)']/1048576)/(timed_df['小区PDCP接收上行数据的总时长 (毫秒)']/1000)
timed_df['扣除小包后上行平均速率'] = ((timed_df['小区PDCP层所接收到的上行数据的总吞吐量 (比特)']-timed_df['使UE缓存为空的最后一个TTI所传的上行PDCP吞吐量 (比特)'])/1048576) \
/(timed_df['扣除使UE缓存为空的最后一个TTI之后的上行数传时长 (毫秒)']/1000)
print('hour based statistics precessed successfully')
# structure summary page for DL Throughput KPI
if not hasattr(cellkpi, 'bh_df'):
cellkpi.busyhour()
pfm_dfs = list()
summary_df = pd.DataFrame()
summary_df['DL Throughput KPI metrics'] = ['Total Throughput', 'Total Scheduling Time','Small packet removed Throughput',
'Small packet removed Scheduling Time', 'Average DL Throughput',
'Average DL Throughput(Small packet removed)']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
nw_df['下行扣除小包'] = nw_df['小区PDCP层所发送的下行数据的总吞吐量 (比特)']-nw_df['使缓存为空的最后一个TTI所传的下行PDCP吞吐量 (比特)']
avg_dl_tput = float((nw_df['小区PDCP层所发送的下行数据的总吞吐量 (比特)']/1048576)/(nw_df['小区PDCP发送下行数据的总时长 (毫秒)']/1024))
avg_dl_tput2 = float((nw_df['下行扣除小包']/1048576)/(nw_df['扣除使下行缓存为空的最后一个TTI之后的数传时长 (毫秒)']/1024))
summary_df[col] = [nw_df['小区PDCP层所发送的下行数据的总吞吐量 (比特)'],nw_df['小区PDCP发送下行数据的总时长 (毫秒)'],
nw_df['下行扣除小包'],nw_df['扣除使下行缓存为空的最后一个TTI之后的数传时长 (毫秒)'],
avg_dl_tput,avg_dl_tput2]
pfm_dfs.append(summary_df)
#UL Throughput KPI
summary_df = pd.DataFrame()
summary_df['UL Throughput KPI metrics'] = ['Total Throughput', 'Total Scheduling Time','Small packet removed Throughput',
'Small packet removed Scheduling Time', 'Average UL Throughput',
'Average UL Throughput(Small packet removed)']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
nw_df['上行扣除小包'] = nw_df['小区PDCP层所接收到的上行数据的总吞吐量 (比特)']-nw_df['使UE缓存为空的最后一个TTI所传的上行PDCP吞吐量 (比特)']
avg_ul_tput = float((nw_df['小区PDCP层所接收到的上行数据的总吞吐量 (比特)']/1048576)/(nw_df['小区PDCP接收上行数据的总时长 (毫秒)']/1024))
avg_ul_tput2 = float((nw_df['上行扣除小包']/1048576)/(nw_df['扣除使UE缓存为空的最后一个TTI之后的上行数传时长 (毫秒)']/1024))
summary_df[col] = [nw_df['小区PDCP层所接收到的上行数据的总吞吐量 (比特)'],nw_df['小区PDCP接收上行数据的总时长 (毫秒)'],
nw_df['上行扣除小包'],nw_df['扣除使UE缓存为空的最后一个TTI之后的上行数传时长 (毫秒)'],
avg_ul_tput,avg_ul_tput2]
pfm_dfs.append(summary_df)
print('summary statistics precessed successfully')
# calculate Cell KPI
cellkpi_df = cellkpi.celled_df.ix[:, 1:]
cellkpi_df['下行平均速率'] = (cellkpi_df['小区PDCP层所发送的下行数据的总吞吐量 (比特)']/1048576)/(cellkpi_df['小区PDCP发送下行数据的总时长 (毫秒)']/1000)
cellkpi_df['上行平均速率'] = (cellkpi_df['小区PDCP层所接收到的上行数据的总吞吐量 (比特)']/1048576)/(cellkpi_df['小区PDCP接收上行数据的总时长 (毫秒)']/1000)
wanted_cols = ['下行平均速率', '上行平均速率']
cellkpi_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
print('Cell KPI precessed successfully')
# calculate 2* Top List
wanted_cols = ['下行平均速率']
dl_top_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
dl_top_df = dl_top_df.sort_values(by='下行平均速率', ascending=True)
wanted_cols = ['上行平均速率']
ul_top_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
ul_top_df = ul_top_df.sort_values(by='上行平均速率', ascending=True)
print('TopN low Throughput precessed successfully')
# write excel file
sheet_list = [pfm_dfs, timed_df, cellkpi.celled_df, cellkpi_df, dl_top_df,ul_top_df]
name_list = ['performance overview', 'hour based', 'cell based', 'Cell KPI', 'DL Low Throughput TOP List','UL Low Throughput TOP List']
cellkpi.writefile(sheet_list, name_list)
print('write Excel file successfully')
def ho_process(filepath):
cellkpi = CellKPI(filepath)
if not cellkpi.readfile(', skiprows=7, na_values=["NIL"]'):
print('Read Excel file failed')
return False
timed_df = cellkpi.timed_df
timed_df['切换准备请求'] = timed_df['eNodeB间异频切换出尝试次数 (无)']+timed_df['eNodeB间同频切换出尝试次数 (无)']+\
timed_df['eNodeB内同频切换出尝试次数 (无)']+timed_df['eNodeB内异频切换出尝试次数 (无)']
timed_df['切换执行请求'] = timed_df['eNodeB间同频切换出执行次数 (无)']+timed_df['eNodeB间异频切换出执行次数 (无)']+\
timed_df['eNodeB内同频切换出执行次数 (无)']+timed_df['eNodeB内异频切换出执行次数 (无)']
timed_df['切换执行成功'] = timed_df['eNodeB间异频切换出成功次数 (无)']+timed_df['eNodeB间同频切换出成功次数 (无)']+\
timed_df['eNodeB内同频切换出成功次数 (无)']+timed_df['eNodeB内异频切换出成功次数 (无)']-\
timed_df['通过重建回源小区的eNodeB内同频切换出执行成功次数 (无)']-\
timed_df['通过重建回源小区的eNodeB间同频切换出执行成功次数 (无)']-\
timed_df['通过重建回源小区的eNodeB间异频切换出执行成功次数 (无)']-\
timed_df['通过重建回源小区的eNodeB内异频切换出执行成功次数 (无)']
timed_df['Handover Successful Rate'] = timed_df['切换执行成功']/timed_df['切换准备请求']
timed_df['Preparation Successful Rate'] = timed_df['切换执行请求']/timed_df['切换准备请求']
timed_df['Execution Successful Rate'] = timed_df['切换执行成功']/timed_df['切换执行请求']
wanted_cols = ['Handover Successful Rate', 'Preparation Successful Rate', 'Execution Successful Rate']
timedoutput_df = pd.DataFrame(timed_df.ix[:, wanted_cols])
print('hour based statistics precessed successfully')
# structure summary page for handover KPI
if not hasattr(cellkpi, 'bh_df'):
cellkpi.busyhour()
pfm_dfs = list()
summary_df = pd.DataFrame()
summary_df['Handover KPI metrics'] = ['Preparation attempt', 'Preparation success/Execution attempt',
'Execution success', 'Handover Successful Rate',
'Preparation Successful Rate', 'Execution Successful Rate']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
prep_att = nw_df.ix['eNodeB间异频切换出尝试次数 (无)']+nw_df.ix['eNodeB间同频切换出尝试次数 (无)']+nw_df.ix['eNodeB内同频切换出尝试次数 (无)']+nw_df.ix['eNodeB内异频切换出尝试次数 (无)']
prep_succ = nw_df.ix['eNodeB间同频切换出执行次数 (无)']+nw_df.ix['eNodeB间异频切换出执行次数 (无)']+nw_df.ix['eNodeB内同频切换出执行次数 (无)']+nw_df.ix['eNodeB内异频切换出执行次数 (无)']
exec_succ = nw_df.ix['eNodeB间异频切换出成功次数 (无)']+nw_df.ix['eNodeB间同频切换出成功次数 (无)']+nw_df.ix['eNodeB内同频切换出成功次数 (无)']+nw_df.ix['eNodeB内异频切换出成功次数 (无)']-nw_df.ix['通过重建回源小区的eNodeB内同频切换出执行成功次数 (无)']-nw_df.ix['通过重建回源小区的eNodeB间同频切换出执行成功次数 (无)']-nw_df.ix['通过重建回源小区的eNodeB间异频切换出执行成功次数 (无)']-nw_df.ix['通过重建回源小区的eNodeB内异频切换出执行成功次数 (无)']
ho_sr = float(exec_succ/prep_att*100.0)
prep_sr = float(prep_succ/prep_att*100.0)
exec_sr = float(exec_succ/prep_succ*100.0)
summary_df[col] = [int(prep_att), int(prep_succ), int(exec_succ), ho_sr, prep_sr, exec_sr]
pfm_dfs.append(summary_df)
summary_df = pd.DataFrame()
summary_df['Handover preparation metrics'] = ['Total Preparation Failure', 'X2 Based Preparation Failure',
'S1 based Preparation Failure',
'Intra-eNodeB Preparation Failure']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
total_prep_fail = nw_df['eNodeB间异频切换出尝试次数 (无)']+nw_df['eNodeB间同频切换出尝试次数 (无)']+\
nw_df['eNodeB内同频切换出尝试次数 (无)']+nw_df['eNodeB内异频切换出尝试次数 (无)']-\
nw_df['eNodeB间同频切换出执行次数 (无)']-nw_df['eNodeB间异频切换出执行次数 (无)']-\
nw_df['eNodeB内同频切换出执行次数 (无)']-nw_df['eNodeB内异频切换出执行次数 (无)']
x2_prep_fail = nw_df['eNodeB间X2接口同频切换出尝试次数 (无)']-nw_df['eNodeB间X2接口同频切换出执行次数 (无)']+\
nw_df['eNodeB间X2接口异频切换出尝试次数 (无)']-nw_df['eNodeB间X2接口异频切换出执行次数 (无)']
intraeNB_fail=nw_df['eNodeB内同频切换出尝试次数 (无)']+nw_df['eNodeB内异频切换出尝试次数 (无)']-\
nw_df['eNodeB内同频切换出执行次数 (无)']-nw_df['eNodeB内异频切换出执行次数 (无)']
s1_prep_fail = total_prep_fail - x2_prep_fail - intraeNB_fail
summary_df[col] = [int(total_prep_fail), int(x2_prep_fail), int(s1_prep_fail), int(intraeNB_fail)]
pfm_dfs.append(summary_df)
# HO Preparation failure breakdown
summary_df = pd.DataFrame()
summary_df ['Handover Preparation Failure Breakdown'] = ['MME reason', 'Target cell no reply',
'Target cell preparation failure',
'HO cancel','Target cell response message illegal','other reason']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
summary_df[col] = [nw_df['核心网原因导致模式内切换出准备失败次数 (无)'], nw_df['目标小区无响应导致模式内切换出准备失败次数 (无)'],nw_df['目标小区回复切换准备失败消息导致模式内切换出准备失败次数 (无)'],nw_df['源小区发送切换取消导致模式内切换出准备失败次数 (无)'],nw_df['对端回复切换响应消息合法性检查失败导致切换出准备失败次数 (无)'],nw_df['其它原因导致小区模式内切换出准备失败次数 (无)']]
pfm_dfs.append(summary_df)
summary_df = pd.DataFrame()
summary_df['Handover execution metrics'] = ['Total Handover Execution Fail', 'Handover Execution Fail',
' Handover Execution Success by Re-establishment']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
total_exec_fail = nw_df['eNodeB间同频切换出执行次数 (无)']+nw_df['eNodeB间异频切换出执行次数 (无)'] +\
nw_df['eNodeB内同频切换出执行次数 (无)']+nw_df['eNodeB内异频切换出执行次数 (无)'] -\
nw_df['eNodeB间异频切换出成功次数 (无)']-nw_df['eNodeB间同频切换出成功次数 (无)'] -\
nw_df['eNodeB内同频切换出成功次数 (无)']-nw_df['eNodeB内异频切换出成功次数 (无)'] +\
nw_df['通过重建回源小区的eNodeB内同频切换出执行成功次数 (无)'] +\
nw_df['通过重建回源小区的eNodeB间同频切换出执行成功次数 (无)'] +\
nw_df['通过重建回源小区的eNodeB间异频切换出执行成功次数 (无)'] +\
nw_df['通过重建回源小区的eNodeB内异频切换出执行成功次数 (无)']
ho_exec_fail = nw_df['eNodeB间同频切换出执行次数 (无)']+nw_df['eNodeB间异频切换出执行次数 (无)'] +\
nw_df['eNodeB内同频切换出执行次数 (无)']+nw_df['eNodeB内异频切换出执行次数 (无)'] -\
nw_df['eNodeB间异频切换出成功次数 (无)']-nw_df['eNodeB间同频切换出成功次数 (无)'] -\
nw_df['eNodeB内同频切换出成功次数 (无)']-nw_df['eNodeB内异频切换出成功次数 (无)'] +\
nw_df['通过重建回源小区的eNodeB内同频切换出执行成功次数 (无)']
ho_exec_succ_reest = nw_df['通过重建回源小区的eNodeB内同频切换出执行成功次数 (无)'] +\
nw_df['通过重建回源小区的eNodeB间同频切换出执行成功次数 (无)'] +\
nw_df['通过重建回源小区的eNodeB间异频切换出执行成功次数 (无)'] +\
nw_df['通过重建回源小区的eNodeB内异频切换出执行成功次数 (无)']
summary_df[col] = [int(total_exec_fail), int(ho_exec_fail), int(ho_exec_succ_reest)]
pfm_dfs.append(summary_df)
summary_df = pd.DataFrame()
summary_df['Intra/Inter Frequency Handover execution metrics'] = ['Execution Failure', 'Execution Attempt',
'Execution Successful Rate']
nw_df = cellkpi.overall_df
# intra-frequency
exec_fail = nw_df['eNodeB间同频切换出执行次数 (无)']+nw_df['eNodeB内同频切换出执行次数 (无)']-nw_df['eNodeB间同频切换出成功次数 (无)']-nw_df['eNodeB内同频切换出成功次数 (无)']+nw_df['通过重建回源小区的eNodeB内同频切换出执行成功次数 (无)']+nw_df['通过重建回源小区的eNodeB间同频切换出执行成功次数 (无)']
exec_att = nw_df['eNodeB间同频切换出执行次数 (无)']+nw_df['eNodeB内同频切换出执行次数 (无)']
exec_sr = float(100.0 - (exec_fail/exec_att)*100.0)
summary_df['Intra frequency handover execution'] = [int(exec_fail), int(exec_att), exec_sr]
# inter-frequency
exec_fail = nw_df['eNodeB间异频切换出执行次数 (无)']+nw_df['eNodeB内异频切换出执行次数 (无)']-nw_df['eNodeB间异频切换出成功次数 (无)']-nw_df['eNodeB内异频切换出成功次数 (无)']+nw_df['通过重建回源小区的eNodeB内异频切换出执行成功次数 (无)']+nw_df['通过重建回源小区的eNodeB间异频切换出执行成功次数 (无)']
exec_att = nw_df['eNodeB间异频切换出执行次数 (无)']+nw_df['eNodeB内异频切换出执行次数 (无)']
exec_sr = float(100.0 - (exec_fail/exec_att)*100.0)
summary_df['Inter frequency handover execution'] = [int(exec_fail), int(exec_att), exec_sr]
pfm_dfs.append(summary_df)
print('summary statistics precessed successfully')
# calculate Cell KPI
cellkpi_df = cellkpi.celled_df.ix[:, 1:]
cellkpi_df['切换准备请求'] = cellkpi_df['eNodeB间异频切换出尝试次数 (无)']+cellkpi_df['eNodeB间同频切换出尝试次数 (无)']+cellkpi_df['eNodeB内同频切换出尝试次数 (无)']+cellkpi_df['eNodeB内异频切换出尝试次数 (无)']
cellkpi_df['切换执行请求'] = cellkpi_df['eNodeB间同频切换出执行次数 (无)']+cellkpi_df['eNodeB间异频切换出执行次数 (无)']+cellkpi_df['eNodeB内同频切换出执行次数 (无)']+cellkpi_df['eNodeB内异频切换出执行次数 (无)']
cellkpi_df['切换执行成功'] = cellkpi_df['eNodeB间异频切换出成功次数 (无)']+cellkpi_df['eNodeB间同频切换出成功次数 (无)']+cellkpi_df['eNodeB内同频切换出成功次数 (无)']+cellkpi_df['eNodeB内异频切换出成功次数 (无)']-cellkpi_df['通过重建回源小区的eNodeB内同频切换出执行成功次数 (无)']-cellkpi_df['通过重建回源小区的eNodeB间同频切换出执行成功次数 (无)']-cellkpi_df['通过重建回源小区的eNodeB间异频切换出执行成功次数 (无)']-cellkpi_df['通过重建回源小区的eNodeB内异频切换出执行成功次数 (无)']
cellkpi_df['切换准备失败'] = cellkpi_df['切换准备请求']-cellkpi_df['切换执行请求']
cellkpi_df['切换执行失败'] = cellkpi_df['切换执行请求']-cellkpi_df['切换执行成功']
cellkpi_df['切换成功率'] = cellkpi_df['切换执行成功']/cellkpi_df['切换准备请求']*100.0
cellkpi_df['切换准备成功率'] = cellkpi_df['切换执行请求']/cellkpi_df['切换准备请求']*100.0
cellkpi_df['切换执行成功率'] = cellkpi_df['切换执行成功']/cellkpi_df['切换执行请求']*100.0
cellkpi_df['同频切换执行成功率'] = (cellkpi_df['eNodeB间同频切换出成功次数 (无)']+cellkpi_df['eNodeB内同频切换出成功次数 (无)']-cellkpi_df['通过重建回源小区的eNodeB内同频切换出执行成功次数 (无)']-cellkpi_df['通过重建回源小区的eNodeB间同频切换出执行成功次数 (无)'])/(cellkpi_df['eNodeB间同频切换出执行次数 (无)']+cellkpi_df['eNodeB内同频切换出执行次数 (无)'])*100.0
cellkpi_df['异频切换执行成功率'] = (cellkpi_df['eNodeB间异频切换出成功次数 (无)']+cellkpi_df['eNodeB内异频切换出成功次数 (无)']-cellkpi_df['通过重建回源小区的eNodeB间异频切换出执行成功次数 (无)']-cellkpi_df['通过重建回源小区的eNodeB内异频切换出执行成功次数 (无)'])/(cellkpi_df['eNodeB间异频切换出执行次数 (无)']+cellkpi_df['eNodeB内异频切换出执行次数 (无)'])*100.0
wanted_cols = ['切换准备请求', '切换执行请求', '切换执行成功', '切换准备失败', '切换执行失败', '切换成功率', '切换准备成功率', '切换执行成功率', '同频切换执行成功率', '异频切换执行成功率','无对应的邻区关系导致无法发起同频切换过程的次数 (无)', '无对应的邻区关系导致无法发起异频切换过程的次数 (无)']
cellkpi_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
print('Cell KPI precessed successfully')
# calculate Handover preparation failure Top List
wanted_cols = ['切换准备失败', '切换准备请求', '切换执行请求', '切换准备成功率']
hoprepfail_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
hoprepfail_df = hoprepfail_df.sort_values(by='切换准备失败', ascending=False)
print('TopN HO Prep precessed successfully')
# calculate Handover execution failure Top List
wanted_cols = ['切换执行失败', '切换执行请求', '切换执行成功', '切换执行成功率', '同频切换执行成功率','异频切换执行成功率','无对应的邻区关系导致无法发起同频切换过程的次数 (无)', '无对应的邻区关系导致无法发起异频切换过程的次数 (无)']
hoexecfail_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
hoexecfail_df = hoexecfail_df.sort_values(by='切换执行失败', ascending=False)
print('TopN HO Exec precessed successfully')
# write excel file
sheet_list = [pfm_dfs, timedoutput_df, cellkpi.celled_df, cellkpi_df, hoprepfail_df, hoexecfail_df]
name_list = ['performance overview', 'hour based', 'cell based', 'Cell KPI', 'TopN HO Prep', 'TopN HO Exec']
cellkpi.writefile(sheet_list, name_list)
print('write Excel file successfully')
def erab_process(filepath):
# hour based statistics
cellkpi = CellKPI(filepath)
if not cellkpi.readfile(', skiprows=7, na_values=["NIL"]'):
print('Read Excel file failed')
return False
timed_df = cellkpi.timed_df
cellkpi.overall_df['E-RAB建立平均时长 (毫秒)'] = cellkpi.df['E-RAB建立平均时长 (毫秒)'].mean()
timed_df['ERAB建立成功率'] = timed_df['E-RAB建立成功总次数 (无)']/timed_df['E-RAB建立尝试总次数 (无)']*100
timed_df['E-RAB建立平均时长 (毫秒)'] = cellkpi.df.groupby('开始时间')['E-RAB建立平均时长 (毫秒)'].mean()
wanted_cols = ['E-RAB建立尝试总次数 (无)', 'E-RAB建立成功总次数 (无)','ERAB建立成功率',
'E-RAB建立平均时长 (毫秒)']
timedoutput_df = pd.DataFrame(timed_df.ix[:, wanted_cols])
print('hour based statistics precessed successfully')
# structure summary page for ERAB KPI
if not hasattr(cellkpi, 'bh_df'):
cellkpi.busyhour()
cellkpi.bh_df['E-RAB建立平均时长 (毫秒)'] = cellkpi.df[cellkpi.df['Busy Hour'] == True]['E-RAB建立平均时长 (毫秒)'].mean()
pfm_dfs = list()
summary_df = pd.DataFrame()
summary_df['ERAB setup successful rate KPI metrics'] = ['L.E-RAB.AttEst', 'L.E-RAB.SuccEst','ERAB setup successful rate',
'L.E-RAB.Est.TimeAvg']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
nw_df['ERAB建立成功率'] = nw_df['E-RAB建立成功总次数 (无)']/nw_df['E-RAB建立尝试总次数 (无)']*100
summary_df[col] = [nw_df['E-RAB建立尝试总次数 (无)'],nw_df['E-RAB建立成功总次数 (无)'],
nw_df['ERAB建立成功率'],nw_df['E-RAB建立平均时长 (毫秒)']]
pfm_dfs.append(summary_df)
#erab procedure failure reason breakdown
summary_df = pd.DataFrame()
summary_df['ERAB Setup Procedure Failure Reason Breakdown'] = ['Total Failure', 'L.E-RAB.FailEst.MME',
'L.E-RAB.FailEst.TNL','L.E-RAB.FailEst.RNL','L.E-RAB.FailEst.Other']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
total_failure = nw_df['E-RAB建立尝试总次数 (无)']-nw_df['E-RAB建立成功总次数 (无)']
summary_df[col] = [total_failure,nw_df['核心网问题导致E-RAB建立失败次数 (无)'],nw_df['传输层问题导致E-RAB建立失败次数 (无)'],
nw_df['无线层问题导致E-RAB建立失败次数 (无)'],nw_df['其它原因导致E-RAB建立失败次数 (无)']]
pfm_dfs.append(summary_df)
#ERAB Setup Failure TNL Reason Breakdown
summary_df = pd.DataFrame()
summary_df['ERAB Setup Failure TNL Reason Breakdown'] = ['L.E-RAB.FailEst.TNL.DLRes', 'L.E-RAB.FailEst.TNL.ULRes',
'L.E-RAB.FailEst.TNL.Other']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
summary_df[col] = [nw_df['下行传输资源不足导致E-RAB建立失败次数 (无)'],
nw_df['上行传输资源不足导致E-RAB建立失败次数 (无)'],
nw_df['其它传输层问题导致E-RAB建立失败次数 (无)']]
pfm_dfs.append(summary_df)
#ERAB Setup Failure RNL Reason Breakdown
summary_df = pd.DataFrame()
summary_df['ERAB Setup Failure RNL Reason Breakdown'] = ['L.E-RAB.FailEst.SecurModeFail', 'L.E-RAB.FailEst.NoReply',
'L.E-RAB.FailEst.SRBReset','L.E-RAB.FailEst.NoRadioRes',
'L.E-RAB.FailEst.Conflict.Hofail','L.E-RAB.FailEst.RNL.Other']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
summary_df[col] = [nw_df['安全模式配置失败导致E-RAB建立失败次数 (无)'],
nw_df['等待UE响应超时导致E-RAB建立失败次数 (无)'],
nw_df['SRB RLC达到最大重传次数导致E-RAB建立失败次数 (无)'],
nw_df['无线资源不足导致E-RAB建立失败次数 (无)'],
nw_df['与站内切换冲突导致E-RAB建立失败次数 (无)'],
nw_df['其它无线资源不足导致E-RAB建立失败次数 (无)']]
pfm_dfs.append(summary_df)
print('summary statistics precessed successfully')
# calculate Cell KPI
cellkpi_df = cellkpi.celled_df.ix[:, 1:]
cellkpi_df['ERAB建立失败次数'] = cellkpi_df['E-RAB建立尝试总次数 (无)']-cellkpi_df['E-RAB建立成功总次数 (无)']
cellkpi_df['ERAB建立成功率'] = cellkpi_df['E-RAB建立成功总次数 (无)']/cellkpi_df['E-RAB建立尝试总次数 (无)']*100
wanted_cols = ['ERAB建立失败次数', 'ERAB建立成功率','E-RAB建立尝试总次数 (无)','E-RAB建立成功总次数 (无)',
'核心网问题导致E-RAB建立失败次数 (无)','传输层问题导致E-RAB建立失败次数 (无)',
'无线层问题导致E-RAB建立失败次数 (无)','其它原因导致E-RAB建立失败次数 (无)',
'下行传输资源不足导致E-RAB建立失败次数 (无)',
'上行传输资源不足导致E-RAB建立失败次数 (无)',
'其它传输层问题导致E-RAB建立失败次数 (无)',
'安全模式配置失败导致E-RAB建立失败次数 (无)',
'等待UE响应超时导致E-RAB建立失败次数 (无)',
'SRB RLC达到最大重传次数导致E-RAB建立失败次数 (无)',
'无线资源不足导致E-RAB建立失败次数 (无)',
'与站内切换冲突导致E-RAB建立失败次数 (无)',
'其它无线资源不足导致E-RAB建立失败次数 (无)']
cellkpi_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
print('Cell KPI precessed successfully')
# calculate failure Top List
wanted_cols = ['ERAB建立失败次数', 'ERAB建立成功率','E-RAB建立尝试总次数 (无)','E-RAB建立成功总次数 (无)',
'核心网问题导致E-RAB建立失败次数 (无)','传输层问题导致E-RAB建立失败次数 (无)',
'无线层问题导致E-RAB建立失败次数 (无)','其它原因导致E-RAB建立失败次数 (无)',
'下行传输资源不足导致E-RAB建立失败次数 (无)',
'上行传输资源不足导致E-RAB建立失败次数 (无)',
'其它传输层问题导致E-RAB建立失败次数 (无)',
'安全模式配置失败导致E-RAB建立失败次数 (无)',
'等待UE响应超时导致E-RAB建立失败次数 (无)',
'SRB RLC达到最大重传次数导致E-RAB建立失败次数 (无)',
'无线资源不足导致E-RAB建立失败次数 (无)',
'与站内切换冲突导致E-RAB建立失败次数 (无)',
'其它无线资源不足导致E-RAB建立失败次数 (无)']
erabTOP_df = pd.DataFrame(cellkpi_df.ix[:, wanted_cols])
erabTOP_df = erabTOP_df.sort_values(by='ERAB建立失败次数', ascending=False)
print('TopN Radio Drop precessed successfully')
# write excel file
sheet_list = [pfm_dfs, timedoutput_df, cellkpi.celled_df, cellkpi_df, erabTOP_df]
name_list = ['performance overview', 'hour based', 'cell based', 'Cell KPI',
'earb setup fail TOP List']
cellkpi.writefile(sheet_list, name_list)
print('write Excel file successfully')
def mcs_process(filepath):
cellkpi = CellKPI(filepath)
if not cellkpi.readfile(', skiprows=7, na_values=["NIL"]'):
print('Read Excel file failed')
return False
# hour based statistics
timed_df = cellkpi.timed_df
print('hour based statistics precessed successfully')
# structure summary page for CQI RI KPI
if not hasattr(cellkpi, 'bh_df'):
cellkpi.busyhour()
pfm_dfs = list()
summary_df = pd.DataFrame()
summary_df['DL MCS KPI metrics'] = ['MCS=0 Count', 'MCS=1 Count','MCS=2 Count','MCS=3 Count','MCS=4 Count','MCS=5 Count',
'MCS=6 Count','MCS=7 Count', 'MCS=8 Count','MCS=9 Count','MCS=10 Count','MCS=11 Count',
'MCS=12 Count','MCS=13 Count','MCS=14 Count','MCS=15 Count','MCS=16 Count','MCS=17 Count',
'MCS=18 Count','MCS=19 Count','MCS=20 Count','MCS=21 Count','MCS=22 Count','MCS=23 Count',
'MCS=24 Count','MCS=25 Count','MCS=26 Count','MCS=27 Count','MCS=28 Count','MCS=29 Count',
'MCS=30 Count','MCS=31 Count',
'Avg MCS','Avg MCS (0&1 excluded)']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
nw_df['MeanMCS1'] = 0
nw_df['MCS Count1'] = 0
for i in range(28):
nw_df['MeanMCS1'] += nw_df['对PDSCH进行调度时选择了MCS index %d的次数 (无)' %i] * i
nw_df['MCS Count1'] += nw_df['对PDSCH进行调度时选择了MCS index %d的次数 (无)' %i]
nw_df['MeanMCS1'] = nw_df['MeanMCS1']/nw_df['MCS Count1']
nw_df['MeanMCS2'] = 0
nw_df['MCS Count2'] = 0
for i in range(2, 28):
nw_df['MeanMCS2'] += nw_df['对PDSCH进行调度时选择了MCS index %d的次数 (无)' %i] * i
nw_df['MCS Count2'] += nw_df['对PDSCH进行调度时选择了MCS index %d的次数 (无)' %i]
nw_df['MeanMCS2'] = nw_df['MeanMCS2']/nw_df['MCS Count2']
summary_df[col] = [nw_df['对PDSCH进行调度时选择了MCS index %d的次数 (无)' %x] for x in range(32)] + [nw_df['MeanMCS1'], nw_df['MeanMCS2']]
pfm_dfs.append(summary_df)
#UL MCS summary
summary_df = pd.DataFrame()
summary_df['UL MCS KPI metrics'] = ['MCS=0 Count', 'MCS=1 Count','MCS=2 Count','MCS=3 Count','MCS=4 Count','MCS=5 Count',
'MCS=6 Count','MCS=7 Count', 'MCS=8 Count','MCS=9 Count','MCS=10 Count','MCS=11 Count',
'MCS=12 Count','MCS=13 Count','MCS=14 Count','MCS=15 Count','MCS=16 Count','MCS=17 Count',
'MCS=18 Count','MCS=19 Count','MCS=20 Count','MCS=21 Count','MCS=22 Count','MCS=23 Count',
'MCS=24 Count','MCS=25 Count','MCS=26 Count','MCS=27 Count','MCS=28 Count','MCS=29 Count',
'MCS=30 Count','MCS=31 Count',
'Avg MCS']
for col, nw_df in zip(['Overall', 'Busy Hour'], [cellkpi.overall_df, cellkpi.bh_df]):
nw_df['MeanULMCS'] = (nw_df['对PUSCH进行调度时选择了MCS index 1的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 2的次数 (无)']*2+\
nw_df['对PUSCH进行调度时选择了MCS index 3的次数 (无)']*3+nw_df['对PUSCH进行调度时选择了MCS index 4的次数 (无)']*4+\
nw_df['对PUSCH进行调度时选择了MCS index 5的次数 (无)']*5+nw_df['对PUSCH进行调度时选择了MCS index 6的次数 (无)']*6+\
nw_df['对PUSCH进行调度时选择了MCS index 7的次数 (无)']*7+nw_df['对PUSCH进行调度时选择了MCS index 8的次数 (无)']*8+\
nw_df['对PUSCH进行调度时选择了MCS index 9的次数 (无)']*9+nw_df['对PUSCH进行调度时选择了MCS index 10的次数 (无)']*10+\
nw_df['对PUSCH进行调度时选择了MCS index 11的次数 (无)']*11+nw_df['对PUSCH进行调度时选择了MCS index 12的次数 (无)']*12+\
nw_df['对PUSCH进行调度时选择了MCS index 13的次数 (无)']*13+nw_df['对PUSCH进行调度时选择了MCS index 14的次数 (无)']*14+\
nw_df['对PUSCH进行调度时选择了MCS index 15的次数 (无)']*15+nw_df['对PUSCH进行调度时选择了MCS index 16的次数 (无)']*16+\
nw_df['对PUSCH进行调度时选择了MCS index 17的次数 (无)']*17+nw_df['对PUSCH进行调度时选择了MCS index 18的次数 (无)']*18+\
nw_df['对PUSCH进行调度时选择了MCS index 19的次数 (无)']*19+nw_df['对PUSCH进行调度时选择了MCS index 20的次数 (无)']*20+\
nw_df['对PUSCH进行调度时选择了MCS index 21的次数 (无)']*21+nw_df['对PUSCH进行调度时选择了MCS index 22的次数 (无)']*22+\
nw_df['对PUSCH进行调度时选择了MCS index 23的次数 (无)']*23+nw_df['对PUSCH进行调度时选择了MCS index 24的次数 (无)']*24+\
nw_df['对PUSCH进行调度时选择了MCS index 25的次数 (无)']*25+nw_df['对PUSCH进行调度时选择了MCS index 26的次数 (无)']*26+\
nw_df['对PUSCH进行调度时选择了MCS index 27的次数 (无)']*27+nw_df['对PUSCH进行调度时选择了MCS index 28的次数 (无)']*28)\
/(nw_df['对PUSCH进行调度时选择了MCS index 0的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 1的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 2的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 3的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 4的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 5的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 6的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 7的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 8的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 9的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 10的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 11的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 12的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 13的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 14的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 15的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 16的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 17的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 18的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 19的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 20的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 21的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 22的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 23的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 24的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 25的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 26的次数 (无)']+\
nw_df['对PUSCH进行调度时选择了MCS index 27的次数 (无)']+nw_df['对PUSCH进行调度时选择了MCS index 28的次数 (无)'])
summary_df[col] = [nw_df['对PUSCH进行调度时选择了MCS index 0的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 1的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 2的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 3的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 4的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 5的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 6的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 7的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 8的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 9的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 10的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 11的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 12的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 13的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 14的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 15的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 16的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 17的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 18的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 19的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 20的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 21的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 22的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 23的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 24的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 25的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 26的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 27的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 28的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 29的次数 (无)'],nw_df['对PUSCH进行调度时选择了MCS index 30的次数 (无)'],\
nw_df['对PUSCH进行调度时选择了MCS index 31的次数 (无)'],nw_df['MeanULMCS']]
pfm_dfs.append(summary_df)
print('summary statistics precessed successfully')
# write excel file
sheet_list = [pfm_dfs, timed_df, cellkpi.celled_df]
name_list = ['performance overview', 'hour based', 'cell based']
cellkpi.writefile(sheet_list, name_list)
print('write Excel file successfully')
