# 获取数据 from LIB.BACKEND import DBManager import os import pandas as pd import numpy as np import datetime # import matplotlib.pyplot as plt #参数输入 Capacity = 41 PackFullChrgVolt=69.99 CellFullChrgVolt=3.5 CellVoltNums=17 CellTempNums=4 FullChrgSoc=98 PeakSoc=57 # #40Ah-OCV LookTab_SOC = [0, 3.534883489, 8.358178409, 13.18141871, 18.00471528, 22.82796155, 27.65123833, 32.47444668, 37.29772717, 42.12099502, 46.94423182, 51.76744813, 56.59070685, 61.4139927, 66.23719857, 71.0604667, 75.88373853, 80.70702266, 85.5302705, 90.35352009, 95.17676458, 100] LookTab_OCV = [3.3159, 3.4384, 3.4774, 3.5156, 3.5478, 3.5748, 3.6058, 3.6238, 3.638, 3.6535, 3.6715, 3.6951, 3.7279, 3.7757, 3.8126, 3.8529, 3.8969, 3.9446, 3.9946, 4.0491, 4.109, 4.183] # #55Ah-OCV # LookTab_SOC = [0.00, 2.40, 6.38, 10.37, 14.35, 18.33, 22.32, 26.30, 30.28, 35.26, 40.24, 45.22, 50.20, 54.19, 58.17, 60.16, 65.14, 70.12, 75.10, 80.08, 84.06, 88.05, 92.03, 96.02, 100.00] # LookTab_OCV = [2.7151, 3.0298, 3.1935, 3.2009, 3.2167, 3.2393, 3.2561, 3.2703, 3.2843, 3.2871, 3.2874, 3.2868, 3.2896, 3.2917, 3.2967, 3.3128, 3.3283, 3.3286, 3.3287, 3.3288, 3.3289, 3.3296, 3.3302, 3.3314, 3.3429] #参数初始化 Soh3=[] Time3=[] Bms_Soh3=[] Soh_Err3=[] sn_list=[] #获取数据时间段 now_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') now_time=datetime.datetime.strptime(now_time,'%Y-%m-%d %H:%M:%S') start_time=now_time-datetime.timedelta(days=31) end_time=str(now_time) strat_time=str(start_time) #输入一个含有‘SN号’的xlsx SNdata = pd.read_excel('骑享资产梳理-20210621.xlsx', sheet_name='6040骑享') SNnums=SNdata['SN号'] for k in range(len(SNnums)): SNnum=str(SNnums[k]) sn = SNnum st = strat_time et = end_time dbManager = DBManager.DBManager() df_data = dbManager.get_data(sn=sn, start_time=st, end_time=et, data_groups=['bms']) data = df_data['bms'] # print(data) packcrnt=data['总电流[A]'] packvolt=data['总电压[V]'] SOC=data['SOC[%]'] SOH=data['SOH[%]'] bmsstat=data['充电状态'] time= pd.to_datetime(data['时间戳'], format='%Y-%m-%d %H:%M:%S') #第一步：筛选充电数据 if len(packcrnt)>100: ChgStart=[] ChgEnd=[] for i in range(3, len(time) - 3): if i==3 and bmsstat[i]==2 and bmsstat[i+1]==2 and bmsstat[i+2]==2: ChgStart.append(i) elif bmsstat[i-2]!=2 and bmsstat[i-1]!=2 and bmsstat[i]==2: ChgStart.append(i) elif bmsstat[i-1]==2 and bmsstat[i]!=2 and bmsstat[i+1]!=2: ChgEnd.append(i-1) elif i == (len(time) - 4) and bmsstat[len(bmsstat)-1] == 2 and bmsstat[len(bmsstat)-2] == 2: ChgEnd.append(len(time)-2) #第二步：筛选充电起始Soc<45% & SOC>85%，电芯温度>5℃ ChgStartValid1=[] ChgEndValid1=[] ChgStartValid2=[] ChgEndValid2=[] StandingNum=[] for i in range(min(len(ChgStart),len(ChgEnd))): #获取最小温度值 celltemp = [] for j in range(1, CellTempNums+1): s = str(j) temp = data['单体温度' + s] celltemp.append(temp[ChgEnd[i]]) #去除电流0点 for k in range(ChgStart[i],ChgEnd[i]): if packcrnt[k]<-0.5 and packcrnt[k+1]>-0.5 and packcrnt[k+2]>-0.5 and packcrnt[k+3]>-0.5: ChgEnd[i]=k #计算最大packvolt if len(packvolt[ChgStart[i]:ChgEnd[i]])>0: packvoltMAX=max(packvolt[ChgStart[i]:ChgEnd[i]]) #筛选满足2点法计算的数据 StandingTime=0 StandingTime1=0 StandingTime2=0 if SOC[ChgEnd[i]]>85 and SOC[ChgStart[i]]<45 and min(celltemp)>5: for m in range(min(len(packcrnt)-ChgEnd[i]-2,ChgStart[i]-2)): if abs(packcrnt[ChgStart[i] - m - 1]) < 0.1: StandingTime = StandingTime + (time[ChgStart[i] - m] - time[ChgStart[i] - m - 1]).total_seconds() if abs(packcrnt[ChgEnd[i] + m + 1]) < 0.1: StandingTime1 = StandingTime1 + (time[ChgEnd[i] + m + 1] - time[ChgEnd[i] + m]).total_seconds() if StandingTime > 900 and StandingTime1>900 and ((time[ChgEnd[i]]-time[ChgStart[i]]).total_seconds())/(ChgEnd[i]-ChgStart[i])<60: #筛选静置时间>15min且慢充过程丢失数据少 ChgStartValid1.append(ChgStart[i]) ChgEndValid1.append(ChgEnd[i]) StandingNum.append(m) break if abs(packcrnt[ChgStart[i] - m - 2])>0.5 and abs(packcrnt[ChgEnd[i] + m + 2])>0.5: break # 计算soh Soh1=[] Soh2=[] Time1=[] Bms_Soh1=[] Soh_Err1=[] sn_list1=[] #两点法计算Soh if len(ChgStartValid1)>0: for i in range(len(ChgStartValid1)): #计算Ah Ah=0 for j in range(ChgStartValid1[i],ChgEndValid1[i]): Step=(time[j+1]-time[j]).total_seconds() Ah=Ah-packcrnt[j+1]*Step/3600 #计算每个电芯的Soh for j in range(1, CellVoltNums+1): s = str(j) cellvolt = data['单体电压' + s]/1000 OCVStart=cellvolt[ChgStartValid1[i]-2] OCVEnd=cellvolt[ChgEndValid1[i]+StandingNum[i]] #soh Ocv_Soc1=np.interp(OCVStart,LookTab_OCV,LookTab_SOC) Ocv_Soc2=np.interp(OCVEnd,LookTab_OCV,LookTab_SOC) Soh2.append(Ah*100/((Ocv_Soc2-Ocv_Soc1)*0.01*Capacity)) Soh1.append(np.mean(Soh2)) Bms_Soh1.append(SOH[ChgStartValid1[i]]) Soh_Err1.append(Bms_Soh1[-1]-Soh1[-1]) Time1.append(time[ChgStartValid1[i]]) sn_list1.append(SNnum) # Soh3.append(np.mean(Soh1)) # Bms_Soh3.append(np.mean(Bms_Soh1)) # Soh_Err3.append(np.mean(Soh_Err1)) # Time3.append(time[ChgStartValid1[-1]]) # sn_list.append(SNnum) #第四步：将数据存入Excel result_soh2={'时间': Time1, 'SN号': sn_list1, 'BMS_SOH': Bms_Soh1, 'SOH': Soh1, 'SOH误差': Soh_Err1} Result_Soh2=pd.DataFrame(result_soh2) Result_Soh2.to_csv('BMS_SOH_'+SNnum+'.csv',encoding='GB18030') # result_soh1={'时间': Time3, # 'SN号':sn_list, # 'BMS_SOH': Bms_Soh3, # 'SOH': Soh3, # 'SOH误差': Soh_Err3} # Result_Soh1=pd.DataFrame(result_soh1) # print(Result_Soh1) # Result_Soh1.to_csv('BMS_SOH_'+'6040'+'.csv',encoding='GB18030')