Converting a pandas MultiIndex DataFrame from rows-wise to column-wise

Question

I'm working in zipline and pandas and have converted a pandas.Panel to a pandas.DataFrame using the to_frame() method. This is the resulting pandas.DataFrame which as you can see is multi-indexed:

                                  price
major                     minor                
2008-01-03 00:00:00+00:00 SPY    129.93
                          KO      26.38
                          PEP     64.78
2008-01-04 00:00:00+00:00 SPY    126.74
                          KO      26.43
                          PEP     64.59
2008-01-07 00:00:00+00:00 SPY    126.63
                          KO      27.05
                          PEP     66.10
2008-01-08 00:00:00+00:00 SPY    124.59
                          KO      27.16
                          PEP     66.63

I need to convert this frame to look like this:

                          SPY     KO     PEP
2008-01-03 00:00:00+00:00 129.93  26.38  64.78
2008-01-04 00:00:00+00:00 126.74  26.43  64.59
2008-01-07 00:00:00+00:00 126.63  27.05  66.10
2008-01-08 00:00:00+00:00 124.59  27.16  66.63

I've tried the pivot method, stack/unstack, etc. but these methods are not what I'm looking for. I'm really quite stuck at this point and any help is appreciated.

Distichous

Answer 1

Because you have a MultiIndex in place already, stack and unstack are what you want to use to move rows to cols and vice versa. That being said, unstack should do exactly what you want to accomplish. If you have a DataFrame df then df2 = df.unstack('minor') should do the trick. Or more simply, since by default stack/unstack use the innermost level, df2 = df.unstack().

Katy

Aargh; beat me to it. pivot should work too, after resetting the index, i.e. df.reset_index().pivot("major", "minor") (with or without a , "price") or ["price"], depending on how important whether the name is still floating around is.) – Syncope

Ha nice, I was obviously not using unstack correctly, thanks. – Distichous