pythonpandas

Pandas Python to count minutes between times

I'm trying to use pandas / python to load a dataframe and count outage minutes that occur between 0900-2100. I've been trying to get this per site but have only been able to get a sum value. Example dataframe is below. I'm trying to produce the data in the third column:

enter image description here

import pandas as pd
from pandas import Timestamp
import pytz
from pytz import all_timezones
import datetime
from datetime import time
from threading import Timer
import time as t
import xlrd
import xlwt
import numpy as np
import xlsxwriter

data = pd.read_excel('lab.xlsx')

data['outage'] = data['Down'] - data['Down']
data['outage'] = data['Down']/np.timedelta64(1,'m')

s = data.apply(lambda row: pd.date_range(row['Down'], row['Up'], freq='T'), axis=1).explode()

#returns total amount of downtime between 9-21 but not by site
total = s.dt.time.between(time(9), time(21)).sum()  

#range of index[0] for s 
slist = range(0, 20) 

#due to thy this loop iterates, it returns the number of minutes between down and up 
for num in slist:
    Duration = s[num].count()
    print(Duration)  

#percentage of minutes during business hours
percentage = (total / sum(data['duration'])) * 100
print('The percentage of outage minutes during business hours is:', percentage)

#secondary function to test
def by_month():
    s = data.apply(lambda row: pd.date_range(row['Adjusted_Down'], row['Adjusted_Up'], freq='T'), axis=1).explode()
    downtime = pd.DataFrame({
        'Month': s.astype('datetime64[M]'),
        'IsDayTime': s.dt.time.between(time(9), time(21))
    })
    downtime.groupby('Month')['IsDayTime'].sum()

#data.to_excel('delete.xls', 'a+')
Solution

  • You can use pandas' DatetimeIndex function to convert the difference between your down time and up time into hours, minutes, and seconds. Then you can multiply the hours by 60 and add minutes to get your total down time in minutes. See example below:

        import pandas as pd

    date_format = "%m-%d-%Y %H:%M:%S"
    # Example up and down times to insert into dataframe
    down1  = dt.datetime.strptime('8-01-2019 00:00:00', date_format)
    up1  = dt.datetime.strptime('8-01-2019 00:20:00', date_format)
    down2  = dt.datetime.strptime('8-01-2019 02:26:45', date_format)
    up2  = dt.datetime.strptime('8-01-2019 03:45:04', date_format)
    down3  = dt.datetime.strptime('8-01-2019 06:04:00', date_format)
    up3  = dt.datetime.strptime('8-01-2019 06:06:34', date_format)

    time_df = pd.DataFrame([{'down':down1,'up':up1},{'down':down2,'up':up2},{'down':down3,'up':up3},])

    # Subtract your up column from your down column and convert the result to a datetime index
    down_time = pd.DatetimeIndex(time_df['up'] - time_df['down'])

    # Access your new index, converting the hours to minutes and adding minutes to get down time in minutes
    down_time_min = time.hour * 60 + time.minute

    # Apply above array to new dataframe column
    time_df['down_time'] = down_time_min

    time_df

    This is the result for this example: Dataframe result