BUG: read_csv with custom date parser and na_filter=True results in ValueError

[smith1401]

import numpy as np
import pandas as pd
from io import StringIO

def __custom_date_parser(time):
    time_temp = time.astype(np.float).astype(np.int) # convert float seconds to int type
    return pd.to_timedelta(time_temp, unit='s')

testdata = StringIO("""time    e   n   h
41047.00	-98573.7297	871458.0640	389.0089
41048.00	-98573.7299	871458.0640	389.0089
41049.00	-98573.7300	871458.0642	389.0088
41050.00	-98573.7299	871458.0643	389.0088
41051.00	-98573.7302	871458.0640	389.0086
    """)

df = pd.read_csv(testdata, delim_whitespace=True, parse_dates=True, date_parser=__custom_date_parser, index_col='time')

I noticed this problem when I executed a piece of old code which has worked before (a few months ago). Normally this code would parse a text file with GPS seconds of week as time and convert it to a TimeDeltaIndex. Now when I execute this, it results in a ValueError: unit abbreviation w/o a number. (Full stack trace below) I tracked it down to the default option na_filter=True in pd.read_csv. When i set it to False everything is working. With a bit of digging I think i found the source of the error in algorithms.py -> _ensure_data -> line 142.

    # datetimelike
    vals_dtype = getattr(values, "dtype", None)
    if needs_i8_conversion(vals_dtype) or needs_i8_conversion(dtype):
        if is_period_dtype(vals_dtype) or is_period_dtype(dtype):
            from pandas import PeriodIndex

            values = PeriodIndex(values)
            dtype = values.dtype
        elif is_timedelta64_dtype(vals_dtype) or is_timedelta64_dtype(dtype):
            from pandas import TimedeltaIndex

            values = TimedeltaIndex(values)  #This is line 142
            dtype = values.dtype
        else:
            # Datetime
            if values.ndim > 1 and is_datetime64_ns_dtype(vals_dtype):
                # Avoid calling the DatetimeIndex constructor as it is 1D only
                # Note: this is reached by DataFrame.rank calls GH#27027
                # TODO(EA2D): special case not needed with 2D EAs
                asi8 = values.view("i8")
                dtype = values.dtype
                return asi8, dtype

            from pandas import DatetimeIndex

            values = DatetimeIndex(values)
            dtype = values.dtype

Here the function tries to parse values as TimeDeltaIndex, but values is ['' 'n/a' '-nan' '#N/A' '1.#QNAN' 'nan' '#NA' 'NaN' '-1.#QNAN' '#N/A N/A', '-NaN' 'N/A' 'NULL' '' 'null' '1.#IND' 'NA' '-1.#IND'] in this case. It executes this if statement, because is_timedelta64_dtype(dtype) is true in this case. I can't believe that this is expected behaviour, as it has worked before.

Traceback (most recent call last):
  File "...\lib\site-packages\pandas\io\parsers.py", line 458, in _read
    data = parser.read(nrows)
  File "...\lib\site-packages\pandas\io\parsers.py", line 1186, in read
    ret = self._engine.read(nrows)
  File "...\lib\site-packages\pandas\io\parsers.py", line 2221, in read
    index, names = self._make_index(data, alldata, names)
  File "...\lib\site-packages\pandas\io\parsers.py", line 1667, in _make_index
    index = self._agg_index(index)
  File "...\lib\site-packages\pandas\io\parsers.py", line 1760, in _agg_index
    arr, _ = self._infer_types(arr, col_na_values | col_na_fvalues)
  File "...\lib\site-packages\pandas\io\parsers.py", line 1861, in _infer_types
    mask = algorithms.isin(values, list(na_values))
  File "...\lib\site-packages\pandas\core\algorithms.py", line 433, in isin
    values, _ = _ensure_data(values, dtype=dtype)
  File "...\lib\site-packages\pandas\core\algorithms.py", line 142, in _ensure_data
    values = TimedeltaIndex(values)
  File "...\lib\site-packages\pandas\core\indexes\timedeltas.py", line 157, in __new__
    data, freq=freq, unit=unit, dtype=dtype, copy=copy
  File "...\lib\site-packages\pandas\core\arrays\timedeltas.py", line 216, in _from_sequence
    data, inferred_freq = sequence_to_td64ns(data, copy=copy, unit=unit)
  File "...\lib\site-packages\pandas\core\arrays\timedeltas.py", line 930, in sequence_to_td64ns
    data = objects_to_td64ns(data, unit=unit, errors=errors)
  File "...\lib\site-packages\pandas\core\arrays\timedeltas.py", line 1040, in objects_to_td64ns
    result = array_to_timedelta64(values, unit=unit, errors=errors)
  File "pandas\_libs\tslibs\timedeltas.pyx", line 273, in pandas._libs.tslibs.timedeltas.array_to_timedelta64
  File "pandas\_libs\tslibs\timedeltas.pyx", line 268, in pandas._libs.tslibs.timedeltas.array_to_timedelta64
  File "pandas\_libs\tslibs\timedeltas.pyx", line 215, in pandas._libs.tslibs.timedeltas.convert_to_timedelta64
  File "pandas\_libs\tslibs\timedeltas.pyx", line 428, in pandas._libs.tslibs.timedeltas.parse_timedelta_string
