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.

bdiamante · Accepted Answer · 2013-04-01 20:45:08Z

17

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().

answered Apr 1, 2013 at 20:45

bdiamante

17.8k6 gold badges43 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DSM Over a year ago

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.)

Jason Strimpel Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related