0

1/ Original data in excel

I have a data frame with these data values as shown below: enter image description here

2/ Coding

Loading this excel into a data frame, wrote a pivot_table code to index the category and display the count of transactions for each month.

This is the code:

loadexpense_df = pd.read_excel("C:ExpensesTestData.xlsx")
month_df = pd.pivot_table(loadexpense_df,index="Category",columns="Month",aggfunc={"Month":len}, fill_value=0)

The result of month_df as shown below: enter image description here

3/ Question

I would want to arrange the month colunms in this order: Feb, Mar, Apr and so on. Is this possible to do?

Thanks for the guidance

vitalstrike82
  • 191
  • 1
  • 14

2 Answers2

1

First, see your current columns:

cols = month_df.columns.tolist()

I think your cols should be like this now:

[('Month', 'Apr'), ('Month', 'Aug'), ...]

so you can reorder your cols like this:

month_names = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
cols = sorted(cols, key=lambda x: month_names.index(x[1]))

and finally set the months_df with new columns ordering:

month_df = month_df[cols]

And I think its done.

You can read more about reordering columns in this question.

Erfan Loghmani
  • 607
  • 4
  • 7
  • Thanks for the guidance. Took some time to understand the lamda syntax and manage to wrote a 3 lines code to get same result as lamda. Good one. Thanks – vitalstrike82 Nov 03 '19 at 00:02
0

IIUC :

You need to reindex the axis in the order you want. There is a method known as reindex_axis

I am assuming you have the name of the columns :

IF NO : 

column_list = month_df.columns.tolist()

Access the month name using :

column_list.index(x[1])

If YES 

order_of_column = ['Feb', 'Mar', 'Apr','May', 'Jun', 'Jul', 'Aug']

month_df1 = month_df.reindex_axis(order_of_column, axis=1)
dper
  • 884
  • 1
  • 8
  • 31