如何计算DataFrame中Created与Resolved列的时间差？

我有一个包含3列的DataFrame：Created、Resolved、Issue Type。想要计算Resolved与Created之间的时间差（天、小时、分钟和秒），但执行计算时始终报错，需要协助清洗数据并解决问题。

示例数据说明：Created和Resolved列均为带日期时间的字符串格式（如YYYY-MM-DD HH:MM:SS）。

错误信息 #

TypeError                                 Traceback (most recent call last)
~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/array_ops.py in _na_arithmetic_op(left, right, op, is_cmp)
    162     try:
--> 163         result = func(left, right)
    164     except TypeError:

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/computation/expressions.py in evaluate(op, a, b, use_numexpr)
    238             # error: "None" not callable
--> 239             return _evaluate(op, op_str, a, b)  # type: ignore[misc]
    240     return _evaluate_standard(op, op_str, a, b)

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/computation/expressions.py in _evaluate_numexpr(op, op_str, a, b)
    127     if result is None:
--> 128         result = _evaluate_standard(op, op_str, a, b)
    129

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/computation/expressions.py in _evaluate_standard(op, op_str, a, b)
     68         _store_test_result(False)
---> 69     return op(a, b)
     70

TypeError: unsupported operand type(s) for -: 'str' and 'str'

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)
/var/folders/k8/_5616sh16zs5g_n08g2sxk640000gp/T/ipykernel_13729/2306751620.py in <module>
----> 1 Time = jiraDump['Resolved'] - jiraDump['Created']

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/common.py in new_method(self, other)
     68         other = item_from_zerodim(other)
     69
---> 70         return method(self, other)
     71
     72     return new_method

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/arraylike.py in __sub__(self, other)
    106     @unpack_zerodim_and_defer("__sub__")
    107     def __sub__(self, other):
--> 108         return self._arith_method(other, operator.sub)
    109
    110     @unpack_zerodim_and_defer("__rsub__")

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/series.py in _arith_method(self, other, op)
   5637     def _arith_method(self, other, op):
   5638         self, other = ops.align_method_SERIES(self, other)
-> 5639         return base.IndexOpsMixin._arith_method(self, other, op)
   5640
   5641

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/base.py in _arith_method(self, other, op)
   1293
   1294         with np.errstate(all="ignore"):
-> 1295             result = ops.arithmetic_op(lvalues, rvalues, op)
   1296
   1297         return self._construct_result(result, name=res_name)

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/array_ops.py in arithmetic_op(left, right, op)
    220         _bool_arith_check(op, left, right)
    221
-> 222         res_values = _na_arithmetic_op(left, right, op)
    223
    224     return res_values

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/array_ops.py in _na_arithmetic_op(left, right, op, is_cmp)
    168             # Don't do this for comparisons, as that will handle complex numbers
    169             #  incorrectly, see GH#32047
--> 170             result = _masked_arith_op(left, right, op)
    171         else:
    172             raise

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/array_ops.py in _masked_arith_op(x, y, op)
    106         # See GH#5284, GH#5035, GH#19448 for historical reference
    107         if mask.any():
--> 108             result[mask] = op(xrav[mask], yrav[mask])
    109
    110     else:

TypeError: unsupported operand type(s) for -: 'str' and 'str'

错误核心原因：Created和Resolved列是字符串类型（str），无法直接执行减法运算。

解决方案 #

1. 检查数据类型 #

先确认列的类型，执行以下代码：

print(df.dtypes)

输出中Created和Resolved应为object类型（即字符串）。

2. 转换为日期时间类型 #

使用pd.to_datetime()将两列转换为pandas的日期时间类型，同时处理无效值：

import pandas as pd

# 转换列类型，errors='coerce'会把无法解析的字符串转为NaT（时间类型的缺失值）
df['Created'] = pd.to_datetime(df['Created'], errors='coerce')
df['Resolved'] = pd.to_datetime(df['Resolved'], errors='coerce')

3. 处理缺失值 #

转换后可能出现NaT，可以选择删除包含缺失值的行（根据业务需求调整）：

# 删除Created或Resolved为NaT的行
df = df.dropna(subset=['Created', 'Resolved'])

4. 计算时间差并提取分量 #

现在可以直接计算时间差，并提取天、小时、分钟、秒：

# 计算时间差，得到Timedelta类型
df['Time_Delta'] = df['Resolved'] - df['Created']

# 提取各时间分量
df['Days'] = df['Time_Delta'].dt.days
df['Hours'] = df['Time_Delta'].dt.seconds // 3600
df['Minutes'] = (df['Time_Delta'].dt.seconds % 3600) // 60
df['Seconds'] = df['Time_Delta'].dt.seconds % 60

5. 验证结果 #

查看转换后的列类型和计算结果：

print(df[['Created', 'Resolved', 'Days', 'Hours', 'Minutes', 'Seconds']].head())

内容的提问来源于stack exchange，提问作者Bem