123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426 |
- import pandas as pd
- import pymysql
- from sqlalchemy import create_engine
- import datetime
- from sqlalchemy.orm import sessionmaker
- import pdb
- def getNextSoc(start_soc):
- '''输入当前的soc,寻找目标soc函数'''
- if start_soc>80:
- next_soc=80
- elif start_soc>60:
- next_soc=60
- elif start_soc>40:
- next_soc=40
- elif start_soc>20:
- next_soc=20
- else:
- next_soc=5
- return next_soc
- def updtSnFct(sn_factor_df,end_soc,delta_range,range_soc):
- '''输入当前的soc区间段,里程变量量,soc变化量,输出新的df
- sn_factor_df为dataframe,delta_range单位为km,range_soc单位为km/persoc'''
- if end_soc==80:
- updtFctByCol(sn_factor_df,'a0',delta_range,range_soc)
- elif end_soc==60:
- updtFctByCol(sn_factor_df,'a1',delta_range,range_soc)
- elif end_soc==40:
- updtFctByCol(sn_factor_df,'a2',delta_range,range_soc)
- elif end_soc==20:
- updtFctByCol(sn_factor_df,'a3',delta_range,range_soc)
- elif end_soc<20:
- updtFctByCol(sn_factor_df,'a4',delta_range,range_soc)
- return sn_factor_df
- def updtFctByCol(sn_factor_df,colmun_name,delta_range,range_soc):
- '''更新制定列的factor,sn_factor_df为dataframe,新的系数更新到第一行。delta_range单位为km,
- range_soc单位为km/persoc,默认按照100km更新续驶里程权重'''
- range_soc_old=sn_factor_df.loc[0,colmun_name]
- debounce_range=100
- new_factor=range_soc*((delta_range)/debounce_range)+range_soc_old*(1-delta_range/debounce_range)
-
- sn_factor_df.loc[1,colmun_name]=new_factor
- return sn_factor_df
- def updtTodayFct(factor_input,sn_day_df):
- '''更新今日的Factor***'''
- sn_factor_df_last=factor_input
- start_soc=sn_day_df.loc[0,'soc']
- next_soc=getNextSoc(start_soc)
- start_range=sn_day_df.loc[0,'vehodo']
- sn=sn_day_df.loc[0,'name']
- for index in range(len(sn_day_df)-1):
-
- index_soc=sn_day_df.loc[index,'soc']
- next_index_soc=sn_day_df.loc[index+1,'soc']
- if (index_soc>=next_soc)&(next_index_soc<next_soc):
-
- delta_soc_tonext=start_soc-next_soc
- delta_range_tonext=sn_day_df.loc[index,'vehodo']-start_range
- delta_range_tonext_km=delta_range_tonext/1000
- range_soc_tonext=(delta_range_tonext_km)/delta_soc_tonext
-
-
- if (delta_range_tonext_km>1)&(delta_range_tonext_km<5*delta_soc_tonext):
-
- sn_factor_df_last=updtSnFct(sn_factor_df_last,next_soc,delta_range_tonext_km,range_soc_tonext)
-
- start_soc=next_index_soc
- next_soc=getNextSoc(start_soc)
- start_range=sn_day_df.loc[index+1,'vehodo']
- return sn_factor_df_last
- def snDayDfPreProcess(sn_day_df):
- '''预处理,判断是否在dirvemode,获取drivemode条件下的累计行驶距离。
- 增加delta_soc列,drive_flg列,vehodo列'''
- sn_day_df=sn_day_df.reset_index(drop=True)
-
- for index in range(len(sn_day_df)):
- if index==0:
- sn_day_df.loc[index,'delta_soc']=0
- else:
- sn_day_df.loc[index,'delta_soc']=sn_day_df.loc[index,'soc']-sn_day_df.loc[index-1,'soc']
-
- drive_flg=False
- accum_distance=0
- for index in range(len(sn_day_df)):
- if index==0:
- sn_day_df.loc[index,'drive_status']=drive_flg
- sn_day_df.loc[index,'vehodo']=0
- else:
- if (sn_day_df.loc[index,'delta_soc']<-0.1)|\
- ((sn_day_df.loc[index,'delta_soc']<=0)&(sn_day_df.loc[index,'distance']>500)):
- drive_flg=True
- elif sn_day_df.loc[index,'delta_soc']>0.1:
- drive_flg=False
- accum_distance=0
- sn_day_df.loc[index,'drive_flg']=drive_flg
- accum_distance+=sn_day_df.loc[index,'distance']
- sn_day_df.loc[index,'vehodo']=accum_distance
-
- sn_day_drive_df=sn_day_df.loc[sn_day_df['drive_flg']==True,:]
-
- sn_day_drive_df=sn_day_drive_df.reset_index(drop=True)
-
- return sn_day_drive_df
- def updtAllSnFct(start_date,end_date, db_engine, db_local, db_qx, sn_table_name='tb_sn_factor'):
- '''计算开始时间到结束时间的,所有sn的factor'''
- start_date_datetime=datetime.datetime.strptime(start_date,'%Y-%m-%d')
- end_date_datetime=datetime.datetime.strptime(end_date,'%Y-%m-%d')
- delta_day=(end_date_datetime-start_date_datetime).days
- i=1
- while i<=delta_day:
- end_date=(start_date_datetime+datetime.timedelta(days=i)).strftime("%Y-%m-%d")
- updtAllSnTodayFct(start_date,end_date, db_engine, db_local, db_qx, sn_table_name)
-
- start_date=end_date
- i+=1
- def updtAllSnTodayFct(start_date,end_date, db_engine, db_local, db_qx, sn_table_name):
- ''''更新今天所有sn的factorx信息,start_date和end_date相隔一天。此处还可优化'''
-
-
-
-
-
-
-
-
- start_date_str="'"+start_date+"'"
- end_date_str="'"+end_date+"'"
- sql_cmd="select * from drive_info where time between "+start_date_str+" and "+end_date_str+" and distance!=0;"
- range_soc_df = pd.read_sql(sql_cmd, db_qx)
-
- today_sn_list=range_soc_df['name'].unique().tolist()
-
- today_sn_fct_df=pd.DataFrame([],columns=['sn','date','a0','a1','a2','a3','a4'])
- for sn in today_sn_list:
-
- sn_str="'"+sn+"'"
- update_today_factor_flg=True
- sql_cmd3="select sn,date,a0,a1,a2,a3,a4 from {} where date=".format(sn_table_name)+start_date_str+" and sn="+sn_str
- factor_today_df=pd.read_sql(sql_cmd3, db_local)
- if len(factor_today_df)>=1:
-
- update_today_factor_flg=False
- sql_cmd2="select sn,date,a0,a1,a2,a3,a4 from {} where date<".format(sn_table_name)+start_date_str+" and sn="+sn_str
-
- factor_df=pd.read_sql(sql_cmd2, db_local)
-
- factor_df=factor_df.drop_duplicates(subset=['sn','date'],keep='first')
- if len(factor_df)==0:
-
- start_date_datetime=datetime.datetime.strptime(start_date,'%Y-%m-%d')
- yesterday=(start_date_datetime+datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
-
- factor_df=pd.DataFrame({'sn':sn,'date':yesterday,'a0':[1],'a1':[1],'a2':[1],'a3':[1],'a4':[1]})
- sn_factor_df=factor_df.loc[factor_df['sn']==sn,:]
- sn_factor_df=sn_factor_df.sort_values(by='date',ascending='True')
- sn_factor_df_last=sn_factor_df.tail(1).copy()
- sn_factor_df_last=sn_factor_df_last.append(sn_factor_df_last)
- sn_factor_df_last=sn_factor_df_last.reset_index(drop=True)
- sn_factor_df_last.loc[1,'date']=start_date
-
- condition_sn=(range_soc_df['name']==sn)
- sn_day_df=range_soc_df.loc[condition_sn,:].copy()
- sn_day_df=sn_day_df.reset_index(drop=True)
-
- if len(sn_day_df)>=2:
-
- sn_day_df=snDayDfPreProcess(sn_day_df)
-
- day_start_time=datetime.datetime.strptime(start_date,'%Y-%m-%d')
- day_morning_time=day_start_time+datetime.timedelta(hours=5)
- morning_time_str=day_morning_time.strftime('%Y-%m-%d %H:%M:%S')
- sn_day_df=sn_day_df.loc[sn_day_df['time']>morning_time_str,:]
- sn_day_df=sn_day_df.reset_index(drop=True)
- if len(sn_day_df)>=2:
- sn_factor_df_new=updtTodayFct(sn_factor_df_last,sn_day_df)
- if (len(sn_factor_df_new)>=2)&(update_today_factor_flg):
- today_sn_fct_df=today_sn_fct_df.append(sn_factor_df_new.loc[1,:])
-
-
- if len(today_sn_fct_df)>=1:
- today_sn_fct_df.to_sql(sn_table_name,con=db_engine,chunksize=10000,if_exists='append',index=False)
- def updtOneSnFct(sn,start_date,end_date,db_engine, db_local, db_qx, sn_table_name='tb_sn_factor'):
- '''计算开始时间到结束时间的,一个sn的所有factor。
- 重复多次调用,updtOneSnTodayFct。
- '''
- start_date_datetime=datetime.datetime.strptime(start_date,'%Y-%m-%d')
- end_date_datetime=datetime.datetime.strptime(end_date,'%Y-%m-%d')
- delta_day=(end_date_datetime-start_date_datetime).days
- i=1
- while i<=delta_day:
- end_date=(start_date_datetime+datetime.timedelta(days=i)).strftime("%Y-%m-%d")
-
- updtOneSnTodayFct(sn,start_date,end_date,db_engine, db_local, db_qx, sn_table_name)
- start_date=end_date
- i+=1
- def updtOneSnTodayFct(sn,start_date,end_date,db_engine, db_local, db_qx, sn_table_name):
- '''更新一个sn,一天的factor。'''
-
-
-
-
-
-
-
-
- start_date_str="'"+start_date+"'"
- end_date_str="'"+end_date+"'"
- sn_str="'"+sn+"'"
- sql_cmd="select * from drive_info where time between "+start_date_str+" and "+end_date_str+\
- " and distance!=0 and name="+sn_str
- range_soc_df = pd.read_sql(sql_cmd, db_qx)
- if len(range_soc_df)>0:
-
- today_sn_list=range_soc_df['name'].unique().tolist()
-
- today_sn_fct_df=pd.DataFrame([],columns=['sn','date','a0','a1','a2','a3','a4'])
- for sn in today_sn_list:
-
- sn_str="'"+sn+"'"
- update_today_factor_flg=True
- sql_cmd3="select sn,date,a0,a1,a2,a3,a4 from {} where date=".format(sn_table_name)+start_date_str+" and sn="+sn_str
- factor_today_df=pd.read_sql(sql_cmd3, db_local)
- if len(factor_today_df)>=1:
-
- update_today_factor_flg=False
- sql_cmd2="select sn,date,a0,a1,a2,a3,a4 from {} where date<=".format(sn_table_name)+start_date_str+" and sn="+sn_str
- factor_df=pd.read_sql(sql_cmd2, db_local)
-
- factor_df=factor_df.drop_duplicates(subset=['sn','date'],keep='first')
-
- if len(factor_df)==0:
-
- start_date_datetime=datetime.datetime.strptime(start_date,'%Y-%m-%d')
- yesterday=(start_date_datetime+datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
- factor_df=pd.DataFrame({'sn':sn,'date':yesterday,'a0':[1],'a1':[1],'a2':[1],'a3':[1],'a4':[1]})
- today_sn_fct_df=today_sn_fct_df.append(factor_df.loc[0,:])
- sn_factor_df=factor_df.loc[factor_df['sn']==sn,:]
- sn_factor_df=sn_factor_df.sort_values(by='date',ascending='True')
- sn_factor_df_last=sn_factor_df.tail(1).copy()
- sn_factor_df_last=sn_factor_df_last.append(sn_factor_df_last)
- sn_factor_df_last=sn_factor_df_last.reset_index(drop=True)
- sn_factor_df_last.loc[1,'date']=start_date
-
- condition_sn=(range_soc_df['name']==sn)
- sn_day_df=range_soc_df.loc[condition_sn,:].copy()
- sn_day_df=sn_day_df.reset_index(drop=True)
-
- if len(sn_day_df)>=2:
-
- sn_day_df=snDayDfPreProcess(sn_day_df)
-
- day_start_time=datetime.datetime.strptime(start_date,'%Y-%m-%d')
- day_morning_time=day_start_time+datetime.timedelta(hours=5)
- morning_time_str=day_morning_time.strftime('%Y-%m-%d %H:%M:%S')
- sn_day_df=sn_day_df.loc[sn_day_df['time']>morning_time_str,:]
- sn_day_df=sn_day_df.reset_index(drop=True)
- if len(sn_day_df)>=2:
- sn_factor_df_new=updtTodayFct(sn_factor_df_last,sn_day_df)
- if (len(sn_factor_df_new)>=2)&(update_today_factor_flg):
- today_sn_fct_df=today_sn_fct_df.append(sn_factor_df_new.loc[1,:])
-
-
- if len(today_sn_fct_df)>=1:
- today_sn_fct_df.to_sql(sn_table_name,con=db_engine,chunksize=10000,if_exists='append',index=False)
-
- return sn_factor_df_new
- def updtNewestFctTb(current_time, db_local, sn_table_name='tb_sn_factor'):
- '''更新tb_sn_factor_newest,只保留最新日期的factor。
- 从tb_sn_factor中,筛选最新的日期。
- 函数每天运行一次,从tb_sn_factor中筛选最新日期的factor。'''
- current_time=current_time
- current_time_str=current_time.strftime('%Y-%m-%d %H:%M:%S')
- current_time_str="'"+current_time_str+"'"
- sql_cmd_4="select sn,date,a0,a1,a2,a3,a4 from {} where date<".format(sn_table_name)+current_time_str
- factor_all_df = pd.read_sql(sql_cmd_4, db_local)
-
- sn_list=factor_all_df['sn'].unique().tolist()
- newest_sn_fct_df=pd.DataFrame([],columns=['sn','date','a0','a1','a2','a3','a4'])
- for sn in sn_list:
- condition_sn=(factor_all_df['sn']==sn)
- factor_pick_df=factor_all_df.loc[condition_sn,:]
- factor_pick_df=factor_pick_df.sort_values(by='date')
- factor_last_df=factor_pick_df.tail(1)
- newest_sn_fct_df=newest_sn_fct_df.append(factor_last_df)
-
-
-
-
-
- return newest_sn_fct_df
- def calDistFromFct(input_df):
- '''根据sn-time-soc-a0-a1-a2-a3-a4,使用factor正向计算计算VehElecRng。'''
- row_df=input_df.copy()
- soc=row_df['soc']
- factor=[]
- factor.append(row_df['a4'])
- factor.append(row_df['a3'])
- factor.append(row_df['a2'])
- factor.append(row_df['a1'])
- factor.append(row_df['a0'])
- gap=20
- yushu=soc%gap
- zhengshu=soc//gap
- i=0
- range=0
- while i<zhengshu:
- dur_factor=factor[i]
- range+=dur_factor*gap
- i=i+1
- if yushu>0.01:
- range=range+yushu*factor[zhengshu]
- row_df['vehelecrng']=range
- return row_df
- def updtVehElecRng(db_qx, db_local, sn_newest_table_name='tb_sn_factor_newest', input_time='2021-07-29 12:01:00'):
- '''更新续驶里程,到tb_sn_factor_soc_range。
- 部署时设置每5min更新一次。
- '''
-
-
- current_time_raw=input_time
- current_time=datetime.datetime.strptime(current_time_raw,'%Y-%m-%d %H:%M:%S')
-
- before6min_time_str=(current_time+datetime.timedelta(minutes=-4,seconds=-59)).strftime('%Y-%m-%d %H:%M:%S')
- before6min_time_str="'"+before6min_time_str+"'"
- current_time_str=current_time.strftime('%Y-%m-%d %H:%M:%S')
- current_time_str="'"+current_time_str+"'"
-
- <<<<<<< HEAD
- sql_cmd="select name,time,soc from drive_info where time between "+before6min_time_str+" and "+current_time_str
- =======
- sql_cmd="select name,time,soc from drive_info where time between " + before6min_time_str + " and " + current_time_str
- >>>>>>> master
-
- range_soc_df = pd.read_sql(sql_cmd, db_qx)
- range_soc_df.rename(columns={'name':'sn'},inplace=True)
-
- sql_cmd_1="select sn,a0,a1,a2,a3,a4 from {}".format(sn_newest_table_name)
-
- sn_factor_newest_df_raw = pd.read_sql(sql_cmd_1, db_local)
-
- sn_soc_factor_df=pd.merge(range_soc_df,sn_factor_newest_df_raw,how='left',on='sn')
- sn_soc_factor_df.fillna(1,inplace=True)
-
-
-
- sn_soc_factor_range_df=pd.DataFrame([],columns=['sn','time','soc','a0','a1','a2','a3','a4','vehelecrng'])
- for index in sn_soc_factor_df.index.tolist():
- input_df=sn_soc_factor_df.loc[index,:]
- sn_soc_factor_range_row=calDistFromFct(input_df)
- sn_soc_factor_range_df=sn_soc_factor_range_df.append(sn_soc_factor_range_row)
-
-
-
- return sn_soc_factor_range_df
|