data_analyze_platform/LIB/MIDDLE/parameters.py

# -*- coding: utf-8 -*-
"""
Created on Wed Dec 30 14:38:35 2020

@author: striv
"""
# import getdata
import time
import datetime
import numpy as np
import pandas as pd
import os
import matplotlib.pyplot as plt
import tools.tools
import importlib
importlib.reload(tools.tools)
import math
from collections import Counter
from sklearn.cluster import DBSCAN


def getcellsoc(Chrg_data_static_cellu,soc_ocv_table):
    pass
    x = soc_ocv_table.iloc[:,0]
    y = soc_ocv_table.iloc[:,1]*1000
    xnew=np.linspace(0,105,1000)
    # print(x,y)
    z1 = np.polyfit(x,y,11)
    ynew = np.polyval(z1,xnew)
    data = [xnew,ynew]
    soc_ocv_ply = pd.DataFrame(data,index=('SOC','OCV')).T
    cellsoc = pd.DataFrame()
    cellsoc_list=[]
    temp = []
    for i_num,i in enumerate(Chrg_data_static_cellu):
        i = i /1000.0
        temp = soc_ocv_table.iloc[:,1]
        ocv_temp = soc_ocv_table.iloc[:,1].tolist()
        ocv_temp.append(i)
        ocv_temp.sort()
        ocv_index = ocv_temp.index(i)
        if ocv_index == 0:
            value = 0
        elif ocv_index == len(ocv_temp)-1:
            value = 100
        else:
            temp1=ocv_temp[ocv_index-1]
            temp2=ocv_temp[ocv_index+1]
            j_temp1 = temp.index[temp==temp1][0]
            j_temp2 = temp.index[temp==temp2][0]
            value = (soc_ocv_table.iloc[j_temp1,0]*(temp2-i) + soc_ocv_table.iloc[j_temp2,0]*(i-temp1))/min(1,(temp2 - temp1))
        cellsoc.loc[0,'cell'+str(i_num+1)] = min([100,value])
        cellsoc_list.append(cellsoc.loc[0,'cell'+str(i_num+1)])
        temp = []
    return cellsoc,cellsoc_list


def get_date(df):
    time = df.loc[0, '时间戳']
    date = time.strftime('%Y-%m-%d %H:%M:%S')
    date = date[0:10]
    return date

def get_daily_odo_and_speed(df,df_gps):
    data_number_list = sorted(list(set(df[df['data_status'].isin(['drive' or 'stand'])]['data_split_by_status'])))
    odo_sum = 0
    res_single = pd.DataFrame(columns=['speed'])
    max_speed_single = []
    max_speed = -1
    for data_number in data_number_list[0:]:
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        # print('bmsstart={},bmsend={}'.format(df_sel_bms.loc[0, '时间戳'], df_sel_bms.loc[len(df_sel_bms)-1, '时间戳']))
        df_sel_gps = df_gps[(df_gps['时间戳']>df_sel_bms.loc[0,'时间戳']) & (df_gps['时间戳']<df_sel_bms.loc[len(df_sel_bms)-1,'时间戳'])]
        df_sel_gps = df_sel_gps.reset_index(drop=True)

        if len(df_sel_gps) >= 2:

            odo, avg_speed, odo_list, speed_list, invalid_flag = tools.tools.cal_odo_speed(df_sel_gps['纬度'], df_sel_gps['经度'],
                                                                                           df_sel_gps['时间戳'])
            # print('gpsstart={},gpsend={},odo={}'.format(df_sel_gps.loc[0, '时间戳'], df_sel_gps.loc[len(df_sel_gps) - 1, '时间戳'],odo))
            # print(df_sel_gps['时间戳'])
            odo_sum = odo_sum + odo
            res_single = res_single.append({'speed':avg_speed},ignore_index = True)
            max_speed_single.append(np.max(speed_list))
    avr_speed = np.mean(res_single['speed'])
    if len(max_speed_single) > 0:
        max_speed = np.max(max_speed_single)
    return odo_sum, avr_speed, max_speed