ValueError: unit abbreviation w/o a number
python-BaseException

INSTALLED VERSIONS

commit : f2ca0a2
python : 3.7.9.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.18362
machine : AMD64
processor : Intel64 Family 6 Model 94 Stepping 3, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.None
pandas : 1.1.1
numpy : 1.18.1
pytz : 2020.1
dateutil : 2.8.1
pip : 20.2.2
setuptools : 49.6.0.post20200814
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : 3.1.2
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pyxlsb : None
s3fs : None
scipy : 1.4.1
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[TomAugspurger]

Thanks for the report. I get a different exception on master

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.parse_timedelta_unit()
    500     try:
--> 501         return timedelta_abbrevs[unit.lower()]
    502     except (KeyError, AttributeError):

KeyError: '#qnan'

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.array_to_timedelta64()
    262             else:
--> 263                 result[i] = parse_timedelta_string(values[i])
    264     except (TypeError, ValueError):

~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.parse_timedelta_string()
    424         if len(number):
--> 425             r = timedelta_from_spec(number, frac, unit)
    426             result += timedelta_as_neg(r, neg)

~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.timedelta_from_spec()
    472             unit = 'm'
--> 473         unit = parse_timedelta_unit(unit)
    474     except KeyError:

~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.parse_timedelta_unit()
    502     except (KeyError, AttributeError):
--> 503         raise ValueError(f"invalid unit abbreviation: {unit}")
    504

ValueError: invalid unit abbreviation: #QNAN

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.parse_timedelta_unit()
    500     try:
--> 501         return timedelta_abbrevs[unit.lower()]
    502     except (KeyError, AttributeError):

