Repository: pandas-dev/pandas
Pull request: BUG: rolling.corr with MultiIndex columns
- [x] closes #21157
- [x] tests added / passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [x] whatsnew entry


Problem statement:
Rolling correlation for DataFrame with MultiIndex columns broken in Pandas 0.23
#### Code Sample

```
import numpy as np
import pandas as pd

index = range(5)
data = np.random.random(size=(len(index), 2))

simple_cols = ['M', 'N']
simple_df = pd.DataFrame(data, index=index, columns=simple_cols)

multi_cols = pd.MultiIndex.from_arrays([['M', 'N'], ['P', 'Q']], names=['a', 'b'])
multi_df = pd.DataFrame(data, index=index, columns=multi_cols)

# Works:
simple_df.rolling(3).corr()

# Fails
multi_df.rolling(3).corr()
```

#### Problem description

Since the Pandas 0.23 release it is no longer possible to calculate rolling correlation on a pd.DataFrame which has a pd.MultiIndex column index. An exception is raised in 0.23. In 0.22 a valid rolling correlation result is returned.

#### Expected Output

Pandas should return the same rolling correlation matrix as it returns for a data frame with simple index, but with the multi index levels in columns and index as they appeared in 0.22.

Hints from issue discussion:
Thanks for the report! Investigation and PRs are always welcome
hello, I am also having this same issue in 0.23, which has broken my working code that was previously using 0.22 @jjbarton were you able to solve this issue without reverting back to pandas 0.22?
Hi Andy

I worked around it in my code (by replacing the multi index with a simple
index and then mapping back once I'd calculated rolling correlation. I
don't think there's any progress otherwise, and my quick fix isn't a
suitable resolution.

Cheers
Joe


On Fri, 16 Nov 2018 at 22:16, andyshermanash <notifications@github.com>
wrote:

> hello, I am also having this same issue in 0.23, which has broken my
> working code that was previously using 0.22 @jjbarton
> <https://github.com/jjbarton> were you able to solve this issue without
> reverting back to pandas 0.22?
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/issues/21157#issuecomment-439545951>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AH9yOP_131clHzYWOoKk-TzSS9dAG8gSks5uvzlIgaJpZM4UHhrj>
> .
>

Commit message: Merge remote-tracking branch 'upstream/master' into bug/mi_columns_rolling_corr

Patch to review:
diff --git a/doc/source/whatsnew/v1.4.0.rst b/doc/source/whatsnew/v1.4.0.rst
index 3b9ddf8138689..36aab9e4f22fe 100644
--- a/doc/source/whatsnew/v1.4.0.rst
+++ b/doc/source/whatsnew/v1.4.0.rst
@@ -408,6 +408,7 @@ Groupby/resample/rolling
 - Bug in :meth:`pandas.DataFrame.rolling` operation along rows (``axis=1``) incorrectly omits columns containing ``float16`` and ``float32`` (:issue:`41779`)
 - Bug in :meth:`Resampler.aggregate` did not allow the use of Named Aggregation (:issue:`32803`)
 - Bug in :meth:`Series.rolling` when the :class:`Series` ``dtype`` was ``Int64`` (:issue:`43016`)
+- Bug in :meth:`DataFrame.rolling.corr` when the :class:`DataFrame` columns was a :class:`MultiIndex` (:issue:`21157`)
 
 Reshaping
 ^^^^^^^^^
diff --git a/pandas/core/window/common.py b/pandas/core/window/common.py
index e0720c5d86df1..adf910d9ee2c0 100644
--- a/pandas/core/window/common.py
+++ b/pandas/core/window/common.py
@@ -83,8 +83,22 @@ def dataframe_from_int_dict(data, frame_template):
                         # mypy needs to know columns is a MultiIndex, Index doesn't
                         # have levels attribute
                         arg2.columns = cast(MultiIndex, arg2.columns)
-                        result.index = MultiIndex.from_product(
-                            arg2.columns.levels + [result_index]
+                        # GH 21157: Equivalent to MultiIndex.from_product(
+                        # <unique combinations of arg2.columns.levels>, [result_index]
+                        # )
+                        # A normal MultiIndex.from_product will produce too many
+                        # combinations.
+                        idx_codes, idx_uniques = result_index.factorize()
+                        result_levels = list(arg2.columns.levels) + [idx_uniques]
+                        result_codes = [
+                            np.tile(code, int(len(result) / len(code)))
+                            for code in arg2.columns.codes
+                        ] + [np.tile(idx_codes, int(len(result) / len(idx_codes)))]
+                        result_names = list(arg2.columns.names) + [result_index.name]
+                        result.index = MultiIndex(
+                            levels=result_levels,
+                            codes=result_codes,
+                            names=result_names,
                         )
                         # GH 34440
                         num_levels = len(result.index.levels)
diff --git a/pandas/tests/window/test_rolling.py b/pandas/tests/window/test_rolling.py
index 2edf22d96a9ba..2fb6b61d46b83 100644
--- a/pandas/tests/window/test_rolling.py
+++ b/pandas/tests/window/test_rolling.py
@@ -1500,3 +1500,19 @@ def test_rolling_numeric_dtypes():
         dtype="float64",
     )
     tm.assert_frame_equal(result, expected)
+
+
+def test_multindex_columns_pairwise_func():
+    # GH 21157
+    columns = MultiIndex.from_arrays([["M", "N"], ["P", "Q"]], names=["a", "b"])
+    df = DataFrame(np.ones((5, 2)), columns=columns)
+    result = df.rolling(3).corr()
+    expected = DataFrame(
+        np.nan,
+        index=MultiIndex.from_arrays(
+            [np.repeat(np.arange(5), 2), ["M", "N"] * 5, ["P", "Q"] * 5],
+            names=[None, "a", "b"],
+        ),
+        columns=columns,
+    )
+    tm.assert_frame_equal(result, expected)


Code review comments:
diff_hunk: @@ -83,8 +83,22 @@ def dataframe_from_int_dict(data, frame_template):
                         # mypy needs to know columns is a MultiIndex, Index doesn't
                         # have levels attribute
                         arg2.columns = cast(MultiIndex, arg2.columns)
-                        result.index = MultiIndex.from_product(
-                            arg2.columns.levels + [result_index]
+                        # GH 21157: Equivalent to MultiIndex.from_product(
+                        # <unique combinations of arg2.columns.levels>, [result_index]
+                        # )
+                        # A normal MultiIndex.from_product will produce too many
+                        # combinations.
+                        idx_codes, idx_uniques = result_index.factorize()
path: pandas/core/window/common.py
text: @user1:
why are you not using `.from_arrays` here? this is really hard to understand *why* you are doing this at all. 

@author:
Good point. Simplified to use `from_arrays`.