panda grouping by month with transpose

Question

Based on the following dataframe, I am trying to create a grouping by month, type and text, I think I am close to what I want, however I am unable to group by month the way I want, so I have to use the column transdate. However, when I transpose this, I lose the order

df = pd.DataFrame({'date':['6/2/2017','5/23/2017','5/20/2017','6/22/2017','4/21/2017','7/2/2017','5/23/2017','5/20/2017','8/22/2017','2/21/2017'],'rev':[100,200,300,400,500,-70,-250,-200,400,500],'text':['Car','House','Car','Truck','House','Car','House','Car','Truck','House']})
df['date'] = pd.to_datetime(df['date'])
df = df.sort_values('date')
#New Column
df['transdate'] = pd.to_datetime(df['date'])
df['transdate'] = df['transdate'].dt.strftime('%B - %Y')
#second new column
df['type'] = np.where(df['rev']>0, 'positive', 'negative')

This give me this:

Then, I create a pivot table that I am transposing

df_pivot = df.pivot_table(index='transdate',columns=['type','text'],aggfunc=sum, fill_value=0).T
df_pivot

I am wondering how I can sort the first row, starting with feb 2017, then april 2017 and so on? Or, starting the other way around, aug 2017 then july 2017 ... but keeping the order of the months?

Or, will be best to do the pivot table with index date and then, do the grouping? If this is the case, how can I do the grouping?

Answer 1

# make a month column to preserve the order
df['month'] = pd.to_datetime(df['date']).dt.strftime('%m')

# create the pivot table with this numeric month column
df_pivot = df.pivot_table(index='month',columns=['type','text'],aggfunc=sum, fill_value=0).T

# create a mapping between numeric months and the English version
mapping = pd.Series(df.transdate.tolist(),index=df.month.values).drop_duplicates()

# replace the columns according to the mapping
df_pivot.columns = [i for i in map(mapping.get, df_pivot.columns)]

Answer 2

It is sorting, but it doesn't know that it is of date type, therefore it's sorting according to alphabetical order. I encourage you to change it to of datetime and do the same again.

Otherwise small hack, replace the name of the months with integers and make it something like 201701 for January, 201704 for April, but this isn't appreciated. But may work for you.