pandas筛选特定行告警并生成多个sheet

pandas筛选特定行并生成多个sheet

代码主要功能：
针对《当前告警_所有.csv》表格中的由于RRU断链和RRU掉电引起的小区退服进行筛选，把这些小区退服单独提取，并作为一个单独sheet写入新表格，然后顺便统计下剩余的告警。

# -*- encoding=UTF-8 -*-
__author__ = 'wjj1982'
__date__ = '2019/7/10 22:00'
__product__ = 'PyCharm'
__filename__ = 'fm-filter1'

from pandas import read_csv, DataFrame, ExcelWriter
from os import path, remove
import re
import csv


# 根据告警位置那一列，筛出几号AAU
def get_AAU_id(cell):
    if 'ReplaceableUnit=A_' in cell:
        pattern_AAU = re.compile('ReplaceableUnit=A_(.)')
    elif 'ReplaceableUnit=5' in cell:
        pattern_AAU = re.compile('ReplaceableUnit=5(.)')
    else:
        pattern_AAU = re.compile('ReplaceableUnit=pR_(.)')
    return re.findall(pattern_AAU, cell)[0]


# 根据告警位置那一列，筛出几号小区
def get_CELL_id(cell):
    pattern_CELL = re.compile('NRCellDU=(.)')
    return re.findall(pattern_CELL, cell)[0]


def read_file_rb(filename):
    # 直接read_csv读取会报错，所以就增加了一步open来规避，注意：open后面一定要跟一个close
    filename = open(filename, 'rb')
    pd_csv = read_csv(filename)
    filename.close()
    return pd_csv


def read_file_r(filename):
    filename = open(filename, 'r')
    wjjdf = read_csv(filename)
    filename.close()
    return wjjdf


# 两个中间文件，上次执行异常没有删除的话，本次程序开始时删除即可
if path.exists('wjj1.csv'):
    remove('wjj1.csv')
if path.exists('wjj2.csv'):
    remove('wjj2.csv')


filename = "当前告警_所有.csv"
pd_csv = read_file_rb(filename)


"""
moid存放的是网元id，moid_AAU存放的是断链AAU的网元id，moid_CELL存放的是退服小区的网元id
AAU_off_index存放的是断链AAU的行号，CELL_off_index存放的是退服小区的行号
AAU_id存放的是几号AAU，CELL_id存放的是退服小区的id，如1，2，3
"""
moid = []
moid_AAU = []
moid_CELL = []
AAU_off_index = []
AAU_id = []
CELL_off_index = []
CELL_id = []

moid_AAU2 = []
AAU_off_index2 = []
AAU_id2 = []
CELL_off_index2 = []
CELL_id2 = []

# 根据网元列通过split取出网元id，对于没有网元id的，直接写0000
for i in pd_csv['网元']:
    if ')_' in i:
        moid.append(i.split(')_')[1])
    else:
        moid.append("0000")

# 根据告警码列取出RRU链路断和小区退服的网元ID和对应行号
for index, value in enumerate(pd_csv['告警码名称']):
    if value == "RRU链路断":
        AAU_off_index.append(index)
        moid_AAU.append(moid[index])
    elif value == "小区退服":
        CELL_off_index.append(index)
        moid_CELL.append(moid[index])

# 根据告警原因取出外部掉电和小区退服的网元ID和对应行号(小区退服的可以复用)
for index, value in enumerate(pd_csv['告警原因']):
    if value == "外部掉电":
        AAU_off_index2.append(index)
        moid_AAU2.append(moid[index])

# 位置列中筛出RRU链路断和小区退服对应的AAUid和cellid
for i in AAU_off_index:
    # print(i)
    # print(pd_csv.iloc[i]['位置'])
    AAU_id.append(get_AAU_id(pd_csv.iloc[i]['位置']))

for i in AAU_off_index2:
    AAU_id2.append(get_AAU_id(pd_csv.iloc[i]['位置']))

for i in CELL_off_index:
    CELL_id.append(get_CELL_id(pd_csv.iloc[i]['位置']))

"""
为了进行AAUid和cellid比对，然后取出对应的行号，所以:
1. 生成一个字典：{行号：AAU网元id和AAUid}
2. 生成另一个字典：{行号：小区退服网元id和cellid}
"""
c = dict(zip(AAU_off_index, [str(moid_AAU[i]) + AAU_id[i] for i in range(0, len(moid_AAU))]))
c2 = dict(zip(AAU_off_index2, [str(moid_AAU2[i]) + AAU_id2[i] for i in range(0, len(moid_AAU2))]))
d = dict(zip(CELL_off_index, [str(moid_CELL[i]) + CELL_id[i] for i in range(0, len(moid_CELL))]))

# AAU链路断id和cell退服id相同的行号，记录在CELL_index_filter里
CELL_index_filter = []
CELL_index_filter2 = []

for k, v in d.items():
    if v in c.values():
        CELL_index_filter.append(k)

for k, v in d.items():
    if v in c2.values():
        CELL_index_filter2.append(k)

# 写入csv文件，先写入表头，再写入各个行，因为多个csv写入，用with不知道咋用，所以用了open-close模式。
wf1 = open('wjj1.csv', 'a', newline='')
# 这个newling=''是为了规避直接writerow写入总是多一行空白
w1 = csv.writer(wf1)
w1.writerow(pd_csv.columns)
wf2 = open('wjj2.csv', 'a', newline='')
w2 = csv.writer(wf2)
w2.writerow(pd_csv.columns)
wf3 = open('wjj3.csv', 'a', newline='')
w3 = csv.writer(wf3)
w3.writerow(pd_csv.columns)

for i in range(0, pd_csv.shape[0]):
    if i in CELL_index_filter:
        w2.writerow(pd_csv.iloc[i])
    elif i in CELL_index_filter2:
        w3.writerow(pd_csv.iloc[i])
    else:
        w1.writerow(pd_csv.iloc[i])
wf1.close()
wf2.close()
wf3.close()

# 统计分类告警数量
filename3 = open("wjj1.csv", 'r')
pd_csv3 = read_csv(filename3)
filename3.close()
dict_FM_code = {}
for i in set(pd_csv3['告警码名称'].values):
    dict_FM_code[i] = list(pd_csv3['告警码名称'].values).count(i)

df = DataFrame({"告警码名称": list(dict_FM_code.keys()), "计数": list(dict_FM_code.values())})
df1 = df.sort_values('计数', ascending=False)

# 下面是为了生成一个xlsx表，一个sheet是删除”AAAU链路断引起退服的小区“，一个是”AAAU链路断引起退服的小区“
# 一个新sheet是“外部电源断引起的退服的小区”
wjj1df = read_file_r("wjj1.csv")
wjj2df = read_file_r("wjj2.csv")
wjj3df = read_file_r("wjj3.csv")

if path.exists('FM-filter.xlsx'):
    remove('FM-filter.xlsx')

writer = ExcelWriter('FM-filter.xlsx')
wjj1df.to_excel(excel_writer=writer, sheet_name='删除AAU断和外部掉电后的小区告警', index=False)
wjj2df.to_excel(excel_writer=writer, sheet_name='AAU断对应的小区告警', index=False)
wjj3df.to_excel(excel_writer=writer, sheet_name='外部掉电对应的小区告警', index=False)
df1.to_excel(excel_writer=writer, sheet_name='删除断和掉电告警后分类统计', index=False)
writer.save()
writer.close()
remove('wjj1.csv')
remove('wjj2.csv')
remove('wjj3.csv')
print("succeed, you can check FM-filter.xlsx now!")
input('Press Enter to exit...')