BUG: DataFrame.pivot fails on multiple columns to set as index

[jorisvandenbossche]

I am not sure anymore if DataFrame.pivot actually supports multiple columns to set as index/columns (it should), but in any case this error is very confusing:

In [1]: df = pd.DataFrame({'lev1': [1, 1, 1, 1,2, 2, 2,2], 'lev2': [1, 1, 2, 2, 1, 1, 2, 2], 'lev3': [1, 2, 1, 2, 1, 2, 1, 2], 'values': range(8)})

In [2]: df
Out[2]: 
   lev1  lev2  lev3  values
0     1     1     1       0
1     1     1     2       1
2     1     2     1       2
3     1     2     2       3
4     2     1     1       4
5     2     1     2       5
6     2     2     1       6
7     2     2     2       7

In [3]: df.pivot(index=['lev1', 'lev2'], columns='lev3', values='values')
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-3-2fef29f9fd39> in <module>()
----> 1 df.pivot(index=['lev1', 'lev2'], columns='lev3', values='values')

~/scipy/pandas/pandas/core/frame.py in pivot(self, index, columns, values)
   5191         """
   5192         from pandas.core.reshape.reshape import pivot
-> 5193         return pivot(self, index=index, columns=columns, values=values)
   5194 
   5195     _shared_docs['pivot_table'] = """

~/scipy/pandas/pandas/core/reshape/reshape.py in pivot(self, index, columns, values)
    406         else:
    407             indexed = self._constructor_sliced(self[values].values,
--> 408                                                index=index)
    409     return indexed.unstack(columns)
    410 

~/scipy/pandas/pandas/core/series.py in __init__(self, data, index, dtype, name, copy, fastpath)
    260                             'Length of passed values is {val}, '
    261                             'index implies {ind}'
--> 262                             .format(val=len(data), ind=len(index)))
    263                 except TypeError:
    264                     pass

ValueError: Length of passed values is 8, index implies 2

[gitgithan]

Shouldn't pd.pivot_table be used if multiple columns for the index is desired, or if a pivoted index-column pair has more than 1 value and thus requires aggregation?
Why does pivot exist actually?
Is there anything pivot can do that pivot_table cannot?

[srcole]

I'd like to complete this (I had this issue the other day, and just switched to pd.pivot_table()). Is it ok if I work on this since the other PRs are closed?

Looks like just a few changes needed in thoo's branch.

[anordin95]

Will I be duplicating someone else's efforts if I begin work on this issue?

[jorisvandenbossche]

Since it is already some weeks that @srcole commented about this (btw, sorry for not answering to your comment back then!), I think you are welcome to do a PR.