def get_daily_odo(df,df_gps):
    odo_sum = 0
    odo_plus = 0
    soc_sum = 0
    data_number_list = sorted(list(set(df[df['data_status'].isin(['drive' or 'stand' or 'charge' or 'None'])]['data_split_by_status'])))
    data_number_list = sorted(list(set(df['data_split_by_status'])))
    for i in range(0, len(data_number_list)):
    # for data_number in data_number_list[0:]:
        data_number = data_number_list[i]
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        # print(df_sel_bms)
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        df_sel_gps = df_gps[
            (df_gps['时间戳'] >= df_sel_bms.loc[0, '时间戳']) & (df_gps['时间戳'] <= df_sel_bms.loc[len(df_sel_bms) - 1, '时间戳'])]
        df_sel_gps = df_sel_gps.reset_index(drop=True)
        if len(df_sel_gps) >= 2:
            odo, avg_speed, odo_list, speed_list, invalid_flag = tools.tools.cal_odo_speed(df_sel_gps['纬度'],
                                                                                           df_sel_gps['经度'],
                                                                                           df_sel_gps['时间戳'])
            soc = abs(df_sel_bms.loc[len(df_sel_bms) - 1, 'SOC[%]'] - df_sel_bms.loc[0, 'SOC[%]'])
        if len(df_sel_gps) >= 2 and len(df_sel_bms) >= 2:
            last_df = df_sel_bms[
            (df_sel_bms['时间戳'] >= df_sel_gps.loc[0, '时间戳']) & (
                        df_sel_bms['时间戳'] <= df_sel_gps.loc[len(df_sel_gps) - 1, '时间戳'])]
            last_df = last_df.reset_index(drop=True)
            last_soc = last_df.loc[len(last_df) - 1, 'SOC[%]']
            last_time = last_df.loc[len(last_df) - 1, '时间戳']
        else:
            last_soc = -1
        odo_sum = odo_sum + odo
        soc_sum = soc_sum + soc
        if i < len(data_number_list) - 1:
            data_number = data_number_list[i + 1]
            df_sel_bms = df[df['data_split_by_status'] == data_number]
            df_sel_bms = df_sel_bms.reset_index(drop=True)
            df_sel_gps = df_gps[
        (df_gps['时间戳'] >= df_sel_bms.loc[0, '时间戳']) & (df_gps['时间戳'] <= df_sel_bms.loc[len(df_sel_bms) - 1, '时间戳'])]
            df_sel_gps = df_sel_gps.reset_index(drop=True)
            if len(df_sel_gps) >= 2 and len(df_sel_bms) >= 2:
                cur_df = df_sel_bms[
                (df_sel_bms['时间戳'] >= df_sel_gps.loc[0, '时间戳']) & (
                            df_sel_bms['时间戳'] <= df_sel_gps.loc[len(df_sel_gps) - 1, '时间戳'])]
                cur_df = cur_df.reset_index(drop=True)
                if len(cur_df)>0:
                    # print('cur={}'.format(cur_df))
                    cur_soc = cur_df.loc[0, 'SOC[%]']
                    cur_time = cur_df.loc[0, '时间戳']
                else:
                    cur_soc = -1
            else:
                cur_soc = -1
            print(last_time, cur_time, last_soc, cur_soc)
            if abs(cur_soc - last_soc) > 1 and soc_sum > 1 and cur_soc >= 0 and last_soc >= 0:
                odo_plus = odo_plus + odo_sum/soc_sum*abs(cur_soc - last_soc)
    return odo_sum+odo_plus, odo_sum, odo_plus

