Stacking MultiIndex DataFrame columns with Timestamps levels fails

[lydakis]

You can see the bug in the following code:

import pandas as pd
import datetime as dt

key = pd.MultiIndex.from_tuples([(
                            dt.datetime(2014,8,1,0,0,0),
                            'SomeColumnName',
                            'AnotherOne')])

data = {
    '1' : 34204,
    '2' : 43580,
    '3' : 84329,
    '5' : 23485
}


ts = pd.Series(data=data)
df = pd.DataFrame(data=ts, columns=key)

stacked = df.stack()

print stacked

We would expect the data to be unchanged, however the returned DataFrame is empty.

The Pandas version used was 0.11.0

[jreback]

when you pass columns it reindexes by the passed columns when you are passing a Series, since the data has the name of '0' (the column assigned) it disappears. This is undocumented (and doesn't work at all > 0.13.0)

Use this to create a frame

In [23]: result = ts.to_frame()

# if you are < 0.13.0
In [31]: result = DataFrame(ts)

In [33]: result
Out[33]: 
       0
1  34204
2  43580
3  84329
5  23485

And simply set the columns.

In [26]: key
Out[26]: 
MultiIndex(levels=[[2014-08-01 00:00:00], [u'SomeColumnName'], [u'AnotherOne']],
           labels=[[0], [0], [0]])

In [28]: result.columns = key

In [29]: result
Out[29]: 
       2014-08-01
   SomeColumnName
       AnotherOne
1           34204
2           43580
3           84329
5           23485

In [30]: result.unstack()
Out[30]: 
2014-08-01  SomeColumnName  AnotherOne  1    34204
                                        2    43580
                                        3    84329
                                        5    23485
dtype: int64

[jreback]

@TomAugspurger not a bug, but a usage issue.

[TomAugspurger]

yeah just read your comment.

[TomAugspurger]

@ldkge's problem was with stack though. Not sure why

In [67]: result.stack(0)
Out[67]: 
              SomeColumnName
                  AnotherOne
1 2014-08-01           34204
2 2014-08-01           43580
3 2014-08-01           84329
5 2014-08-01           23485

In [68]: result.stack(1)
Out[68]: 
Empty DataFrame
Columns: [(2014-08-01 00:00:00, AnotherOne)]
Index: []

would be different.

[jreback]

This works in master (recently added feature).

In [54]: result.stack([0,1])
Out[54]: 
                             AnotherOne
1 2014-08-01 SomeColumnName       34204
2 2014-08-01 SomeColumnName       43580
3 2014-08-01 SomeColumnName       84329
5 2014-08-01 SomeColumnName       23485

I am not what stack(1) would/should actually do

What would you expect?

[TomAugspurger]

I thought it should shift the 1 level of the column's MultiIndex down to the row labels. so expected would be

>>>df.stack(1)
                  2014-08-01
                  AnotherOne
1 SomeColumnName           34204
2 SomeColumnName           43580
3 SomeColumnName           84329
5 SomeColumnName           23485

[jreback]

cc @onesandzeroes what do you think?