Impute Missing Values by Group with pandas

A product list exported to Excel has a weight column with blanks scattered through it. Filling every blank with the overall average weight is quick, and wrong: the average weight of everything in the catalogue is a meaningless number for a missing bag of screws or a missing pallet of paving slabs. A much better guess is the typical weight of similar items — the median of the same category, or of the same category and size class. pandas makes this group-wise imputation a one-liner with groupby().transform(), but doing it well takes a little more: a fallback for groups that are entirely blank, a minimum number of known values before trusting a group's median, a flag on every filled cell, and a check of how much the filling changed the figures you report. This page builds all of that. It belongs to Handling Missing Data in Excel Reports.

Prerequisites

pip install pandas openpyxl

products.xlsx lists 19 products with a category, a size class and a weight in kilograms; 8 weights are blank. Some groups are fully known, some partly, and the two bulk gravel products have no known weight in their size class at all.

Step 1 — See where the gaps are

Before filling anything, count the gaps per group. That shows which groups have enough known values to estimate from:

import pandas as pd

products = pd.read_excel("products.xlsx")
summary = products.groupby(["Category", "Size"]).agg(
    rows=("Weight kg", "size"),
    known=("Weight kg", "count"),
    median=("Weight kg", "median"),
)
summary["missing"] = summary["rows"] - summary["known"]
print(summary.to_string())
print("blank weights:", products["Weight kg"].isna().sum(), "of", len(products))

size counts rows and count counts non-blank values, so their difference is the number of gaps. The table makes the problem concrete: Fixings/Small and Fixings/Medium each have two known values, Tools/Large only one, Garden/Medium one, and Landscaping/Bulk none. A group median from a single value is a weak estimate, and from none it does not exist. It also helps to look at the spread inside each group before deciding: a group whose known values range from 0.2 to 20 kg is not a good source for an estimate, however many values it has, and may mean the grouping columns are too coarse. A general overview of missing values is in Find and Report Missing Values in an Excel File.

Step 2 — Fill with the group median

groupby(...).transform("median") returns a series aligned with the original rows, holding each row's group median. fillna with that series fills each blank from its own group:

group_median = products.groupby(["Category", "Size"])["Weight kg"].transform("median")
products["Weight simple"] = products["Weight kg"].fillna(group_median)
print(products[["SKU", "Category", "Size", "Weight kg", "Weight simple"]].to_string(index=False))

transform is the key: unlike agg, which returns one row per group, it returns one value per original row, so the result lines up with the column it fills. SCR-03 gets 0.225, the median of the two other small fixings; DRL-04 gets 2.95. The median is usually a better choice than the mean for weights, prices and durations, because one unusual item in a group pulls the mean much further than the median. Notice the bulk gravel rows are still blank — their group has no known values, so its median is NaN too.

Step 3 — A fallback chain with a minimum group size

A sensible rule is: use the most specific group that has enough known values, otherwise step back to a broader one. Here, use the category-and-size median if that group has at least two known weights, otherwise the category median, otherwise leave the value blank and report it:

MIN_KNOWN = 2
levels = [["Category", "Size"], ["Category"]]

products["Weight filled"] = products["Weight kg"]
products["Filled from"] = products["Weight kg"].notna().map({True: "known", False: None})

for keys in levels:
    g = products.groupby(keys)["Weight kg"]
    med = g.transform("median")
    enough = g.transform("count") >= MIN_KNOWN
    use = products["Weight filled"].isna() & enough & med.notna()
    products.loc[use, "Weight filled"] = med[use]
    products.loc[use, "Filled from"] = "median of " + "+".join(keys).lower()

print(products[["SKU", "Category", "Size", "Weight kg", "Weight filled", "Filled from"]].to_string(index=False))
print("still blank:", list(products.loc[products["Weight filled"].isna(), "SKU"]))

Each pass only touches rows that are still blank, so the more specific level wins whenever it qualifies. Tools/Large has only one known weight, so SAW-02 falls back to the Tools median of 3.025 kg — less than half the one saw we do know (6.2 kg), because the drills dominate the category. That is the fallback working as specified and still giving a poor estimate, and it is exactly why the Filled from column matters; Garden/Medium has one known value and the whole Garden category has the same single value, so HOS-02 stays blank rather than being filled from one data point. The bulk gravel rows reach the Landscaping category median — but two slabs at 21–23.5 kg tell you nothing about a bulk bag of gravel, so the fallback chain is only as good as its levels. Whether a fallback is reasonable is a judgement about the data, and it is the part of this page worth discussing with whoever owns the figures.