def sta_one_drive_cycle(df_bms, df_gps, prepro_record, time_window=3600, step=3600, start_time="00:00:00"):
    st = datetime.datetime.strptime(str(df_bms.loc[0, '时间戳'])[0:10] + ' ' + start_time, '%Y-%m-%d %H:%M:%S')
    et = st + datetime.timedelta(seconds=time_window)
    time_list = []
    driveT_list = []
    driveSoc_list = []
    driveOdo_list = []
    driveOdoRevise_list = []
    while (et < df_bms.loc[len(df_bms) - 1, '时间戳']):
        df_t = df_bms[(df_bms['时间戳'] > st) & (df_bms['时间戳'] < et)]
        df_t = df_t.reset_index(drop=True)

        driveT = 0
        driveSoc = 0
        driveOdo = 0
        driveOdoRevise = 0
        if not df_t.empty:
            deltaT = (df_t.loc[len(df_t) - 1, '时间戳'] - df_t.loc[0, '时间戳']).total_seconds()
            df_drive = df_t[df_t['data_status'] == 'drive']
            df_drive = df_drive.reset_index(drop=True)
            data_number_list = sorted(list(set(df_drive['data_split_by_status'])))

            for data_number in data_number_list[:]:
                df_d = df_drive[df_drive['data_split_by_status'] == data_number]
                df_d = df_d.reset_index(drop=True)
                driveT = driveT + (df_d.loc[len(df_d) - 1, '时间戳'] - df_d.loc[0, '时间戳']).total_seconds()
                driveSoc = driveSoc + (df_d.loc[0, 'SOC[%]'] - df_d.loc[len(df_d) - 1, 'SOC[%]'])
                if df_d.loc[0, 'gps_rely'] == 1 and driveOdo != None:
                    df_sel_gps = df_gps[
                        (df_gps['时间戳'] >= df_d.loc[0, '时间戳']) & (df_gps['时间戳'] <= df_d.loc[len(df_d) - 1, '时间戳'])]
                    df_sel_gps = df_sel_gps.reset_index(drop=True)
                    if len(df_sel_gps) > 0:
                        driveOdo = driveOdo + (df_sel_gps.loc[len(df_sel_gps) - 1, 'odo'] - df_sel_gps.loc[0, 'odo'])

                    else:
                        driveOdo = None
                else:
                    driveOdo = None
        time_list.append(st)
        driveT_list.append(driveT)
        driveSoc_list.append(driveSoc)
        driveOdo_list.append(driveOdo)
        st = st + datetime.timedelta(seconds=step)
        et = st + datetime.timedelta(seconds=time_window)
        print(driveOdo_list)
    if prepro_record['drive'] < 0.8 and sum(driveSoc_list) > 0:
        # 计算能耗
        sum_odo = 0
        sum_soc = 0
        for i, odo in enumerate(driveOdo_list):
            if odo != 0 and not pd.isnull(odo):
                sum_odo += odo
                sum_soc += driveSoc_list[i]
        if sum_soc > 0:
            ene_consump = sum_odo / sum_soc
        else:
            ene_consump = -1
        st = datetime.datetime.strptime(str(df_bms.loc[0, '时间戳'])[0:10] + ' ' + start_time, '%Y-%m-%d %H:%M:%S')
        et = st + datetime.timedelta(seconds=time_window)
        driveOdoRevise_list = []
        while (et < df_bms.loc[len(df_bms) - 1, '时间戳']):
            df_t = df_bms[(df_bms['时间戳'] > st) & (df_bms['时间戳'] < et)]
            df_t = df_t.reset_index(drop=True)

            driveOdoRevise = 0
            if not df_t.empty:
                deltaT = (df_t.loc[len(df_t) - 1, '时间戳'] - df_t.loc[0, '时间戳']).total_seconds()
                df_drive = df_t[df_t['data_status'] == 'drive']
                df_drive = df_drive.reset_index(drop=True)
                data_number_list = sorted(list(set(df_drive['data_split_by_status'])))

                for data_number in data_number_list[:]:
                    df_d = df_drive[df_drive['data_split_by_status'] == data_number]
                    df_d = df_d.reset_index(drop=True)

                    if df_d.loc[0, 'gps_rely'] == 1 and driveOdo != None:
                        df_sel_gps = df_gps[
                            (df_gps['时间戳'] >= df_d.loc[0, '时间戳']) & (df_gps['时间戳'] <= df_d.loc[len(df_d) - 1, '时间戳'])]
                        df_sel_gps = df_sel_gps.reset_index(drop=True)
                        if len(df_sel_gps) > 0:
                            driveOdoRevise = driveOdoRevise + (
                                        df_sel_gps.loc[len(df_sel_gps) - 1, 'odo'] - df_sel_gps.loc[0, 'odo'])
                        else:
                            driveOdoRevise = driveOdoRevise + (
                                        df_d.loc[0, 'SOC[%]'] - df_d.loc[len(df_d) - 1, 'SOC[%]']) * ene_consump
                    else:
                        driveOdoRevise = driveOdoRevise + (
                                    df_d.loc[0, 'SOC[%]'] - df_d.loc[len(df_d) - 1, 'SOC[%]']) * ene_consump
            driveOdoRevise_list.append(driveOdoRevise)
            st = st + datetime.timedelta(seconds=step)
            et = st + datetime.timedelta(seconds=time_window)
    else:
        driveOdoRevise_list = [None] * len(driveSoc_list)
    df_res = pd.DataFrame(
        {'time': time_list, 'driveT': driveT_list, 'driveSoc': driveSoc_list, 'driveOdo': driveOdo_list,
         'driveOdoRevise': driveOdoRevise_list})
    return df_res


def get_daily_capacity_and_energy(df, cap):
    data_number_list = sorted(list(set(df[df['data_status'].isin(['drive'])]['data_split_by_status'])))
    energy_sum = 0
    capacity_sum = 0
    for data_number in data_number_list[0:]:
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        capacity = (df_sel_bms.loc[0, 'SOC[%]'] - df_sel_bms.loc[len(df_sel_bms)-1,'SOC[%]']) * np.mean(df_sel_bms['SOH[%]']) * \
               cap / 100 / 100
        energy = capacity * np.mean(df_sel_bms['总电压[V]']) / 1000
        capacity_sum = capacity_sum + capacity
        energy_sum = energy_sum + energy
    return capacity_sum, energy_sum

def get_daily_accum_time(df, status):
    data_number_list = sorted(list(set(df[df['data_status'].isin([status])]['data_split_by_status'])))
    accum_time_sum = 0
    for data_number in data_number_list[0:]:
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        accum_time = (df_sel_bms.loc[len(df_sel_bms)-1, '时间戳'] - df_sel_bms.loc[0,'时间戳']).seconds
        accum_time_sum = accum_time_sum + accum_time / 3600
    return accum_time_sum

