如何批量将pywintypes.datetime转为numpy.datetime64适配Pandas？

我使用win32com读取大型Excel文件数据，通过DataBody = tbl.DataBodyRange()获取数据元组，再转成numpy数组。但win32com返回的时间列数据为pywintypes.datetime类型，直接将数组转为Pandas DataFrame时触发错误：AttributeError: 'NoneType' object has no attribute 'total_seconds'。需要找到无需迭代的批量转换方法（避免遍历大数据），比如用np.where实现转换替换。

Python代码 #

import win32com.client as MyWinCOM
import numpy as np
import pandas as pd

# 打开Excel实例
xl = MyWinCOM.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
# 打开工作簿
wb = xl.Workbooks.Open('Test Excel.xlsx')
# 获取工作表对象
ws = wb.Worksheets('Sheet1')
# 获取表格对象
tbl = ws.ListObjects('Table1')
# 获取表头并转为列表
ColumnNames = tbl.HeaderRowRange()
ColumnNamesList = list(np.array(ColumnNames))

# 获取数据区域并转为numpy数组
DataBody = tbl.DataBodyRange()
DataBody_array = np.array(DataBody)

# 打印数据元组
for item in DataBody:
    print(item)
# 打印数据数组
for item in DataBody_array:
    print(item)

# 尝试转为DataFrame
Data_df = pd.DataFrame(DataBody_array, columns=ColumnNamesList)

print(Data_df)

输出结果 #

('a', pywintypes.datetime(2022, 6, 22, 0, 0, tzinfo=TimeZoneInfo('GMT Standard Time', True)), 1.0)
('b', pywintypes.datetime(2023, 10, 18, 0, 0, tzinfo=TimeZoneInfo('GMT Standard Time', True)), None)
('c', pywintypes.datetime(2022, 6, 21, 0, 0, tzinfo=TimeZoneInfo('GMT Standard Time', True)), 3.0)
['a'
 pywintypes.datetime(2022, 6, 22, 0, 0, tzinfo=TimeZoneInfo('GMT Standard Time', True))
 1.0]
['b'
 pywintypes.datetime(2023, 10, 18, 0, 0, tzinfo=TimeZoneInfo('GMT Standard Time', True))
 None]
['c'
 pywintypes.datetime(2022, 6, 21, 0, 0, tzinfo=TimeZoneInfo('GMT Standard Time', True))
 3.0]

报错栈 #

Traceback (most recent call last):
  File "***.py", line 32, in <module>
    print (Data_df)
  File "C:\Program Files\Python39\lib\site-packages\pandas\core\frame.py", line 1011, in __repr__
    return self.to_string(**repr_params)
  File "C:\Program Files\Python39\lib\site-packages\pandas\core\frame.py", line 1192, in to_string
    return fmt.DataFrameRenderer(formatter).to_string(
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\format.py", line 1128, in to_string
    string = string_formatter.to_string()
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\string.py", line 25, in to_string
    text = self._get_string_representation()
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\string.py", line 40, in _get_string_representation
    strcols = self._get_strcols()
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\string.py", line 31, in _get_strcols
    strcols = self.fmt.get_strcols()
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\format.py", line 611, in get_strcols
    strcols = self._get_strcols_without_index()
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\format.py", line 875, in _get_strcols_without_index
    fmt_values = self.format_col(i)
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\format.py", line 889, in format_col
    return format_array(
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\format.py", line 1316, in format_array
    return fmt_obj.get_result()
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\format.py", line 1347, in get_result
    fmt_values = self._format_strings()
  File "C:\Program Files\Python39\lib\site-packages\pandas\io\formats\format.py", line 1810, in _format_strings
    values = self.values.astype(object)
  File "C:\Program Files\Python39\lib\site-packages\pandas\core\arrays\datetimes.py", line 666, in astype
    return dtl.DatetimeLikeArrayMixin.astype(self, dtype, copy)
  File "C:\Program Files\Python39\lib\site-packages\pandas\core\arrays\datetimelike.py", line 415, in astype
    converted = ints_to_pydatetime(
  File "pandas\_libs\tslibs\vectorized.pyx", line 158, in pandas._libs.tslibs.vectorized.ints_to_pydatetime
  File "pandas\_libs\tslibs\timezones.pyx", line 266, in pandas._libs.tslibs.timezones.get_dst_info
AttributeError: 'NoneType' object has no attribute 'total_seconds'

解决方案 #

报错原因是pywintypes.datetime附带的TimeZoneInfo无法被Pandas正确解析，导致内部处理时出现空值调用total_seconds()的情况。以下是几种批量转换方法：

1. 使用numpy.vectorize批量转换

通过向量化函数批量处理整个数组，避免手动遍历：

import pywintypes

def convert_pywintime(dt):
    if isinstance(dt, pywintypes.datetime):
        # 转为ISO格式字符串再转numpy.datetime64
        return np.datetime64(dt.isoformat())
    return dt

# 生成向量化转换函数
vectorized_convert = np.vectorize(convert_pywintime)
# 转换数组
DataBody_array_converted = vectorized_convert(DataBody_array)
# 生成DataFrame
Data_df = pd.DataFrame(DataBody_array_converted, columns=ColumnNamesList)

2. 针对时间列单独处理（更高效）

如果已知时间列的索引，直接对该列批量转换，减少不必要的计算：

import pywintypes

# 假设第二列是时间列（索引为1）
time_col_idx = 1
# 批量转换时间列元素
DataBody_array[:, time_col_idx] = np.array([
    np.datetime64(dt.isoformat()) if isinstance(dt, pywintypes.datetime) else dt 
    for dt in DataBody_array[:, time_col_idx]
])
# 生成DataFrame
Data_df = pd.DataFrame(DataBody_array, columns=ColumnNamesList)

3. 利用Pandas内置函数转换

先创建DataFrame，再对时间列进行转换：

# 先创建DataFrame
Data_df = pd.DataFrame(DataBody_array, columns=ColumnNamesList)
# 指定时间列名称，用to_datetime自动转换
time_col_name = "你的时间列名称"
Data_df[time_col_name] = pd.to_datetime(Data_df[time_col_name], errors="ignore")

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