Repository: pandas-dev/pandas
Pull request: GH28301 check for non-unique index in stack_multi_columns
- [x] closes #28301
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

The old implementation used `arange` to generate codes for a non-multiindex, which ended up creating bad codes if the index was not unique.  The fix is just a straightforward factorize.

Problem statement:
groupby level after stack not actually grouping
#### Code Sample, a copy-pastable example if possible

```python
columns = pd.MultiIndex.from_product([['a', 'b'], [1, 2]])
index = [0, 0, 1, 1]
values = np.arange(16).reshape(-1, 4)

df = pd.DataFrame(values, columns=columns, index=index)

    a       b
    1   2   1   2
0   0   1   2   3
0   4   5   6   7
1   8   9  10  11
1  12  13  14  15

df.stack(1)

      a   b
0 1   0   2
  2   1   3
  1   4   6
  2   5   7
1 1   8  10
  2   9  11
  1  12  14
  2  13  15

# ACTUAL BEHAVIOR
df.stack(1).groupby(level=[0, 1]).sum()

      a   b
0 1   0   2
  2   1   3
  1   4   6
  2   5   7
1 1   8  10
  2   9  11
  1  12  14
  2  13  15

# EXPECTED

      a   b
0 1   4   8
  2   6  10
1 1  20  24
  2  22  26

# WORKAROUND

df.stack(1).reset_index().groupby(['level_0', 'level_1']).sum().rename_axis([None, None])


```
#### Problem description

When grouping by levels after stacking, no grouping seems to be taking place.  However, after resetting the index and using those series to group, the groupby works as expected

#### Output of ``pd.show_versions()``

<details>
INSTALLED VERSIONS
------------------
commit           : None
python           : 3.6.7.final.0
python-bits      : 64
OS               : Windows
OS-release       : 10
machine          : AMD64
processor        : Intel64 Family 6 Model 58 Stepping 9, GenuineIntel
byteorder        : little
LC_ALL           : None
LANG             : en_US.UTF-8
LOCALE           : None.None

pandas           : 0.25.0
numpy            : 1.17.0
pytz             : 2018.6
dateutil         : 2.7.3
pip              : 18.1
setuptools       : 39.1.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : 4.3.4
html5lib         : 1.0.1
pymysql          : None
psycopg2         : 2.7.5 (dt dec pq3 ext lo64)
jinja2           : 2.10.1
IPython          : 7.2.0
pandas_datareader: None
bs4              : 4.7.1
bottleneck       : None
fastparquet      : None
gcsfs            : None
lxml.etree       : 4.3.4
matplotlib       : 3.0.0
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : None
pytables         : None
s3fs             : None
scipy            : 1.2.1
sqlalchemy       : 1.2.12
tables           : None
xarray           : None
xlrd             : None
xlwt             : None
xlsxwriter       : None
</details>

Hints from issue discussion:
Just found something strange, when I create the DataFrame created by the stack from scratch, the `groupby` seems to work:

```
index = pd.MultiIndex.from_product([[0, 0, 1, 1], [1, 2]])
columns = ['a', 'b']
values = np.arange(16).reshape(-1, 2)

scratch_df = pd.DataFrame(values, columns=columns, index=index)

      a   b
0 1   0   1
  2   2   3
  1   4   5
  2   6   7
1 1   8   9
  2  10  11
  1  12  13
  2  14  15

scratch_df.groupby(level=[0, 1]).sum()

      a   b
0 1   4   6
  2   8  10
1 1  20  22
  2  24  26
```

Yet as far as I can tell, the two indexes are equal:

```
>>> pd.testing.assert_index_equal(df.stack(1).index, scratch_df.index)
```
Hmm that is rather strange. Care to investigate? Relevant code is going to be in pandas.core.groupby
Looks like the issue is in `stack` as far as I can tell.  For this DataFrame, [this line of code](https://github.com/pandas-dev/pandas/blob/ee37443fa6c54f552434dda705fbc86062a944f9/pandas/core/reshape/reshape.py#L729) is creating codes based on an arbitrary length of the DataFrame without taking into account duplicate index values, which are then used by the `groupby`.

```
>>> df.stack(1).index.codes
FrozenList([[0, 0, 1, 1, 2, 2, 3, 3], [0, 1, 0, 1, 0, 1, 0, 1]])

>>> scratch_df.index.codes
FrozenList([[0, 0, 0, 0, 1, 1, 1, 1], [0, 1, 0, 1, 0, 1, 0, 1]])
```

A naive solution to `stack` for a non-`MultiIndex` might be replacing [this](https://github.com/pandas-dev/pandas/blob/ee37443fa6c54f552434dda705fbc86062a944f9/pandas/core/reshape/reshape.py#L728-L730):

```
new_levels = [this.index]
new_codes = [np.arange(N).repeat(levsize)]
new_names = [this.index.name]  # something better?
```

with this:

```
ncodes, nlevels = _factorize_from_iterable(this.index)
new_levels = [nlevels]
new_codes = [ncodes.repeat(levsize)]
new_names = [this.index.name]  # something better?
```

But I'm not sure how that would transfer to the `MultiIndex` path.

It seems odd that `pd.testing.assert_index_equal` doesn't catch a difference in codes, maybe something else worth investigating?

Commit message: GH28301 check for non-unique index in stack_multi_columns

Patch to review:
diff --git a/pandas/core/reshape/reshape.py b/pandas/core/reshape/reshape.py
index 374de6156c807..c32ca47c19160 100644
--- a/pandas/core/reshape/reshape.py
+++ b/pandas/core/reshape/reshape.py
@@ -725,8 +725,9 @@ def _convert_level_number(level_num, columns):
         new_names = list(this.index.names)
         new_codes = [lab.repeat(levsize) for lab in this.index.codes]
     else:
-        new_levels = [this.index]
-        new_codes = [np.arange(N).repeat(levsize)]
+        old_codes, old_levels = _factorize_from_iterable(this.index)
+        new_levels = [old_levels]
+        new_codes = [old_codes.repeat(levsize)]
         new_names = [this.index.name]  # something better?
 
     new_levels.append(level_vals)
diff --git a/pandas/tests/frame/test_reshape.py b/pandas/tests/frame/test_reshape.py
index 84e343f07f990..8926644f59d1d 100644
--- a/pandas/tests/frame/test_reshape.py
+++ b/pandas/tests/frame/test_reshape.py
@@ -1002,6 +1002,26 @@ def test_stack_preserve_categorical_dtype_values(self):
         )
         tm.assert_series_equal(result, expected)
 
+    @pytest.mark.parametrize(
+        "index, columns",
+        [
+            ([0, 0, 1, 1], pd.MultiIndex.from_product([[1, 2], ["a", "b"]])),
+            ([0, 0, 2, 3], pd.MultiIndex.from_product([[1, 2], ["a", "b"]])),
+            ([0, 1, 2, 3], pd.MultiIndex.from_product([[1, 2], ["a", "b"]])),
+        ],
+    )
+    def test_stack_multi_columns_non_unique_index(self, index, columns):
+        # GH-28301
+        df = pd.DataFrame(index=index, columns=columns).fillna(1)
+        stacked = df.stack(-1)
+        new_index = pd.MultiIndex.from_tuples(stacked.index.values)
+
+        tm.assert_index_equal(stacked.index, new_index)
+
+        a = np.asarray(stacked.index.codes)
+        b = np.asarray(new_index.codes)
+        tm.assert_numpy_array_equal(a, b)
+
     @pytest.mark.parametrize("level", [0, 1])
     def test_unstack_mixed_extension_types(self, level):
         index = pd.MultiIndex.from_tuples(


Code review comments:
diff_hunk: @@ -1002,6 +1002,26 @@ def test_stack_preserve_categorical_dtype_values(self):
         )
         tm.assert_series_equal(result, expected)
 
+    @pytest.mark.parametrize(
+        "index, columns",
+        [
+            ([0, 0, 1, 1], pd.MultiIndex.from_product([[1, 2], ["a", "b"]])),
+            ([0, 0, 2, 3], pd.MultiIndex.from_product([[1, 2], ["a", "b"]])),
+            ([0, 1, 2, 3], pd.MultiIndex.from_product([[1, 2], ["a", "b"]])),
+        ],
+    )
+    def test_stack_multi_columns_non_unique_index(self, index, columns):
+        # GH-28301
+        df = pd.DataFrame(index=index, columns=columns).fillna(1)
+        stacked = df.stack(-1)
+        new_index = pd.MultiIndex.from_tuples(stacked.index.values)
+
+        tm.assert_index_equal(stacked.index, new_index)
+
+        a = np.asarray(stacked.index.codes)
+        b = np.asarray(new_index.codes)
path: pandas/tests/frame/test_reshape.py
text: @user1:
can u created the full expected frame here and compare that instead

@author:
Added in frame comparison, I left the `assert_numpy_equal` because it doesn't seem like `assert_frame_equal` checks that the codes on each of the indexes match.