def get_daily_stand_temp(df, status):
    data_number_list = sorted(list(set(df[df['data_status'].isin([status])]['data_split_by_status'])))
    res_single = pd.DataFrame(columns = ['avr_tem', 'avr_tem_rate'])
    cycle = 0
    avr_tem_sum = 0
    avr_tem_rate_sum =0
    avr_tem = 0
    avr_tem_rate = 0
    for data_number in data_number_list[0:]:
        cycle = cycle + 1
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        avr_tem = np.mean(df_sel_bms['单体温度1'])
        avr_tem_rate = abs(df_sel_bms.loc[len(df_sel_bms)-1, '单体温度1'] - df_sel_bms.loc[0, '单体温度1'])/\
                   (df_sel_bms.loc[len(df_sel_bms)-1, '时间戳'] - df_sel_bms.loc[0, '时间戳']).seconds * 3600
        avr_tem_sum = avr_tem_sum + avr_tem
        avr_tem_rate_sum = avr_tem_rate_sum + avr_tem_rate
    if cycle > 0:
        avr_tem = avr_tem_sum / cycle
        avr_tem_rate = avr_tem_rate_sum / cycle
    return avr_tem, avr_tem_rate

def get_daily_max_pwr(df, status):
    data_number_list = sorted(list(set(df[df['data_status'].isin([status])]['data_split_by_status'])))
    max_pwr = 0
    res_pwr = pd.DataFrame(columns=['pwr'])
    for data_number in data_number_list[0:]:
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        for index in df_sel_bms.index:
            res_pwr = res_pwr.append({'pwr':df_sel_bms.loc[index, '总电流[A]'] * df_sel_bms.loc[index, '总电压[V]'] / 1000},
                             ignore_index=True )
        max_pwr_single = np.max(res_pwr['pwr'])
        max_pwr = max(max_pwr, max_pwr_single)
    return max_pwr

def get_daily_regen(df, status):
    data_number_list = sorted(list(set(df[df['data_status'].isin([status])]['data_split_by_status'])))
    regen_flag = 0
    regen_rate = 0
    regen_count = 0
    total_count = 0
    res_pwr = pd.DataFrame(columns=['pwr'])
    for data_number in data_number_list[0:]:
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        regen_count = regen_count + len(df_sel_bms[df_sel_bms['总电流[A]'] < -1])
        total_count = total_count + len(df_sel_bms)
    if regen_count > 10:
        regen_flag = 1
        regen_rate = regen_count / total_count * 100
    return regen_flag, regen_rate

def get_working_scope(df,df_gps,status):
    data_number_list = sorted(list(set(df[df['data_status'].isin([status])]['data_split_by_status'])))
    dis_single = []
    rad = 3
    df_sel_gps = pd.DataFrame(columns=['时间戳','经度','纬度','海拔m','速度[km/h]','odo','speed'])
    for data_number in data_number_list[0:]:
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        df_sel_gps_single = df_gps[(df_gps['时间戳']>df_sel_bms.loc[0,'时间戳']) & (df_gps['时间戳']<df_sel_bms.loc[len(df_sel_bms)-1,'时间戳'])]
        df_sel_gps = df_sel_gps.append(df_sel_gps_single)
    df_sel_gps = df_sel_gps.reset_index(drop=True)
    center_long = np.mean(df_sel_gps['经度'])
    center_lat = np.mean(df_sel_gps['纬度'])
    for i in range(1, len(df_sel_gps)):
        dis_single.append(math.sqrt(math.pow((df_sel_gps.loc[i, '经度'] - center_long),2) \
                         + math.pow((df_sel_gps.loc[i, '纬度'] -center_lat),2)) * 111)
    dis = sorted(dis_single)
    if len(dis) > 100:
        rad = dis[math.floor(len(dis) * 0.99)]
    else:
        rad = -1
    return center_long, center_lat, rad

def get_isc(df, status):
    total_len = 10
    data_number_list = sorted(list(set(df[df['data_status'].isin([status])]['data_split_by_status'])))
#     df_res = pd.DataFrame(columns=['time','limit','t1', 't2','t3', 't4','t5', 't6','t7', 't8','t9', 't10','t11', 't12','t13', 't14','tmean',
#                           'isc1','isc2','isc3','isc4','isc5','isc6','isc7','isc8','isc9','isc10','isc11','isc12','isc13','isc14',
#                           'res1','res2','res3','res4','res5','res6','res7','res8','res9','res10','res11','res12','res13','res14'])
    df_res = pd.DataFrame(columns=['time','limit','t1', 't2','t3', 't4','t5', 't6','t7', 't8','t9', 't10','tmean',
                          'isc1','isc2','isc3','isc4','isc5','isc6','isc7','isc8','isc9','isc10',
                          'res1','res2','res3','res4','res5','res6','res7','res8','res9','res10'])

    limits = [3360,3361,3362,3363,3364,3365,3366, 3367, 3368, 3369, 3370]
    num = len(limits)
    index = 0
    dd = -1
    timestamp = 1545730073
    base_time = datetime.fromtimestamp(timestamp)
    for data_number in data_number_list[0:]:
        dd = dd + 1
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        df_sel_bms = df_sel_bms.reset_index(drop=True)
#         current = np.mean(df_sel_bms[df_sel_bms['总电压[V]']>=limits[0]*14 and df_sel_bms['总电压[V]']<=limits[-1]*14]['总电流[A]'])
        current = 9
        if df_sel_bms.loc[0, 'SOC[%]'] > 40:
#             print('delete_data={},time={},soc={}'.format(dd,df_sel_bms.loc[0, '时间戳'],df_sel_bms.loc[0, 'SOC[%]']))
            continue
        else:
            k = -1
            for limit in limits:
                k = k + 1
                df_res.loc[index,'time'] = df_sel_bms.loc[0, '时间戳']
                for i in range(1,total_len+1):
#                     print('data={},index={},limit{}={},cell={},time={}'.format(dd,index,k,limit,i,df_sel_bms.loc[0, '时间戳']))
                    time_list = sorted(list(set(df_sel_bms[df_sel_bms['单体电压'+ str(i)]==limit]['时间戳'])))
                    time_list1 = list(df_sel_bms[df_sel_bms['单体电压'+ str(i)]==limit]['时间戳'])
                    
#                     print(df_sel_bms['单体电压'+ str(i)])
#                     print('index={},limit{}={},cell={},time={}'.format(index,k,limit,i,time_list[0]))
                    df_res.loc[index,'limit'] = limit
                    if len(time_list) > 0:
                        time_counter = Counter(time_list1)
                        most_counterNum = time_counter.most_common(2)
                        time_raw = most_counterNum[0]
#                         print('value={},type={}'.format(most_counterNum,type(time_raw)))
                        time = (time_list[-1] - base_time).seconds
#                     print('limit={},i={},time={}'.format(limit, i ,time))
                        df_res.loc[index,'t'+str(i)] = time_list[-1]
                        df_res.loc[index,'isc'+str(i)] = time
#                         print('data={},index={},limit{}={},cell={},time={}'.format(dd,index,k,limit,i,time_list[-1]))
                        if i == total_len:
                            print(df_res.loc[index,'t1':'t'+str(total_len)])
                            df_res.loc[index,'tmean'] = np.mean(df_res.loc[index,'isc1':'isc'+str(total_len)])
                            df_res.loc[index,'isc1':'isc'+str(total_len)] = df_res.loc[index,'isc1':'isc'+str(total_len)] -                                                                       df_res.loc[index,'tmean']
#                             print('data={},k={},index={}'.format(dd,k,index))
                            total_index = index
                            index = index + 1
#                             df_res.loc[index*num+k,'isc1':'isc14'] = df_res.loc[index*num+k,'t1':'t14'] -                                                                              df_res.loc[index*num+k,'tmean']

                    else:
#                         print('NoValue_data={},time={},soc={},limit{}={},cell={}'.
#                               format(dd,df_sel_bms.loc[0, '时间戳'],df_sel_bms.loc[0, 'SOC[%]'],k,limit,i))
                        break
    for limit in limits:
#         print(df_res)
        data_number_list =  sorted(list(set(df_res[df_res['limit']==limit].index)))
#         print(data_number_list)
        df_res.loc[data_number_list[0],'res1':'res'+str(total_len)]=0
        index = 1
        for data_number in data_number_list[1:]:
            for i in range(1,total_len+1):
                df_res.loc[data_number_list[index],'res'+str(i)]=(df_res.loc[data_number_list[index],'isc'+str(i)] -                                                                    df_res.loc[data_number_list[index-1],'isc'+str(i)])*1000*current /                                                                (df_res.loc[data_number_list[index],'time'] -                                                                          df_res.loc[data_number_list[index-                                                                                         1],'time']).total_seconds()
            index = index + 1
    result=[]
    for i in range(1,total_len+1):
#         result.append(np.mean(df_res[11:total_index,'res'+str(i)]))
        mean_value = df_res['res'+str(i)][11:total_index+1].mean()
        result.append(mean_value)
#         print('i{}={},total_index={}'.format(i,df_res['res'+str(i)][11:total_index+1],total_index))
        
    return df_res,result


def get_charge_statics(df, status):
    # 计算充电行为
    df_res = pd.DataFrame(columns=['time', 'num','duration','Capacity'])
    df_res_single = pd.DataFrame(columns=['time','cycle','start_time','stop_time','start_soc','stop_soc','gps_long','gps_lat',])
    start_time = df_bms.loc[3771, '时间戳']
    timeDelta = datetime.timedelta(days=1)
    end_time = start_time + timeDelta
    while end_time < df_bms.loc[len(df_bms)-1, '时间戳']:
#     import pdb;pdb.set_trace()
        cycle = 0
        df_res_temp = pd.DataFrame(columns=['time', 'num'])
        df_sel = df_bms[(df_bms['时间戳']>=start_time) & (df_bms['时间戳']<=end_time)]
        origin_index = list(df_sel.index)
#     print(origin_index[0])
        data_number_list = sorted(list(set(df_sel[df_sel['data_status'].isin(['charge'])]['data_split_by_status'])))
        if (df_bms.loc[origin_index[0], 'data_status'] == df_bms.loc[origin_index[0]-1, 'data_status'] == ['charge']):
            charge_num = len(data_number_list)-1
            start = 1
        else:
            charge_num = len(data_number_list)
            start = 0
    
        for data_number in data_number_list[start:]:
        
            df_sel_bms = df_sel[df_sel['data_split_by_status'] == data_number]
            df_sel_bms = df_sel_bms.reset_index(drop=True)
            df_sel_gps = df_gps[(df_gps['时间戳']>df_sel_bms.loc[0,'时间戳']) & (df_gps['时间戳']<df_sel_bms.loc[len(df_sel_bms)-1,'时间戳'])]
            df_sel_gps = df_sel_gps.reset_index(drop=True)
        
            if data_number != data_number_list[start]:
#             startTime= datetime.datetime.strptime(df_sel_bms.loc[0, '时间戳'],"%Y-%m-%d %H:%M:%S")
                startTime= df_sel_bms.loc[0, '时间戳']
                startSoc= df_sel_bms.loc[0, 'SOC[%]']
                if (startTime - endTime).seconds < 5*60 or abs(endSoc - startSoc) < 5:
                    reconn = 1
                else:
                    reconn = 0
            else:
                reconn = 0
        

#         endTime= datetime.datetime.strptime(df_sel_bms.loc[len(df_sel_bms)-1, '时间戳'],"%Y-%m-%d %H:%M:%S")
            endTime= df_sel_bms.loc[len(df_sel_bms)-1, '时间戳']
            endSoc= df_sel_bms.loc[len(df_sel_bms)-1, 'SOC[%]']
#         index = sorted(np.where(df_sel['data_split_by_union'] == data_number))[0]
#         print(index)
#         min_index = index[0]
#         print(data_number_list)
#         print(df_sel)
#          print('start={},index={},data_num={},value={}'.format(start,min_index,data_number,df_sel.loc[min_index+1, '时间戳']))
#         if ((df_sel_bms.loc[len(df_sel_bms)-1, 'SOC[%]'] - df_sel_bms.loc[0, 'SOC[%]']) > 10.0) and reconn==0:
            if 1:
                cycle = cycle + 1
#             print('cycle = {}, delta = {}'.format(cycle, df_sel_bms.loc[len(df_sel_bms)-1, 'SOC[%]'] - df_sel_bms.loc[0, 'SOC[%]']))
                df_res_single = df_res_single.append({'time': df_sel.loc[origin_index[0], '时间戳'], 
                                              'cycle': cycle,
                                              'start_time': df_sel_bms.loc[0, '时间戳'],
                                              'stop_time': df_sel_bms.loc[len(df_sel_bms)-1, '时间戳'],
                                              'start_soc': df_sel_bms.loc[0, 'SOC[%]'],
                                              'stop_soc': df_sel_bms.loc[len(df_sel_bms)-1, 'SOC[%]'],
                                              'gps_long': np.mean(df_sel_gps['经度']),
                                              'gps_lat': np.mean(df_sel_gps['纬度'])}, ignore_index=True)
#     print(df_sel_bms)
        df_res = df_res.append({'time': df_sel.loc[origin_index[0], '时间戳'], 
                            'num': charge_num}, ignore_index=True)
        start_time = end_time
        end_time = start_time + timeDelta
    print(df_res_single)




def get_soh(df_bms, sn):
    # 计算SOH和一致性
    df_res = pd.DataFrame(columns=['sn','time', 'accum_ah','soh_cell01','soh_cell02','soh_cell03',
                               'soh_cell04','soh_cell05','soh_cell06','soh_cell07',
                               'soh_cell08','soh_cell09','soh_cell10','soh_cell11',
                               'soh_cell12','soh_cell13','soh_cell14','soh_cell15',
                               'soh_cell16','soh_cell17','soh_cell18','soh_cell19',
                               'soh_cell20'])
    df_cell_res = pd.DataFrame(columns=['sn','time','status','volt_cell01','volt_cell02','volt_cell03',
                               'volt_cell04','volt_cell05','volt_cell06','volt_cell07',
                               'volt_cell08','volt_cell09','volt_cell10','volt_cell11',
                               'volt_cell12','volt_cell13','volt_cell14','volt_cell15',
                               'volt_cell16','volt_cell17','volt_cell18','volt_cell19',
                               'volt_cell20','volt_max','volt_min', 'volt_avr','soc_diff','soc_diff_div','over_discharge'])

    df_sel = df_bms
    # print(sn[0][0:2])
    if sn[0][0:5] == 'PK504':
        soc_ocv_table = pd.read_excel(r'D:\Work\Interior\Cloud_BMS\Overall\产品信息\电池OCV表格.xlsx', '60AH')
        cellcap = 55 # Capacity
    elif sn[0][0:5] == 'PK501':
        soc_ocv_table = pd.read_excel(r'D:\Work\Interior\Cloud_BMS\Overall\产品信息\电池OCV表格.xlsx', '40AH')
        cellcap = 40  # Capacity
    elif sn[0][0:2] == 'UD':
        soc_ocv_table = pd.read_excel(r'D:\Work\Interior\Cloud_BMS\Overall\产品信息\电池OCV表格.xlsx', '50AH')
        cellcap = 50  # Capacity

    cellsoh_list = []
    data_number_list = sorted(list(set(df_sel[df_sel['data_status'].isin(['charge'])]['data_split_by_status'])))
    start = 1
    for data_number in data_number_list[start:-2]:
        ## 充电以及前后数据全部获取
        df_sel_bms_relax_start = df_sel[df_sel['data_split_by_status'] == data_number-1]
        df_sel_bms = df_sel[df_sel['data_split_by_status'] == data_number]
        df_sel_bms_relax_end = df_sel[df_sel['data_split_by_status'] == data_number+1]
        origin_index = list(df_sel_bms.index)
        origin_index_relax_start = list(df_sel_bms_relax_start.index)
        origin_index_relax_end = list(df_sel_bms_relax_end.index)
        df_sel_bms_relax_start = df_sel_bms_relax_start.reset_index(drop=True)
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        df_sel_bms_relax_end = df_sel_bms_relax_end.reset_index(drop=True)
        ## 充电前后静置时间
