ENH/API: DataFrame.stack() support for level=None, sequentially=True/False, and NaN level values.
State: closed

closes #8851
closes #9399 
closes #9406
closes #9533 


Labels:
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode
name: API Design

Comments:
I don't like this conversions to sets. why not just use a list and make a scalar into a single-element list. thus guaranteeing you can always iterate over the levels?

(its kind of what you are doing with a set), but we use lists for this purpose.

I didn't want to break backwards compatibility with the existing treatment of lists, e.g. the fact that `df.stack(level=[0,1])` is equivalent to `df.stack(level=0).stack(level=0)`, and is different from `df.stack(level=[1,0])` (which is equivalent to `df.stack(level=1).stack(level=0)`). Note that the order of the levels in the list matters.

The behavior I implemented for `df.stack(level={0, 1})` is different from `df.stack(level=[0, 1])`, in that I want to stack the two levels simultaneously, without introducing extra rows corresponding to unused combinations of `level=0` and `level=1` values. (Note that the `dropna` parameter isn't really sufficient to get at the desired behavior, since it doesn't make a distinction between values that were missing in the original DataFrame and values that are missing because the index value didn't exist in the original DataFrame.) Since here I want to stack the levels simultaneously, their order doesn't matter, which seems like the natural semantics of a set.

With this PR, one can do `df.stack(level=[0, {1, 5}, 3])` to say first stack level 0, then stack levels 1 and 5 simultaneously, and then stack level 3. That's probably excessive, but I figured once I allowed for a distinction between `level=[0, 1]` and `level={0, 1}`, might as well go all out and allow a list of levels and/or sets of levels.

An alternative implementation, which would be more restrictive, would be be to stick with lists, but add a boolean `simultaneous` flag (which would default to `False` for backwards compatibility) indicating that all the levels in the list should be stacked simultaneously. But it's not as flexible.

Argh, Python 2.6 doesn't support set comprehension.

Arguments whose meaning changes depending on their type seems very un-pythonic to me, so I am -1 on the set/list distinction.

Using a list of sets to control simultaneous stacking of levels is cute but complex, and I'm struggling to think of when this would actually be useful. I don't think it's so bad to call `stack` repeatedly.

However, I do think handling stacking simultaneously would be a good change to the API. It is technically a break in backwards compatibility, so we'll need to think about how that could be rolled out. We could do that with a keyword. But I also think the number of people who would be effected by this is likely to be quite small (when the existing behavior is encountered, it is probably followed by dropna), and in the long term there wouldn't be any point to including the flag.

I agree that providing a list of sets is extremely unlikely.  My main requirement is to be able to support simultaneous stacking of multiple levels, and in particular of all levels: If `df.shape = (m, n)`, I'd like to be able to stack the columns to get a `Series` of length `m * n`, regardless of the level structure of `df.columns` (in particular if it is not a "product", i.e. where `np.product([len(lev) for lev in df.columns.levels]) > n`), and regardless of whether `df` has any missing values. (If `df` has no missing values, `df.stack(level=list(range(len(df.columns.levels))), dropna=True)` will do. But since in interpreting `dropna=True` there is no distinction made between values that are missing in the original data and missing values for "phantom" level combinations that are created when stacking levels sequentially, this doesn't work in general.)

I sort of like my current distinction between lists and sets, but if every else thinks it's non-pythonic, I'm happy to change it. It would be reasonably straightforward to change this PR to simply stack a list of levels either sequentially or simultaneously based on a new flag. The only thing one would give up is supporting a list of sets, which I agree is a rather unlikely use case.

As an aside, it would be useful, I think, to support an 'all' value for the `levels` parameter, so that one doesn't need hard-code `level=[1, 2, 3]` or `level=list(range(len(df.columns.levels)))`. Anything better than `level='all'`?

> As an aside, it would be useful, I think, to support an 'all' value for the levels parameter, so that one doesn't need hard-code `level=[1, 2, 3]` or `level=list(range(len(df.columns.levels)))`. Anything better than `level='all'`?

