Repository: pandas-dev/pandas
Pull request: sort_index not sorting when multi-index made by different categorical types
- [ ] closes #24271
- [ ] tests added / passed
- [ ] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [ ] whatsnew entry


Problem statement:
sort_index not sorting when multi-index made by different categorical types
#### Code Sample, a copy-pastable example if possible

This is the shorter code to reproduce the problem

```python
import pandas as pd
from pandas.api.types import CategoricalDtype
number_type = CategoricalDtype(['one', 'two', 'three', 'four', 'five'], ordered=True)
day_type = CategoricalDtype(['monday', 'tuesday', 'wednesday', 'thursday', 'friday'], ordered=True)

dd = pd.DataFrame([('two', 'tuesday', 'one', 'up', 10),
                   ('one', 'wednesday', 'two', 'up', 20),
                   ('five', 'monday', 'two', 'up', 1),
                   ('three', 'tuesday', 'three', 'up', 1),
                   ('four', 'monday', 'one', 'up', 2),
                   ('one', 'friday', 'one', 'up', 2),
                   
                   ('two', 'tuesday', 'one', 'down', 10),
                   ('one', 'wednesday', 'two', 'down', 20),
                   ('five', 'monday', 'two', 'down', 1),
                   ('three', 'tuesday', 'three', 'down', 1),
                   ('four', 'monday', 'one', 'down', 2),
                   ('one', 'friday', 'one', 'down', 2),
                  ])
dd = dd.set_index([0, 1, 2, 3])
dd = dd.unstack(3)[4]


dd.index = dd.index.set_levels(dd.index.levels[0].astype(number_type), 0)
dd.index = dd.index.set_levels(dd.index.levels[1].astype(day_type), 1)
dd.index = dd.index.set_levels(dd.index.levels[2].astype(number_type), 2)

print (dd.sort_index())
```
#### Problem description

The dataframe is not sorted. I get

```
0     1         2              
five  monday    two       1   1
four  monday    one       2   2
one   friday    one       2   2
      wednesday two      20  20
three tuesday   three     1   1
two   tuesday   one      10  10
```
which is exactly what you get without any sorting.

If all the index levels have the same categorical type it seems to work.

It works if I reset the index:

```python
print(dd.reset_index().set_index([0, 1, 2]).sort_index())
```

#### Expected Output

```
0     1         2              
one   wednesday two      20  20
      friday    one       2   2
two   tuesday   one      10  10
three tuesday   three     1   1
four  monday    one       2   2
five  monday    two       1   1
```

#### Output of ``pd.show_versions()``

<details>

[paste the output of ``pd.show_versions()`` here below this line]
INSTALLED VERSIONS
------------------
commit: None
python: 3.7.0.final.0
python-bits: 64
OS: Linux
OS-release: 4.18.16-300.fc29.x86_64
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: it_IT.UTF-8
LOCALE: it_IT.UTF-8

pandas: 0.23.4
pytest: 4.0.0
pip: 18.1
setuptools: 40.5.0
Cython: None
numpy: 1.15.1
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 7.1.1
sphinx: None
patsy: None
dateutil: 2.7.5
pytz: 2018.5
blosc: None
bottleneck: 1.2.1
tables: 3.4.4
numexpr: 2.6.6
feather: None
matplotlib: 3.0.1
openpyxl: None
xlrd: 1.0.0
xlwt: 1.1.2
xlsxwriter: None
lxml: None
bs4: 4.6.3
html5lib: 1.0.1
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
</details>

Hints from issue discussion:
It seems like this might be the same issue.  I have this MWE with pandas-0.23.4

```
import pandas as pd
import numpy as np

df = pd.DataFrame({'group':['A']*6 + ['B']*6,
                   'dose':['high', 'med', 'low']*4,
                   'outcomes':np.arange(12.0)})

df.dose = pd.Categorical(df.dose, 
                         categories=['low', 'med', 'high'], 
                         ordered=True)

# dose is sorted low, med, high (works as expected)
# df.groupby('dose')['outcomes'].mean() 

df.groupby('group')['dose'].value_counts().sort_index(level=0, 
                                                       sort_remaining=True)
```

Output:
```
group  dose
A      high    2
       low     2
       med     2
B      high    2
       low     2
       med     2
Name: dose, dtype: int64
```

