ENH: MultiIndex.from_frame
State: closed

- [x] closes #22420
- [x] tests added / passed
- [ ] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

This pull request is to add the from_frame method of creating multiindexes. Along with this feature the helper method "squeeze" has been added for squeezing single level multiindexes to a standard index (analogous to df.squeeze).

Additionally the to_frame method was updated to guarantee that the order of the labels is preserved when converting a multiindex to a dataframe. Currently this cannot be guaranteed in Python 2.7 due to the use of a dictionary for creating the frame. With this change from_frame and to_frame are perfectly complementary.


Labels:
name: Enhancement
name: MultiIndex

Comments:
Hello @ArtinSarraf! Thanks for updating the PR.

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

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

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



##### Comment last updated on November 19, 2018 at 04:25 Hours UTC
huh? pls show a real usecase for this

in the future pls open an issue for discussion first 
Sorry should have tagged this issue opened ~2 months ago. The issue contains an example as well.

https://github.com/pandas-dev/pandas/issues/22420
Here are some more concrete examples of how these can be used as well.

Adding metadata.

    df = pd.DataFrame(np.random.randn(10, 6), columns=pd.MultiIndex.from_tuples([('NY', 'KJFK'), ('NY', 'KLGA'), ('CA', 'KLAX'), ('CA', 'KBUR'), ('AB', 'CYEG'), ('AB', 'CYYC')], names=['state', 'ICAO']))
    country_state_mapping = pd.DataFrame([['US', 'NY'], ['US', 'CA'], ['CDN', 'AB'], ['CDN', 'ON']], columns=['country', 'state'])
    meta = df.columns.to_frame(index=False)
    new_meta = meta.merge(country_state_mapping, on='state', how='left')[['country', 'state', 'ICAO']]
    df.columns = pd.MultiIndex.from_frame(new_meta)  # this can only work if to_frame guarantees label ordering, which it currently does not in 2.7, but does with this PR.

Filtering

    # continuing from previous
    meta = df.columns.to_frame(index=False)
    new_meta = meta.loc[meta.state.isin(['CA', 'AB'])]
    df = df.reindex(columns=pd.MultiIndex.from_frame(new_meta))