I agree, this would be useful functionality. But using `level='all'` is a bad idea, because `'all'` is a valid name for a level. Rather than adding a new keyword argument or sentinel value, I think this would be handled most cleanly by adding a new method: `stack_all`.

Also: whatever we settle on here for changing `stack`, we should probably also implement for `unstack` (though `unstack_all` would be unnecessary).

another option 

is to do something like (IIRC this is useful other times as well)

`df.stack(levels=pd.AllLevels)`

I'm not sure why when I looked earlier I thought the changes I made to `stack()` weren't applicable to `unstack()`. Only difference I see now is that `unstack()` is missing the `dropna` parameter.

@seth-p can you revisit?

Haven't looked at this in a while. Will try to revisit over next few days.

@jreback, @shoyer, question for you guys: Is `pd.core.reshape.stack()` a "public" API, that I should not alter (except in a backwards compatible way), or am I free to change it as I see fit so long as `pd.DataFrame.stack()` remains backwards compatible? I think it should be regarded as internal -- it doesn't show up in the API Reference, http://pandas.pydata.org/pandas-docs/version/0.15.2/api.html (though that's not necessarily complete) -- and that I can change it if I want, but figured I'd check first.

@seth-p I agree, I don't think we make any API guarantees for `pandas.core` -- only for the top level namespace.

I don't really like the `pd.Index.ALL_LEVELS`. Can't we use `None` for this? `df.unstack(level=None)`

@jorisvandenbossche, ok, I've removed `ALL_LEVELS` and made `df.stack(level=None)` mean all levels.

I haven't made any corresponding changes to `DataFrame.unstack()`, as that code is very different from `DataFrame.stack()`. I would have expected `DataFrame.unstack()` to be implemented as `DataFrame.T.stack().T`, but it's not (except that it is implemented as `DataFrame.T.stack()` in the special case that `level` is a single number and `df.index` is a simple `Index` (not a `MultiIndex`).)

In general the code (in `reshape.py`) that implements `DataFrame.unstack()` (`unstack()`, `_unstack_frame()`, and `_unstack_multiple()`) looks quite different from the code that implements `DataFrame.stack()` (`stack()`, `stack_multiple()`, and `_stack_multi_columns()` -- before the changes of this PR).

Comparing the results of `df.unstack()` to `df.T.stack().T`, I observe two differences:
1.  dtypes. Since dtypes are done by column, transposing can mess up dtypes. Given this,I'm still thinking what to do about `unstack()`.
2. When a MultiIndex label is `NaN`, things are just plain weird. Perhaps there should be a separate issue for this. While the current code passes the `test_unstack_nan_index()` in `test_frame.py`, the treatment of `NaN` labels seems very inconsistent/unreliable:

```
In [140]: df = pd.DataFrame(np.arange(4).reshape(2, 2),
                            columns=pd.MultiIndex.from_tuples([('A','a'), ('B', 'b')],
                                                              names=['Upper', 'Lower']),
                            index=Index([0, 1], name='Num'), dtype=np.float64)

In [141]: df = pd.DataFrame(np.arange(4).reshape(2, 2),
                            columns=pd.MultiIndex.from_tuples([('A',np.nan), ('B', 'b')],
                                                              names=['Upper', 'Lower']),
                            index=Index([0, 1], name='Num'), dtype=np.float64)

In [148]: df
Out[148]:
Upper  A  B
Lower  a  b
Num
0      0  1
1      2  3

In [149]: df.stack()
Out[149]:
Upper       A   B
Num Lower
0   a       0 NaN
    b     NaN   1
1   a       2 NaN
    b     NaN   3

In [150]: df.T.unstack().T
Out[150]:
Upper       A   B
Num Lower
0   a       0 NaN
    b     NaN   1
1   a       2 NaN
    b     NaN   3

In [151]: df_nan
Out[151]:
Upper   A  B
Lower NaN  b
Num
0       0  1
1       2  3

In [152]: df_nan.stack()
Out[152]:
Upper      A  B
Num Lower
0   NaN    0  1
    b      0  1
1   NaN    2  3
    b      2  3

In [153]: df_nan.T.unstack().T
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-153-edbcaeb64f64> in <module>()
----> 1 df_nan.T.unstack().T

C:\Python34\lib\site-packages\pandas\core\frame.py in unstack(self, level)
   3486         """
   3487         from pandas.core.reshape import unstack
-> 3488         return unstack(self, level)
   3489
   3490     #----------------------------------------------------------------------

C:\Python34\lib\site-packages\pandas\core\reshape.py in unstack(obj, level)
    439     if isinstance(obj, DataFrame):
    440         if isinstance(obj.index, MultiIndex):
--> 441             return _unstack_frame(obj, level)
    442         else:
    443             return obj.T.stack(dropna=False)

C:\Python34\lib\site-packages\pandas\core\reshape.py in _unstack_frame(obj, level)
    479     else:
    480         unstacker = _Unstacker(obj.values, obj.index, level=level,
--> 481                                value_columns=obj.columns)
    482         return unstacker.get_result()
    483

C:\Python34\lib\site-packages\pandas\core\reshape.py in __init__(self, values, index, level, value_columns)
    101
    102         self._make_sorted_values_labels()
--> 103         self._make_selectors()
    104
    105     def _make_sorted_values_labels(self):

C:\Python34\lib\site-packages\pandas\core\reshape.py in _make_selectors(self)
    143
    144         if mask.sum() < len(self.index):
--> 145             raise ValueError('Index contains duplicate entries, '
    146                              'cannot reshape')
    147

ValueError: Index contains duplicate entries, cannot reshape
```

@seth-p maybe don't rush with changing it `None`, it was just an idea of me, I don't know what others think of it

And on `unstack`, what does it have to do with the issue you are solving here? Why does this need to be changed?

Re: `unstack()`. I would think that everything that applies to `stack()` should apply to `unstack` -- e.g. passing `level=None` to (un)stack all index levels; and passing `sequentially=True/False` to specify whether an array of levels should be (un)stacked sequentially or simultaneously -- no? Since `.unstack()` is roughly (up to edge cases) equivalent to `.T.stack().T`, I would think so. I could leave `unstack()` unchanged, but it seems deficient relative to `stack()`.

Ah, yes, when introducing new behaviour/arguments, that should indeed best in both stack/unstack if that makes sense

But how does unstacking all levels look like?

Well, if `df.index` is a `MultiIndex`, then I would expect/want `df.unstack(level=None)` to be the same as `df.T.stack(level=None).T` -- i.e. result in a `Series` whose index is a `MultiIndex` whose levels are those of `df.columns` followed by those of `df.index`. (In comparison, `df.stack(level=None)` will result in a `Series` whose index is a `MultiIndex` whose levels are those of `df.index` followed by those of `df.columns`.)

The behaviour you describe is what `df.unstack()` already does when you have a single index, I think.

```
In [39]: index = pd.MultiIndex.from_tuples([('one', 'a'), ('one', 'b'),
   ....: ...                                    ('two', 'a'), ('two', 'b')])

In [40]: s = pd.Series(np.arange(1.0, 5.0), index=index)

In [42]: s
Out[42]:
one  a    1
     b    2
two  a    3
     b    4
dtype: float64

In [43]: s.unstack()
Out[43]:
     a  b
one  1  2
two  3  4

In [44]: df = s.unstack()

In [45]: df.unstack()
Out[45]:
a  one    1
   two    3
b  one    2
   two    4
dtype: float64
```

Yes, precisely. But one may want to unstack all levels of the `.df.index` simultaneously -- particularly with `sequentially=False`. I know personally I've found myself wanting to `stack()` all column levels simultaneously, and while I haven't had personal need for `unstack()`, I must think that someone would want it to support that behavior.

