两天之间的差异(不包括周末)(以小时为单位)

2022-01-13 00:00:00 python pandas dataframe timestamp timedelta

问题描述

我有一个代码使用 np.busdaycount 计算不包括周末的日期差异,但我需要它在我无法获得的时间.

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

问题

inflow_date_time=2019-08-01 23:22:46End_date_time = 2019-08-05 17:43:51预计小时数 42 小时(1+24+17)

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:46End_date_time = 2019-08-05 17:43:51
预计小时数 17 小时(0+0+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:46End_date_time = 2019-08-05 17:43:51预计小时数 17 小时(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)

流入日期时间=2019-07-26 23:22:46End_date_time = 2019-08-05 17:43:51
预计小时数 138 小时(1+120+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:46End_date_time = 2019-08-05 17:43:51
预计小时数 6 小时(0+0+6)

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)

请提出建议.


解决方案

想法是按天删除times的下限日期时间,并获取开始日+一天之间的工作日数到numpy.busday_count >hours3 列 然后为开始和结束时间创建 hour1hour2 列,如果不是周末时间,则按小时计算.最后将所有小时列加在一起:

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  

相关文章