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

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当我在datetime1和DateTime2中都有时间戳时，你给我的解决方案非常有效。如果我有一个像下面这样的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和datetime2中没有时间戳，是否有可能在“差分”列中获取NaN，如果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