Repository: pandas-dev/pandas
Pull request: TST: multi indexing when one column exclusively contains NaT(#38025)
- [X] closes #38025
- [X] tests added / passed
- [X] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit) for how to run them


Problem statement:
BUG: Setting a MultiIndex through set_index where one of the columns contains exclusively NaTs converts them to NaNs
- [X] I have checked that this issue has not already been reported.

- [X] 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.

---
```python
import pandas as pd

df1 = pd.DataFrame({
    "date": ["2015-03-22", pd.NaT],
    "a": ["a", "aa"],
    "b": [2, 3]
})

df1 = df1.set_index("date") # works as intended
print(df1)
df1 = df1.reset_index()
df1 = df1.set_index(["date", "a"]) # works as intended
print(df1)

df2 = pd.DataFrame({
    "date": [pd.NaT, pd.NaT],
    "a": ["a", "aa"],
    "b": [2, 3]
})

df2 = df2.set_index("date") # works as intended
print(df2)
df2 = df2.reset_index()
df2 = df2.set_index(["date", "a"]) # date will be converted to NaN here
print(df2)

df3 = pd.DataFrame({
    "date": [pd.NaT, pd.NaT],
    "a": ["a", "aa"],
    "b": [2, 3]
})

# working alternative, but at what cost
df3.index = pd.MultiIndex.from_frame(df3.loc[:, ["date", "a"]])
df3 = df3.drop(columns=["date", "a"]) 
print(df3)
df3.reset_index() # ValueError thrown here

```
python 3.8.3
pandas 1.1.3

This bug will not occur if the column in question contains any values other than NaT, nor if setting a single column as the index.

With df1, everything works as expected.

In df2 (identical to df1 except "date" contains exclusively pd.NaT values) when set_index is used with more than one column, the date level of the resulting MultiIndex has now been converted to NaNs.

With df3 (identical to df2), the use of set_index is circumvented and the MultiIndex is set as intended, however this now leads to another issue where attempting to use reset_index will throw "ValueError: cannot convert float NaN to integer".

Hints from issue discussion:
Hi, thanks for your report. This works on master.

```
             a  b
date             
2015-03-22   a  2
NaT         aa  3
               b
date       a    
2015-03-22 a   2
NaT        aa  3
       a  b
date       
NaT    a  2
NaT   aa  3
         b
date a    
NaT  a   2
     aa  3
         b
date a    
NaT  a   2
     aa  3

```

I am not sure if we have tests covering exactly this behavior
take

Commit message: TST: added test for multi indexing when one column exclusively contains NaT(#38025)

Patch to review:
diff --git a/pandas/tests/indexing/multiindex/test_multiindex.py b/pandas/tests/indexing/multiindex/test_multiindex.py
index 479d048d35fbd..322875adb841d 100644
--- a/pandas/tests/indexing/multiindex/test_multiindex.py
+++ b/pandas/tests/indexing/multiindex/test_multiindex.py
@@ -15,7 +15,6 @@
 
 class TestMultiIndexBasic:
     def test_multiindex_perf_warn(self):
-
         df = DataFrame(
             {
                 "jim": [0, 0, 1, 1],
@@ -47,7 +46,6 @@ def test_indexing_over_hashtable_size_cutoff(self):
         _index._SIZE_CUTOFF = old_cutoff
 
     def test_multi_nan_indexing(self):
-
         # GH 3588
         df = DataFrame(
             {
@@ -70,6 +68,26 @@ def test_multi_nan_indexing(self):
         )
         tm.assert_frame_equal(result, expected)
 
+    def test_exclusive_nat_column_indexing(self):
+        # GH 38025
+        # test multi indexing when one column exclusively contains NaT values
+        df = DataFrame(
+            {
+                "a": [pd.NaT, pd.NaT, pd.NaT, pd.NaT],
+                "b": ["C1", "C2", "C3", "C4"],
+                "c": [10, 15, np.nan, 20],
+            }
+        )
+        df = df.set_index(["a", "b"])
+        expected = DataFrame(
+            {
+                "a": [pd.NaT, pd.NaT, pd.NaT, pd.NaT],
+                "b": ["C1", "C2", "C3", "C4"],
+                "c": [10, 15, np.nan, 20],
+            }
+        )
+        tm.assert_equal(df.values, expected.values)
+
     def test_nested_tuples_duplicates(self):
         # GH#30892
 


Code review comments:
diff_hunk: @@ -70,6 +68,26 @@ def test_multi_nan_indexing(self):
         )
         tm.assert_frame_equal(result, expected)
 
+    def test_exclusive_nat_column_indexing(self):
+        # GH 38025
+        # test multi indexing when one column exclusively contains NaT values
+        df = DataFrame(
+            {
+                "a": [pd.NaT, pd.NaT, pd.NaT, pd.NaT],
+                "b": ["C1", "C2", "C3", "C4"],
+                "c": [10, 15, np.nan, 20],
+            }
+        )
+        df = df.set_index(["a", "b"])
+        expected = DataFrame(
+            {
+                "a": [pd.NaT, pd.NaT, pd.NaT, pd.NaT],
+                "b": ["C1", "C2", "C3", "C4"],
+                "c": [10, 15, np.nan, 20],
+            }
path: pandas/tests/indexing/multiindex/test_multiindex.py
text: @user1:
```suggestion
                "c": [10, 15, np.nan, 20]
            },
            index=[
                Index([pd.NaT, pd.NaT, pd.NaT, pd.NaT], name="a"),
                Index(["C1", "C2", "C3", "C4"], name="b"),
            ]            
```
This should correct it. Passes in 1.3.3 and throws an AssertionError in 1.1.3, where the bug was reported.

@author:
Thank you for your review. I fixed the previous problem. Should I still apply your suggestion?

@user1:
Yeah, as far as I can tell it should still be broken after your fix as the expected DataFrame's index is not equal to df. I made the suggestion with the dropped columns in mind before seeing your fix though so you'd have to reverse it before applying the suggestion.

@author:
But I'm asserting the values of those dataframes, not the objects. If the logic of the test is flawed, I'll change it.

@user1:
True, I was asserting the object in my mockup. However, when testing in 1.1.3 with drop=False, the following is the result:
![image](https://user-images.githubusercontent.com/28331593/134748493-4e7ff408-6ae3-4a1a-bd6d-52825f44f10d.png)
The reported bug occurs only in the index and the column remains NaT, so the test will still fail to check for the regression. I believe going with the suggestion and asserting the entire object is the correct way to go here.

@author:
I got it. Thank you for your information.

@author:
@user1 the problem with your suggestion is:

```
df:
a   b   c    
NaT C1  10.0
    C2  15.0
    C3   NaN
    C4  20.0
expected:

 a  b   a   b     c             
NaT C1 NaT  C1  10.0
    C2 NaT  C2  15.0
    C3 NaT  C3   NaN
    C4 NaT  C4  20.0

```
setting the index like that in `expected` doesn't drop the columns as `set_index` does.
by doing:
```python
        expected = expected.drop(columns=["a", "b"])
```
the problem gets solved. but I wonder if there is a better way to do this.


@user1:
If you just include "a" and "b" in the index argument without also putting them in the first argument they don't need to be dropped:
```python
expected = DataFrame(
    {
        "c": [10, 15, np.nan, 20]
    },
    index=[
        Index([pd.NaT, pd.NaT, pd.NaT, pd.NaT], name="a"),
        Index(["C1", "C2", "C3", "C4"], name="b"),
    ]
)
```