Python pandas: pivot table columns sorting

Question

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

Answer 1

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)

Answer 2

Answer Date: 11.04.2025 Friday

I encountered same issue and I tried to find solution for hours but I just the pivot_table base function and I see sort parameter, default value is True. All you have to do is just set the sort value is False, probable it works. if you work, can you approve my answer.

pivot_table base function:

@Substitution("")
@Appender(_shared_docs["pivot_table"])
def pivot_table(
    self,
    values=None,
    index=None,
    columns=None,
    aggfunc: AggFuncType = "mean",
    fill_value=None,
    margins: bool = False,
    dropna: bool = True,
    margins_name: Level = "All",
    observed: bool | lib.NoDefault = lib.no_default,
    sort: bool = True,
) -> DataFrame:
    from pandas.core.reshape.pivot import pivot_table

    return pivot_table(
        self,
        values=values,
        index=index,
        columns=columns,
        aggfunc=aggfunc,
        fill_value=fill_value,
        margins=margins,
        dropna=dropna,
        margins_name=margins_name,
        observed=observed,
        sort=sort,
    )

example:

pivot = df.pivot_table(index = "index", columns = "columns", values = "values", sort = False,"other parameters")