The examples here are extremely simplified, but reflect how my coworkers and I use pandas for working with multiindices.
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/23141?src=pr&el=h1) Report
> Merging [#23141](https://codecov.io/gh/pandas-dev/pandas/pull/23141?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/fc64ca869670d24af46b5e017baba88fdd5e9532?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/23141/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/23141?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #23141      +/-   ##
==========================================
+ Coverage    92.2%    92.2%   +<.01%     
==========================================
  Files         162      162              
  Lines       51700    51709       +9     
==========================================
+ Hits        47670    47679       +9     
  Misses       4030     4030
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.6% <100%> (ø)` | :arrow_up: |
| #single | `43.02% <40%> (ø)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/23141?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/indexes/multi.py](https://codecov.io/gh/pandas-dev/pandas/pull/23141/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9tdWx0aS5weQ==) | `95.58% <100%> (+0.03%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/23141?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/23141?src=pr&el=footer). Last update [fc64ca8...9159b2d](https://codecov.io/gh/pandas-dev/pandas/pull/23141?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

you can do the same thing if everything is in long form.

```
In [93]: (pd.merge(df.unstack().reset_index(), country_state_mapping, on='state', how='left')
    ...:    .set_index(['country', 'state', 'ICAO', 'level_2'])
    ...:    .sort_index()
    ...:    .unstack(level=[0,1,2])
    ...:    .sort_index(axis=1)
    ...: )
Out[93]: 
                0                                                  
country       CDN                  US                              
state          AB                  CA                  NY          
ICAO         CYEG      CYYC      KBUR      KLAX      KJFK      KLGA
level_2                                                            
0        0.368735  0.428429 -1.173573  0.256197  1.792880  0.414737
1        0.993952 -0.313126 -1.814687 -0.025160 -0.775963 -0.665919
2       -0.635185  2.063095  0.063650 -0.939815  0.219432  1.489314
3       -0.156358  0.201501  0.344305 -1.562817 -0.103385  0.482686
4        0.551272 -1.628747 -1.387776 -1.400052 -1.493635  2.323896
5       -1.487852  0.466714  1.195675  0.006298  0.277463  1.170253
6       -0.453253  1.850580  0.240695  1.375471  0.144580  0.012171
7       -0.079981 -1.139398  0.855885  2.687475 -1.374322  0.543441
8       -1.432287  0.100697 -0.554310 -0.306952  1.011671 -0.723025
9       -0.235468  0.893887  0.638946  0.916163  0.149475  1.121286
```

What you are proposing is wide from manipulation, which though a nice idea, and in this particular case makes sense. Is not generaly purpose, nor easy for people to do (and requires sorting guarantees).

So why should this be added?
One of the nice features of multiindexes is that they act as meta-dataframes for the associated numeric data, allowing a frame to be much more concise and memory efficient. Long form manipulations require multiple pivots (which can be time consuming) and can require significant extra memory. The relationship between multiindexes and a standard dataframe can also be demonstrated through this following example. One can substitute a multiindex (and I have seen this done before) with a separate metaframe like so:

    df = pd.DataFrame(np.random.randn(10, 6))
    meta = pd.DataFrame([('NY', 'KJFK'), ('NY', 'KLGA'), ('CA', 'KLAX'), ('CA', 'KBUR'), ('AB', 'CYEG'), ('AB', 'CYYC')], columns=['state', 'ICAO'])
    df.loc[:, meta.loc[meta.state.isin(['NY', 'AB'])].index]  # equivalent to above filtering example

I believe that this, along with the fact that to_frame already exists shows that from_frame is a reasonable and logical complementary method to be added as a way to instantiate multiindexes.

Regarding the sorting guarantees of to_frame, I think that this is something that any user would reasonably expect, considering the similarities between a mi and a df. Additionally, not only does it keep manipulations, that might involve using it, consistent, but all other related casting methods (e.g. tolist) ensure the sorting of the resultant type matches that of the original multiindex. At the very least I think adding a parameter to to_frame for preserving the original sorting would make sense. It would be very easy to add to the existing implementation.


I would be happy to contribute to any further discussions. Is there a more appropriate forum for that?

Also I will add tests to the PR this week.

Are there any types of examples in particular that would help move along the addition of this feature?

Thanks.
> Are there any types of examples in particular that would help move along the addition of this feature?

some usecases, and showing how its possible now, but how the 'new' method makes it easeir.
Here is a demo to show the benefits of the from_frame method. Thanks for taking the time to look.
Some tests have been added as well.

    # for convenience of this demo
    pd.MultiIndex.from_frame = staticmethod(lambda df: pd.MultiIndex.from_tuples(list(df.values), names=list(df)))

# Setup df

    # memory usage of below frame ~82000000
    #    multiindexes this large are not uncommon when dealing with things such as weather data for many cities
    #    with their associated meta location info (i.e. country, state, county, etc.)
    df = df = pd.DataFrame(
        np.random.randn(10000, 1024), 
        index=pd.date_range('19910905', periods=10000, name='date'), 
        columns=pd.MultiIndex.from_product(
            [list('abcd'), list('efgh'), list('ijkl'), list('mnop'), list('qrst')], 
            names=['L1', 'L2', 'L3', 'L4', 'L5']
        )
    )

    meta_mapping = pd.DataFrame([['x', 'a'], ['x', 'b'], ['y', 'c'], ['z', 'd']], columns=['L0', 'L1'])


# Adding meta data

    # Long Form
    # peak memory usage ~573440080  --  ~7x larger than original df
    # execution time: ~13.7 secs

    (
        df.unstack().reset_index().merge(meta_mapping, on='L1', how='left')
        .set_index(['L0', 'L1', 'L2', 'L3', 'L4', 'L5', 'date'])[0].unstack([0, 1, 2, 3, 4, 5])
    )


    # Wide Form
    # peak memory usage ~82000000  --  no difference from original df
    # execution time: ~0.271 sec  --  ~50x faster than long form
    # cleaner and does not require explicitly writing out positional arguments or axis 0 names
    # requires guaranteed ordering of to_frame - easy to guarantee/implement

    (
        df.T.set_index(pd.MultiIndex.from_frame(
            df.columns.to_frame(index=False)
            .merge(meta_mapping, on='L1', how='left')[['L0', 'L1', 'L2', 'L3', 'L4', 'L5']]
        )).T
    )

# Quick Prototyping
Use this df for the example below

    # Often find it easier to prototype meta frames in excel. I then need to use that frame as a multiindex.
    # Assume the below snippet was copied from excel rather than pyperclip
    pyperclip.copy('''
    country   state   county        city       
    USA       ct      fairfield     Greenwich  
    USA       ct      fairfield     Stamford   
    USA       ny      westchester   Bronxville 
    USA       ny      bronx         Bronx      
    USA       nj      monmouth      Rumson     
    USA       nj      monmouth      Middletown 
    USA       nj      ocean         Ocean      
    Canada    on      york          Toronto    
    ''')
    meta = pd.read_clipboard()
    df = pd.DataFrame(np.random.randn(10, 8), columns=pd.MultiIndex.from_frame(meta))

    # As far as I know there is no good and quick way to do this currently. (aside from the from_tuples method I showed above)


# Formatting MultiIndex
Use this formatted df for remainder of demo

    # With from_frame
    meta = df.columns.to_frame(index=False)
    meta = meta.assign(state=meta.state.str.upper(), county=meta.county.str.title())
    df.columns = pd.MultiIndex.from_frame(meta)

    # Without from_frame
    # More verbose, not as clear and requires the use of positional levels (as far as I know there is no complementary set_level_values method for get_level_values, which would also be nice)
    df.columns = df.columns.set_levels(df.columns.levels[1].str.upper(), level=1).set_levels(df.columns.levels[2].str.title(), level=2)

# Complex Filtering/Slicing

    meta = df.columns.to_frame(index=False)  # used for all from_frame examples below


Keep only counties with > 1 city

    # With from_frame
    df.reindex(columns=pd.MultiIndex.from_frame(meta.groupby('county').filter(lambda df: len(df)>1)))

    # Long Form - same disadvantages as stated above
    (
        df.unstack().reset_index().groupby('county').filter(lambda dfx: len(dfx.city.unique())>1)
        .set_index(['country', 'state', 'county', 'city', 'level_4']).unstack([0,1,2,3])[0]
    )


Admittedly the 2 remaining examples below can be done without from_frame as well, like so:

    # Requires to_frame to be ordered
    mask = (meta...) & (meta...) | (meta...)
    df.loc[:, mask]


Get CT + NJ without Greenwich

    # With from_frame
    filtered_df = meta.loc[(meta.state.isin(['CT', 'NJ'])) & (meta.city != 'Greenwich')]
    df.reindex(columns=pd.MultiIndex.from_frame(filtered_df))

    # Without from_frame
    # While this is more concise, it requires two different chained methods, while the from_frame
    # method simplifies the filter to a standard dataframe filter
    df.loc[:, pd.IndexSlice['USA', ['CT', 'NJ'], :]].drop('Greenwich', axis=1, level='city')


Get all cities that don't start with "Bron"

    # With from_frame
    filtered_df = meta.loc[~meta.city.str.startswith('Bron')]
    df.reindex(columns=pd.MultiIndex.from_frame(filtered_df))

    # Without from_frame
    # Not sure of a good way without going to long form

# Custom Sorting
This is nice for formatting report output

Given the following custom ordering

    order = {'state': ['NJ', 'CT', 'NY', 'ON'], 'country': ['USA', 'Canada']}

    # With from_frame
    def custom_sort(df, order):  # would be nice to see something like this as a df method (with some edge case handling)
        df = df.copy()
        orig_dtypes = df.dtypes.to_dict()
        for col in df:
            if col in order:
                df.loc[:, col] = pd.Categorical(df.loc[:, col], order[col])
        df = df.sort_values(by=df.columns.tolist())
        for col in df:
            df[col] = df[col].astype(orig_dtypes[col])
        return df
    
    meta = df.columns.to_frame(index=False)
    df.reindex(columns=pd.MultiIndex.from_frame(custom_sort(meta, order)))

    # Without from_frame
    # However, this requires explicitly listing out all the columns
    # Not ideal/practical if you have many more items
    df.iloc[:, [5,4,6,0,1,3,2,7]]

Is there a way to rerun the Azure pipeline tests? The tests only failed for “Windows py36_np14” environment and the failed tests don’t seem to be related to my commits at all. I’m guessing it was a blip in the testing framework.
@jreback

Please see additions to demo (Formatting and Custom Sorting):
https://github.com/pandas-dev/pandas/pull/23141#issuecomment-430083542

Also any ideas on the tests? I don't see anything in the logs that point to any changes I've made.
@jreback. Any thoughts on the examples provided above?
@pandas-dev/pandas-core if anyone has thoughts here
@datapythonista. Changes have been made and pushed. I've also added from_frame to the "See Also" of the other 3 contructor methods. Thanks.
@gfyoung. All requested changes have been applied. Thanks.
before we get more comments on this PR code itself. would like commentary on the merits of this idea. 

@ArtinSarraf actually might be beneficial to open an issue about this first.
@ArtinSarraf i see you did actually. 
@jreback - I’ve referenced a couple of the important comments in this PR in the associated issue.
@TomAugspurger - I've made the changes to ensure that the dtypes are preserved in both from/to_frame. I've also added tests to both to catch this. Also added one extra ability to from_frame, passing an optional names argument for overriding the default names (similar to the to_frame method).
@jreback / @TomAugspurger / @gfyoung 

Any other feedback on code/merits of this PR?
I'm not convinced ``squeeze`` should be exposed - not even as a parameter. ``._squeeze()`` is - in the only case in which it makes sense - just an alternative for ``.get_level_values(0)``. And in general in the API we do not distinguish much between ``Index`` and ``MultiIndex`` with 1 level. In fact, I guess any difference (not just in behavior, also in performance) can be considered a bug.

So why should (unsophisticated) users bother about this difference?!

(Contrast with ``df.squeeze()`` which changes a ``DataFrame`` in a ``Series`` with very different indexing semantics)

Also if we provide ``squeeze`` we probably want to provide the equivalent flat ``Index`` -> 1-level ``MultiIndex`` syntactic sugar?
Another thought: is there any reason why we should not accept a 2-d ``array``, or even list of lists, rather than just a ``DataFrame``? After all, we are primarily looking at the ``df.values`` (although ``.columns`` is useful for the level names). We would have to change the method name (``from_data``?), but we would mirror slightly better both the initialization of a ``pd.Series(a_1d_array)``, and the interchangeability of 2d structures (``DataFrame``/``MultiIndex``/``array``/nested lists) as right hand side of an assignment.
@toobaz - Agreed regarding the squeeze method. The original inspiration for it was dealing with dataframes coming from external processes that I have no control over which contained single level multiindexes. I think almost all of these cases were due to bad coding practices that in older versions of pandas would have resulted in a regular index instead (e.g. df.columns = [['a','b','c']]). The only time I would see it having any usefulness over get_level_values(0) is if you don't know whether an object is a multilevel multiindex or not, which probably shouldn't ever be the case. That certainly isn't the case here, so I really should just be using get_level values, and will update accordingly.

> And in general in the API we do not distinguish much between Index and MultiIndex with 1 level. In fact, I guess any difference (not just in behavior, also in performance) can be considered a bug.

Off the top of my head, groupby, while behaving as I would expect, is slightly different.

df.groupby('col1') -> df.groupby(('col1',))
only problem with accepting generic 2d input is we lose the names
but that would be ok if names is None

so agree this would be a nice addition (just need to wrap the input in the Dataframe constructor)
@toobaz regarding the second commnet, would that maybe make more sense as an orient parameter in from_arrays?
> @toobaz regarding the second commnet, would that maybe make more sense as an orient parameter in from_arrays?

Good point... but then, I would as well merge the functionality of ``from_frame`` in ``from_arrays``.

Not just for avoiding duplication in the API, but also because I find it wrong (I had never noticed) that

```
In [2]: d = [[1, 2], [3, 4]]

In [3]: pd.MultiIndex.from_arrays(pd.DataFrame(d))
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
[...]
TypeError: Input must be list-like

In [4]: pd.MultiIndex.from_arrays(np.array(d))
Out[4]: 
MultiIndex(levels=[[1, 2], [3, 4]],
           labels=[[0, 1], [0, 1]])
```

Again: my point is that, where possible, we treat all kinds of 2d data in the same way. So if ``from_arrays`` is able to manage a 2d array, it should also be able to manage a ``pd.DataFrame``. Now, as you correctly point out, the orientation changes - and it would be cool to have ``from_arrays`` to manage the different orientation, rather than a new method. I would just not add a new method only for taking care of column names, which are optional.
If from_arrays is updated to take a DataFrame, it will need to have a very specific implementation for them. First it will need to implicitly change the default orientation. Even if the default is set to None so that it can be inferred, it would be strange that the from_arrays(df) and from_arrays(df.values) would be different (since df.values probably shouldn’t be treated differently than any other 2d list).

Also from_frame is not just a replacement for from_arrays(df.values.T) since that would potentially lose dtype info (e.g. categoricals).

I think tailoring from_arrays to allow DataFrames might introduce quite a bit more inconsistencies and specifically tailored logic than we would want in a generic method.
> If from_arrays is updated to take a DataFrame, it will need to have a very specific implementation for them. First it will need to implicitly change the default orientation. Even if the default is set to None so that it can be inferred, it would be strange that the from_arrays(df) and from_arrays(df.values) would be different (since df.values probably shouldn’t be treated differently than any other 2d list).

What I had in mind would actually be that ``from_arrays(df)`` would behave exactly like ``from_arrays(df.values)`` and that ``from_arrays(df, orient="horizontal")`` would behave like you are designing ``from_frame(df)``. But I do realize now that one expects a ``DataFrame`` to be transformed (by default) in a ``MultiIndex`` where each column becomes a level. So my proposal is not good.

I think the original sin is in ``from_arrays`` which should have defaulted to not changing the orientation of data, and I assume it is too late to change this. So you are right that ``from_arrays(df)`` is not the correct replacement for ``from_frame``. But I still believe that:
- the difference between the new method and ``from_arrays`` should primarily be of orientation, not of type: it should accept any 2d data structure (and ``from_arrays`` too, but now that's a separate issue)
- the name should reflect this. I would prefer ``from_data`` or even better ``from_array`` to ``from_frame``
Agreed that the orientation difference warrants two methods.

________________________________
From: ArtinSarraf <notifications@github.com>
Sent: Monday, November 12, 2018 12:36:17 PM
To: pandas-dev/pandas
Cc: Tom Augspurger; Mention
Subject: Re: [pandas-dev/pandas] Enhancement/from frame (#23141)


If from_arrays is updated to take a DataFrame, it will need to have a very specific implementation for them. First it will need to implicitly change the default orientation. Even if the default is set to None so that it can be inferred, it would be strange that the from_arrays(df) and from_arrays(df.values) would be different (since df.values probably shouldn’t be treated differently than any other 2d list).

Also from_frame is not just a replacement for from_arrays(df.values.T) since that would potentially lose dtype info (e.g. categoricals).

I’m not opposed to moving the functionality into somewhere more generic, but I think tailoring from_arrays to allow DataFrames might introduce quite a bit more inconsistencies and specificity tailored logic than we would want in a generic method.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<https://github.com/pandas-dev/pandas/pull/23141#issuecomment-437985400>, or mute the thread<https://github.com/notifications/unsubscribe-auth/ABQHIpmVdqpACWOsJCo0wnKevRgs91G4ks5uub-hgaJpZM4Xa2wj>.