BUG: cannot locate multiIndex column when NaT is in column · Issue #60388 · pandas-dev/pandas (original) (raw)

Pandas version checks

Reproducible Example

import pandas as pd

from datetime import datetime

data = dict(Date=[datetime(2024, 11, 1), datetime(2024, 11, 1), datetime(2024, 11, 2), datetime(2024, 11, 2)], sub=['a', 'b', 'c', 'd'], value1=[1,2,3,4], value2=[5,6,7,8]) df = pd.DataFrame(data) pivot_table = df.pivot(index='sub', columns='Date', values=['value1', 'value2'])

df2 = pivot_table.reset_index()

Checked following ways to locate column, but all failed

df2[df2.columns[0]]

df2.loc[:, df2.columns[0]]

Issue Description

Hi,

I was transforming some dataframes and found that when there is NaT in multiIndex columns, I cannot locate that column but got keyError instead. The stack is below:

`
Traceback (most recent call last):
File "/Users/meg/opt/anaconda3/envs/py311/lib/python3.11/site-packages/pandas/core/indexes/multi.py", line 3053, in get_loc
return self._engine.get_loc(key)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "index.pyx", line 776, in pandas._libs.index.BaseMultiIndexCodesEngine.get_loc
File "index.pyx", line 167, in pandas._libs.index.IndexEngine.get_loc
File "index.pyx", line 196, in pandas._libs.index.IndexEngine.get_loc
File "pandas/_libs/hashtable_class_helper.pxi", line 2152, in pandas._libs.hashtable.UInt64HashTable.get_item
File "pandas/_libs/hashtable_class_helper.pxi", line 2176, in pandas._libs.hashtable.UInt64HashTable.get_item
KeyError: 33

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/Users/meg/codes/leetcode/testpandas.py", line 11, in
df2[df2.columns[0]]
~~~^^^^^^^^^^^^^^^^
File "/Users/meg/opt/anaconda3/envs/py311/lib/python3.11/site-packages/pandas/core/frame.py", line 4101, in getitem
return self._getitem_multilevel(key)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/meg/opt/anaconda3/envs/py311/lib/python3.11/site-packages/pandas/core/frame.py", line 4159, in _getitem_multilevel
loc = self.columns.get_loc(key)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/meg/opt/anaconda3/envs/py311/lib/python3.11/site-packages/pandas/core/indexes/multi.py", line 3055, in get_loc
raise KeyError(key) from err
KeyError: ('sub', NaT)
`

Expected Behavior

pandas should return target column without error.

Installed Versions

INSTALLED VERSIONS

commit : 0691c5c
python : 3.11.10
python-bits : 64
OS : Darwin
OS-release : 24.1.0
Version : Darwin Kernel Version 24.1.0: Thu Oct 10 21:05:14 PDT 2024; root:xnu-11215.41.3~2/RELEASE_ARM64_T8103
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.3
numpy : 2.1.3
pytz : 2024.2
dateutil : 2.9.0.post0
pip : 24.2
Cython : None
sphinx : None
IPython : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : None
blosc : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : None
lxml.etree : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
psycopg2 : None
pymysql : None
pyarrow : None
pyreadstat : None
pytest : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlsxwriter : None
zstandard : None
tzdata : 2024.2
qtpy : None
pyqt5 : None