# encoding: utf-8

def filterWatchTimeLessThanFive(dataset):
    import pandas as pd;

    data = pd.read_excel(dataset)
    data[u'观看时长'] = 0
    for i in range(len(data)):
          start = str(data.iloc[i:i+1,4:5].values[0][0])
          end =str(data.iloc[i:i+1,5:6].values[0][0])
          startyear = start[0:4]
          startmonth = start[5:7]
          startday = start[8:10]
          endyear = end[0:4]
          endmonth = end[5:7]
          endday = end[8:10]
          startH = start[11:13]
          startM = start[14:16]
          startS  = start[17:19]
          endH= end[11:13]
          endM= end[14:16]
          endS = end[17:19]
          data.iloc[i:i+1,7:8] = calculateTime(startyear,startmonth,startday,endyear,endmonth,endday,startH,startM,startS,endH,endM,endS)

    data = data[data[u'观看时长']>5]
    data.to_excel('./dataset/filterWatchTimeLessThanFiveNew.xlsx',index=False)

#整理影片名称，方便去重
def dealTheOfVideo(dataset):
    import pandas as pd;
    data = pd.read_excel(dataset)

    for i in range(len(data)):
        str = data.iloc[i:i + 1, 3:4].values[0][0]
        index = str.find('(')
        if (index != -1):
             str = str[0:index]
        index = str.find(' ')
        if (index != -1):
            str = str[index+1:]
        data.iloc[i:i + 1, 8:9] =  str
    #data = data[u'影片名称'].drop_duplicates();
    #data.reset_index(drop=True)
    #pd.DataFrame(data).to_excel('./dataset/dealTheOfVideoNew.xlsx',index=False)
    data.to_excel('./dataset/filterWatchTimeLessThanFiveNew.xlsx',index=False)



#计算两个日期的秒数差
def calculateTime(startyear,startmonth,startday,endyear,endmonth,endday,startH,startM,startS,endH,endM,endS):
    import datetime
    start = datetime.datetime(int(startyear), int(startmonth), int(startday), int(startH), int(startM), int(startS))
    end = datetime.datetime(int(endyear), int(endmonth), int(endday), int(endH), int(endM), int(endS))
    value = end-start
    return int(value.total_seconds()/60)



#dataset = './dataset/watch.xlsx'
dataset = './dataset/filterWatchTimeLessThanFiveNew.xlsx'
dealTheOfVideo(dataset=dataset)
#filterWatchTimeLessThanFive(dataset=dataset)