pd.to_datetime() throws if caching is on with Null-like arguments

[realead]

Code Sample, a copy-pastable example if possible

import pandas as pd
result = pd.to_datetime([pd.NaT, None], cache=True)

Problem description

It results in error:

...
~/anaconda3/lib/python3.6/site-packages/pandas/core/indexes/base.py in get_indexer(self, target, method, limit, tolerance)
3242
3243 if not self.is_unique:
-> 3244 raise InvalidIndexError('Reindexing only valid with uniquely'
3245 ' valued Index objects')
3246

InvalidIndexError: Reindexing only valid with uniquely valued Index objects

Expected Output

The same as result = pd.to_datetime([pd.NaT, None],cache=False):

DatetimeIndex(['NaT', 'NaT'], dtype='datetime64[ns]', freq=None)

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.6.2.final.0 python-bits: 64 OS: Linux OS-release: 4.4.0-53-generic machine: x86_64 processor: x86_64 byteorder: little LC_ALL: None LANG: en_US.UTF-8 LOCALE: en_US.UTF-8

pandas: 0.23.4
pytest: 3.2.1
pip: 10.0.1
setuptools: 36.5.0.post20170921
Cython: 0.28.3
numpy: 1.13.1
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 6.1.0
sphinx: 1.6.3
patsy: 0.4.1
dateutil: 2.6.1
pytz: 2017.2
blosc: None
bottleneck: 1.2.1
tables: 3.4.2
numexpr: 2.6.2
feather: None
matplotlib: 2.0.2
openpyxl: 2.4.8
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 0.9.8
lxml: 3.8.0
bs4: 4.6.0
html5lib: 0.9999999
sqlalchemy: 1.1.13
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: 0.1.3
fastparquet: None
pandas_gbq: None
pandas_datareader: None
[paste the output of pd.show_versions() here below this line]

[jorisvandenbossche]

Thanks for the report! Looking into it / a fix is certainly welcome.

[mroeschke]

We have this test:

pandas/pandas/tests/indexes/datetimes/test_tools.py

Line 505 in eb0ac54

def test_datetime_bool(self, cache):

Where currently this test with cache=True is skipped because of #18111 which I think is similar to what you found.

[realead]

In the end it comes down to the following difference between unique and infer_dtype:

While unique mangles pd.NaT and np.nan but not None:

            # `val is None` below is exception to prevent mangling of None and
            # other NA values; note however that other NA values (ex: pd.NaT
            # and np.nan) will still get mangled, so many not be a permanent
            # solution; see GH 20866
           if not checknull(val) or val is None:
                  ...

infer_dtype magles them all:

        if val is None or util.is_nan(val):
            pass
        elif val is NaT:
            seen_pdnat = True
        else:
            ....

I ask myself, whether this infer_dtype-business is really necessary and maybe can be avoided.

This issue kind of blocks PR #22296, because PR #22296 fixes the mangling of pd.NaT and np.nan in unique, and thus adding another problem similar to [None, pd.NaT]: i.e. [np.nan, pd.NaT] - and this case is covered in the test suite.

[realead]

This test should probably pass once it is fixed:

    def test_NA_values_with_cache(self):
        # GH 22305
        na_values = [None, np.nan, pd.NaT]
        # check pairwise, that no pair of na values
        # is mangled
        for f in na_values:
            for s in na_values:
                if f is not s:  # otherwise not unique
                    expected = Index([NaT, NaT], dtype='datetime64[ns]')
                    result = to_datetime([f, s], cache=True)
                    tm.assert_index_equal(result, expected)

[blinkseb]

Hello guys,

It looks like this bug is back in business in the latest version, but a bit harder to trigger:

pandas versions

>>> pd.show_versions()

INSTALLED VERSIONS

commit : f2ca0a2
python : 3.8.5.final.0
python-bits : 64
OS : Linux
OS-release : 5.7.15-200.fc32.x86_64
Version : #1 SMP Tue Aug 11 16:36:14 UTC 2020
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_GB.UTF-8
LOCALE : en_GB.UTF-8

pandas : 1.1.1
numpy : 1.19.1
pytz : 2020.1
dateutil : 2.8.1
pip : 20.0.2
setuptools : 46.1.3
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : 0.10.0
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : 1.3.19
tables : None
tabulate : 0.8.7
xarray : None
xlrd : None
xlwt : None
numba : None

How to reproduce:

import pandas as pd

s = pd.Series([pd.NaT] * 2000 + [None] * 2000, dtype='object')
pd.to_datetime(s)

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/sbrochet/venvs/tmp-fa372ee62ee9bef/lib64/python3.8/site-packages/pandas/core/tools/datetimes.py", line 801, in to_datetime
    result = arg.map(cache_array)
  File "/home/sbrochet/venvs/tmp-fa372ee62ee9bef/lib64/python3.8/site-packages/pandas/core/series.py", line 3970, in map
    new_values = super()._map_values(arg, na_action=na_action)
  File "/home/sbrochet/venvs/tmp-fa372ee62ee9bef/lib64/python3.8/site-packages/pandas/core/base.py", line 1131, in _map_values
    indexer = mapper.index.get_indexer(values)
  File "/home/sbrochet/venvs/tmp-fa372ee62ee9bef/lib64/python3.8/site-packages/pandas/core/indexes/base.py", line 2980, in get_indexer
    raise InvalidIndexError(
pandas.errors.InvalidIndexError: Reindexing only valid with uniquely valued Index objects

The key here is to have enough entries in the Series to trigger the caching system.

[jreback]

pls raise a new issue with the example