Repository: pandas-dev/pandas
Pull request: Add to_flat_index method to MultiIndex
- [X] closes #19950
- [X] tests added / passed
- [X] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

Very simple implementation at the moment. The thought here is to introduce this method and perhaps subsequently extend to allow for string concatenation of the elements. Longer term there could also be a keyword added to `.agg` of GroupBy which will dispatch to this instead of simply returning a MultiIndex column, which could alleviate some of the pain users are experience when trying to rename columns after an aggregation.

@TomAugspurger and @jorisvandenbossche from the dev chat today

Problem statement:
Add method to flatten all multi-index levels
Sometimes it is useful to flatten all levels of a multi-index. For example, when pivoting data into a wide format, the new columns are generally multi-indexed. However, when exporting to CSV, sometimes it might be desirable to have only one header row.

AFAIK, there is no dedicated method to flatten an existing multi-index. Assuming I want to combine the multi-index levels with an underscore, I would do this to get one header row:

```
df.columns = ["_".join(v) for v in df.columns.values]
````

This is not really obvious, and maybe a dedicated `MultiIndex` method should be added, e.g. `df.columns.flatten(sep="_", inplace=False)`. What do you think?

Hints from issue discussion:
having a ``.to_index(sep=None)`` would be useful, the default would be equivalent of ``.tolist()``, IOW return tuples, while a ``sep`` could stringify and join. Note that this is a bit tricky because for example what do you do with a ``Timestamp``?
I like your idea of a `to_index` method. Good point, specifying a `sep` character implies that individual entries are converted to strings. How do `Timestamp` objects behave when I try to `str` them? If this is not possible, the method could raise an error in this case (and only work for string or int index objects).
Actually, is this even possible in the use case I described? I mean, I was referring to a multi-index created by `pivot_table` - can a level consist of timestamps?
```
In [29]: index=pd.MultiIndex.from_product([list('abc'), pd.date_range('20180101', periods=3)])

In [30]: index
Out[30]: 
MultiIndex(levels=[['a', 'b', 'c'], [2018-01-01 00:00:00, 2018-01-02 00:00:00, 2018-01-03 00:00:00]],
           labels=[[0, 0, 0, 1, 1, 1, 2, 2, 2], [0, 1, 2, 0, 1, 2, 0, 1, 2]])


In [32]: pd.Index(['_'.join([str(_) for _ in v]) for v in index.values])
Out[32]: 
Index(['a_2018-01-01 00:00:00', 'a_2018-01-02 00:00:00',
       'a_2018-01-03 00:00:00', 'b_2018-01-01 00:00:00',
       'b_2018-01-02 00:00:00', 'b_2018-01-03 00:00:00',
       'c_2018-01-01 00:00:00', 'c_2018-01-02 00:00:00',
       'c_2018-01-03 00:00:00'],
      dtype='object')
