Repository: pandas-dev/pandas
Pull request: BUG: Fix MutliIndexed unstack failures at tuple names
- [x] closes #19966
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
MultiIndexed unstack with tuple names fails with KeyError
```python
In [8]: idx = pd.MultiIndex.from_product([['a', 'b', 'c'], [1, 2, 3]], names=[('A', 'a'), ('B', 'b')])

In [9]: s = pd.Series(1, index=idx)

In [10]: s
Out[10]:
(A, a)  (B, b)
a       1         1
        2         1
        3         1
b       1         1
        2         1
        3         1
c       1         1
        2         1
        3         1
dtype: int64

In [11]: s.unstack(("A", "a"))
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
~/Envs/pandas-dev/lib/python3.6/site-packages/pandas/pandas/core/indexes/multi.py in _get_level_number(self, level)
    749                                  'level number' % level)
--> 750             level = self.names.index(level)
    751         except ValueError:

ValueError: 'A' is not in list

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
<ipython-input-11-1ce241b42d82> in <module>()
----> 1 s.unstack(("A", "a"))

~/Envs/pandas-dev/lib/python3.6/site-packages/pandas/pandas/core/series.py in unstack(self, level, fill_value)
   2231         """
   2232         from pandas.core.reshape.reshape import unstack
-> 2233         return unstack(self, level, fill_value)
   2234
   2235     # ----------------------------------------------------------------------

~/Envs/pandas-dev/lib/python3.6/site-packages/pandas/pandas/core/reshape/reshape.py in unstack(obj, level, fill_value)
    481             # _unstack_multiple only handles MultiIndexes,
    482             # and isn't needed for a single level
--> 483             return _unstack_multiple(obj, level, fill_value=fill_value)
    484         else:
    485             level = level[0]

~/Envs/pandas-dev/lib/python3.6/site-packages/pandas/pandas/core/reshape/reshape.py in _unstack_multiple(data, clocs, fill_value)
    315     index = data.index
    316
--> 317     clocs = [index._get_level_number(i) for i in clocs]
    318
    319     rlocs = [i for i in range(index.nlevels) if i not in clocs]

~/Envs/pandas-dev/lib/python3.6/site-packages/pandas/pandas/core/reshape/reshape.py in <listcomp>(.0)
    315     index = data.index
    316
--> 317     clocs = [index._get_level_number(i) for i in clocs]
    318
    319     rlocs = [i for i in range(index.nlevels) if i not in clocs]

~/Envs/pandas-dev/lib/python3.6/site-packages/pandas/pandas/core/indexes/multi.py in _get_level_number(self, level)
    751         except ValueError:
    752             if not isinstance(level, int):
--> 753                 raise KeyError('Level %s not found' % str(level))
    754             elif level < 0:
    755                 level += self.nlevels

KeyError: 'Level A not found'
```

cc @ibrahimsharaf, @toobaz does this look difficult?

Hints from issue discussion:
~~Reminds me of #18321~~ probably unrelated, and probably simpler... one of these cases in which we probably only need to tell ``unstack`` that tuples are not lists.
Hi @TomAugspurger, I'm willing to help here, do you have any initial ideas on how to fix this? (before I dive in the code)
in `reshape/reshape`, the line here

> --> 317     clocs = [index._get_level_number(i) for i in clocs]

is incorrect. It's assuming that all iterables are referring to multiple levels. In this case, we have a single level whose name happens to be an iterable. We want to do

```python
In [8]: s.index._get_level_number(('A', 'a'))
Out[8]: 0
```

but there may be other problems later on. You'll probably run into ambiguous situations as well (e.g. the index names are `[('A', 'a'), 'A', 'a']`. Not sure how best to handle those.

Commit message: fix issue 19966

