python - 如何计算两个时间戳的小时差并排除周末

Question

我有一个这样的数据框:

     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         

         

使用这段代码:

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)

我有这个 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

基本上，这就是我想要的，但是，在计算 Folder1 和 Folder2 的时间戳之间的差异时，如何排除周末时间？我应该改变什么才能拥有这样的 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                32:00:00    NaN
3   2021-11-23 10:00:00            NaN                            NaN     03:00:00

因此，在索引 2 的行中，13.11 和 14.11 是周末，所以在时间 1 中，差异应该是 32 而不是 80

Answer 1

我认为您可以利用 pandas.date_range功能结合 pandas.tseries.offsets.CustomBusinessHour像这样:

# 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()

这对我来说会产生:

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

作为奖励，您还可以使用它更有效地进行 Time2 计算:

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

对我来说(欧洲中部时间 14:45):

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