Repository: pandas-dev/pandas
Pull request: Fix MultiIndex melt when col_level is used
- [x] closes #34129
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
BUG: melt MultiIndex columns using index columns as identifier variables
- [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.

- [x] (optional) I have confirmed this bug exists on the master branch of pandas.

---

#### Code Sample, a copy-pastable example

```python
import pandas as pd
df = pd.DataFrame(
    [['p', 'q', 'r'],
     ['s', 't', 'u'],
     ['v', 'w', 'x'],
    ],
    index=pd.MultiIndex.from_arrays(
        [list('123'), list('456')],
        names=['ind1', 'ind2']
        ),
    columns=pd.MultiIndex.from_arrays(
        [list('ABC'), list('DEF')])
)

# melt using level1 (or above in other cases) fails
df_l1 = df.reset_index(col_level=1)
print("\nL1 index insert:\n", df_l1)
# NOTE: THIS FAILS!
df_l1 = pd.melt(df_l1, col_level=1, 
    id_vars=['ind1'], value_vars=['D','E'])
print("\nL1 melt:\n", df_l1)
```

#### Problem description
Suppose that we have multi-index columns and we would like to `melt`, using the index as the `id_vars`: 
In this example, if we `reset_index(col_level=1)` and then `melt()` will fail as shown below:

```python

L1 index insert:
              A  B  C
  ind1 ind2  D  E  F
0    1    4  p  q  r
1    2    5  s  t  u
2    3    6  v  w  x

FAILS: KeyError: 'ind1'
```

<details>

```python-traceback
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
~/Repos/spec17/venv/lib/python3.8/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   2896             try:
-> 2897                 return self._engine.get_loc(key)
   2898             except KeyError:

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'ind1'

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
 in 
     17 print("\nL1 index insert:\n", df_l1)
     18 # NOTE: THIS FAILS!
---> 19 df_l1 = pd.melt(df_l1, col_level=1, 
     20     id_vars=['ind1'], value_vars=['D','E'])
     21 print("\nL1 melt:\n", df_l1)

~/Repos/spec17/venv/lib/python3.8/site-packages/pandas/core/reshape/melt.py in melt(frame, id_vars, value_vars, var_name, value_name, col_level)
    102     mdata = {}
    103     for col in id_vars:
--> 104         id_data = frame.pop(col)
    105         if is_extension_type(id_data):
    106             id_data = concat([id_data] * K, ignore_index=True)

~/Repos/spec17/venv/lib/python3.8/site-packages/pandas/core/generic.py in pop(self, item)
    860         3  monkey        NaN
    861         """
--> 862         result = self[item]
    863         del self[item]
    864         try:

~/Repos/spec17/venv/lib/python3.8/site-packages/pandas/core/frame.py in __getitem__(self, key)
   2993             if self.columns.nlevels > 1:
   2994                 return self._getitem_multilevel(key)
-> 2995             indexer = self.columns.get_loc(key)
   2996             if is_integer(indexer):
   2997                 indexer = [indexer]

~/Repos/spec17/venv/lib/python3.8/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
   2897                 return self._engine.get_loc(key)
   2898             except KeyError:
-> 2899                 return self._engine.get_loc(self._maybe_cast_indexer(key))
   2900         indexer = self.get_indexer([key], method=method, tolerance=tolerance)
   2901         if indexer.ndim > 1 or indexer.size > 1:

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'ind1'

```

</details>

#### Output of ``pd.show_versions()``

<details>

INSTALLED VERSIONS
------------------
commit           : None
python           : 3.8.0.final.0
python-bits      : 64
OS               : Linux
OS-release       : 5.3.0-51-generic
machine          : x86_64
processor        : x86_64
byteorder        : little
LC_ALL           : None
LANG             : en_GB.UTF-8
LOCALE           : en_GB.UTF-8

pandas           : 1.0.3
numpy            : 1.18.4
pytz             : 2020.1
dateutil         : 2.8.1
pip              : 20.1
setuptools       : 40.8.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : None
IPython          : 7.14.0
pandas_datareader: None
bs4              : None
bottleneck       : None
fastparquet      : None
gcsfs            : None
lxml.etree       : None
matplotlib       : None
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : None
pytables         : None
pytest           : None
pyxlsb           : None
s3fs             : None
scipy            : None
sqlalchemy       : None
tables           : None
tabulate         : None
xarray           : None
xlrd             : None
xlwt             : None
xlsxwriter       : None
numba            : None

</details>

Hints from issue discussion:
@ashtou can you edit down your original issue to just the part that you don't think is working correctly? It's a bit hard to follow.

In general: you should pass the keys that you want to use for `value_vars`, etc. For a MultiIndex in the columns those will be tuples.
@TomAugspurger no worries, updated the issue.
I tried a few things. what you are saying (adding columns as tuples) is only correct if I don't specify the `col_level` in `melt`. When I specify it, as shown in the [API Reference](https://pandas.pydata.org/docs/reference/api/pandas.melt.html), it should work by the level name (not tuples). Now, the example in the API reference uses `col_level=0` which works fine, but my example uses `col_level=1` which fails
Gotcha, thanks. So it's just `col_level != 0` that's failing.

Are you interested in working on this?

Commit message: Add a whatsnew entry

Patch to review:
diff --git a/doc/source/whatsnew/v1.1.0.rst b/doc/source/whatsnew/v1.1.0.rst
index 41d519e0765dc..bab8b5cd99255 100644
--- a/doc/source/whatsnew/v1.1.0.rst
+++ b/doc/source/whatsnew/v1.1.0.rst
@@ -853,6 +853,7 @@ Reshaping
 - Bug in :func:`cut` raised an error when non-unique labels (:issue:`33141`)
 - Bug in :meth:`DataFrame.replace` casts columns to ``object`` dtype if items in ``to_replace`` not in values (:issue:`32988`)
 - Ensure only named functions can be used in :func:`eval()` (:issue:`32460`)
+- Fixed bug in :func:`melt` where melting MultiIndex columns with ``col_level`` > 0 would raise a ``KeyError`` on ``id_vars`` (:issue:`34129`)
 
 Sparse
 ^^^^^^
diff --git a/pandas/core/reshape/melt.py b/pandas/core/reshape/melt.py
index 1a315ff0ead52..d1f9a95878c18 100644
--- a/pandas/core/reshape/melt.py
+++ b/pandas/core/reshape/melt.py
@@ -72,7 +72,14 @@ def melt(
                     "The following 'value_vars' are not present in "
                     f"the DataFrame: {list(missing)}"
                 )
-        frame = frame.loc[:, id_vars + value_vars]
+        # use `iloc` instead of `loc` when `col_level` is specified
+        if col_level is not None:
+            nlevels = frame.columns.nlevels
+            iid_vars = [int(cols.index(elm) / nlevels) for elm in id_vars]
+            ivalue_vars = [int(cols.index(elm) / nlevels) for elm in value_vars]
+            frame = frame.iloc[:, iid_vars + ivalue_vars]
+        else:
+            frame = frame.loc[:, id_vars + value_vars]
     else:
         frame = frame.copy()
 
diff --git a/pandas/tests/reshape/test_melt.py b/pandas/tests/reshape/test_melt.py
index 6a670e6c729e9..3eba79e953898 100644
--- a/pandas/tests/reshape/test_melt.py
+++ b/pandas/tests/reshape/test_melt.py
@@ -101,15 +101,25 @@ def test_vars_work_with_multiindex(self):
         tm.assert_frame_equal(result, expected)
 
     def test_single_vars_work_with_multiindex(self):
-        expected = DataFrame(
+        expected0 = DataFrame(
             {
                 "A": {0: 1.067683, 1: -1.321405, 2: -0.807333},
                 "CAP": {0: "B", 1: "B", 2: "B"},
                 "value": {0: -1.110463, 1: 0.368915, 2: 0.08298},
             }
         )
-        result = self.df1.melt(["A"], ["B"], col_level=0)
-        tm.assert_frame_equal(result, expected)
+        result0 = self.df1.melt(["A"], ["B"], col_level=0)
+        tm.assert_frame_equal(result0, expected0)
+
+        expected1 = DataFrame(
+            {
+                "a": {0: 1.067683, 1: -1.321405, 2: -0.807333},
+                "low": {0: "b", 1: "b", 2: "b"},
+                "value": {0: -1.110463, 1: 0.368915, 2: 0.08298},
+            }
+        )
+        result1 = self.df1.melt(["a"], ["b"], col_level=1)
+        tm.assert_frame_equal(result1, expected1)
 
     def test_tuple_vars_fail_with_multiindex(self):
         # melt should fail with an informative error message if


Code review comments:
diff_hunk: @@ -72,7 +72,14 @@ def melt(
                     "The following 'value_vars' are not present in "
                     f"the DataFrame: {list(missing)}"
                 )
-        frame = frame.loc[:, id_vars + value_vars]
+        # use `iloc` instead of `loc` when `col_level` is specified
+        if col_level is not None:
+            nlevels = frame.columns.nlevels
+            iid_vars = [int(cols.index(elm) / nlevels) for elm in id_vars]
+            ivalue_vars = [int(cols.index(elm) / nlevels) for elm in value_vars]
path: pandas/core/reshape/melt.py
text: @user1:
Thanks @author 

Would it work to do something like
```python
idx = frame.columns.get_level_values(col_level).get_indexer(id_vars+value_vars)
frame = frame.iloc[:, idx]
```
?

@author:
@user1 of course! thanks for the tip! passes the tests too.