Patch to review:
diff --git a/doc/source/whatsnew/v1.0.0.rst b/doc/source/whatsnew/v1.0.0.rst
index 5f79accc5c679..6abad0922416f 100755
--- a/doc/source/whatsnew/v1.0.0.rst
+++ b/doc/source/whatsnew/v1.0.0.rst
@@ -1133,6 +1133,7 @@ Reshaping
 - Bug in :func:`merge_asof` merging on a tz-aware ``left_index`` and ``right_on`` a tz-aware column (:issue:`29864`)
 - Improved error message and docstring in :func:`cut` and :func:`qcut` when `labels=True` (:issue:`13318`)
 - Bug in missing `fill_na` parameter to :meth:`DataFrame.unstack` with list of levels (:issue:`30740`)
+- Bug in :func:`unstack` can take tuple names in MultiIndexed data (:issue:`19966`)
 
 Sparse
 ^^^^^^
diff --git a/pandas/core/reshape/reshape.py b/pandas/core/reshape/reshape.py
index 97f416e32d07b..a96f5d602713c 100644
--- a/pandas/core/reshape/reshape.py
+++ b/pandas/core/reshape/reshape.py
@@ -317,6 +317,8 @@ def _unstack_multiple(data, clocs, fill_value=None):
 
     index = data.index
 
+    if clocs in index.names:
+        clocs = [clocs]
     clocs = [index._get_level_number(i) for i in clocs]
 
     rlocs = [i for i in range(index.nlevels) if i not in clocs]
diff --git a/pandas/tests/frame/test_reshape.py b/pandas/tests/frame/test_reshape.py
index 56a0c8cf4f5bd..014d6522cfe9b 100644
--- a/pandas/tests/frame/test_reshape.py
+++ b/pandas/tests/frame/test_reshape.py
@@ -336,6 +336,80 @@ def test_unstack_fill_frame_categorical(self):
         )
         tm.assert_frame_equal(result, expected)
 
