@[TOC]pandas读取csv，按每天的时间区间筛选数据
需求读取csv内容，以0到6点， 6到12点，12到18点，18点到24点为4个区间，把读取的内容筛进4个区间里
然后获取同周期CRC字段>100超过10次的数据
把数据生成到模板里，输出到csv文件
1.读取数据
引用了pandas读取csv数据，方便快捷
data_extract = pd.read_csv(r’OLT上联端口CRC越限告警明细.csv’, engine=‘python’, encoding=‘gb18030’)
2.日期去重
对于设置时间区间上，因为每天都有一个区间，要先读取到日期，由于日期是带有年月日时分秒格式的，这里采用了一个全局列表，把日期先截取到年月日，再放入列表里，对于同一天的日期数据，先判断有没有添加，已添加则不加入列表
3.每天的周期设置
采用timedelta(hours=6)，以每天的零点，往后延6,12,18,24小时，分好4个区间
4.dataframe格式数据清洗
dataframe格式的数据有独特的数据筛选方式，比如
data_cleaning_0to6 = data_extract[
(data_extract['startTime'] >= range_0) & (data_extract['startTime'] <= range_6) & (data_extract['CRC'] > 100)]
5.编写模板
略
6.数据写入csv
def clientToServer(self, strData):
today = time.strftime('%Y%m%d', time.localtime(time.time()))
# self.filename = "/data/FBAndIPTVsocket/result/sourceData/OLTUOOCRCAlarm6%s.csv" % (today)
# self.filenameOK = "/data/FBAndIPTVsocket/result/sourceDataOK/OLTUOOCRCAlarm6%s.csv.ok" % (today)
script_path = os.path.dirname(__file__)
self.filename = script_path+"/OLT上联端口CRC越限告警明细%s.csv" % (today)
self.filenameOK = script_path+"/OLT上联端口CRC越限告警明细%s.csv.ok" % (today)
with open(self.filename.encode('utf-8'), "a") as csvfile:
writer = csv.writer(csvfile)
writer.writerow([strData])
with open(self.filenameOK.encode('utf-8'), "a") as csvfile:
writer = csv.writer(csvfile)
writer.writerow([strData])
cmd = "cp -f %s %s " % (self.filename, self.filenameOK)
ret = os.system(cmd)
self.logger.info(str(ret))
全部代码
import csv
import os
import time
import pandas as pd
from datetime import date
from datetime import datetime
from datetime import timedelta
from getLog import FinalLogger
date_list = []
data_lists = []
ds = date.today().strftime(“%Y%m%d”)
class warningProcess():
def init(self):
self.logger = FinalLogger.getLogger()
self.fileName = “”
self.totalWarningCnt = 0
def clientToServer(self, strData):
today = time.strftime('%Y%m%d', time.localtime(time.time()))
# self.filename = "/data/FBAndIPTVsocket/result/sourceData/OLTUOOCRCAlarm6%s.csv" % (today)
# self.filenameOK = "/data/FBAndIPTVsocket/result/sourceDataOK/OLTUOOCRCAlarm6%s.csv.ok" % (today)
script_path = os.path.dirname(__file__)
self.filename = script_path+"/OLT上联端口CRC越限告警明细%s.csv" % (today)
self.filenameOK = script_path+"/OLT上联端口CRC越限告警明细%s.csv.ok" % (today)
with open(self.filename.encode('utf-8'), "a") as csvfile:
writer = csv.writer(csvfile)
writer.writerow([strData])
with open(self.filenameOK.encode('utf-8'), "a") as csvfile:
writer = csv.writer(csvfile)
writer.writerow([strData])
def alarmListingTemplate(self, data_cleaning):
for index, row in data_cleaning.iterrows():
id = row['id'],
reportTime = row['reportTime'],
manufacturerName = str(row['manufacturerName']),
startTime = row['startTime'],
oltIp = row['oltIp'],
upLinkPort = row['upLinkPort'],
CRC = row['CRC'],
oltName = row['oltName']
manufacturerName = manufacturerName[0].strip()
if manufacturerName == 'ZTE':
manufacturerCHName = '中兴'
maintain_group = '中兴'
elif manufacturerName == 'HW':
manufacturerCHName = '华为'
maintain_group = '华为贝尔'
elif manufacturerName == 'BeiEr':
manufacturerCHName = '贝尔'
maintain_group = '华为贝尔'
else:
manufacturerCHName = '未知'
maintain_group = '未知'
strData1 = ""
strData2 = "IntVersion:V1.0.0"
strData3 = "MsgSerial:"
strData4 = "AlarmUniqueId:" + str(id[0])
strData5 = "AlarmUniqueClearId:"
strData6 = "NeName:" + str(oltName).strip()
strData7 = "NeIp:" + str(oltIp[0]).strip()
strData8 = "SystemName:集中性能系统"
strData9 = "EquipmentClass:"
strData10 = "Version:V1.0"
strData11 = "LocateNeName:" + str(upLinkPort[0])
strData12 = "LocateNeType:"
strData13 = "LocateInfo:000000000"
strData14 = "EventTime:" + str(reportTime[0])
strData15 = "CancelTime:" + str(startTime[0])
strData16 = "VendorSeverity:三级告警"
strData17 = "VendorAlarmId:"
strData18 = "AlarmTitle:" + "OLT上联端口CRC误码越限"
strData19 = "ProbableCauseTxt:"
strData20 = "AlarmText:" + "周期内上联端口" + "CRC大于100的次数大于10次"
strData21 = "AlarmStatus:1"
strData22 = "Vendor:" + manufacturerCHName
strData23 = "maintain_group:" + maintain_group
strData24 = ""
strDataU8 = "rn" + strData1 + "rn" + strData2 + "rn" + strData3 + "rn" + strData4 + "rn" + strData5 + "rn" + strData6 + "rn" + strData7 + "rn" + strData8 + "rn" + strData9 + "rn" + strData10 + "rn" + strData11 + "rn" + strData12 + "rn" + strData13 + "rn" + strData14 + "rn" + strData15 + "rn" + strData16 + "rn" + strData17 + "rn" + strData18 + "rn" + strData19 + "rn" + strData20 + "rn" + strData21 + "rn" + strData22 + "rn" + strData23 + "rn" + strData24 + "rn"
self.logger.info("生成告警模块")
self.logger.info(strDataU8)
print(strDataU8)
self.clientToServer(strDataU8)
self.totalWarningCnt = self.totalWarningCnt + 1
break
def data_to_excel(self, data_cleaning, data_lists):
for index, row in data_cleaning.iterrows():
id = row['id']
reportTime = row['reportTime']
manufacturerName = row['manufacturerName']
startTime = row['startTime']
oltIp = row['oltIp']
upLinkPort = row['upLinkPort']
CRC = row['CRC']
oltName = row['oltName']
data_list = [id, reportTime, manufacturerName, startTime, oltIp, upLinkPort, CRC, oltName]
data_lists.append(data_list)
# 输出到excel
# col = ['id', 'reportTime', 'manufacturerName', 'startTime', 'oltIp', 'upLinkPort', 'CRC', 'oltName']
# result = pd.DataFrame(data_lists, columns=col)
# result.to_excel(excel_writer=r"周期内上联端口CRC大于100的次数大于10次告警输出结果%s.xlsx" % ds, index=False)
# print('结果输出成功！')
def read_data(self):
# 读取数据
data_extract = pd.read_csv(r'OLT上联端口CRC越限告警明细.csv', engine='python', encoding='gb18030')
data_extract['startTime'] = pd.to_datetime(data_extract['startTime'])
DateStart = data_extract['startTime']
# 获取日
for date in DateStart:
d = date.strftime("%Y-%m-%d")
if d not in date_list:
date_list.append(d)
# 获取同周期CRC>100超过10次
for date in date_list:
range_0 = datetime.strptime(date, "%Y-%m-%d")
range_6 = range_0 + timedelta(hours=6)
range_12 = range_0 + timedelta(hours=12)
range_18 = range_0 + timedelta(hours=18)
range_24 = range_0 + timedelta(hours=24)
# 设置区间
data_cleaning_0to6 = data_extract[
(data_extract['startTime'] >= range_0) & (data_extract['startTime'] <= range_6) & (
data_extract['CRC'] > 100)]
data_cleaning_6to12 = data_extract[
(data_extract['startTime'] > range_6) & (data_extract['startTime'] <= range_12) & (
data_extract['CRC'] > 100)]
data_cleaning_12to18 = data_extract[
(data_extract['startTime'] > range_12) & (data_extract['startTime'] <= range_18) & (
data_extract['CRC'] > 100)]
data_cleaning_18to24 = data_extract[
(data_extract['startTime'] > range_18) & (data_extract['startTime'] <= range_24) & (
data_extract['CRC'] > 100)]
if len(data_cleaning_0to6) > 10:
self.alarmListingTemplate(data_cleaning_0to6)
if len(data_cleaning_6to12) > 10:
self.alarmListingTemplate(data_cleaning_6to12)
if len(data_cleaning_12to18) > 10:
self.alarmListingTemplate(data_cleaning_12to18)
if len(data_cleaning_18to24) > 10:
self.alarmListingTemplate(data_cleaning_18to24)
if self.totalWarningCnt > 0:
print(self.filename)
cmd = "cp -f %s %s " % (self.filename, self.filenameOK)
ret = os.system(cmd)
self.logger.info(str(ret))
else:
self.logger.info("当前告警数为：%d" % self.totalWarningCnt)
if __name__ == '__main__':
WarningProcess = warningProcess()
WarningProcess.read_data()
数据