Step 4 — Keep a flag on every filled cell

An imputed number looks exactly like a measured one once it is in a spreadsheet. Keep the Filled from column in the output and colour the imputed cells, so nobody mistakes an estimate for a fact:

from openpyxl.styles import Font, PatternFill

out = products[["SKU", "Category", "Size", "Weight filled", "Filled from"]].rename(columns={"Weight filled": "Weight kg"})
with pd.ExcelWriter("products_filled.xlsx", engine="openpyxl") as xw:
    out.to_excel(xw, sheet_name="Products", index=False)
    ws = xw.sheets["Products"]
    grey = PatternFill("solid", fgColor="EDEDED")
    for r, how in enumerate(out["Filled from"], start=2):
        if how and how != "known":
            ws.cell(r, 4).fill = grey
            ws.cell(r, 4).font = Font(italic=True)
    for c in ws[1]:
        c.font = Font(bold=True)
    ws.column_dimensions["E"].width = 28
print(out["Filled from"].value_counts(dropna=False).to_dict())

Imputed weights are shaded grey and italic, and the Filled from column says exactly which group's median was used. If the filled file feeds another process, the flag column also lets that process exclude estimated values where accuracy matters — a freight quote, for example, should probably not be based on an imputed weight.

Step 5 — Check what imputation did to the totals

Imputation changes any total or average built on the column. Compare the figures with and without filled values, per category, so the effect is visible before anyone reports them:

compare = products.groupby("Category").agg(
    known_rows=("Weight kg", "count"),
    sum_known=("Weight kg", "sum"),
    sum_filled=("Weight filled", "sum"),
    mean_known=("Weight kg", "mean"),
    mean_filled=("Weight filled", "mean"),
).round(3)
compare["sum change %"] = ((compare["sum_filled"] / compare["sum_known"] - 1) * 100).round(1)
print(compare.to_string())

Group-median imputation leaves a group's mean almost unchanged when the fill comes from that same group — Fixings, Landscaping and Garden keep their means exactly — but it raises totals, because rows that previously contributed nothing to a sum now contribute an estimate. Fixings' total weight rises by 50% and Landscaping's by 150%, almost all of it from the two gravel bags filled with the slab median. Tools shows the other risk: the saw filled from the drill-heavy category median pulls the Tools mean down from 3.76 to 3.50 kg. Whether such changes are acceptable depends on the use: for a typical weight per category they may not matter, for total shipping weight they matter a lot. Report the share of imputed values alongside any figure built on them.

Common pitfalls

• One column-wide average. It ignores obvious structure; impute within groups.
• Trusting a group of one. Require a minimum number of known values.
• Fallbacks across unlike items. A broader group is only useful if its items are comparable.
• Unflagged estimates. Keep a column saying what was filled and how.
• Reporting totals without saying they include estimates. Show the imputed share.

Conclusion

Group-wise imputation fills each blank with a typical value from comparable rows: groupby(keys)[col].transform("median") lines up a group median with every row, and fillna uses it. Make it robust with a fallback chain from specific to broader groups, a minimum count of known values before trusting a group, and a Filled from flag on every estimate. Then compare sums and means with and without the filled values, because imputation barely moves averages but can noticeably raise totals.

Frequently asked questions

How do I fill missing values with the group mean in pandas? Group the column by the key columns, call transform("mean"), and pass the result to fillna on the same column. transform returns a value per row aligned with the original data, so each blank gets its own group's mean.

Should I use the mean or the median to impute? The median is usually safer for weights, prices and durations because it is not pulled by a few extreme values. Use the mean when the data is roughly symmetric.

What happens when a whole group is missing? Its median is NaN, so the values stay blank. Add a fallback to a broader group, or leave them blank and report them for review.

Does imputation change my totals? Yes. Filled rows now contribute to sums, so totals rise. Group means change little. Report how many values were imputed alongside any figure that uses them.

Related

• Up one level: Handling Missing Data in Excel Reports — finding and treating gaps.
• Fill Missing Values in Excel with pandas fillna — the simpler fills.
• Interpolate Missing Numeric Values in Excel Data — filling along a sequence instead of a group.
• How Missing Values Change Sums, Averages and Counts — the effect of blanks before you fill them.