Differance between two days excluding weekends in hours

Question

I have a code that calculates the date differance excluding the weekends using np.busdaycount, but i need it in the hours which i cannot able to get.

import datetime
import numpy as np


df.Inflow_date_time= [pandas.Timestamp('2019-07-22 21:11:26')]
df.End_date_time= [pandas.Timestamp('2019-08-02 11:44:47')]

df['Day'] = ([np.busday_count(b,a) for a, b in zip(df['End_date_time'].values.astype('datetime64[D]'),df['Inflow_date_time'].values.astype('datetime64[D]'))])

  Day
0  9

I need the out put as hours excluding the weekend. Like

  Hours
0  254

Problems

Inflow_date_time=2019-08-01 23:22:46 End_date_time = 2019-08-05 17:43:51 Hours expected 42 hours (1+24+17)

Inflow_date_time=2019-08-03 23:22:46 End_date_time = 2019-08-05 17:43:51
Hours expected 17 hours (0+0+17)

Inflow_date_time=2019-08-01 23:22:46 End_date_time = 2019-08-05 17:43:51 Hours expected 17 hours (0+0+17)

Inflow_date_time=2019-07-26 23:22:46 End_date_time = 2019-08-05 17:43:51
Hours expected 138 hours (1+120+17)

Inflow_date_time=2019-08-05 11:22:46 End_date_time = 2019-08-05 17:43:51
Hours expected 6 hours (0+0+6)

Please suggest.

Answer 1

I updated jezrael answer's to work with version 1.x.x of pandas. I edited the code and the logic a bit to calculate the difference in hours and minutes.

Function

def datetimes_hours_difference(df_end: pd.Series, df_start: pd.Series) -> pd.Series:
    """
    Calculate the total hours difference between two Pandas Series
    containing datetime values (df_end - df_start)

    Args:
        df_end (pd.Series): Contains datetime values
        df_start (pd.Series): Contains datetime values

    Returns:
        df_date_diff (pd.Series): Difference between df_end and df_start 
    """
    df_start_hours = df_start.dt.ceil('d')
    df_end_hours = df_end.dt.floor('d')
    one_day_mask = df_start.dt.floor('d') == df_end_hours

    df_days_hours = [np.busday_count(
        b, a, weekmask='1111011') * 24 for a, b in zip(
            df_end_hours.dt.strftime('%Y-%m-%d'),
            df_start_hours.dt.strftime('%Y-%m-%d')
        )
    ]

    mask1 = df_start.dt.dayofweek != 4
    hours1 = df_start_hours - df_start.dt.floor('min')
    hours1.loc[~mask1] = pd.NaT

    df_start_hours = hours1 / pd.to_timedelta(1, unit='H')
    df_start_hours = df_start_hours.fillna(0)

    mask2 = df_end.dt.dayofweek != 4
    hours2 = df_end.dt.floor('min') - df_end_hours
    hours2.loc[~mask2] = pd.NaT

    df_end_hours = hours2 / pd.to_timedelta(1, unit='H')
    df_end_hours = df_end_hours.fillna(0)

    df_date_diff = df_start_hours + df_end_hours + df_days_hours
    one_day = (df_end.dt.floor('min') - df_start.dt.floor('min'))
    one_day = one_day / pd.to_timedelta(1, unit='H')
    df_date_diff = df_date_diff.mask(one_day_mask, one_day)

    return df_date_diff

Example

df = pd.DataFrame({
   'datetime1': ["2022-06-15 16:06:00", "2022-06-15 03:45:00", "2022-06-10 12:13:00", "2022-06-11 12:13:00", "2022-06-10 12:13:00", "2022-05-31 17:20:00"], 
   'datetime2': ["2022-06-22 22:36:00", "2022-06-15 22:36:00", "2022-06-22 10:10:00", "2022-06-22 10:10:00", "2022-06-24 10:10:00", "2022-06-02 05:29:00"],
   'hours_diff': [150.5, 18.9, 250.9, 237.9, 288.0, 36.2]
})
df['datetime1'] = pd.to_datetime(df['datetime1'])
df['datetime2'] = pd.to_datetime(df['datetime2'])

df['hours_diff_fun'] = datetimes_hours_difference(df['datetime2'], df['datetime1'])

print(df)
    datetime1            datetime2             hours_diff  hours_diff_fun
