pandasdataframesortingpivot-table

Python pandas: pivot table columns sorting

This is my sorted dataframe by year and month:

df:

Year  Month  YearMonthName  revenue
2015      1       2015 Jan      166
2015      2       2015 Feb      170
2015      3       2015 Mar      187
2015      4       2015 Apr      102
2015      5       2015 May      166
 ...    ...            ...      ...
2020     12       2020 Dec      133

So the col list is also sorted by year and month:

    col=list(df['YearMonthName'])
    print(col)

Result:

['      2015 Jan', '      2015 Feb', '      2015 Mar', '      2015 Apr', ... ,  '      2020 Dec']

But when I put the col list into pandas pivot table, columns are sorted alphabetically by YearMonthName. Any ideas how can I sort this?

 df_table1 = pd.pivot_table(df, values='revenue', columns=col, aggfunc=np.sum).reset_index()
 print(df_table1)

Result:

     index               2015 Apr  ...        2020 Oct        2020 Sep
0  revenue                 353726  ...          349309          340451
Solution

  • what you could do is first perform the pivoting then reindex by cols, as followed:

    # given that you have sorted YearMonthName
col = df['YearMonthName'].tolist()

# pivot table as you did 
pv = df.pivot_table(values='revenue', columns='YearMonthName', aggfunc='sum')

    then you can reindex using the variable col:

    pv = pv.reindex(labels=col, axis=1)