#         delta_time_start = (df_sel_bms_relax_start.loc[len(df_sel_bms_relax_start)-1,'时间戳'] - df_sel_bms_relax_start.loc[0,'时间戳']).seconds
        delta_time_end = (df_sel_bms_relax_end.loc[len(df_sel_bms_relax_end)-1,'时间戳'] - df_sel_bms_relax_end.loc[0,'时间戳']).seconds
#         print(df_sel_bms_relax_start.loc[len(df_sel_bms_relax_start)-1,'时间戳'],
#               df_sel_bms_relax_start.loc[0,'时间戳'],
#               df_sel_bms_relax_start.loc[len(df_sel_bms_relax_start)-1,'data_status'],
#               df_sel_bms_relax_end.loc[len(df_sel_bms_relax_end)-1,'data_status'],
#               df_sel_bms.loc[0,'时间戳'],
#               delta_time_start,
#               delta_time_end)
        ##  充电前后静置并且充电前静置超过10min，充电后静置超过3h
        if (df_sel_bms_relax_start.loc[len(df_sel_bms_relax_start)-1,'data_status'] == 'stand' or \
            df_sel_bms_relax_start.loc[len(df_sel_bms_relax_start)-1,'data_status'] == 'drive') and \
            df_sel_bms_relax_end.loc[len(df_sel_bms_relax_end)-1,'data_status'] == 'stand' and\
            delta_time_end > 18000:
            ## 记录充电相关信息
            start_time = df_sel_bms.loc[0,'时间戳']
            end_time = df_sel_bms.loc[len(df_sel_bms)-1,'时间戳']
            relax_time = df_sel_bms_relax_end.loc[len(df_sel_bms_relax_end)-1,'时间戳']
            start_current = df_sel_bms_relax_start.loc[len(df_sel_bms_relax_start)-1,'总电流[A]']
            end_current = df_sel_bms_relax_end.loc[len(df_sel_bms_relax_end)-1,'总电流[A]']
