API: indexing DatetimeIndex with date object

[Macfly]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

down load this file
bug.xlsx

or create an excel file with the follwoing:

col1	8/21/2020 12:00:00 AM
value	10.1

then run the following code

    import pandas as pd
    import datetime as dt

    df = pd.read_excel("bug.xlsx", index_col=0)
    toto = df[dt.date(2020, 8, 21)]

Problem description

I have this error on 1.1.0 and 1.1.1:

  File "C:/Users/snoof/PycharmProjects/palms_db_script/palms_db.py", line 58, in <module>
    toto = df[dt.date(2020, 8, 21)]
  File "C:\Users\snoof\PycharmProjects\palms_db_script\venv\lib\site-packages\pandas\core\frame.py", line 2899, in __getitem__
    indexer = self.columns.get_loc(key)
  File "C:\Users\snoof\PycharmProjects\palms_db_script\venv\lib\site-packages\pandas\core\indexes\datetimes.py", line 622, in get_loc
    raise KeyError(key)
KeyError: datetime.date(2020, 8, 21)

Expected Output

no error, with pandas 1.0.5, it finds the element.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : f2ca0a2
python : 3.7.3.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19041
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.19.1
pytz : 2020.1
dateutil : 2.8.1
pip : 20.1.1
setuptools : 49.1.0
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 : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : 1.2.0
xlwt : None
numba : Non

[jbrockmendel]

The issue is unrelated to Excel, just in how to index with a DatetimeIndex.

Minimal reproducer:

df = pd.DataFrame([[10.1]], columns=pd.DatetimeIndex(["8/21/2020 12:00:00 AM"]))
key = df.columns[0].date()

>>> df[date]
KeyError: datetime.date(2020, 8, 21)

In general, you should use datetime or Timestamp objects when indexing on a DatetimeIndex, as in general date objects are not supported (we are not as consistent about this as we'd like to be)

[simonjayhawkins]

In general, you should use datetime or Timestamp objects when indexing on a DatetimeIndex, as in general date objects are not supported (we are not as consistent about this as we'd like to be)

since this was working in 1.0.5 will mark as regression

>>> pd.__version__
'1.0.5'
>>> df = pd.DataFrame([[10.1]], columns=pd.DatetimeIndex(["8/21/2020 12:00:00 AM"]))
>>> key = df.columns[0].date()
>>>
>>> df[key]
0    10.1
Name: 2020-08-21 00:00:00, dtype: float64
>>>

[simonjayhawkins]

moved off 1.1.2 milestone (scheduled for this week) as no PRs to fix in the pipeline

[simonjayhawkins]

first bad commit: [9b0ef5d] refactor DTI.get_loc (#31023)

https://github.com/simonjayhawkins/pandas/runs/1082023137?check_suite_focus=true

see also #35466

[simonjayhawkins]

moved off 1.1.3 milestone (overdue) as no PRs to fix in the pipeline

[seth-p]

Any chance of this getting fixed?

[TomAugspurger]

#35478 is perhaps addressing it, though it's not clear that the current behavior is incorrect. IMO, we __getitem__ should follow the semantics of dict.__getitem__, where date objects aren't treated as datetime objects at midnight.

This is complicated a bit by partial string indexing, but I still think there's reason to be stricter here, given the ambiguity of:

In [6]: s = pd.Series([1, 2], index=pd.DatetimeIndex(['2000-01-01 00:00:00', '2000-01-01 00:00:01']))

In [7]: s[datetime.date(2000, 1, 1)]

Does that behave like a datetime at midnight, selecting just the first row? Or is it similar to partial string indexing, selecting all rows on that day regardless of the time?

[seth-p]

My thoughts:

1. s[datetime.date(YYYY, MM, DD)] should behave the same as s['YYYY-MM-DD']. As for what that behavior should be when the DatetimeIndex contains non-midnight timestamps, I don't know.
2. In my case (which I suspect is quite common) the DatetimeIndex consists solely of 'YYYY-MM-DD 00:00:00' timestamps, so the ambiguity doesn't arise.

By the way, note that s[datetime.date(YYYY, MM, DD):datetime.date(YYYY, MM, DD)] does work. So clearly there is already some (implicit?) logic for interpreting datetime.date objects in a DatetimeIndex. Interestingly, there appears to be an inconsistency betwee [11] and [12]:

In [9]: s = pd.Series([1, 2], index=pd.DatetimeIndex(['2000-01-01 00:00:00', '2000-01-01 00:00:01']))

In [10]: s['2000-01-01']
Out[10]: 
2000-01-01 00:00:00    1
2000-01-01 00:00:01    2
dtype: int64

In [11]: s['2000-01-01':'2000-01-01']
Out[11]: 
2000-01-01 00:00:00    1
2000-01-01 00:00:01    2
dtype: int64

In [12]: s[dt.date(2000, 1, 1):dt.date(2000, 1, 1)]
Out[12]: 
2000-01-01    1
dtype: int64

[jbrockmendel]

By the way, note that s[datetime.date(YYYY, MM, DD):datetime.date(YYYY, MM, DD)] does work.

This is incorrect as is, should be deprecated.

[simonjayhawkins]

moved off 1.1.4 milestone (scheduled for release tomorrow) as no PRs to fix in the pipeline

[davidia]

Hello, Indexing DateTime index by date as above has worked forever and we have hundreds of loc that depend on this behaviour. Would you be open to a PR that promotes date to datetime in get_loc?

        """
        Get integer location for requested label

        Returns
        -------
        loc : int
        """
        if not is_scalar(key):
            raise InvalidIndexError(key)

        orig_key = key
        if is_valid_nat_for_dtype(key, self.dtype):
            key = NaT

        if isinstance(key, date):
            # GH35830
            key = datetime.combine(key, time.min)

        if isinstance(key, self._data._recognized_scalars):
            # needed to localize naive datetimes
            self._deprecate_mismatched_indexing(key)
            key = self._maybe_cast_for_get_loc(key)

[davidia]

@TomAugspurger that's a fair point but the problem here is that this behaviour has been around for ever and is likely relied upon by many people using daily data, a very common use-case. I don't unintentionally breaking this without warning is in the spirit of the contribution guidelines.

[TomAugspurger]

IIRC we were inconsistent earlier. @jbrockmendel would know best.