比较包含日期和时间的数据帧中的两列，并在另一列中给出差异

Question

我有一个这样的数据帧：

datetime1                datetime2             
0   2021-05-09 19:52:14      2021-05-09 20:52:14  
1   2021-05-09 19:52:14      2021-05-09 21:52:14  

我想对它们进行比较，并创建一个新的列，其中包含它们之间的差异：

理想的输出如下：

datetime1                datetime2              Difference in H:m:s
0   2021-05-09 19:52:14      2021-05-09 20:52:14                  01:00:00
1   2021-05-09 19:52:14      2021-05-09 21:52:14                  02:00:00

编辑：

@Andrej，当我在日期时间1和2都有时间戳时，你给我的解决方案非常有效。如果我有一个像下面这样的df，它就是失败的，因为它没有什么可以比较的

df1:

datetime1                datetime2             
0   2021-05-09 19:52:14      2021-05-09 20:52:14  
1   2021-05-09 19:52:14      2021-05-09 21:52:14 
2           NaN                      NaN
3  2021-05-09 16:30:14               NaN
4           NaN                      NaN
5  2021-05-09 12:30:14        2021-05-09 14:30:14

df2(理想输出(：

datetime1            datetime2        Difference in H:m:s    Compared with datetime.now()
0   2021-05-09 19:52:14  2021-05-09 20:52:14         01:00:00           NaN
1   2021-05-09 19:52:14  2021-05-09 21:52:14         02:00:00           NaN
2           NaN               NaN                      NaN              NaN
3   2021-05-09 16:30:14       NaN                      NaN       e.g(04:00:00)
4           NaN               NaN                      NaN              NaN
5  2021-05-09 12:30:14   2021-05-09 14:30:14         02:00:00           NaN

在一个真实的场景中，我有一种情况，我在datetime1和datetime2中没有值，或者我在datatime1中有值，但在datatime2中没有，所以有没有一种可能的方法可以在"；差异"；列，如果datetime1和2中没有时间戳，并且只有datetime1中有时间戳，则获取与datetime.now((相比的差异，并将其放在另一列中。

Answer 1

尝试：

def strfdelta(tdelta, fmt):
d = {"days": tdelta.days}
d["hours"], rem = divmod(tdelta.seconds, 3600)
d["minutes"], d["seconds"] = divmod(rem, 60)
return fmt.format(**d)

# if datetime1/datetime2 aren't already datetime, apply `.to_datetime()`:
df["datetime1"] = pd.to_datetime(df["datetime1"])
df["datetime2"] = pd.to_datetime(df["datetime2"])
df["Difference in H:m:s"] = df.apply(
lambda x: strfdelta(
x["datetime2"] - x["datetime1"],
"{hours:02d}:{minutes:02d}:{seconds:02d}",
),
axis=1,
)
print(df)

打印：

datetime1           datetime2 Difference in H:m:s
0 2021-05-09 19:52:14 2021-05-09 20:52:14            01:00:00
1 2021-05-09 19:52:14 2021-05-09 21:52:14            02:00:00

---

编辑：处理NaNs：

# if datetime1/datetime2 aren't already datetime, apply `.to_datetime()`:
df["datetime1"] = pd.to_datetime(df["datetime1"])
df["datetime2"] = pd.to_datetime(df["datetime2"])
df["Difference in H:m:s"] = df.apply(
lambda x: strfdelta(
x["datetime2"] - x["datetime1"],
"{hours:02d}:{minutes:02d}:{seconds:02d}",
)
if pd.notna(x["datetime1"]) and pd.notna(x["datetime2"])
else np.nan,
axis=1,
)
df["Compared with datetime.now()"] = df.apply(
lambda x: strfdelta(
pd.Timestamp.now() - x["datetime1"],
"{hours:02d}:{minutes:02d}:{seconds:02d}",
)
if pd.notna(x["datetime1"]) & pd.isna(x["datetime2"])
else np.nan,
axis=1,
)
print(df)

打印：

datetime1           datetime2 Difference in H:m:s Compared with datetime.now()
0 2021-05-09 19:52:14 2021-05-09 20:52:14            01:00:00                          NaN
1 2021-05-09 19:52:14 2021-05-09 21:52:14            02:00:00                          NaN
2                 NaT                 NaT                 NaN                          NaN
3 2021-05-09 16:30:14                 NaT                 NaN                     03:00:20
4                 NaT                 NaT                 NaN                          NaN
5 2021-05-09 12:30:14 2021-05-09 14:30:14            02:00:00                          NaN