import CBMSBatChrgcopy import log #coding=utf-8 import datetime import pandas as pd from LIB.BACKEND import DBManager, Log from LIB.MIDDLE.CellStateEstimation.Common.V1_0_1 import DBDownload # from LIB.MIDDLE.CellStateEstimation.Common.V1_0_1 import log from sqlalchemy import create_engine import time, datetime import os import numpy as np from apscheduler.schedulers.blocking import BlockingScheduler import QX_BatteryParam import pymysql import matplotlib.pyplot as plt #-------------------------------------------新建电压排序函数-------------------------------------------- # def CellVolt_rang(): # global SNnums # global df_Diag_Ram # start=time.time() # end_time=datetime.datetime.now() # start_time=end_time-datetime.timedelta(seconds=720) # start_time=start_time.strftime('%Y-%m-%d %H:%M:%S') # end_time=end_time.strftime('%Y-%m-%d %H:%M:%S') # print('---------------计算中-------------------------') # for sn in SNnums: # if 'PK500' in sn: # celltype=1 #6040三元电芯 # elif 'PK502' in sn: # celltype=2 #4840三元电芯 # elif 'K504B' in sn: # celltype=99 #60ah林磷酸铁锂电芯 # elif 'MGMLXN750' in sn: # celltype=3 #力信50ah三元电芯 # elif 'MGMCLN750' or 'UD' in sn: # celltype=4 #CATL 50ah三元电芯 # else: # print('SN：{}，未找到对应电池类型！！！'.format(sn)) # continue # # sys.exit() # param=QX_BatteryParam.BatteryInfo(celltype) # #读取结果数据库数据........................................................................................................................................................ # host='47.97.96.242' # port=3306 # db='didi' # user='root' # password='qx123456' # tablename='didi_data' # param='date,device_id,bat_model,position,current,soc,celltemp,cellvolt_2,cellvolt_3,cellvolt_4,cellvolt_5,cellvolt_6,cellvolt_7,cellvolt_8,cellvolt_9' # mysql = pymysql.connect (host=host, user=user, password=password, port=port, database=db) # cursor = mysql.cursor() # sql = "select %s from %s where device_id='%s'" %(param,tablename,sn) # cursor.execute(sql) # res = cursor.fetchall() # df_bms= pd.DataFrame(res,columns=param.split(',')) # cursor.close() # mysql.close() # # mode=1 # # # tablename4='cellstateestimation_intershort' # # DBRead=DBDownload.DBDownload(host, port, db, user, password, mode) #mode==1取数据库最后一行数据 # # with DBRead as DBRead: # # df_data=DBRead.getdata('date,device_id,position,current,soc,cellvolt_2,cellvolt_3,cellvolt_4,cellvolt_5,cellvolt_6,cellvolt_7,cellvolt_8,cellvolt_9', tablename=tablename, sn=sn, timename='date', st=start_time, sp=end_time) # # df_bms.to_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\origin_data\\'+'CBMS_diag_'+sn+'.csv',encoding='gbk') # #电压排序................................................................................................................................................................ # df_temp = df_bms[df_bms['position']==2]#筛选充电数据 # df_chrgr = df_temp.reset_index(drop=True) # df_chrgr_cellvolt = df_chrgr[['cellvolt_2','cellvolt_3','cellvolt_4','cellvolt_5','cellvolt_6','cellvolt_7','cellvolt_8','cellvolt_9']] # df_chrgr_cellvolt_change = np.array(df_chrgr_cellvolt)#转数组 # df_chrgr_cellvolt_sort = np.argsort(df_chrgr_cellvolt_change)#取排序号 # df_cellvolt_sort_dif = np.diff(df_chrgr_cellvolt_sort)#一次微分 # df_cellvolt_sort_dif_confir = np.nonzero(df_cellvolt_sort_dif)#取非0值 # Cell_num = set(df_cellvolt_sort_dif_confir[1])#寻找哪号电芯序号异常np.unique # X_col=np.size(df_chrgr_cellvolt,0) #计算 X 的列数 # #df_cellvolt_sort_difdif = np.diff(df_cellvolt_sort_dif)#二次微分 # problem_data = pd.DataFrame() # temp_list = [] # for item in Cell_num: # temp_list.append(np.sum(df_cellvolt_sort_dif_confir[1]==item) > X_col/20) # if any(temp_list):#序号变化的电芯 # data_temp = pd.DataFrame(df_chrgr_cellvolt_sort) # problem_data = pd.concat([df_chrgr,data_temp], axis = 1) # problem_data.to_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+'CBMS_diag_'+sn+'.csv',encoding='gbk') # # volt_column = ['0','1','2','3','4','5','6','7'] # # fig = plt.figure(figsize=(20,10)) # # length = len(problem_data) # # for column in volt_column: # # plt.scatter([x for x in range(0, length)], problem_data[column][0:length], label=column) # # plt.legend() # # title = problem_data['device_id'][0] # # plt.title(title) # # plt.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+title+'.png', dpi=300) # # plt.show() # # fig = plt.figure() # # #ax = plt.subplot() # # plt.scatter(problem_data['date'], problem_data['0'], c='black',marker='o',alpha=0.5, label = "2#电芯") # # plt.scatter(problem_data['date'], problem_data['1'], c='green', marker='o',alpha=0.5, label = "3#电芯") # # plt.scatter(problem_data['date'], problem_data['2'], c='red',marker='o', alpha=0.5, label = "4#电芯") # # plt.scatter(problem_data['date'], problem_data['3'], c='black',marker='o',alpha=0.5, label = "5#电芯") # # plt.scatter(problem_data['date'], problem_data['4'], c='green', marker='o',alpha=0.5, label = "6#电芯") # # plt.scatter(problem_data['date'], problem_data['5'], c='red',marker='o', alpha=0.5, label = "7#电芯") # # plt.scatter(problem_data['date'], problem_data['6'], c='black',marker='o',alpha=0.5, label = "8#电芯") # # plt.scatter(problem_data['date'], problem_data['7'], c='green', marker='o',alpha=0.5, label = "9#电芯") # # plt.xlabel("时间", fontsize=15) # # plt.ylabel("电压排序", fontsize=15) # # title = problem_data['device_id'][0] # # plt.title(title, fontsize=20) # # plt.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+title+'.png', dpi=300) # # plt.show() # #print(problem_data) # end=time.time() # print('--------------计算时间：------------') # print(end-start) # # print(df_soh) #...............................................主函数....................................................................................................................... if __name__ == "__main__": excelpath=r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\sn.csv' # SNdata_6060 = pd.read_excel(excelpath, sheet_name='科易6060') # SNdata_6040 = pd.read_excel(excelpath, sheet_name='科易6040') # SNdata_4840 = pd.read_excel(excelpath, sheet_name='科易4840') # SNdata_L7255 = pd.read_excel(excelpath, sheet_name='格林美-力信7255') # SNdata_C7255 = pd.read_excel(excelpath, sheet_name='格林美-CATL7255') # SNdata_U7255 = pd.read_excel(excelpath, sheet_name='优旦7255') # SNdata_didi = pd.read_csv(excelpath, encoding='gbk') SNdata_didi_trw = pd.read_csv(excelpath, encoding='gbk') # SNnums_6060=SNdata_6060['SN号'].tolist() # SNnums_6040=SNdata_6040['SN号'].tolist() # SNnums_4840=SNdata_4840['SN号'].tolist() # SNnums_L7255=SNdata_L7255['SN号'].tolist() # SNnums_C7255=SNdata_C7255['SN号'].tolist() # SNnums_U7255=SNdata_U7255['SN号'].tolist() # SNnums_didi = SNdata_didi['device_id'].tolist() SNnums_didi_trw = SNdata_didi_trw['device_id'].tolist() # SNnums=SNnums_L7255 + SNnums_C7255 + SNnums_6040 + SNnums_4840 + SNnums_U7255+ SNnums_6060 # SNnums=['BAA3219081550897'] SNnums = SNnums_didi_trw mylog=log.Mylog('log_diag.txt','error') mylog.logcfg() #............................模块运行前，先读取数据库中所有结束时间为0的数据，需要从数据库中读取................ #result=pd.read_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\result.csv',encoding='gbk') #df_Diag_Ram=result[result['end_time']=='0000-00-00 00:00:00'] print('----------------输入--------') #定时任务....................................................................................................................................................................... # scheduler = BlockingScheduler() # scheduler.add_job(CellVolt_rang, 'interval', seconds=720, id='diag_job') # try: # scheduler.start() # except Exception as e: # scheduler.shutdown() # print(repr(e)) # mylog.logopt(e) print('---------------计算中-------------------------') start=time.time() for sn in SNnums: if 'PK500' in sn: celltype=1 #6040三元电芯 elif 'PK502' in sn: celltype=2 #4840三元电芯 elif 'K504B' in sn: celltype=99 #60ah林磷酸铁锂电芯 elif 'MGMLXN750' in sn: celltype=3 #力信50ah三元电芯 elif 'MGMCLN750' or 'UD' in sn: celltype=4 #CATL 50ah三元电芯 else: print('SN：{}，未找到对应电池类型！！！'.format(sn)) continue # sys.exit() param=QX_BatteryParam.BatteryInfo(celltype) #读取结果数据库数据........................................................................................................................................................ host='47.97.96.242' port=3306 db='didi' user='root' password='qx123456' tablename='didi_data' param='date,device_id,bat_model,position,current,soc,celltemp,cellvolt_2,cellvolt_3,cellvolt_4,cellvolt_5,cellvolt_6,cellvolt_7,cellvolt_8,cellvolt_9' mysql = pymysql.connect (host=host, user=user, password=password, port=port, database=db) cursor = mysql.cursor() sql = "select %s from %s where device_id='%s'" %(param,tablename,sn) cursor.execute(sql) res = cursor.fetchall() df_bms= pd.DataFrame(res,columns=param.split(',')) cursor.close() mysql.close() # mode=1 # # tablename4='cellstateestimation_intershort' # DBRead=DBDownload.DBDownload(host, port, db, user, password, mode) #mode==1取数据库最后一行数据 # with DBRead as DBRead: # df_data=DBRead.getdata('date,device_id,position,current,soc,cellvolt_2,cellvolt_3,cellvolt_4,cellvolt_5,cellvolt_6,cellvolt_7,cellvolt_8,cellvolt_9', tablename=tablename, sn=sn, timename='date', st=start_time, sp=end_time) # df_bms.to_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\origin_data\\'+'CBMS_diag_'+sn+'.csv',encoding='gbk') #电压排序................................................................................................................................................................ df_temp_crnt = df_bms[df_bms['current']>1]#筛选充电数据 df_temp = df_temp_crnt[df_temp_crnt['position']==2]#筛选充电数据 df_chrgr = df_temp.reset_index(drop=True) df_chrgr_cellvolt = df_chrgr[['cellvolt_2','cellvolt_3','cellvolt_4','cellvolt_5','cellvolt_6','cellvolt_7','cellvolt_8','cellvolt_9']] df_chrgr_cellvolt_change = np.array(df_chrgr_cellvolt)#转数组 df_chrgr_cellvolt_sort = np.argsort(df_chrgr_cellvolt_change)#取排序号 df_cellvolt_sort_dif = np.diff(df_chrgr_cellvolt_sort,axis=0)#一次微分 df_cellvolt_sort_dif_confir = np.nonzero(df_cellvolt_sort_dif)#取非0值 Cell_num = set(df_cellvolt_sort_dif_confir[1])#寻找哪号电芯序号异常np.unique X_col=np.size(df_chrgr_cellvolt,0) #计算 X 的列数 #df_cellvolt_sort_difdif = np.diff(df_cellvolt_sort_dif)#二次微分 problem_data = pd.DataFrame() temp_list = [] for item in Cell_num: temp_list.append(np.sum(df_cellvolt_sort_dif_confir[1]==item) > X_col/20) if any(temp_list):#序号变化的电芯 data_temp = pd.DataFrame(df_chrgr_cellvolt_sort) #problem_data = pd.concat([df_chrgr,data_temp], axis = 1) problem_data = data_temp sn=sn.replace('/','') # volt_column = [0,1,2,3,4,5,6,7] # fig = plt.figure(figsize=(20,10)) # length = len(problem_data) # num_length = np.arange(length) # for column in volt_column: # plt.scatter(num_length, problem_data[column], label=column) # plt.legend() # # title = problem_data['device_id'][0] # plt.title(sn) # plt.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+sn+'.png', dpi=300) # plt.show() if not problem_data.empty: problem_data.to_csv(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+'CBMS_diag_'+sn+'.csv',encoding='gbk') print(problem_data) ax=problem_data.plot(marker='*',markersize=15, figsize=(16,9)) plt.xlabel('时间', fontsize=20) plt.ylabel('排序变化', fontsize=20) plt.xticks(fontsize=15) plt.yticks(fontsize=15) # plt.ylim(-30,30) plt.title(str(sn),fontsize=25) plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签 plt.rcParams['axes.unicode_minus']=False #用来正常显示负号 plt.legend(bbox_to_anchor=(1, 0), loc=3, fontsize=14) plt.show() fig = ax.get_figure() fig.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+str(sn)+'电压排序.png') # fig = plt.figure() # #ax = plt.subplot() # plt.scatter(problem_data['date'], problem_data['0'], c='black',marker='o',alpha=0.5, label = "2#电芯") # plt.scatter(problem_data['date'], problem_data['1'], c='green', marker='o',alpha=0.5, label = "3#电芯") # plt.scatter(problem_data['date'], problem_data['2'], c='red',marker='o', alpha=0.5, label = "4#电芯") # plt.scatter(problem_data['date'], problem_data['3'], c='black',marker='o',alpha=0.5, label = "5#电芯") # plt.scatter(problem_data['date'], problem_data['4'], c='green', marker='o',alpha=0.5, label = "6#电芯") # plt.scatter(problem_data['date'], problem_data['5'], c='red',marker='o', alpha=0.5, label = "7#电芯") # plt.scatter(problem_data['date'], problem_data['6'], c='black',marker='o',alpha=0.5, label = "8#电芯") # plt.scatter(problem_data['date'], problem_data['7'], c='green', marker='o',alpha=0.5, label = "9#电芯") # plt.xlabel("时间", fontsize=15) # plt.ylabel("电压排序", fontsize=15) # title = problem_data['device_id'][0] # plt.title(title, fontsize=20) # plt.savefig(r'D:\Work\Code_write\data_analyze_platform\test\lzx\01Qixiang\01电压排序\01算法\DBDownload\\'+title+'.png', dpi=300) # plt.show() #print(problem_data) end=time.time() print('--------------计算时间：------------') print(end-start) # print(df_soh)