0   2022-06-15 16:06:00  2022-06-22 22:36:00   150.5       150.500000
1   2022-06-15 03:45:00  2022-06-15 22:36:00   18.9        18.850000
2   2022-06-10 12:13:00  2022-06-22 10:10:00   250.9       250.166667
3   2022-06-11 12:13:00  2022-06-22 10:10:00   237.9       237.950000
4   2022-06-10 12:13:00  2022-06-24 10:10:00   288.0       288.000000
5   2022-05-31 17:20:00  2022-06-02 05:29:00   36.2        36.150000

Answer 2

If i am not completly wrong you can also use a shorter workaround:

First save your day difference in an array:

res = np.busday_count(df['Inflow_date_time'].values.astype('datetime64[D]'), df['End_date_time'].values.astype('datetime64[D]'))

Then we need an extra hour column for every row:

df['starth'] = df['Inflow_date_time'].dt.hour
df['endh'] = df['End_date_time'].dt.hour

Then we will get the day difference to your dataframe:

my_list = res.tolist()
dfhelp =pd.DataFrame(my_list,columns=['col1'])
df2 = pd.concat((df, df2) , axis=1)

Then we have to get a help column, as the hour of End_date_timecan be before Inflow_date-time:

df2['h'] = df2['endh']-df2['starth']

And then we can calculate the hour difference (one day has 24 hours, based if the hour of the end date is before the start hour date or not):

df2['differenceh'] = np.where(df2['h'] >= 0, df2['col1']*24+df2['h'], df2['col1']*24-24+(24+df2['h']))

Answer 3

Idea is floor datetimes for remove times by floor by days and get number of business days between start day + one day to hours3 column by numpy.busday_count and then create hour1 and hour2 columns for start and end hours with floor by hours if not weekends hours. Last sum all hours columns together:

df = pd.DataFrame(columns=['Inflow_date_time','End_date_time', 'need'])
df.Inflow_date_time= [pd.Timestamp('2019-08-01 23:22:46'),
                      pd.Timestamp('2019-08-03 23:22:46'),
                      pd.Timestamp('2019-08-01 23:22:46'),
                      pd.Timestamp('2019-07-26 23:22:46'),
                      pd.Timestamp('2019-08-05 11:22:46')]
df.End_date_time= [pd.Timestamp('2019-08-05 17:43:51')] * 5
df.need = [42,17,41,138,6]

#print (df)

---

df["hours1"] = df["Inflow_date_time"].dt.ceil('d')
df["hours2"] =  df["End_date_time"].dt.floor('d')
one_day_mask = df["Inflow_date_time"].dt.floor('d') == df["hours2"]

df['hours3'] = [np.busday_count(b,a)*24 for a, b in zip(df['hours2'].dt.strftime('%Y-%m-%d'),
                                                        df['hours1'].dt.strftime('%Y-%m-%d'))]

mask1 = df['hours1'].dt.dayofweek < 5
hours1 = df['hours1']  - df['Inflow_date_time'].dt.floor('H')

df['hours1'] = np.where(mask1, hours1, np.nan) / np.timedelta64(1 ,'h')

mask2 = df['hours2'].dt.dayofweek < 5

df['hours2'] = (np.where(mask2, df['End_date_time'].dt.floor('H')-df['hours2'], np.nan) / 
                np.timedelta64(1 ,'h'))

df['date_diff'] = df['hours1'].fillna(0) + df['hours2'].fillna(0) + df['hours3']

one_day = (df['End_date_time'].dt.floor('H') - df['Inflow_date_time'].dt.floor('H')) / 
            np.timedelta64(1 ,'h')
df["date_diff"] = df["date_diff"].mask(one_day_mask, one_day)

---

print (df)
     Inflow_date_time       End_date_time  need  hours1  hours2  hours3  \
0 2019-08-01 23:22:46 2019-08-05 17:43:51    42     1.0    17.0      24   
1 2019-08-03 23:22:46 2019-08-05 17:43:51    17     NaN    17.0       0   
2 2019-08-01 23:22:46 2019-08-05 17:43:51    41     1.0    17.0      24   
3 2019-07-26 23:22:46 2019-08-05 17:43:51   138     NaN    17.0     120   
4 2019-08-05 11:22:46 2019-08-05 17:43:51     6    13.0    17.0     -24   

   date_diff  
0       42.0  
1       17.0  
2       42.0  
3      137.0  
4        6.0