pandas: Change order of multiindex when using unstack

Question

I have a question regarding unstack column ordering. Let's take the following DataFrame:

import pandas as pd
df = pd.DataFrame([{'Branch':'A', 'Date':datetime.datetime(2017, 1, 1), 'profit':35},
          {'Branch':'B', 'Date':datetime.datetime(2017, 2, 1), 'profit':10},
          {'Branch':'A', 'Date':datetime.datetime(2017, 2, 1), 'profit':40},
          {'Branch':'B', 'Date':datetime.datetime(2017, 2, 1), 'profit':20},
          {'Branch':'A', 'Date':datetime.datetime(2017, 3, 1), 'profit':80},
          {'Branch':'B', 'Date':datetime.datetime(2017, 3, 1), 'profit':5}
          ])
df.set_index('Date', inplace=True)

Now, I would like to do some math before using unstack like this:

tmp = df.groupby([pd.TimeGrouper('M'),'Branch']).sum()
pd.DataFrame(tmp.unstack(level='Date'))

Is there any way so that the resulting DataFrame starts with the latest date (2017-03-31) in the first column and sorts the remaining columns (dates) in decending order?

I have already tried various ways including (python pandas: reverse df column order) but it seems that this is not applicable for MultiIndex

Thanks in advance

score 0 · Accepted Answer · answered Jun 25 '17 at 18:19

Try this:

tmp.unstack('Date').sort_index(axis=1, ascending=False)

Output:

           profit                      
Date   2017-03-31 2017-02-28 2017-01-31
Branch                                 
A            80.0       40.0       35.0
B             5.0       30.0        NaN

pandas: Change order of multiindex when using unstack

1 Answers1