pandas-dev / pandas

Flexible and powerful data analysis / manipulation library for Python, providing labeled data structures similar to R data.frame objects, statistical functions, and much more
https://pandas.pydata.org
BSD 3-Clause "New" or "Revised" License
43.83k stars 18k forks source link

BUG: cannot locate multiIndex column when NaT is in column #60388

Open CraigMeng opened 20 hours ago

CraigMeng commented 20 hours ago

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

<details>

INSTALLED VERSIONS
------------------
commit                : 0691c5cf90477d3503834d983f69350f250a6ff7
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

</details>
rhshadrach commented 12 hours ago

Thanks for the report. This is similar to https://github.com/pandas-dev/pandas/issues/56366, but with NaT. So leaving this open.