KeyError: '#qnan'

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
<ipython-input-25-1b8638be91b4> in <module>
     15     """)
     16
---> 17 df = pd.read_csv(testdata, delim_whitespace=True, parse_dates=True, date_parser=__custom_date_parser, index_col='time')

~/sandbox/pandas/pandas/io/parsers.py 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, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options)
    687     )
    688
--> 689     return _read(filepath_or_buffer, kwds)
    690
    691

~/sandbox/pandas/pandas/io/parsers.py in _read(filepath_or_buffer, kwds)
    457
    458     try:
--> 459         data = parser.read(nrows)
    460     finally:
    461         parser.close()

~/sandbox/pandas/pandas/io/parsers.py in read(self, nrows)
   1187     def read(self, nrows=None):
   1188         nrows = _validate_integer("nrows", nrows)
-> 1189         ret = self._engine.read(nrows)
   1190
   1191         # May alter columns / col_dict

~/sandbox/pandas/pandas/io/parsers.py in read(self, nrows)
   2220
   2221             names, data = self._do_date_conversions(names, data)
-> 2222             index, names = self._make_index(data, alldata, names)
   2223
   2224         # maybe create a mi on the columns

~/sandbox/pandas/pandas/io/parsers.py in _make_index(self, data, alldata, columns, indexnamerow)
   1668         elif not self._has_complex_date_col:
   1669             index = self._get_simple_index(alldata, columns)
-> 1670             index = self._agg_index(index)
   1671         elif self._has_complex_date_col:
   1672             if not self._name_processed:

~/sandbox/pandas/pandas/io/parsers.py in _agg_index(self, index, try_parse_dates)
   1761                     )
   1762
-> 1763             arr, _ = self._infer_types(arr, col_na_values | col_na_fvalues)
   1764             arrays.append(arr)
   1765

~/sandbox/pandas/pandas/io/parsers.py in _infer_types(self, values, na_values, try_num_bool)
   1862         na_count = 0
   1863         if issubclass(values.dtype.type, (np.number, np.bool_)):
-> 1864             mask = algorithms.isin(values, list(na_values))
   1865             na_count = mask.sum()
   1866             if na_count > 0:

~/sandbox/pandas/pandas/core/algorithms.py in isin(comps, values)
    432
    433     comps, dtype = _ensure_data(comps)
--> 434     values, _ = _ensure_data(values, dtype=dtype)
    435
    436     # faster for larger cases to use np.in1d

~/sandbox/pandas/pandas/core/algorithms.py in _ensure_data(values, dtype)
    140             from pandas import TimedeltaIndex
    141
--> 142             values = TimedeltaIndex(values)
    143             dtype = values.dtype
    144         else:

~/sandbox/pandas/pandas/core/indexes/timedeltas.py in __new__(cls, data, unit, freq, closed, dtype, copy, name)
    154         # - Cases checked above all return/raise before reaching here - #
    155
--> 156         tdarr = TimedeltaArray._from_sequence(
    157             data, freq=freq, unit=unit, dtype=dtype, copy=copy
    158         )

~/sandbox/pandas/pandas/core/arrays/timedeltas.py in _from_sequence(cls, data, dtype, copy, freq, unit)
    214         freq, freq_infer = dtl.maybe_infer_freq(freq)
    215
--> 216         data, inferred_freq = sequence_to_td64ns(data, copy=copy, unit=unit)
    217         freq, freq_infer = dtl.validate_inferred_freq(freq, inferred_freq, freq_infer)
    218         if explicit_none:

~/sandbox/pandas/pandas/core/arrays/timedeltas.py in sequence_to_td64ns(data, copy, unit, errors)
    928     if is_object_dtype(data.dtype) or is_string_dtype(data.dtype):
    929         # no need to make a copy, need to convert if string-dtyped
--> 930         data = objects_to_td64ns(data, unit=unit, errors=errors)
    931         copy = False
    932

~/sandbox/pandas/pandas/core/arrays/timedeltas.py in objects_to_td64ns(data, unit, errors)
   1038     values = np.array(data, dtype=np.object_, copy=False)
   1039
-> 1040     result = array_to_timedelta64(values, unit=unit, errors=errors)
   1041     return result.view("timedelta64[ns]")
   1042

~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.array_to_timedelta64()
    271                     result[i] = NPY_NAT
    272                 else:
--> 273                     raise
    274
    275     return iresult.base  # .base to access underlying np.ndarray

~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.array_to_timedelta64()
    266         for i in range(n):
    267             try:
--> 268                 result[i] = convert_to_timedelta64(values[i], parsed_unit)
    269             except ValueError:
    270                 if errors == 'coerce':

~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.convert_to_timedelta64()
    213             ts = parse_iso_format_string(ts)
    214         else:
--> 215             ts = parse_timedelta_string(ts)
    216         ts = np.timedelta64(ts)
    217     elif is_tick_object(ts):

~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.parse_timedelta_string()
    423     elif len(unit):
    424         if len(number):
--> 425             r = timedelta_from_spec(number, frac, unit)
    426             result += timedelta_as_neg(r, neg)
    427         else:

~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.timedelta_from_spec()
    471             # not month
    472             unit = 'm'
--> 473         unit = parse_timedelta_unit(unit)
    474     except KeyError:
    475         raise ValueError(f"invalid abbreviation: {unit}")

~/sandbox/pandas/pandas/_libs/tslibs/timedeltas.pyx in pandas._libs.tslibs.timedeltas.parse_timedelta_unit()
    501         return timedelta_abbrevs[unit.lower()]
    502     except (KeyError, AttributeError):
--> 503         raise ValueError(f"invalid unit abbreviation: {unit}")
    504
    505 # ----------------------------------------------------------------------

ValueError: invalid unit abbreviation: #QNAN

Can you investigate where things changed?

[phofl]

This works now on master. Could need a test

[BrianKissell]

take