hz-application-ml/LIB/MIDDLE/Anomaly_Detection/V1_0_0/main_detection.py

#热失控预警：PCA异常指数
#预测及异常预警

from LIB.BACKEND import DBManager

dbManager = DBManager.DBManager()
import datetime

import joblib
import pandas as pd
import pymysql
from LIB.MIDDLE.CellStateEstimation.Common import log

from anomalyPCA import *

dataSOH = pd.read_excel('sn-20210903.xlsx',sheet_name='sn-20210903')
fileNames = dataSOH['sn']
fileNames = list(fileNames)
l = len(fileNames)

now_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')   #type: str
now_time=datetime.datetime.strptime(now_time,'%Y-%m-%d %H:%M:%S')     #type: datetime
start_time=now_time-datetime.timedelta(hours=6)
end_time=str(now_time)
start_time=str(start_time)

mylog=log.Mylog('log.txt','error')
mylog.logcfg()

#数据库配置
host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
port=3306
user='qx_algo_readonly'
password = 'qx@123456'

#读取故障结果库中code==119且end_time='0000-00-00 00:00:00'...............................
db='safety_platform'
mysql = pymysql.connect (host=host, port=port, user=user, password=password, database=db)
cursor = mysql.cursor()
param='start_time,end_time,product_id,code,level,info,advice'
tablename='all_fault_info'
sql =  "select %s from %s where code='C493' and end_time='0000-00-00 00:00:00'" %(param,tablename)
cursor.execute(sql)
res = cursor.fetchall()
df_diag_ram= pd.DataFrame(res,columns=param.split(','))
cursor.close()
mysql.close()

anomalies=pd.DataFrame()
df_res=pd.DataFrame(columns=['start_time','end_time','product_id','code','level','info','advice'])
for k in range(l): 
    try: 
        sn = fileNames[k]
        df_diag_ram_sn=df_diag_ram[df_diag_ram['product_id']==sn]
        df_data = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time ,data_groups=['bms'])
        data_test = df_data['bms']
        data_test=data_test[data_test['SOC[%]']>20]
        if len(data_test)>15:
            pca1 = joblib.load('pca1_'+sn+'.m')  
            pca2 = joblib.load('pca2_'+sn+'.m') 
            res1 = pd.read_csv('res1_'+sn+'.csv',encoding='gbk')
            res2 = pd.read_csv('res2_'+sn+'.csv',encoding='gbk')
            pred1,pred2=prediction(data_test,pca1,pca2)
            pred1=pred1.reset_index()
            pred2=pred2.reset_index()
            outliers1=detect_outliers(res1,pred1,threshold=30)
            outliers2=detect_outliers(res2,pred2,threshold=16)
            if (len(outliers1)>0) & (len(outliers2)>0):
                outliers=check_anomaly(outliers1,outliers2,res2)
                if len(outliers)>5:
                    outliers['sn']=sn
                    outliers=outliers.reset_index()
                    anomalies=anomalies.append(outliers)
                    u_th=boxplot_fill(res2)
                    outliers3=pred2[pred2['低压差']>u_th]
                    if df_diag_ram_sn.empty: 
                        product_id=sn
                        start_time=outliers.loc[0,'时间']
                        start_time=start_time+'0:00'
                        if outliers.loc[outliers.index[-1],'时间'] == pred1.loc[pred1.index[-1],'时间']:
                            end_time='0000-00-00 00:00:00'
                        elif outliers1.loc[outliers1.index[-1],'时间'] == outliers3.loc[outliers3.index[-1],'时间']:
                            end_time='0000-00-00 00:00:00'
                        else:
                            end_time=outliers.loc[outliers.index[-1],'时间']
                            end_time=end_time+'0:00'
                        code='C493'
                        level=4
                        info='热失控预警'
                        advice='建议返厂维修'
                        df_res=df_res.append({'start_time':start_time, 'end_time':end_time,'product_id':product_id, 'code':code, 'level':level, 'info':info,'advice':advice},ignore_index=True)
                        with open(r'D:\Platform\platform_python\data_analyze_platform\USER\spf\01qixiang\06BatSafetyAlarm\热失控报警.txt','a') as file:
                            file.write(str(tuple(df_res.iloc[-1]))+'\n')    
                    else:
                        if outliers.loc[outliers.index[-1],'时间'] == pred1.loc[pred1.index[-1],'时间']:
                            end_time='0000-00-00 00:00:00'
                        elif outliers1.loc[outliers1.index[-1],'时间'] == outliers3.loc[outliers3.index[-1],'时间']:
                            end_time='0000-00-00 00:00:00'
                        else:
                            end_time=outliers.loc[outliers.index[-1],'时间']
                            end_time=end_time+'0:00'
                            df_diag_ram_sn['end_time']=end_time
                            with open(r'D:\Platform\platform_python\data_analyze_platform\USER\spf\01qixiang\06BatSafetyAlarm\热失控报警.txt','a') as file:
                                file.write(str(tuple(df_diag_ram_sn.iloc[-1]))+'\n')
                        
           
    except Exception as e:
        print(repr(e))
        mylog.logopt(sn,e)
        pass