#             print(start_time,end_time,relax_time,start_current,end_current)
            ## 记录充电开始前信息
            start_volt_class = df_sel.loc[origin_index[0]-3,'单体电压1':'单体电压20']
            start_index = start_volt_class
            start_index = start_index.apply(lambda x: 1 if x<3.279 or (x > 3.292 and x < 3.307) else 0)
            start_index = np.array(start_index)
            start_soc,start_cellsoc_list = getcellsoc(start_volt_class,soc_ocv_table)
            if np.min(start_volt_class) < 3182:
                over_discharge = 1
            else:
                over_discharge = 0
            if np.max(start_volt_class) > 3640:
                over_charge = 1
            else:
                over_charge = 0
            df_cell_res = df_cell_res.append({'sn':sn,
                                              'time':df_sel.loc[origin_index[0]-3,'时间戳'],
                                              'status':'start',
                                              'volt_cell01':start_volt_class[0],'volt_cell02':start_volt_class[1],
                                              'volt_cell03':start_volt_class[2],'volt_cell04':start_volt_class[3],
                                              'volt_cell05':start_volt_class[4],'volt_cell06':start_volt_class[5],
                                              'volt_cell07':start_volt_class[6],'volt_cell08':start_volt_class[7],
                                              'volt_cell09':start_volt_class[8],'volt_cell10':start_volt_class[9],
                                              'volt_cell11':start_volt_class[10],'volt_cell12':start_volt_class[11],
                                              'volt_cell13':start_volt_class[12],'volt_cell14':start_volt_class[13],
                                              'volt_cell15':start_volt_class[14],'volt_cell16':start_volt_class[15],
                                              'volt_cell17':start_volt_class[16],'volt_cell18':start_volt_class[17],
                                              'volt_cell19':start_volt_class[18],'volt_cell20':start_volt_class[19],
                                              'volt_max':np.max(start_volt_class),
                                              'volt_min':np.min(start_volt_class), 
                                              'volt_avr':np.mean(start_volt_class),
                                              'soc_diff':np.max(start_cellsoc_list)-np.min(start_cellsoc_list),
                                              'soc_diff_div':np.mean(start_volt_class[0:9])-np.mean(start_volt_class[10:19]),
                                              'over_discharge':over_discharge,'over_charge':over_charge}, ignore_index=True)
            ## 记录充电结束信息
            end_volt_class = df_sel_bms_relax_end.loc[len(df_sel_bms_relax_end)-3,'单体电压1':'单体电压20']
            end_index = end_volt_class
            end_index = end_index.apply(lambda x: 1 if x>3.346 or (x > 3.292 and x < 3.307) else 0)
            end_index = np.array(end_index)
            end_soc,end_cellsoc_list = getcellsoc(end_volt_class,soc_ocv_table)
            if np.min(end_volt_class) < 3182:
                over_discharge = 1
            else:
                over_discharge = 0
            if np.max(end_volt_class) > 3640:
                over_charge = 1
            else:
                over_charge = 0
            df_cell_res = df_cell_res.append({'sn':sn,
                                              'time':df_sel_bms_relax_end.loc[len(df_sel_bms_relax_end)-3,'时间戳'],
                                              'status':'end',
                                              'volt_cell01':end_volt_class[0],'volt_cell02':end_volt_class[1],
                                              'volt_cell03':end_volt_class[2],'volt_cell04':end_volt_class[3],
                                              'volt_cell05':end_volt_class[4],'volt_cell06':end_volt_class[5],
                                              'volt_cell07':end_volt_class[6],'volt_cell08':end_volt_class[7],
                                              'volt_cell09':end_volt_class[8],'volt_cell10':end_volt_class[9],
                                              'volt_cell11':end_volt_class[10],'volt_cell12':end_volt_class[11],
                                              'volt_cell13':end_volt_class[12],'volt_cell14':end_volt_class[13],
                                              'volt_cell15':end_volt_class[14],'volt_cell16':end_volt_class[15],
                                              'volt_cell17':end_volt_class[16],'volt_cell18':end_volt_class[17],
                                              'volt_cell19':end_volt_class[18],'volt_cell20':end_volt_class[19],
                                              'volt_max':np.max(end_volt_class),
                                              'volt_min':np.min(end_volt_class), 
                                              'volt_avr':np.mean(end_volt_class),
                                              'soc_diff':np.max(end_cellsoc_list)-np.min(end_cellsoc_list),
                                              'soc_diff_div':np.mean(end_volt_class[0:9])-np.mean(end_volt_class[10:19]),
                                              'over_discharge':over_discharge,'over_charge':over_charge}, ignore_index=True)
            ## 计算过程量，deltaSOC和deltaAh
            delta_cellsoc_list = np.array(end_cellsoc_list) - np.array(start_cellsoc_list)
            
            accum = 0
            for time_num in range(1,len(df_sel_bms)):
                delta_time = (df_sel_bms.loc[time_num,'时间戳'] - df_sel_bms.loc[time_num-1,'时间戳']).seconds
                accum = accum - df_sel_bms.loc[time_num,'总电流[A]']* delta_time/3600
            ## 单次SOC间隔超过10%，计算SOH
            if np.mean(delta_cellsoc_list) > 10:
                cellsoh=accum/delta_cellsoc_list*100/cellcap*100
#                 cellsoh=cellsoh*start_index*end_index
#                 print(cellsoh)
                df_res = df_res.append({'sn':sn,
                                        'time':df_sel_bms.loc[0,'时间戳'],
                                        'accum_ah':accum,
                                        'soh_cell01':cellsoh[0],'soh_cell02':cellsoh[1],'soh_cell03':cellsoh[2],
                                        'soh_cell04':cellsoh[3],'soh_cell05':cellsoh[4],'soh_cell06':cellsoh[5],
                                        'soh_cell07':cellsoh[6],'soh_cell08':cellsoh[7],'soh_cell09':cellsoh[8],
                                        'soh_cell10':cellsoh[9],'soh_cell11':cellsoh[10],'soh_cell12':cellsoh[11],
                                        'soh_cell13':cellsoh[12],'soh_cell14':cellsoh[13],'soh_cell15':cellsoh[14],
                                        'soh_cell16':cellsoh[15],'soh_cell17':cellsoh[16],'soh_cell18':cellsoh[17],
                                        'soh_cell19':cellsoh[18],'soh_cell20':cellsoh[19]},ignore_index=True)
                cellsoh_list_single=cellsoh.tolist()
                cellsoh_list.append(cellsoh_list_single)
                
    return df_res, df_cell_res


def get_charge_start_time(df,status):
    data_number_list = sorted(list(set(df[df['data_status'].isin([status])]['data_split_by_status'])))
    for data_number in data_number_list[0:]:
        df_sel_bms = df[df['data_split_by_status'] == data_number]
        df_sel_bms = df_sel_bms.reset_index(drop=True)
        start_time = df_sel_bms.loc[0,'时间戳']
        start_time = datetime.strftime(start_time, "%H:%M:%S")
        limit_time = datetime.strptime("20:00:00", "%H:%M:%S")
        print('start_time{}>20:00:{}'.format(start_time,limit_time))