Add to_flat_index method to MultiIndex
State: closed

- [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

Labels:
name: API Design
name: MultiIndex

Comments:
Hello @WillAyd! Thanks for submitting the PR.

 - There are no PEP8 issues in the file [`pandas/core/indexes/multi.py`](https://github.com/WillAyd/pandas/blob/c9b26295bed1e70c7ef2f03dc3e265d74848ec1e/pandas/core/indexes/multi.py) !

 - There are no PEP8 issues in the file [`pandas/tests/indexes/multi/test_conversion.py`](https://github.com/WillAyd/pandas/blob/c9b26295bed1e70c7ef2f03dc3e265d74848ec1e/pandas/tests/indexes/multi/test_conversion.py) !


# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/22866?src=pr&el=h1) Report
> Merging [#22866](https://codecov.io/gh/pandas-dev/pandas/pull/22866?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/20bdb3e4019afe2037b26524a447ac8913d6e325?src=pr&el=desc) will **increase** coverage by `<.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/22866/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/22866?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #22866      +/-   ##
==========================================
+ Coverage   92.24%   92.24%   +<.01%     
==========================================
  Files         161      161              
  Lines       51317    51318       +1     
==========================================
+ Hits        47338    47339       +1     
  Misses       3979     3979
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.63% <100%> (ø)` | :arrow_up: |
| #single | `42.31% <50%> (-0.01%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/22866?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/indexes/multi.py](https://codecov.io/gh/pandas-dev/pandas/pull/22866/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9tdWx0aS5weQ==) | `95.47% <100%> (ø)` | :arrow_up: |
| [pandas/core/indexes/base.py](https://codecov.io/gh/pandas-dev/pandas/pull/22866/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9iYXNlLnB5) | `96.46% <100%> (ø)` | :arrow_up: |
| [pandas/core/series.py](https://codecov.io/gh/pandas-dev/pandas/pull/22866/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvc2VyaWVzLnB5) | `93.68% <0%> (-0.03%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/22866/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.02% <0%> (-0.01%)` | :arrow_down: |
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/pull/22866/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `96.82% <0%> (ø)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/22866?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/22866?src=pr&el=footer). Last update [20bdb3e...bea4e85](https://codecov.io/gh/pandas-dev/pandas/pull/22866?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

can you add to api.rst. I would add the ``sep=None`` arg here, maybe can defer the actual handling of the string concat to a later PR
Yep my goal is to defer the actual sep implementation, if only because I'm not sure how we'd want to handle say timestamp and categorical objects so probably needs further discussion. 

For now I've stubbed it out and raise a `NotImplementedError` but if you want to go about in a different way lmk
> I'm not sure how we'd want to handle say timestamp and categorical objects so probably needs further discussion.

I think if you specify `sep`, everything gets stringified? (just using the default conversion to string, without any ability to use a custom format, the user can always do that before they use this method)

---

About the name, do we want to call this `to_index` ? It might feel a bit strange, since you already have an index to start with (it's only a MultiIndex, but which is still an index). 
So you would get something like `df.index.to_index()` or `df.columns.to_index()`, which seems a bit strange.

Alternative would be `.flatten(..)`, but I don't know to what extent it is a problem that this clashes with the numpy method. 
Or `to_single`, `collapse_levels`, ... (but I don't really like those :-))


Yesterday, I almost left the same comment as Joris about the name :)
Something about MultiIndex.to_index bothered
me, because it implies that MultiIndex isn't an Index.

In the end, I deleted the comment because

1.) there's a nice symmetry with the other to_* methods
2.) I couldn't come up with a better name.

On Wed, Oct 3, 2018 at 3:52 AM Joris Van den Bossche <
notifications@github.com> wrote:

> I'm not sure how we'd want to handle say timestamp and categorical objects
> so probably needs further discussion.
>
> I think if you specify sep, everything gets stringified? (just using the
> default conversion to string, without any ability to use a custom format,
> the user can always do that before they use this method)
> ------------------------------
>
> About the name, do we want to call this to_index ? It might feel a bit
> strange, since you already have an index to start with (it's only a
> MultiIndex, but which is still an index).
> So you would get something like df.index.to_index() or
> df.columns.to_index(), which seems a bit strange.
>
> Alternative would be .flatten(..), but I don't know to what extent it is
> a problem that this clashes with the numpy method.
> Or to_single, collapse_levels, ... (but I don't really like those :-))
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/22866#issuecomment-426559768>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/ABQHIliuvpWPdt0UAEJ0_B5-hfB37kaqks5uhHqvgaJpZM4W9ksx>
> .
>

maybe ``as_index()``, though I agree I like the symmetry. btw is ``Index.to_index()`` tested? (iow identity)
Agreed with all comments so far - the name was initially off-putting to me but ultimately went with it given the consistency of it with the API, and I didn't want to obfuscate what `flatten` meant from a NumPy perspective.

> I think if you specify `sep`, everything gets stringified?

Probably. I'm probably being ultra-conservative here but something about the string conversion and "round-tripability" of this is why I was planning on holding off until a separate PR to even go down that path. If it's a blocker here can certainly add the simple implementation.

> `Index.to_index()` tested? (iow identity)

No but I also don't believe this is implemented atm. Are you thinking this implementation in this PR is better served in the `base.py` module? 

Any other thoughts on this?
Added the identity method. I did not change the name from `to_index` to `to_flat` because I personally prefer the API consistency with `to_series` and `to_frame` though I'm not going to get terribly hung up on that if we feel `to_flat` is better.

I also kept the documentation geared only towards MultiIndex atm. Not sure if we want to document the identity and if it serves a purpose outside of not breaking chained ops (at least in its current state) but happy to update with whatever we feel necessary
What about `to_flat_index()`? I think `to_index()` is pretty confusing for transforming a `MultiIndex` to a base `pandas.Index` (my expectation would be that this is the identity function), and it would be better to pick something slightly verbose rather than slightly confusing.
As said above, I am also -1 on `to_index`, this is just a confusing name. I agree with @shoyer that it's then better to have a verbose but at least readable / clear name. 

Other names I was thinking about: `.flatten_levels()` or `flatten_index_levels` (this last one is maybe too verbose, although explicit). But `to_flat_index()` is fine for me.

Now you added it to the Index/MultiIndex. But if you want to do be able to directly do this is in a method chain (eg after a groupby operation), it would be needed on the DataFrame as well. Is this something we would also want to consider (but perfectly fine with start to add it at least to the index itself)


Sorry for coming late. I'm also not convinced by ``to_index``, and weakly in favor of ``to_flat`` (although ``flatten`` would not disturb me).

I'm also not convinced by ``sep``. I think that:
- the necessity that emerged lately (e.g. in the mailing list, when discussing ``MultiIndex``es produced by ``GroupBy.apply``) was for instance to transform a level of operation names ('mean', 'min', 'max') and one of variables ('a', 'b', 'c') in an index of strings such as 'mean of a', 'mean of b' and so on. Now, ``sep=' of '`` does (once implemented) allow this, but just because it happens that there are two levels, and in the correct order, not because it makes much sense to think to the labels for each level as a flat ordered list
- I think giving less freedom today in terms of aggregation methods just calls for adding more arguments later

My idea would be to pass a callable (e.g. ``fmt``) accepting an iterable and returning anything (not necessarily a string, by the way). The ``sep`` solution would just become, on a ``MultiIndex`` with 2 levels, ``fmt='{} of {}'.format``.

In any case, if we want to discuss this and not block this PR, I would just remove the ``sep`` argument for the moment.

**EDIT**: one objection I can see to ``fmt`` is that it would be doing precisely what can be done as ``mi.to_flat().apply(fmt)``. But this is an objection to ``sep`` too I think. And probably a weak objection in any case.
> My idea would be to pass a callable (e.g. fmt) accepting an iterable and returning anything

The idea is that this would be a keyword of this (to be renamed) `to_index` method, or a method on its own?

In any case, I like this idea, it gives indeed more flexibility than a `sep`.

We had some discussion about this before (I think in light of deprecating `add_prefix`/`add_suffix`) to have an easier way to format values in an Index/Series (related issues: https://github.com/pandas-dev/pandas/pull/18347, https://github.com/pandas-dev/pandas/issues/17211)

This is kind of 

```
In [142]: idx = pd.MultiIndex.from_product([['mean', 'sum'], ['a', 'b']])

In [143]: idx.map(lambda x: "{0} of {1}".format(*x))
Out[143]: Index(['mean of a', 'mean of b', 'sum of a', 'sum of b'], dtype='object')
```
> The idea is that this would be a keyword of this (to be renamed) `to_index` method, or a method on its own?

A keyword of this.

> This is kind of

Right, I had missed this. Basically ``idx.map(lambda x: "{0} of {1}".format(*x))`` would just become ``idx.to_flat("{0} of {1}".format)``. So maybe this would be a bit redundant. Still, ``sep`` seems too restrictive to me, and this added sugar candy would have a very low implementation cost.
after reading #23141 maybe we should call this ``.squeeze()``?
This is a bit different from the squeeze semantics.

squeeze will reduce dimensionality *if possible* (only one item on that axis).

`.flatten` / `.to_index` / whatever will always reduce the dimensionality.
from https://github.com/pandas-dev/pandas/pull/22866#issuecomment-437688069

ahh right, ok then.
> This is a bit different from the squeeze semantics.
> 
> squeeze will reduce dimensionality _if possible_ (only one item on that axis).
> 
> `.flatten` / `.to_index` / whatever will always reduce the dimensionality.

Just for pickiness: the two differ in fact also for the "only one item on that axis" case, as the former will return the labels as items, while the latter will return length one tuples as items.
Renamed to `to_flat_index` and removed `sep` parameter based off feedback above
Seems good to me
The inheritance model does seem to make more sense here so I've updated the latest commit to reflect that
Thanks @WillAyd!