BUG: Change of behavior in casting of datetime-like types in MultiIndex

[jgmarcel]

• 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.

---

Code Sample, a copy-pastable example

[Edited to inform a much simpler example.]

import datetime
import pandas as pd

print(f"Pandas version:\t{pd.__version__}\n")

df = pd.DataFrame({'date': [datetime.date(2021, 8, 1),
                            datetime.date(2021, 8, 2),
                            datetime.date(2021, 8, 3)],
                   'ticker': ['aapl', 'goog', 'yhoo'],
                   'value': [5.63269, 4.45609, 2.74843]})

df.set_index(['date', 'ticker'], inplace=True)

print(df.index.get_level_values(0))

Output

The output below has been generated with pandas 1.3.0 or higher.

Pandas version:	1.3.0

Index([2021-08-01, 2021-08-02, 2021-08-03], dtype='object', name='date')

Expected Output

The output below has been generated with pandas 1.2.5.

Pandas version:	1.2.5

DatetimeIndex(['2021-08-01', '2021-08-02', '2021-08-03'], dtype='datetime64[ns]', name='date', freq=None)

Problem description

Starting from pandas 1.3.0, the observed behavior changed: in a MultiIndex creation, datetime.date objects are not cast to datetime64 anymore. I fail to find in the What’s new page the reason for that change of behavior. Is it by design or a bug?

Output of pd.show_versions()

INSTALLED VERSIONS
------------------
commit           : 5f648bf1706dd75a9ca0d29f26eadfbb595fe52b
python           : 3.9.6.final.0
python-bits      : 64
OS               : Darwin
OS-release       : 19.6.0
Version          : Darwin Kernel Version 19.6.0: Tue Jun 22 19:49:55 PDT 2021; root:xnu-6153.141.35~1/RELEASE_X86_64
machine          : x86_64
processor        : i386
byteorder        : little
LC_ALL           : en_US.UTF-8
LANG             : en_US.UTF-8
LOCALE           : en_US.UTF-8

pandas           : 1.3.2
numpy            : 1.21.2
pytz             : 2021.1
dateutil         : 2.8.2
pip              : 21.2.4
setuptools       : 57.4.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : 2.9.1 (dt dec pq3 ext lo64)
jinja2           : None
IPython          : 7.26.0
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
pyxlsb           : None
s3fs             : None
scipy            : None
sqlalchemy       : 1.4.22
tables           : None
tabulate         : None
xarray           : None
xlrd             : None
xlwt             : None
numba            : None

[simonjayhawkins]

Thanks @jgmarcel for the report.

first bad commit: [545a942] BUG: Index([date]).astype("category").astype(object) roundtrip (#38552)

I'll mark as a regression for now pending further investigation.

Note that the set_index followed by a reset_index still creates a datetime64[ns] column from the original object column of date objects.

cc @jbrockmendel

[jbrockmendel]

Note that the set_index followed by a reset_index still creates a datetime64[ns] column from the original object column of date objects.

#38552 was about round-tripping faithfully, so im inclined to think that reset_index should also roundtrip.

@jgmarcel support for date objects in generally is spotty. Your best bet is to use datetime or Timestamp objects

[jgmarcel]

@jgmarcel support for date objects in generally is spotty. Your best bet is to use datetime or Timestamp objects

Agreed. Thank you for the good advice. Problem is data objects are all over our code, since anytime a DATE column is read via pandas.read_sql(), it is translated to a series of date objects. Hence, casting everything to datetime or Timestamp objects would be a lot of effort… For now, we reverted to version 1.2.5, but I would like to avoid the work if possible.

[jgmarcel]

Hi guys,

Do you believe it will be possible to get this fixed on version 1.3.4? I ask so I can better plan the effort of adapting our whole code base.

Thank you.

[jreback]

@jgmarcel would likely take a community pull request

core can provide review

might be quite tricky as date have very little support

[simonjayhawkins]

changing milestone to 1.3.5