Sort Pivot Rows in a Custom Order with pandas

A monthly sales pivot comes out as Apr, Aug, Dec, Feb, Jan… A size breakdown reads L, M, S, XL, XS. Ticket priorities appear as High, Low, Medium, Urgent. pandas sorts group labels alphabetically because it has no way to know that months run January to December or that XS comes before S — unless you tell it. The cleanest way to tell it is an ordered Categorical: a column type that carries its own sort order, which groupby, pivot_table, sort_values and charts all respect. For one-off reports, reindex with an explicit list does the same job. This page shows both, plus the details that trip up real reports: empty categories, an "Other" bucket that belongs at the end, and keeping the order when the table is written to Excel. It belongs to Creating Pivot Tables from Excel Data.

Prerequisites

pip install pandas openpyxl

orders_categories.xlsx has 300 order lines with a month abbreviation (January to September), a garment size including a "One size" bucket, a priority and a region that includes "Other".

Step 1 — The alphabetical problem

import pandas as pd

orders = pd.read_excel("orders_categories.xlsx")
plain = orders.pivot_table(index="Month", columns="Size", values="Units", aggfunc="sum", fill_value=0)
print(plain)

Rows run Apr, Aug, Feb, Jan, Jul, Jun, Mar, May, Sep, and columns L, M, One size, S, XL, XS. Every reader has to reorder the table in their head, and a line chart drawn from it zigzags through the year. Sorting the result with sort_index changes nothing, because alphabetical is the index's natural order.

Step 2 — Ordered Categoricals carry the order with the data

MONTHS = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
SIZES = ["XS", "S", "M", "L", "XL", "One size"]
orders["Month"] = pd.Categorical(orders["Month"], categories=MONTHS, ordered=True)
orders["Size"] = pd.Categorical(orders["Size"], categories=SIZES, ordered=True)
ordered = orders.pivot_table(index="Month", columns="Size", values="Units", aggfunc="sum",
                             fill_value=0, observed=False)
print(ordered)

Rows now run January to December and columns XS to One size. The order is part of the column's type, so every later operation — groupby, sort_values, value_counts(sort=False), a chart — follows it without being told again. Two side effects matter. Values not in the category list become NaN, so a typo such as "Sept" silently disappears; check with orders["Month"].isna().sum() after converting. And observed=False keeps categories with no data: October to December appear as rows of zeros. That is right for a year-to-date table that should show the empty months, and wrong for a table that should only show months so far — pass observed=True then.

Step 3 — reindex for a one-off order

When the order only matters for one output, reorder the finished table instead of converting columns:

PRIORITY = ["Urgent", "High", "Medium", "Low"]
by_priority = orders.groupby("Priority")["Units"].agg(["count", "sum"]).reindex(PRIORITY)
print(by_priority)
cols_first = ordered.reindex(columns=["One size", "XS", "S", "M", "L", "XL"])
print(cols_first.columns.tolist())

reindex(PRIORITY) returns the rows in the listed order: Urgent first, Low last — the order a support manager reads in. Labels missing from the data appear as NaN rows and labels missing from the list are dropped, so compare the list with set(df.index) when the data can contain unexpected values. reindex(columns=...) does the same for columns; here "One size" is moved to the front for a report that treats it as the default. Reindexing is simpler for presentation; Categoricals are better when the order is a property of the data that many steps rely on.

Step 4 — Sort by value, but keep "Other" last

A common report sorts regions by total, biggest first — with "Other" at the bottom regardless of its size, and the total row after that:

by_region = orders.groupby("Region")["Units"].sum()
named = by_region.drop("Other").sort_values(ascending=False)
ranked = pd.concat([named, by_region[["Other"]]])
ranked["Total"] = by_region.sum()
print(ranked)

Dropping "Other", sorting the rest, and appending it again is clearer than a clever sort key. The total is computed from the original series and added last. If the totals come from pivot_table(margins=True), the margins row is named All and sorts like any other label, so move it to the end explicitly with table.reindex([*sorted_labels, "All"]). Subtotal layouts are covered in Add Subtotals and Grand Totals to a pandas Pivot Table.

Step 5 — Fixed group order, value order inside each group

Two-level layouts often need both: categories in a fixed order (priority), and rows within each sorted by value (regions by units). Sort on both columns, with the Categorical providing the first key:

orders["Priority"] = pd.Categorical(orders["Priority"], categories=PRIORITY, ordered=True)
two_level = (orders.groupby(["Priority", "Region"], observed=True)["Units"].sum()
                   .reset_index()
                   .sort_values(["Priority", "Units"], ascending=[True, False]))
print(two_level.head(10).to_string(index=False))

sort_values sorts the Priority column by its categorical order (Urgent first) and Units descending within each priority — a sort that is awkward with plain strings and natural with Categoricals. observed=True drops priority-region combinations that never occurred.

Step 6 — Keep the order in Excel

The order is preserved when the table is written, because to_excel writes rows in the DataFrame's order:

with pd.ExcelWriter("pivot_ordered.xlsx", engine="xlsxwriter") as xw:
    ordered.to_excel(xw, sheet_name="Month x size")
    by_priority.to_excel(xw, sheet_name="By priority")
    ranked.rename("Units").to_frame().to_excel(xw, sheet_name="By region")
check = pd.read_excel("pivot_ordered.xlsx", sheet_name="Month x size", index_col=0)
print(check.index[:4].tolist(), check.columns.tolist())

The file opens with months in calendar order and sizes from XS up, and reading it back confirms it. One caution: if the recipient sorts the sheet in Excel, Excel sorts text alphabetically again; for a sheet people will re-sort, add a numeric MonthNumber or SortKey column they can sort by. A native Excel pivot table built from the data has its own custom-list sorting, covered in Add a Native Excel Pivot Table with Python.

Step 7 — Order columns by their total

Sometimes the order should come from the data: a size or product matrix with its biggest columns on the left. Sort the column labels by their totals and reindex — keeping a deliberate catch-all at the end:

totals = ordered.sum().drop("One size").sort_values(ascending=False)
by_volume = ordered.reindex(columns=[*totals.index, "One size"])
print(by_volume.columns.tolist())
print(by_volume.loc[["Jan", "Feb", "Mar"]])

Medium is the best-selling size, so it moves to the left, followed by Large and Small; "One size" stays at the end because it is not comparable with the graded sizes. The rows keep their calendar order from the Categorical. This kind of data-driven order changes from month to month, so it belongs in the report step, not in the column type.

Performance notes

Converting a column to a Categorical is cheap and usually saves memory, because each distinct label is stored once and rows hold small integer codes; group-bys on categorical columns are also faster than on strings. The one cost is observed=False on several categorical keys at once: pandas builds the full product of all categories, which for three columns of a few hundred categories each is millions of mostly empty groups. Use observed=True for multi-key group-bys and add back only the empty categories you need with reindex.

Common pitfalls

• Values outside the category list. They become NaN without warning; check the count after converting.
• Forgetting observed. observed=False shows empty categories; observed=True hides them — choose deliberately.
• Sorting the margins row. The All row of margins=True is just another label; move it last explicitly.
• Unordered Categoricals. Without ordered=True, comparisons like < "Jun" raise and some sorts fall back to category codes inconsistently; set the flag.
• Re-sorting in Excel. Alphabetical again; provide a numeric sort column.

Conclusion

Alphabetical order is pandas' default for labels, and it is rarely the business order. Convert columns with a natural order — months, sizes, priorities — to ordered Categoricals so every group-by and pivot follows it, decide with observed whether empty categories appear, use reindex for one-off orders, drop and re-append "Other" and totals to keep them last, sort by a categorical and a value together for two-level layouts, and write a numeric sort key when readers will re-sort the sheet in Excel.

Frequently asked questions

How do I sort pandas pivot rows by month instead of alphabetically? Convert the month column to an ordered Categorical with categories in calendar order, pd.Categorical(df"Month", categories=MONTHS, ordered=True), before building the pivot table.

How do I put rows in a specific order in a pandas pivot table? Call reindex on the finished table with a list of labels in the order you want, for example table.reindex("Urgent", "High", "Medium", "Low").

Why do empty categories show up as zero rows in my pivot? With Categorical columns and observed=False, pandas includes every category, even those with no data. Pass observed=True to show only categories that occur.

How do I keep an "Other" category at the bottom when sorting by value? Drop "Other" from the series, sort the remaining rows by value, then concatenate the "Other" row back at the end, followed by any total row.

Related

• Up one level: Creating Pivot Tables from Excel Data — summarising data with pandas.
• Create a Pivot Table from Excel with pandas — the pivot_table basics.
• Sort Excel Rows with Python Before Writing — sorting detail tables for output.
• Group Excel Rows by Month and Quarter with pandas — grouping real dates, which sort correctly by themselves.