With or without the `sort_index` call, the inner level (`dose`) of the `MultiIndex` for the value counts appears to be sorting in lexicographic order, not in the defined `pd.Categorical` order.  Somewhere along the way, either the values lose their `Categorical`-ness or it isn't interpreted within the `MultiIndex`.
Looks like this is fixed on master. Could use a test

```
In [51]: df.groupby('group')['dose'].value_counts().sort_index(level=0,
    ...:                                                        sort_remaining=True)
Out[51]:
group  dose
A      low     2
       med     2
       high    2
B      low     2
       med     2
       high    2
Name: dose, dtype: int64

In [52]: pd.__version__
Out[52]: '1.1.0.dev0+1466.ga3477c769.dirty'
```
```
pd.__version__

1.1.0.dev0+1901.gaaa9cd03f
```

Setting type **then** setting index gives expected output:
```py
dd = pd.DataFrame([
    ("five", "monday", "two", 1),
    ("four", "monday", "one", 1),
    ("one", "friday", "one", 1),
    ("one", "wednesday", "two", 1),
    ("three", "tuesday", "three", 1),
    ("two", "tuesday", "one", 1),
])
dd[0] = dd[0].astype(number_type)
dd[1] = dd[1].astype(day_type)
dd[2] = dd[2].astype(number_type)
dd.set_index([0,1,2]).sort_index()
```
```
3
0     1         2       
one   wednesday two    1
      friday    one    1
two   tuesday   one    1
three tuesday   three  1
four  monday    one    1
five  monday    two    1
```

Conversely, setting index **then** setting dtype gives the described "bug":
```py
dd = pd.DataFrame([
    ("five", "monday", "two", 1),
    ("four", "monday", "one", 1),
    ("one", "friday", "one", 1),
    ("one", "wednesday", "two", 1),
    ("three", "tuesday", "three", 1),
    ("two", "tuesday", "one", 1),
])
dd = dd.set_index([0, 1, 2])
dd.index = dd.index.set_levels(dd.index.levels[0].astype(number_type), 0)
dd.index = dd.index.set_levels(dd.index.levels[1].astype(day_type), 1)
dd.index = dd.index.set_levels(dd.index.levels[2].astype(number_type), 2)
```
```
3
0     1         2       
five  monday    two    1
four  monday    one    1
one   friday    one    1
      wednesday two    1
three tuesday   three  1
two   tuesday   one    1
```

This is because in the latter case, we only modify the levels but not the codes. Don't know if we have proper way for this usecase yet. Maybe it's in df.reindex but i couldnt work it out.

Commit message: pandas-dev issue #24271

Patch to review:
diff --git a/pandas/tests/groupby/test_groupby.py b/pandas/tests/groupby/test_groupby.py
index 4dce7e8553be4..9c69a29bb2462 100644
--- a/pandas/tests/groupby/test_groupby.py
+++ b/pandas/tests/groupby/test_groupby.py
@@ -146,6 +146,33 @@ def func(dataf):
     assert isinstance(result, DataFrame)
 
 
+def test_multi_index_sort():
+    # GH 24271
+    df = DataFrame(
+        {
+            "group": ["A"] * 6 + ["B"] * 6,
+            "dose": ["high", "med", "low"] * 4,
+            "outcomes": np.arange(12.0),
+        }
+    )
+
+    df.dose = pd.Categorical(df.dose, categories=["low", "med", "high"], ordered=True)
+
+    result = df.groupby("group")["dose"].value_counts()
+    result = result.sort_index(level=0, sort_remaining=True)
+    index = [
+        ("A", "low"),
+        ("A", "med"),
+        ("A", "high"),
+        ("B", "low"),
+        ("B", "med"),
+        ("B", "high"),
+    ]
+    index = MultiIndex.from_tuples(index, names=["group", "dose"])
+    expected = Series([2] * 6, index=index, name="dose")
+    tm.assert_series_equal(result, expected)
+
+
 def test_inconsistent_return_type():
     # GH5592
     # inconsistent return type


Code review comments:
diff_hunk: @@ -146,6 +146,33 @@ def func(dataf):
     assert isinstance(result, DataFrame)
 
 
+def test_multi_index_sort():
path: pandas/tests/groupby/test_groupby.py
text: @user1:
can you rename to 

test_sorting_with_different_categoricals

@user1:
move to test_categorical.py (same directory)