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:

enter image description here

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

enter image description here

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?


2 Answers 2

# 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)]

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.

  • $\begingroup$ Really good suggestion, the problem with the datetime, is about readability, not feasible at this stage having the dates the way it was plus different days on the same month werent grouped, the small hack sounds good too, i wish you had place a code snippet to check it out or help other that might have similar issue :) $\endgroup$
    – Manza
    Jul 2, 2018 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.