使用健康档案数据计算OLT的用户光衰不合格率

import pandas as pd
import numpy as np

def read_unqualified_light_decay_excel(excel_name):
    data=pd.read_excel(excel_name,skiprows=[0,1,2],header=None)
    colname=['本地网','区县','运营分局','运营包区','营销中心','营销分局','营销网格','营销包区','营销单元','营销小区',
             '综调分公司','综调代理商','综调班组','综调包区','综调装维','综调装维电话',
             '端到端IP','智能网管IP','OLT名称','OLT编码','OLT地址','OLT下行端口编码','OLT下行端口名称','OLT下行端口网管标志','OLT局向信息',
             '一分编码','一分名称','一分网管标志','一分地址','一分上行端口编码','一分下行端口编码','一分下行端口名称','一分下行端口网管标志',
             '一分光网络箱类型','一分光网络箱编码','一分光网络箱名称','一分光网络箱地址',
             '二分编码','二分名称','二分网管标志','二分地址','二分上行端口编码','二分下行端口编码','二分下行端口名称','二分下行端口网管标志',
             '二分光网络箱类型','二分光网络箱编码','二分光网络箱名称','二分光网络箱地址',
             '网关','厂商','终端型号','宽带接入号','宽带账号','olt接收功率','olt发送功率','onu接收功率','onu发送功率','上行光衰','下行光衰',
             'olt发光最小','olt发光最大','olt发光平均','光猫收光最小','光猫收光最大','光猫收光平均',
             '上行光衰最小','上行光衰最大','上行光衰平均','下行光衰最小','下行光衰最大','下行光衰平均','收敛状态','挂测类型','挂测时间',
             'olt发送功率','olt接收功率','光猫发送功率','光猫接收功率','上行光衰','下行光衰','分光器到OLT距离','是否达标','最新是否挂测成功',
             '是补挂测','挂测/补挂测时间','到olt的距离','光猫到OLT的距离','光猫到二分的距离','ONU温度','在线时长','上线时间点','下线时间点',
             '用户星级','沙盘政企类型','是否高带宽','城市农村标','用户地址','采集状态','采集时间','光猫状态','光衰是否合格',
             '是否4星级及以上用户标识','当月最新光衰日期','当月最新光衰值','当月最新合格光衰日期','当月最新合格光衰值']
    data.columns=colname
    return data

def read_qualified_light_decay_excel(excel_name_list):
    data=pd.DataFrame()
    for excel_name in excel_name_list:
        temp=pd.read_excel(excel_name,sheet_name=None,skiprows=[0,1,2],header=None)
        data_temp=pd.DataFrame()
        for each in temp.values():
            data_temp=data_temp.append(each)
        data=data.append(data_temp)
    colname=['本地网','区县','运营分局','运营包区','营销中心','营销分局','营销网格','营销包区','营销单元','营销小区',
             '综调分公司','综调代理商','综调班组','综调包区','综调装维','综调装维电话',
             '端到端IP','智能网管IP','OLT名称','OLT编码','OLT地址','OLT下行端口编码','OLT下行端口名称','OLT下行端口网管标志','OLT局向信息',
             '一分编码','一分名称','一分网管标志','一分地址','一分上行端口编码','一分下行端口编码','一分下行端口名称','一分下行端口网管标志',
             '一分光网络箱类型','一分光网络箱编码','一分光网络箱名称','一分光网络箱地址',
             '二分编码','二分名称','二分网管标志','二分地址','二分上行端口编码','二分下行端口编码','二分下行端口名称','二分下行端口网管标志',
             '二分光网络箱类型','二分光网络箱编码','二分光网络箱名称','二分光网络箱地址',
             '网关','厂商','终端型号','宽带接入号','宽带账号','olt接收功率','olt发送功率','onu接收功率','onu发送功率','上行光衰','下行光衰',
             'olt发光最小','olt发光最大','olt发光平均','光猫收光最小','光猫收光最大','光猫收光平均',
             '上行光衰最小','上行光衰最大','上行光衰平均','下行光衰最小','下行光衰最大','下行光衰平均','收敛状态','挂测类型','挂测时间',
             'olt发送功率','olt接收功率','光猫发送功率','光猫接收功率','上行光衰','下行光衰','分光器到OLT距离','是否达标','最新是否挂测成功',
             '是补挂测','挂测/补挂测时间','到olt的距离','光猫到OLT的距离','光猫到二分的距离','ONU温度','在线时长','上线时间点','下线时间点',
             '用户星级','沙盘政企类型','是否高带宽','城市农村标','用户地址','采集状态','采集时间','光猫状态','光衰是否合格',
             '是否4星级及以上用户标识','当月最新光衰日期','当月最新光衰值','当月最新合格光衰日期','当月最新合格光衰值']
    data.columns=colname
    return data

def unqualified_rate_by_olt(unqualified_data,qualified_data):
    unqualified_users_olt=pd.DataFrame(unqualified_data['端到端IP'].value_counts()).reset_index().rename(columns={'index':'端到端IP','端到端IP':'不合格用户数'})
    qualified_users_olt=pd.DataFrame(qualified_data['端到端IP'].value_counts()).reset_index().rename(columns={'index':'端到端IP','端到端IP':'合格用户数'})
    users_olt=pd.merge(unqualified_users_olt,qualified_users_olt,on=['端到端IP'],how='left')
    users_olt['总用户数']=users_olt['不合格用户数']+users_olt['合格用户数']
    users_olt['不合格率']=users_olt['不合格用户数']/users_olt['总用户数']
    users_olt.sort_values('不合格率',ascending=False,inplace=True)
    return users_olt

unqualified_data=read_unqualified_light_decay_excel('用户光衰达标明细20190927210916234.xlsx')
qualified_data=read_qualified_light_decay_excel(['用户光衰达标明细20190927210930470_2_1.xlsx','用户光衰达标明细20190927210930470_2_2.xlsx'])
unqualified_rate_by_olt=unqualified_rate_by_olt(unqualified_data,qualified_data)
unqualified_rate_by_olt.to_excel('用户光衰不合格率（按OLT）.xlsx',index=False)