How to calculate the difference between in hours two timestamps and exclude weekends

user_14073111 0
Viewed 103

I have a dataframe like this:

     Folder1                   Folder2                 
0   2021-11-22 12:00:00      2021-11-24 10:00:00
1   2021-11-23 10:30:00      2021-11-25 18:30:00    
2   2021-11-12 10:30:00      2021-11-15 18:30:00    
3   2021-11-23 10:00:00            NaN

Using this code:

def strfdelta(td: pd.Timestamp):
    seconds = td.total_seconds()
    hours = int(seconds // 3600)
    minutes = int((seconds % 3600) // 60)
    seconds = int(seconds % 60)
    return f"{hours:02}:{minutes:02}:{seconds:02}"
            
df["Folder1"] = pd.to_datetime(df["Folder1"])
df["Folder2"] = pd.to_datetime(df["Folder2"])

bm1 = df["Folder1"].notna() & df["Folder2"].notna()
bm2 = df["Folder1"].notna() & df["Folder2"].isna()

df["Time1"] = (df.loc[bm1, "Folder2"] - df.loc[bm1, "Folder1"]).apply(strfdelta)
df["Time2"] = (datetime.now() - df.loc[bm2, "Folder1"]).apply(strfdelta)

I have this df:

     Folder1                   Folder2                           Time1     Time2
0   2021-11-22 12:00:00      2021-11-24 10:00:00                46:00:00    NaN
1   2021-11-23 10:30:00      2021-11-25 18:30:00                56:00:00    NaN
2   2021-11-12 10:30:00      2021-11-15 18:30:00                80:00:00    NaN
3   2021-11-23 10:00:00            NaN                             NaN     03:00:00

Basically, this is what i want, but, how can i exclude weekends hours when calculating the the difference between timestamps from Folder1 and Folder2? What should i change to have a df like this:

     Folder1                   Folder2                           Time1     Time2
0   2021-11-22 12:00:00      2021-11-24 10:00:00                46:00:00    NaN
1   2021-11-23 10:30:00      2021-11-25 18:30:00                56:00:00    NaN
2   2021-11-12 10:30:00      2021-11-15 18:30:00                32:00:00    NaN
3   2021-11-23 10:00:00            NaN                            NaN     03:00:00

So, in row with index 2, 13.11 and 14.11 were weekends so, in Time 1 the difference should be 32 instead of 80

pandas dataframe python-3.x datetime python
3 Answers

I think you could leverage on pandas.date_range function combined with pandas.tseries.offsets.CustomBusinessHour like this:

# import pandas and numpy
import pandas as pd
import numpy as np

# construct dataframe
df = pd.DataFrame()
df["Folder1"] = pd.to_datetime(
    pd.Series(
        [
            "2021-11-22 12:00:00",
            "2021-11-23 10:30:00",
            "2021-11-12 10:30:00",
            "2021-11-23 10:00:00",
        ]
    )
)
df["Folder2"] = pd.to_datetime(
    pd.Series(
        [
            "2021-11-24 10:00:00", 
            "2021-11-25 18:30:00", 
            "2021-11-15 18:30:00", 
            np.NaN
        ]
    )
)

# define custom business hours
cbh = pd.tseries.offsets.CustomBusinessHour(start="0:00", end="23:59")

# actual calculation
df["Time1"] = df[~(df["Folder1"].isnull() | df["Folder2"].isnull())].apply(
    lambda row: len(
        pd.date_range(
            start=row["Folder1"], 
            end=row["Folder2"], 
            freq=cbh)),
    axis=1,
)

df.head()

Which for me yields:

print(df.head())
              Folder1             Folder2  Time1
0 2021-11-22 12:00:00 2021-11-24 10:00:00   46.0
1 2021-11-23 10:30:00 2021-11-25 18:30:00   56.0
2 2021-11-12 10:30:00 2021-11-15 18:30:00   32.0
3 2021-11-23 10:00:00                 NaT    NaN

As a bonus you can do your Time2 calculation more efficiently using it as well:

df["Time2"] = df[df["Folder2"].isnull()].apply(
    lambda row: len(
        pd.date_range(
            start=row["Folder1"],
            end=datetime.datetime.now(),
            freq=cbh)),
    axis=1,
)

Which for me yields (at 14:45 CET):

print(df.head())
              Folder1             Folder2  Time1  Time2
0 2021-11-22 12:00:00 2021-11-24 10:00:00   46.0    NaN
1 2021-11-23 10:30:00 2021-11-25 18:30:00   56.0    NaN
2 2021-11-12 10:30:00 2021-11-15 18:30:00   32.0    NaN
3 2021-11-23 10:00:00                 NaT    NaN    5.0
user_21861840 
df['Folder1']=pd.to_datetime(df['Folder1'])
df['Folder2']=pd.to_datetime(df['Folder2']).fillna(df['Folder1'])

df['missing']=df.apply(lambda x: pd.date_range(start=x['Folder1'], end=x['Folder2'], freq='D'), axis=1)#Create column with missing date periods



df=(df.assign(time=np.where((df['missing'].apply(lambda x: x.strftime('%w'))).map(set).astype(str).str.contains('0|6'),#Where missing periods have a Saturday or Sunday
                            
                            (df['Folder2']-df['Folder1']).astype('timedelta64[h]')-48,# When above condition is met, subtract two 48 hours from the two days columns difference
                            (df['Folder2']-df['Folder1']).astype('timedelta64[h]'))#When condition not met substract just the two date columns)
             ).drop(columns=['missing']) )             
print(df)



Folder1             Folder2  time
0 2021-11-22 12:00:00 2021-11-24 10:00:00  46.0
1 2021-11-23 10:30:00 2021-11-25 18:30:00  56.0
2 2021-11-12 10:30:00 2021-11-15 18:30:00  32.0
3 2021-11-23 10:00:00 2021-11-23 10:00:00   0.0
user_89869750 
df = pd.DataFrame({'Folder1': ['2021-11-22 12:00:00', '2021-11-23 10:30:00', '2021-11-12 10:30:00', '2021-11-23 10:00:00'],
                   'Folder2': ['2021-11-24 10:00:00', '2021-11-25 18:30:00', '2021-11-15 18:30:00', None]})
df[['Folder1','Folder2']] = df[['Folder1','Folder2']].astype('datetime64')

def strfdelta(t1, t2):
    hd = pd.date_range(t1, t2, freq='W-SAT').append(pd.date_range(t1, t2, freq='W-SUN'))
    sec = (t2-t1).total_seconds() - len(hd)*24*3600
    return f"{int(sec//3600):02d}:{int((sec%3600)//60):02d}:{int(sec%60):02d}"

now = pd.to_datetime('now')
df['Time1'] = df.fillna(now).apply(lambda x: strfdelta(x['Folder1'], x['Folder2']), axis=1)
print(df)

Prints:

              Folder1             Folder2     Time1
0 2021-11-22 12:00:00 2021-11-24 10:00:00  46:00:00
1 2021-11-23 10:30:00 2021-11-25 18:30:00  56:00:00
2 2021-11-12 10:30:00 2021-11-15 18:30:00  32:00:00
3 2021-11-23 10:00:00                 NaT  20:58:26
user_150353140
Related