I updated the `stack()` code to handle `NaN` level values. Since `unstack()` already handles them (#9061 and #9292), I believe this closes #9406.

@seth-p Can you recapitulate why we need this `sequentially` keyword? As I was looking again at the original issue #8851, and I am not sure anymore.

Because, actually, the `dropna` keyword argument already ensures the default stacking is kind of simultaneous no (not in implementation, but in result)?

```
In [49]: df = pd.DataFrame(np.zeros((2, 3)), index=pd.Index([0, 1], name='index'),
    ...:                   columns=pd.MultiIndex.from_tuples([('A','x'), ('A','y'), ('B','z')], names=['upper', 'lower']))

In [50]: df.stack(level=[0, 1])
Out[50]: 
index  upper  lower
0      A      x        0
              y        0
       B      z        0
1      A      x        0
              y        0
       B      z        0
dtype: float64
```

So in the default case, there would not be a difference with if you do it 'sequentially' or 'simultaneously'? 
It is only if you do `dropna=False` that you get NaN values that were not there before:

```
In [51]: df.stack(level=[0, 1], dropna=False)
Out[51]: 
index  upper  lower
0      A      x         0
              y         0
              z       NaN
       B      x       NaN
              y       NaN
              z         0
1      A      x         0
              y         0
              z       NaN
       B      x       NaN
              y       NaN
              z         0
```

So it is only in this case that the `sequentially` keyword would have an effect? (and in practice the result of `df.stack(level=[0, 1], dropna=False, sequentially=False)` would then be the same as `df.stack(level=[0, 1])`)  So for this case, I don't really see the value of this new keyword.

So actually, unless I miss something, a usecase for `sequentially=False` is if you already have NaNs in your original dataframe, and you want to be able to discriminate between those 'real' NaNs and the 'introduced' NaNs by the stacking operation.

```
In [57]: df2 = df.copy()

In [58]: df2.iloc[0,1] = np.nan

In [59]: df2
Out[59]: 
upper  A      B
lower  x   y  z
index          
0      0 NaN  0
1      0   0  0

In [60]: df2.stack(level=[0, 1])
Out[60]: 
index  upper  lower
0      A      x        0
       B      z        0
1      A      x        0
              y        0
       B      z        0
dtype: float64

In [62]: df2.stack(level=[0, 1], dropna=False)
Out[62]: 
index  upper  lower
0      A      x         0
              y       NaN
              z       NaN
       B      x       NaN
              y       NaN
              z         0
1      A      x         0
              y         0
              z       NaN
       B      x       NaN
              y       NaN
              z         0
dtype: float64


```

In this case, `df.stack(level=[0, 1], dropna=False, sequentially=False)` would then be this?

```
index  upper  lower
0      A      x         0
              y       NaN
       B      z         0
1      A      x         0
              y         0
       B      z         0
dtype: float64

```

And would we want to change the value of `sequentially` in the future? That is becomes `False` by default. 

I just am not sure this is worth an extra keyword argument (as this also induces extra confusion about what it means among users)

Precisely, `sequentially` is needed because `dropna` doesn't differentiate between "native" `NaN`s in the data and "introduced" `NaN`s from the stacking.

Personally I would have preferred not having `sequentially` and differentiating between the two modes based on the type of `level`: a list/tuple means stack sequentially; a set means stack simultaneously. This is the way I originally implemented it (and also supported nesting sets in a list, e.g. [1, {4, 5}, 8], with the obvious meaning), but @jreback and @shoyer didn't like that, so I changed it to use the explicit `sequentially` argument.

As for the default value of `sequentially`, I set it to `True` for backwards compatibility, though my preference if I were starting from scratch would be `False`.

@seth-p you mentioned above that `df.T.stack().T` is not equiv to `df.unstack()`. You can use `convert_objects()` on the final result from `.unstack()` to reconverted any dtypes that have been objectified by transposes. I don't think the `df.T.stack().T` _can_ be preserved (and this is the **reason** for `unstack()` existing to some extent), because these are independent operations (and you have columns converted to rows which then mix dtypes).

This PR addresses the following issues:

(1) #9406. `DataFrame.stack()` now supports `NaN`s in the column levels. (This is analogous to #9061 and #9292 for `DataFrame.unstack()`.) Let me just say that this is a royal pain in the !@#$ due to the fact that `NaN`s don't behave "normally" -- e.g. `np.NaN == np.NaN` is `False` -- and I doubt that in practice one would get very far manipulating objects with `NaN`s in the indices. (Aside: @behzadnouri's `test_unstack_nan_index()` from #9061 was extremely helpful (via (5) below) in testing this.)

(2) `DataFrame.stack()` now supports `level=None` to mean all levels (in order, i.e. `list(range(self.columns.nlevels))`).

(3) #8851. `DataFrame.stack()` now supports a boolean `sequentially` argument (default = `True` for backwards compatibility). When `sequentially` is `True`, the listed levels are stacked one at a time, as before. When `sequentially` is `False`, the listed levels are all stacked simultaneously.

(4) #9399. Fixed a couple or tests of error messages regarding duplicate `MultiIndex` level names.

(5) In order to add the features described in (2) and (3) above, as well as `dropna`, to `DataFrame.unstack()` -- which heretofore has been implemented completely separately from `DataFrame.stack()` -- I changed `DataFrame.unstack(...)` to simply call `self.T.stack(...).T` (along with some `dtype` adjustments). It seems to work fine (though see (6) below). I am not set up to run vbench (am on Windows), so don't know how performance compares. If people object, this is trivial to reverse (just uncomment two lines).

(6) Previously when unstacking columns of ints that introduce `NaN`s, all columns were converted to floats. With my change in (5), only columns that actually have `NaN`s are converted to floats; those that don't remain ints. This seems to me like more correct behavior. I had to adjust a couple of tests to recognize this changed behavior.

(7) I removed a bunch of gratuitous `pd.`s from `test_frame.py`.

[Let me just repeat how painful it was to get the code to deal with `NaN`s in the column levels. If possible I would love to prohibit this practice. Dealing with them requires lots of special tests, as many functions (like `Index.get_loc()`) don't work reliably for them. But I fear that they arise in practice (e.g. via pivoting), so one probably has to deal with them to some extent...]

If looks ok, I will add release notes.

```
Invoked with :
--ncalls: 3
--repeats: 12


-------------------------------------------------------------------------------
Test name                                    | head[ms] | base[ms] |  ratio   |
-------------------------------------------------------------------------------
reshape_stack_simple                         |  15.0343 |   5.3796 |   2.7947 |
reshape_unstack_simple                       | 4494.4494 |   5.0020 | 898.5265 |
unstack_sparse_keyspace                      | 6584.2943 |   2.4393 | 2699.2114 |
-------------------------------------------------------------------------------
Test name                                    | head[ms] | base[ms] |  ratio   |
-------------------------------------------------------------------------------

Ratio < 1.0 means the target commit is faster then the baseline.
Seed used: 1234

Target [86a6e51] : ENH/API: DataFrame.stack() supports level=None, sequentially=True/False, and NaN level values.
Base   [e266c3d] : Merge pull request #9331 from shoyer/categorical-unique-order

BUG: don't sort unique values from categoricals
```

@behzadnouri, thank you for that! So it looks like my stack()-based implementation of unstack() is _MUCH_ slower than the existing one. Rather astonishing, actually. Maybe I should implement stack() in terms of unstack()? Only thing preventing me from doing that I don't really feel like adding `dropna` and `sequentially` support to the unstack() code...

@jreback, other than the docs I think I've addressed all your issues with the latest commit. Thank you for the detailed comments.

FYI, I'd like to implement `stack()` in terms of `unstack()` -- to eliminate redundant/inconsistent code, and perhaps make `stack()` run faster -- but my tests are failing in view of #9497 and #9514.