# -*- coding: utf-8 -*- """ Created on Wed Jan 12 15:43:33 2022 @author: 田中玉 """ import xlsxwriter import numpy as np import pandas as pd import os import zipfile import shutil import time from docx.shared import Pt from docx.enum.style import WD_STYLE_TYPE from docx.oxml.ns import qn from docx.shared import Inches from docx import Document import xlsxwriter import numpy as np import pandas as pd import os import matplotlib.pyplot as plt from openpyxl.styles import Alignment,Font from openpyxl import Workbook, load_workbook from openpyxl.drawing.image import Image import openpyxl import xlrd,xlsxwriter from docx.shared import Pt from math import ceil import docx from datetime import datetime import datetime, time from datetime import timedelta path = 'D:/_5G周报' path_5g='D:/_5G周报/原始数据'+'//' path_5g_busy='D:/_5G周报/忙时数据'+'//' path_out='D:/_5G周报/输出文件'+'//' os.chdir(path) files_5G = os.listdir(path_5g) zte_5G_files=[x for x in files_5G if '5G' in x] eric_5G_files = [x for x in files_5G if '爱立信' in x] files_5G_busy = os.listdir(path_5g_busy) zte_5G_busy_files=[x for x in files_5G_busy if '5G' in x] eric_5G_busy_files = [x for x in files_5G_busy if '爱立信' in x] df_enodeb_name = pd.read_excel('物理站址清单.xlsx',encoding = 'utf-8') #处理爱立信基础数据 df_eric_5G_traffic = pd.DataFrame() for file in eric_5G_files: df_tmp = pd.read_excel(path_5g+file) df_tmp.fillna(0,inplace=True) df_tmp.columns df_tmp['地市']=df_tmp['ManagedElement'].map(lambda x : list(x.split('_')[1])[0])+df_tmp['ManagedElement'].map(lambda x : list(x.split('_')[1])[1]) df_tmp=df_tmp[['地市','DATE_ID','cell','PLMN','RLC_RxBytesUl','RLC_TxBytesDl','RRC_ConnMax']] df_tmp=df_tmp[df_tmp['地市'].isin(['QJ'])] df_tmp['地市']=df_tmp['地市'].map(lambda x :x.replace('QJ','曲靖')) df_tmp['上行RLC层用户面总流量(GB)']=df_tmp['RLC_RxBytesUl']/1024 df_tmp['下行RLC层用户面总流量(GB)']=df_tmp['RLC_TxBytesDl']/1024 df_tmp['总流量']=(df_tmp['RLC_RxBytesUl']+df_tmp['RLC_TxBytesDl'])/1024 df_tmp['厂家']='爱立信' df_tmp['日期']=df_tmp['DATE_ID'].map(lambda x : str(x).split()[0].replace('-','/')) df_tmp['基站ID+小区ID']=df_tmp['cell'].map(lambda x :x.split('_')[0])+"_"+df_tmp['cell'].map(lambda x :x.split('_')[1]) df_tmp['标记']=df_tmp['日期']+'_'+df_tmp['基站ID+小区ID'] df_eric_5G_traffic=df_eric_5G_traffic.append(df_tmp) #处理爱立信忙时数据 df_eric_5G_traffic_bush = pd.DataFrame() for file in eric_5G_busy_files: df_tmp = pd.read_excel(path_5g_busy+file) df_tmp.fillna(0,inplace=True) df_tmp.columns df_tmp=df_tmp[['date_id','gNB_cell','PRB_UsedDl','PRB_AvailDl','UL_User_Avg_ThroughPut_Rate_Mbps','DL_User_Avg_ThroughPut_Rate_Mbps']] df_tmp['日期']=df_tmp['date_id'].map(lambda x : str(x).split()[0].replace('-','/')) df_tmp['PRB利用率']=df_tmp['PRB_UsedDl']/df_tmp['PRB_AvailDl'] df_tmp['基站ID+小区ID']=df_tmp['gNB_cell'].map(lambda x :x.split('_')[0])+"_"+df_tmp['gNB_cell'].map(lambda x :x.split('_')[1]) df_tmp['标记']=df_tmp['日期']+'_'+df_tmp['基站ID+小区ID'] df_tmp=df_tmp[['标记','UL_User_Avg_ThroughPut_Rate_Mbps','DL_User_Avg_ThroughPut_Rate_Mbps','PRB利用率']] df_eric_5G_traffic_bush=df_eric_5G_traffic_bush.append(df_tmp) df_eric_5G=pd.merge(df_eric_5G_traffic,df_eric_5G_traffic_bush,how='left',on='标记') df_eric_5G.columns df_eric_5G.rename(columns={'UL_User_Avg_ThroughPut_Rate_Mbps':'上行体验速率', 'DL_User_Avg_ThroughPut_Rate_Mbps':'下行体验速率', 'RRC_ConnMax':'RRC最大连接用户数',},inplace =True) df_eric_5G1=df_eric_5G[['地市','厂家','基站ID+小区ID','日期','PLMN','RRC最大连接用户数','总流量','上行体验速率','下行体验速率','PRB利用率']] with pd.ExcelWriter(path_out+'给联通数据.xlsx') as f: df_eric_5G1.to_excel(f, index =False) df_eric_5G2=pd.merge(df_eric_5G,df_enodeb_name,how='left',on='基站ID+小区ID') df_eric_5G2=df_eric_5G2[['地市','厂家','基站ID+小区ID','区县','基站名称','日期','PLMN','RRC最大连接用户数','上行RLC层用户面总流量(GB)','下行RLC层用户面总流量(GB)','总流量','PRB利用率']] # df_zte_5G_traffic = pd.DataFrame() for x in zte_5G_files: file_zip = zipfile.ZipFile(path_5g+x, 'r') file_zip.extractall(path_5g) file_zip.close() zte_list=os.listdir(path_5g) zte_list=[x for x in zte_list if '5G' in x and 'xlsx'in x] df_tmp = pd.read_excel(path_5g+zte_list[0]) df_tmp['地市']=df_tmp['子网名称'].map(lambda x:x[:2]) df_tmp['厂家']='中兴' df_tmp['基站ID+小区ID']=df_tmp['gNBId'].astype(str)+"_"+df_tmp['cellId'].astype(str) df_tmp['日期']=df_tmp['开始时间'].map(lambda x : str(x).split()[0].replace('-','/')) df_tmp['PLMN']=df_tmp['gNBplmn'].map(lambda x:int(x.replace('-',''))) df_tmp['RRC最大连接用户数']=df_tmp['RRC连接最大连接用户数'] df_tmp['上行RLC层用户面总流量(GB)']=df_tmp['上行RLC层用户面流量_1609386307605(GB)'] df_tmp['下行RLC层用户面总流量(GB)']=df_tmp['下行RLC层用户面流量_1609386307658(GB)'] df_tmp['总流量']=df_tmp['RLC层用户面总流量(GB)'] df_tmp['标记']=df_tmp['日期']+'_'+df_tmp['基站ID+小区ID'] df_tmp=df_tmp[['地市','厂家','基站ID+小区ID','日期','PLMN','RRC最大连接用户数','上行RLC层用户面总流量(GB)','下行RLC层用户面总流量(GB)','总流量','标记']] df_zte_5G_traffic=df_zte_5G_traffic.append(df_tmp) os.remove(path_5g+zte_list[0]) df_zte_5G_traffic_busy= pd.DataFrame() for x in zte_5G_busy_files: file_zip = zipfile.ZipFile(path_5g_busy+x, 'r') file_zip.extractall(path_5g_busy) file_zip.close() zte_list=os.listdir(path_5g_busy) zte_list=[x for x in zte_list if '5G' in x and 'xlsx'in x] df_tmp = pd.read_excel(path_5g_busy+zte_list[0]) df_tmp.columns df_tmp['基站ID+小区ID']=df_tmp['gNBId'].astype(str)+"_"+df_tmp['cellId'].astype(str) df_tmp['日期']=df_tmp['开始时间'].map(lambda x : str(x).split()[0].replace('-','/')) df_tmp['PRB利用率']=df_tmp['下行PRB利用率-tucq'] df_tmp['标记']=df_tmp['日期']+'_'+df_tmp['基站ID+小区ID'] df_tmp=df_tmp[['标记','PRB利用率']] df_tmp=df_tmp.drop_duplicates()#整表去重 df_zte_5G_traffic_busy=df_zte_5G_traffic_busy.append(df_tmp) os.remove(path_5g_busy+zte_list[0]) df_zte_5G_traffic=pd.merge(df_zte_5G_traffic,df_enodeb_name,how='left',on='基站ID+小区ID') df_zte_5G=pd.merge(df_zte_5G_traffic,df_zte_5G_traffic_busy,how='left',on='标记') df_zte_5G=df_zte_5G[['地市','厂家','基站ID+小区ID','区县','基站名称','日期','PLMN','RRC最大连接用户数','上行RLC层用户面总流量(GB)','下行RLC层用户面总流量(GB)','总流量','PRB利用率']] df_zong=pd.concat([df_eric_5G2,df_zte_5G],ignore_index=True) df_zong.fillna(0,inplace=True) with pd.ExcelWriter(path_out+'5G周报.xlsx') as f: df_zong.to_excel(f, index =False)