How to preserve category order during grouping in Pandas (with Code)
The Short Answer (TL;DR)
Define a CategoricalDtype with the required category sequence, group with explicit observed and sort settings, then reindex the summary to that sequence. Keep missing labels separate and check unknown labels before conversion so they cannot silently become missing.
import pandas as pd
df = pd.DataFrame({"stage":pd.Categorical(["high","low","high"],categories=["low","medium","high"],ordered=True),"value":[8,2,10]})
print(df.groupby("stage",observed=False,sort=True)["value"].agg(["size","count","mean"]).reindex(["low","medium","high"]))Detailed Implementation
This example uses synthetic data and does not need a network connection. Install the recorded package versions in a separate Python environment before running it.
python -m pip install numpy==2.5.4 pandas==3.0.6from pathlib import Path
import numpy as np
import pandas as pd
def summarize_stages(frame, order):
if (not frame.columns.is_unique or not {"stage","value"}.issubset(frame.columns)
or not order or len(set(order))!=len(order)
or not all(isinstance(x,str) and x.strip() for x in order)):
raise ValueError("Require unique columns and a nonempty sequence of distinct labels.")
labels = frame["stage"]
# Check raw labels before astype: unknown categories would otherwise become NA.
if not labels.dropna().isin(order).all():
raise ValueError("An observed label is outside the declared category sequence.")
values = frame["value"]
if not pd.api.types.is_numeric_dtype(values) or pd.api.types.is_bool_dtype(values):
raise ValueError("value must be a real numeric column with optional missing values.")
if pd.api.types.is_complex_dtype(values) or not np.isfinite(values.dropna()).all():
raise ValueError("Nonmissing values must be finite real measurements.")
missing_key = labels.isna()
missing_rows = frame.loc[missing_key].copy()
work = frame.loc[~missing_key].copy()
work["stage"] = work["stage"].astype(pd.CategoricalDtype(order,ordered=True))
result = work.groupby("stage",observed=False,sort=True).agg(
rows=("value","size"),observed=("value","count"),mean=("value","mean"))
result = result.reindex(order)
return result, missing_rows
data = pd.DataFrame({"stage":["high","low","high","medium",None,"low"],
"value":[8.0,2.0,np.nan,5.0,100.0,4.0]},index=[4,4,9,1,2,8])
order = ["low","medium","high","critical"]
summary, missing_rows = summarize_stages(data,order)
text = (summary.to_string()+"\n\nMISSING LABEL ROWS\n"+missing_rows.to_string()+
f"\ninput={len(data)}; grouped={summary['rows'].sum()}; missing_label={len(missing_rows)}\n")
Path("ordered-category-summary.txt").write_text(text,encoding="utf-8")
print(text)Rows appear as low, medium, high, critical. Their source-row counts are 2, 1, 2, 0; observed counts are 2, 1, 1, 0; means are 3, 5, 8 and missing. The original missing-label row remains in a separate audit.
How is category order different from alphabetical or input order?
The declared sequence is low, medium, high, critical. Alphabetical sorting would put critical first, while first appearance in this fixture would put high first. Neither matches the report contract. The ordered categorical stores that sequence as metadata, and the final reindex makes the output layout explicit. Reindexing affects summary rows; it does not rearrange the original observations or change their measurements.
Why are observed and sort written explicitly?
The example requests all declared levels with observed=False and sorted category keys with sort=True. Current Pandas documentation records observed=True as the default from version 3.0, so relying on a default could omit unused levels. sort=False follows first appearance even for ordered categorical groupers in recent Pandas. The explicit settings and reindex avoid depending on either behavior for the report order.
What should an unused category contain?
Critical has no source rows, so its row count and observed-value count are both zero and its mean is missing. High has two source rows but only one known measurement, so its mean is 8 rather than 4. Size counts source rows; count counts nonmissing measurements. A missing mean is not a measured zero, and an empty category is different from a category whose recorded measurements are all absent.
How are unknown labels and missing labels handled?
The helper rejects a nonmissing label outside the permitted sequence before categorical conversion. Otherwise conversion could erase the distinction between a new unexpected spelling and an absent label. Missing source labels are returned as a separate table with their original index and value. Here the row with measurement 100 is excluded from named categories and remains visible in that audit. Already-converted categorical input cannot recover unknown labels that an earlier conversion discarded.
Which observations and denominators are preserved?
Five rows have known category labels, four of those rows have known measurements, and one row has a missing label. The three counts answer different questions. The input frame and its duplicate index labels remain unchanged. Group means use only observed measurements within their named category. The separate missing-label table allows a downstream report to explain why its group totals cover five of the six source rows.
Does ordered grouping establish a numerical scale?
An ordering can describe priority or a reporting convention without establishing equal distances. Low to medium does not automatically represent the same numerical change as medium to high. The example averages the separate measurement column, not the category codes. It rejects infinite and nonnumeric measurements. A category order supports reproducible presentation; causal comparisons, uncertainty estimates and missing-data assumptions require their own analysis.
Mathematical Context
For category g, let n_g count all rows with that known label and m_g count rows whose measurement is observed. The group mean is the sum of observed measurements divided by m_g when m_g is positive. It is undefined when m_g is zero. Here n_g is 2, 1, 2, 0 and m_g is 2, 1, 1, 0 in declared order. The mean has the measurement units; row counts are dimensionless. Ordering is a permutation of summary positions and does not alter any group numerator or denominator.
\[ \bar{x}_g=\frac{\sum_{i:g_i=g,\ x_i\ \mathrm{observed}}x_i}{m_g},\qquad m_g>0 \]
Reproducibility
Executed with Python 3.12.14; numpy 2.5.4, pandas 3.0.6. Automated checks confirm the stated output properties.