含非日期值的DataFrame列转datetime报错，求非循环解决方案

问题背景 #

我有一个DataFrame，其中grn_date列包含部分datetime值和部分通过df['grn_date'].fillna('-', inplace=True)填充的"-"。执行以下代码后触发错误：

# Format the 'date' column to match the rest of the dates
df['grn_date'] = df['grn_date'].dt.strftime('%Y-%m-%d %H:%M:%S.%f+00:00')

# Calculate the "Cycle time" by subtracting 'grn_date' from 'date'
df['allot_date'] = pd.to_datetime(df['allot_date'])  # Ensure 'grn_date' is in datetime format
df['grn_date'] = pd.to_datetime(df['grn_date'])  # Ensure 'date' is in datetime format

# Calculate the difference and store it in a new column 'Cycle time'
df['Cycle time'] = (df['grn_date'] - df['allot_date']).dt.days

df['grn_date'] = pd.to_datetime(df['grn_date']).dt.strftime('%d-%m-%Y')
df['allot_date'] = pd.to_datetime(df['allot_date']).dt.strftime('%d-%m-%Y')

错误栈 #

Traceback (most recent call last):
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/django/views/generic/base.py", line 71, in view
    return self.dispatch(request, *args, **kwargs)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/rest_framework/views.py", line 505, in dispatch
    response = self.handle_exception(exc)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/rest_framework/views.py", line 465, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/rest_framework/views.py", line 476, in raise_uncaught_exception
    raise exc
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/rest_framework/views.py", line 502, in dispatch
    response = handler(request, *args, **kwargs)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend copy/reports/views.py", line 1421, in get
    df['grn_date'] = df['grn_date'].dt.strftime('%Y-%m-%d %H:%M:%S.%f+00:00')
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/pandas/core/generic.py", line 5575, in __getattr__
    return object.__getattribute__(self, name)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/pandas/core/accessor.py", line 182, in __get__
    accessor_obj = self._accessor(obj)
  File "/Users/rahulsharma/PycharmProjects/Trakkia-Backend/venv/lib/python3.8/site-packages/pandas/core/indexes/accessors.py", line 509, in __new__
    raise AttributeError("Can only use .dt accessor with datetimelike values")
AttributeError: Can only use .dt accessor with datetimelike values
[30/Oct/2023 09:09:16] "GET /rfid-reportsdownload-1/ HTTP/1.1" 500 17740

原因分析 #

错误根源在于：grn_date列中混合了字符串"-"和datetime值，导致整个列的类型是object而非datetime64。直接调用.dt.strftime时，Pandas无法识别非datetime类型的元素，因此抛出异常。

最优解决方案（无需循环/try-except） #

利用Pandas的矢量化操作批量处理，比逐行循环高效得多：

1. 先统一转换为datetime类型，处理无效值
   使用pd.to_datetime的errors='coerce'参数，将无法转换的"-"转为NaT（Not a Time），确保列类型为datetime64：

   df['grn_date'] = pd.to_datetime(df['grn_date'], errors='coerce')
   df['allot_date'] = pd.to_datetime(df['allot_date'], errors='coerce')
   

2. 计算Cycle Time
   直接基于datetime列计算日期差，NaT对应的结果会自动转为NaN：

   df['Cycle time'] = (df['grn_date'] - df['allot_date']).dt.days
   

3. 格式化日期为目标格式，保留占位符
   对datetime列调用.dt.strftime，NaT会转为NaN，再用fillna('-')转回占位符：

   # 第一步要求的高精度格式
   df['grn_date'] = df['grn_date'].dt.strftime('%Y-%m-%d %H:%M:%S.%f+00:00').fillna('-')
   # 临时转回datetime以便最终格式转换
   grn_date_temp = pd.to_datetime(df['grn_date'], errors='coerce')
   # 最终显示格式
   df['grn_date'] = grn_date_temp.dt.strftime('%d-%m-%Y').fillna('-')
   df['allot_date'] = df['allot_date'].dt.strftime('%d-%m-%Y').fillna('-')
   

整合后的完整代码 #

# 转换日期列，处理无效值
df['grn_date'] = pd.to_datetime(df['grn_date'], errors='coerce')
df['allot_date'] = pd.to_datetime(df['allot_date'], errors='coerce')

# 计算Cycle time
df['Cycle time'] = (df['grn_date'] - df['allot_date']).dt.days

# 按流程格式化日期
df['grn_date'] = df['grn_date'].dt.strftime('%Y-%m-%d %H:%M:%S.%f+00:00').fillna('-')
grn_date_temp = pd.to_datetime(df['grn_date'], errors='coerce')
df['grn_date'] = grn_date_temp.dt.strftime('%d-%m-%Y').fillna('-')
df['allot_date'] = df['allot_date'].dt.strftime('%d-%m-%Y').fillna('-')

为什么不用循环？ #

Pandas的矢量化操作基于C语言实现，性能比Python级别的循环/try-except高几个数量级，尤其是处理大数据集时差距明显。同时代码更简洁、易维护，避免了逐行处理的冗余逻辑。

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