+    def test_unstack_tuplename_in_multiindex(self):
+        # GH 19966
+        idx = pd.MultiIndex.from_product(
+            [["a", "b", "c"], [1, 2, 3]], names=[("A", "a"), ("B", "b")]
+        )
+        df = pd.DataFrame({"d": [1] * 9, "e": [2] * 9}, index=idx)
+        result = df.unstack(("A", "a"))
+
+        expected = pd.DataFrame(
+            [[1, 1, 1, 2, 2, 2], [1, 1, 1, 2, 2, 2], [1, 1, 1, 2, 2, 2]],
+            columns=pd.MultiIndex.from_tuples(
+                [
+                    ("d", "a"),
+                    ("d", "b"),
+                    ("d", "c"),
+                    ("e", "a"),
+                    ("e", "b"),
+                    ("e", "c"),
+                ],
+                names=[None, ("A", "a")],
+            ),
+            index=pd.Index([1, 2, 3], name=("B", "b")),
+        )
+        tm.assert_frame_equal(result, expected)
+
+    @pytest.mark.parametrize(
+        "unstack_idx, expected_values, expected_index, expected_columns",
+        [
+            (
+                ("A", "a"),
+                [[1, 1, 2, 2], [1, 1, 2, 2], [1, 1, 2, 2], [1, 1, 2, 2]],
+                pd.MultiIndex.from_tuples(
+                    [(1, 3), (1, 4), (2, 3), (2, 4)], names=["B", "C"]
+                ),
+                pd.MultiIndex.from_tuples(
+                    [("d", "a"), ("d", "b"), ("e", "a"), ("e", "b")],
+                    names=[None, ("A", "a")],
+                ),
+            ),
+            (
+                (("A", "a"), "B"),
+                [[1, 1, 1, 1, 2, 2, 2, 2], [1, 1, 1, 1, 2, 2, 2, 2]],
+                pd.Index([3, 4], name="C"),
+                pd.MultiIndex.from_tuples(
+                    [
+                        ("d", "a", 1),
+                        ("d", "a", 2),
+                        ("d", "b", 1),
+                        ("d", "b", 2),
+                        ("e", "a", 1),
+                        ("e", "a", 2),
+                        ("e", "b", 1),
+                        ("e", "b", 2),
+                    ],
+                    names=[None, ("A", "a"), "B"],
+                ),
+            ),
+        ],
+    )
+    def test_unstack_mixed_type_name_in_multiindex(
+        self, unstack_idx, expected_values, expected_index, expected_columns
+    ):
+        # GH 19966
+        idx = pd.MultiIndex.from_product(
+            [["a", "b"], [1, 2], [3, 4]], names=[("A", "a"), "B", "C"]
+        )
+        df = pd.DataFrame({"d": [1] * 8, "e": [2] * 8}, index=idx)
+        result = df.unstack(unstack_idx)
+
+        expected = pd.DataFrame(
+            expected_values, columns=expected_columns, index=expected_index,
+        )
+        tm.assert_frame_equal(result, expected)
+
     def test_unstack_preserve_dtypes(self):
         # Checks fix for #11847
         df = pd.DataFrame(
diff --git a/pandas/tests/series/test_reshape.py b/pandas/tests/series/test_reshape.py
new file mode 100644
index 0000000000000..bb7d1fcdd4070
--- /dev/null
+++ b/pandas/tests/series/test_reshape.py
@@ -0,0 +1,59 @@
+import pytest
+
+import pandas as pd
+import pandas._testing as tm
+
+
+def test_unstack_tuplename_in_multiindex():
+    # GH 19966
+    idx = pd.MultiIndex.from_product(
+        [["a", "b", "c"], [1, 2, 3]], names=[("A", "a"), ("B", "b")]
+    )
+    ser = pd.Series(1, index=idx)
+    result = ser.unstack(("A", "a"))
+
+    expected = pd.DataFrame(
+        [[1, 1, 1], [1, 1, 1], [1, 1, 1]],
+        columns=pd.MultiIndex.from_tuples(
+            [("a",), ("b",), ("c",)], names=[("A", "a")],
+        ),
+        index=pd.Index([1, 2, 3], name=("B", "b")),
+    )
+    tm.assert_frame_equal(result, expected)
+
+
+@pytest.mark.parametrize(
+    "unstack_idx, expected_values, expected_index, expected_columns",
+    [
+        (
+            ("A", "a"),
+            [[1, 1], [1, 1], [1, 1], [1, 1]],
+            pd.MultiIndex.from_tuples(
+                [(1, 3), (1, 4), (2, 3), (2, 4)], names=["B", "C"]
+            ),
+            pd.MultiIndex.from_tuples([("a",), ("b",)], names=[("A", "a")]),
+        ),
+        (
+            (("A", "a"), "B"),
+            [[1, 1, 1, 1], [1, 1, 1, 1]],
+            pd.Index([3, 4], name="C"),
+            pd.MultiIndex.from_tuples(
+                [("a", 1), ("a", 2), ("b", 1), ("b", 2)], names=[("A", "a"), "B"]
+            ),
+        ),
+    ],
+)
+def test_unstack_mixed_type_name_in_multiindex(
+    unstack_idx, expected_values, expected_index, expected_columns
+):
+    # GH 19966
+    idx = pd.MultiIndex.from_product(
+        [["a", "b"], [1, 2], [3, 4]], names=[("A", "a"), "B", "C"]
+    )
+    ser = pd.Series(1, index=idx)
+    result = ser.unstack(unstack_idx)
+
+    expected = pd.DataFrame(
+        expected_values, columns=expected_columns, index=expected_index,
+    )
+    tm.assert_frame_equal(result, expected)


Code review comments:
diff_hunk: @@ -1133,6 +1133,7 @@ Reshaping
 - Bug in :func:`merge_asof` merging on a tz-aware ``left_index`` and ``right_on`` a tz-aware column (:issue:`29864`)
 - Improved error message and docstring in :func:`cut` and :func:`qcut` when `labels=True` (:issue:`13318`)
 - Bug in missing `fill_na` parameter to :meth:`DataFrame.unstack` with list of levels (:issue:`30740`)
+- Bug in :func:`unstack` can take tuple names in MultiIndexed data (:issue:`19966`)
path: doc/source/whatsnew/v1.0.0.rst
text: @user1:
<code>:func:\`unstack\`</code> doesn't exist, so instead should probably be "<code>:meth:\`DataFrame.unstack\`</code> and <code>:meth:\`Series.unstack\`</code>"

@author:
thanks! changed!