使用pandas读取CSV时遇TypeError：无法识别'datetime'数据类型

用户尝试读取大型CSV文件的代码：

header = ["SKU","STORAGE_AREA","MOVE_TYPE","ORDER_NO","ORDER_ITEM","PICK_VOL","M_UNIT","DATE"] 
d_type = {"SKU":"str","STORAGE_AREA":"str","MOVE_TYPE":"str","ORDER_NO":"category","ORDER_ITEM":"str","PICK_VOL":"int","M_UNIT":"str","DATE":"datetime"} 
product = pd.read_csv('pick_data.csv', encoding='latin-1', sep=',', index_col=False, header=None, names=header, dtype=d_type)

报错信息：

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Input In [68], in <cell line: 3>()
      1 header = ["SKU","STORAGE_AREA","MOVE_TYPE","ORDER_NO","ORDER_ITEM","PICK_VOL","M_UNIT","DATE"] 
      2 d_type = {"SKU":"str","STORAGE_AREA":"str","MOVE_TYPE":"str","ORDER_NO":"category","ORDER_ITEM":"str","PICK_VOL":"int","M_UNIT":"str","DATE":"datetime"} 
----> 3 product = pd.read_csv('pick_data.csv', encoding='latin-1', sep=',', index_col=False, header=None, names=header, dtype=d_type)

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\util\_decorators.py:311, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
    305 if len(args) > num_allow_args:
    306     warnings.warn(
    307         msg.format(arguments=arguments),
    308         FutureWarning,
    309         stacklevel=stacklevel,
    310     )
--> 311 return func(*args, **kwargs)

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers\readers.py:680, in read_csv(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, error_bad_lines, warn_bad_lines, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options)
    665 kwds_defaults = _refine_defaults_read(
    666     dialect,
    667     delimiter,
   (...)
    676     defaults={"delimiter": ","},
    677 )
    678 kwds.update(kwds_defaults)
--> 680 return _read(filepath_or_buffer, kwds)

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers\readers.py:575, in _read(filepath_or_buffer, kwds)
    572 _validate_names(kwds.get("names", None))
    574 # Create the parser.
--> 575 parser = TextFileReader(filepath_or_buffer, **kwds)
    577 if chunksize or iterator:
    578     return parser

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers\readers.py:933, in TextFileReader.__init__(self, f, engine, **kwds)
    930     self.options["has_index_names"] = kwds["has_index_names"]
    932 self.handles: IOHandles | None = None
--> 933 self._engine = self._make_engine(f, self.engine)

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers\readers.py:1235, in TextFileReader._make_engine(self, f, engine)
   1232     raise ValueError(msg)
   1234 try:
-> 1235     return mapping[engine](f, **self.options)
   1236 except Exception:
   1237     if self.handles is not None:

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers\c_parser_wrapper.py:74, in CParserWrapper.__init__(self, src, **kwds)
     64 for key in (
     65     "storage_options",
     66     "encoding",
   (...)
     70     "warn_bad_lines",
     71 ):
     72     kwds.pop(key, None)
--> 74 kwds["dtype"] = ensure_dtype_objs(kwds.get("dtype", None))
     75 self._reader = parsers.TextReader(src, **kwds)
     77 self.unnamed_cols = self._reader.unnamed_cols

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers\c_parser_wrapper.py:416, in ensure_dtype_objs(dtype)
    411 """
    412 Ensure we have either None, a dtype object, or a dictionary mapping to
    413 dtype objects.
    414 """
    415 if isinstance(dtype, dict):
--> 416     return {k: pandas_dtype(dtype[k]) for k in dtype}
    417 elif dtype is not None:
    418     return pandas_dtype(dtype)

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers\c_parser_wrapper.py:416, in <dictcomp>(.0)
    411 """
    412 Ensure we have either None, a dictionary mapping to
    413 dtype objects.
    414 """
    415 if isinstance(dtype, dict):
--> 416     return {k: pandas_dtype(dtype[k]) for k in dtype}
    417 elif dtype is not None:
    418     return pandas_dtype(dtype)

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\dtypes\common.py:1777, in pandas_dtype(dtype)
   1774 # try a numpy dtype
   1775 # raise a consistent TypeError if failed
   1776 try:
-> 1777     npdtype = np.dtype(dtype)
   1778 except SyntaxError as err:
   1779     # np.dtype uses `eval` which can raise SyntaxError
   1780     raise TypeError(f"data type '{dtype}' not understood") from err

TypeError: data type 'datetime' not understood

解决方案 #

• 问题根源：pd.read_csv的dtype参数不支持直接传入"datetime"作为类型，日期解析需要特殊处理，无法像字符串、整数那样直接指定 dtype。
• 修正代码：从d_type字典中移除"DATE":"datetime"，改用parse_dates参数指定需要解析为日期的列：

header = ["SKU","STORAGE_AREA","MOVE_TYPE","ORDER_NO","ORDER_ITEM","PICK_VOL","M_UNIT","DATE"] 
d_type = {"SKU":"str","STORAGE_AREA":"str","MOVE_TYPE":"str","ORDER_NO":"category","ORDER_ITEM":"str","PICK_VOL":"int","M_UNIT":"str"} 
product = pd.read_csv('pick_data.csv', encoding='latin-1', sep=',', index_col=False, header=None, names=header, dtype=d_type, parse_dates=["DATE"])

• 大型CSV优化：如果文件过大，可添加chunksize参数分块读取，避免内存溢出：

# 按10000行分块读取
chunk_iter = pd.read_csv('pick_data.csv', encoding='latin-1', sep=',', index_col=False, header=None, names=header, dtype=d_type, parse_dates=["DATE"], chunksize=10000)
# 合并所有块（内存允许时执行）
product = pd.concat(chunk_iter)

内容的提问来源于stack exchange，提问作者Mariejem Jessa Crisostomo