```
slightly OT: note that ``.wide_to_long`` basically does the inverse of this, meaning it splits columns names on regex. 
Looks good! So I guess no problem then? I didn't know about `.wide_to_long`, maybe adding a `.long_to_wide` would make sense?

Just to motivate this idea: I love how `unite` and `spread` from the `tidyr` R package do exactly what I describe here.
FWIW There is a very popular [question related to this on SO](https://stackoverflow.com/questions/14507794/python-pandas-how-to-flatten-a-hierarchical-index-in-columns), so by that metric it seems like quite a few people could benefit from a solution similar to what is suggested here.

Commit message: Fixed up docstring

Patch to review:
diff --git a/doc/source/api.rst b/doc/source/api.rst
index 6e8eb83577c46..3a9d3aa8c5194 100644
--- a/doc/source/api.rst
+++ b/doc/source/api.rst
@@ -1725,6 +1725,7 @@ MultiIndex Components
    MultiIndex.set_levels
    MultiIndex.set_labels
    MultiIndex.to_hierarchical
+   MultiIndex.to_index
    MultiIndex.to_frame
    MultiIndex.is_lexsorted
    MultiIndex.sortlevel
diff --git a/doc/source/whatsnew/v0.24.0.txt b/doc/source/whatsnew/v0.24.0.txt
index 768868d585721..d084e64370e0b 100644
--- a/doc/source/whatsnew/v0.24.0.txt
+++ b/doc/source/whatsnew/v0.24.0.txt
@@ -214,6 +214,7 @@ Other Enhancements
 - Compatibility with Matplotlib 3.0 (:issue:`22790`).
 - Added :meth:`Interval.overlaps`, :meth:`IntervalArray.overlaps`, and :meth:`IntervalIndex.overlaps` for determining overlaps between interval-like objects (:issue:`21998`)
 - :meth:`Timestamp.tz_localize`, :meth:`DatetimeIndex.tz_localize`, and :meth:`Series.tz_localize` have gained the ``nonexistent`` argument for alternative handling of nonexistent times. See :ref:`timeseries.timezone_nonexsistent` (:issue:`8917`)
+- :meth:`MultiIndex.to_index` has been added to flatten multiple levels into a single-level :class:`Index` object.
 
 .. _whatsnew_0240.api_breaking:
 
diff --git a/pandas/core/indexes/base.py b/pandas/core/indexes/base.py
index e9b0b087179c9..8eb1ea6429a49 100644
--- a/pandas/core/indexes/base.py
+++ b/pandas/core/indexes/base.py
@@ -1105,6 +1105,52 @@ def _format_attrs(self):
         """
         return format_object_attrs(self)
 
+    def to_index(self, sep=None):
+        """
+        Convert a MultiIndex to an Index of Tuples containing the level values.
+
+        .. versionadded:: 0.24.0
+
+        Parameters
+        ----------
+        sep : str, optional
+            Not yet implemented.
+
+        Returns
+        -------
+        pd.Index : an Index with the MultiIndex data represented in Tuples.
+
+        Notes
+        -----
+        This method will simply return the caller if called by anything other
+        than a MultiIndex.
+
+        See Also
+        --------
+        to_series : Similar method to construct a Series.
+        to_frame : Similar method to construct a DataFrame.
+
+        Examples
+        --------
+        >>> index = pd.MultiIndex.from_product(
+        ...     [['foo', 'bar'], ['baz', 'qux']],
+        ...     names=['a', 'b'])
+        >>> index.to_index()
+        Index([('foo', 'baz'), ('foo', 'qux'),
+               ('bar', 'baz'), ('bar', 'qux')],
+              dtype='object')
+        """
+        if sep is not None:
+            # TODO: Add support for separator to return strs instad of tuples
+            raise NotImplementedError
+
+        if not isinstance(self, ABCMultiIndex):
+            return self
+
+        idx = Index(self.values, tupleize_cols=False)
+
+        return idx
+
     def to_series(self, index=None, name=None):
         """
         Create a Series with both index and values equal to the index keys
diff --git a/pandas/core/indexes/multi.py b/pandas/core/indexes/multi.py
index 79ac32d2f6a0b..5cc5e97b524b2 100644
--- a/pandas/core/indexes/multi.py
+++ b/pandas/core/indexes/multi.py
@@ -194,6 +194,7 @@ class MultiIndex(Index):
     set_levels
     set_labels
     to_frame
+    to_index
     is_lexsorted
     sortlevel
     droplevel
diff --git a/pandas/tests/indexes/multi/test_conversion.py b/pandas/tests/indexes/multi/test_conversion.py
index 8c9566b7e651f..bc38d45e0daa7 100644
--- a/pandas/tests/indexes/multi/test_conversion.py
+++ b/pandas/tests/indexes/multi/test_conversion.py
@@ -169,3 +169,11 @@ def test_to_series_with_arguments(idx):
     assert s.values is not idx.values
     assert s.index is not idx
     assert s.name != idx.name
+
+
+def test_to_index(idx):
+    expected = pd.Index((('foo', 'one'), ('foo', 'two'), ('bar', 'one'),
+                         ('baz', 'two'), ('qux', 'one'), ('qux', 'two')),
+                        tupleize_cols=False)
+    result = idx.to_index()
+    tm.assert_index_equal(result, expected)
diff --git a/pandas/tests/indexes/test_base.py b/pandas/tests/indexes/test_base.py
index 921bcda62794d..af991ce8d1e2d 100644
--- a/pandas/tests/indexes/test_base.py
+++ b/pandas/tests/indexes/test_base.py
@@ -2201,6 +2201,18 @@ def test_tab_complete_warning(self, ip):
             with provisionalcompleter('ignore'):
                 list(ip.Completer.completions('idx.', 4))
 
+    def test_to_index(self, indices):
+        # 22866
+        if isinstance(indices, MultiIndex):
+            pytest.skip("Separate expectation for MultiIndex")
+
+        result = indices.to_index()
+        tm.assert_index_equal(result, indices)
+
+    def test_to_index_sep_raises(self, indices):
+        with pytest.raises(NotImplementedError):
+            indices.to_index(sep="")
+
 
 class TestMixedIntIndex(Base):
     # Mostly the tests from common.py for which the results differ


Code review comments:
diff_hunk: @@ -1105,6 +1105,52 @@ def _format_attrs(self):
         """
         return format_object_attrs(self)
 
+    def to_index(self, sep=None):
+        """
+        Convert a MultiIndex to an Index of Tuples containing the level values.
+
+        .. versionadded:: 0.24.0
+
+        Parameters
+        ----------
+        sep : str, optional
+            Not yet implemented.
+
+        Returns
+        -------
+        pd.Index : an Index with the MultiIndex data represented in Tuples.
+
+        Notes
+        -----
+        This method will simply return the caller if called by anything other
+        than a MultiIndex.
+
+        See Also
+        --------
+        to_series : Similar method to construct a Series.
+        to_frame : Similar method to construct a DataFrame.
+
+        Examples
+        --------
+        >>> index = pd.MultiIndex.from_product(
+        ...     [['foo', 'bar'], ['baz', 'qux']],
+        ...     names=['a', 'b'])
+        >>> index.to_index()
+        Index([('foo', 'baz'), ('foo', 'qux'),
+               ('bar', 'baz'), ('bar', 'qux')],
+              dtype='object')
+        """
+        if sep is not None:
+            # TODO: Add support for separator to return strs instad of tuples
+            raise NotImplementedError
+
+        if not isinstance(self, ABCMultiIndex):
+            return self
+
+        idx = Index(self.values, tupleize_cols=False)
path: pandas/core/indexes/base.py
text: @user1:
Maybe ``iter(self)`` is slightly more efficient? (as I think it happens anyway inside ``self.values``, and I don't think passing through an ``array`` helps)

@user1:
Sorry, I had forgot this... what do you @author think?

@user2:
If use don't do it here, the `Index` constructors has to convert tot an array anyway for storage, right?

```python
In [4]: pd.Index(np.array([(1, 2), (3, 4)], dtype=object), tupleize_cols=False).values
Out[4]: array([(1, 2), (3, 4)], dtype=object)
```

In theory, the array of tuples should just pass through without copy.

@user1:
@user2 You're right

``` python
In [2]: a = np.array([1, 2], dtype=object)

In [3]: a[:] = [(1, 2), (3, 4)]

In [4]: id(a), id(pd.Index(a, tupleize_cols=False).values.base)
Out[4]: (140686849540864, 140686849540864)
```
sorry for the noise