BUG: Panel.to_frame() with MultiIndex major axis
State: closed

Closes #5402  WIP for now.

So this is a hack to get things "working".

I mainly just wanted to ask if there was a better way to get the levels and labels to the MultiIndex constructor.
Don't spend long thinking about it (this is my PR after all), but if you know of a quick way off the top of your head, I'd be interested.
I essentially need to
1. flatten the MultiIndex from major axis (levels and labels)
2. combine that flattened index with the minor axis (levels and labels)

Step 2 could be complicated by `minor_axis` being a MultiIndex also, I should be able to refactor this pretty easily to handle that.  I think my was is making several copies of each index, and I'm not sure that they're all necessary.


Labels:

Comments:
What do you mean by flatten? You can get tuples by using the values
property. Check out _sparsify too.
On Nov 2, 2013 3:37 PM, "Tom Augspurger" notifications@github.com wrote:

> Closes #5402 https://github.com/pydata/pandas/issues/5402 WIP for now.
> 
> So this is a hack to get things "working".
> 
> I mainly just wanted to ask if there was a better way to get the levels
> and labels to the MultiIndex constructor.
> Don't spend long thinking about it (this is my PR after all), but if you
> know of a quick way off the top of your head, I'd be interested.
> I essentially need to
> 1. flatten the MultiIndex from major axis (levels and labels)
> 2. combine that flattened index with the minor axis (levels and labels)
> 
> Step 2 could be complicated by minor_axis being a MultiIndex also, I
> should be able to refactor this pretty easily to handle that. I think my
> was is making several copies of each index, and I'm not sure that they're
> 
> ## all necessary.
> 
> You can merge this Pull Request by running
> 
>   git pull https://github.com/TomAugspurger/pandas to-frame-multi
> 
> Or view, comment on, or merge it at:
> 
>   https://github.com/pydata/pandas/pull/5417
> Commit Summary
> - BUG: Panel.to_frame() with MultiIndex major axis
> 
> File Changes
> - _M_ pandas/core/panel.pyhttps://github.com/pydata/pandas/pull/5417/files#diff-0(36)
> - _M_ pandas/tests/test_panel.pyhttps://github.com/pydata/pandas/pull/5417/files#diff-1(19)
> 
> Patch Links:
> - https://github.com/pydata/pandas/pull/5417.patch
> - https://github.com/pydata/pandas/pull/5417.diff

OK, I think this is just about there if you want to take a look. Essentially I check if the major and minor axes are a MultiIndex.  If they are, I repeat labels the necessary number of times and rearrange them appropriately. I need to write a few more test cases to make sure I'm not getting any false positives.

I didn't see a vbench covering to_frame. Think we need one?

I believe we need to set up a way to have vbench conditional on version -
@y-p I recall you saying that vbenches were supposed to run across many
versions of pandas, but maybe we could add a version check (decorator?
kwarg?) to the setup code?

maybe put those conversions in a function (in index?)

@jtratner, that was re: test_perf.py, I had some scripts relying on it to auto. sample perf back in history
and find regression points using bisection. When some contrib. changes  to test_perf broke 
it for older pandas, I added some comments to it to make it clear that back-compat is a requirement,
since test_perf itself relies on pandas for data processing.

I didn't see anything here that calls for changes in vbench, can you explain the issue?

okay, that makes sense. I just wasn't sure if to_frame() has always been a method on Panel. thanks for clarifying.

@jreback Which conversions? Just this line?

``` python
major_labels = [np.repeat(x, K) for x in self.major_axis.labels]
```

@jtratner, ok well In that case I think the existing `start_date` kwarg attached to each vbench should be all you need.

wow, I should have noticed that.

makes a lot of sense as an index method because has method in index and multi index 

Ok those tests should be clearer now. Earlier @jtratner mentioned

> I'm not totally sure, but I believe you could actually pay the cost of filtering the panel on selector first, and then you wouldn't have to filter on selector everywhere else. Slower, but cleaner maybe?

which makes a lot of sense.  I just need to figure out the best way filter the panel... It's not immediately obvious to me.

> Can you post some examples of 'selectors' - - might be easier to reason
> about the selector separate from the context of this method.

`selector` is a 1-d array of bools built by `ravel`ing a 2-d `mask`. The mask is the `(major, minor)` axis pairs that contain `NaNs` (or will contain `NaN`s).

Example:

``` python
idx = pd.MultiIndex.from_tuples([(1, 'one'), (1, 'two'), (2, 'one'), (2, 'two')])
df = pd.DataFrame({'A': [1, 2, 3, 4], 'B': ['a', 'b', 'c', 'd']}, index=idx)
wp = pd.Panel({'i1': df, 'i2': df})

wp.loc['i1', (1, 'one')]['A'] = np.nan
In [24]: mask = com.notnull(wp.values).all(axis=0)

In [25]: mask
Out[25]: 
array([[False,  True],
       [ True,  True],
       [ True,  True],
       [ True,  True]], dtype=bool)
In [28]: selector = mask.ravel()

In [29]: selector
Out[29]: array([False,  True,  True,  True,  True,  True,  True,  True], dtype=bool)
```

so that one false is coming from the `nan`. It means we want to filter the first `(major, minor)` pair from the output.

EDIT: I think this is a bug. made an issue at #5773

Getting back to this, and I'm cleaning up some setting with copy errors in my tests.

Should the following work?

``` python

        idx = MultiIndex.from_tuples([(1, 'one'), (1, 'two'), (2, 'one'),
                                      (2, 'two')])
        df = DataFrame([[1, 'a', 1], [2, 'b', 1], [3, 'c', 1], [4, 'd', 1]],
                       columns=['A', 'B', 'C'], index=idx)
        wp = Panel({'i1': df, 'i2': df})

        wp.loc['i1', (1, 'one'), 'A'] = np.nan
```

Raises an

```
IndexError                                Traceback (most recent call last)
<ipython-input-2-3346cc0dd38b> in <module>()
      5 wp = Panel({'i1': df, 'i2': df})
      6 
----> 7 wp.loc['i1', (1, 'one'), 'A'] = np.nan

/Users/tom/Envs/pandas-dev/lib/python2.7/site-packages/pandas-0.13.0rc1_87_g9fefe8c-py2.7-macosx-10.8-x86_64.egg/pandas/core/indexing.pyc in __setitem__(self, key, value)
     94             indexer = self._convert_to_indexer(key, is_setter=True)
     95 
---> 96         self._setitem_with_indexer(indexer, value)
     97 
     98     def _has_valid_type(self, k, axis):

/Users/tom/Envs/pandas-dev/lib/python2.7/site-packages/pandas-0.13.0rc1_87_g9fefe8c-py2.7-macosx-10.8-x86_64.egg/pandas/core/indexing.pyc in _setitem_with_indexer(self, indexer, value)
    297                 obj = self.obj[item]
    298                 index = obj.index
--> 299                 idx = indexer[:info_axis][0]
    300                 try:
    301                     if idx in index:

IndexError: tuple index out of range
```

@jreback ready to go at your leisure. let me know if you want a note in `v0.14.0.txt`. I put one in `release.rst`.

@TomAugspurger this is a bug fix, so unless you want to, nothing needed for whatsnew (fyi now targeting 0.13.1, though it still may go to 0.14, depends if we have critical bugs ..)

@jtratner look ok to u?

@TomAugspurger this looks fine...pls move release notes to 0.13.1 and rebase...

@jreback rebased and moved to 0.13.1

thanks @TomAugspurger !