MEDIAN, MODE and QUARTILE Equivalents in pandas

Averages hide skew, so reports on delivery times, order values or salaries lean on the median, the quartiles and the most common value instead. Excel has a small family of functions for them — MEDIAN, MODE.SNGL, MODE.MULT, QUARTILE.INC, QUARTILE.EXC and the matching PERCENTILE pair — and pandas has median, mode and quantile. Most of them line up directly. Two do not, and those two are where a migrated report starts disagreeing with the spreadsheet it replaced: the "exclusive" quartiles, which use a different interpolation, and MODE.SNGL, which breaks ties differently from pandas. This page maps each function, shows the differences on real numbers and checks them against the spreadsheet. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas numpy openpyxl

deliveries.xlsx holds delivery times in days for two regions, eleven numbers in total, plus one cell containing the text n/a — the sort of value that Excel's statistics functions skip without comment. LibreOffice (soffice) recalculates the spreadsheet formulas in Step 6.

Step 1 — MEDIAN, and what Excel ignores

import pandas as pd

df = pd.read_excel("deliveries.xlsx")
print(df["DeliveryDays"].dtype)
days = pd.to_numeric(df["DeliveryDays"], errors="coerce")
print("median:", days.median(), "| values used:", days.notna().sum())

Because of the n/a, the column arrives as text-and-numbers, and calling .median() on it directly fails. Excel's MEDIAN(A2:A13) silently skips text and empty cells and uses the eleven numbers. pd.to_numeric(..., errors="coerce") reproduces that: text becomes NaN, which every pandas statistic skips by default. The median is 12. Whether skipping is acceptable is a separate question — n/a may mean "not delivered yet", which is not the same as "no data" — and Replace Placeholder Text with NaN in pandas shows how to decide that explicitly.

Step 2 — QUARTILE.INC and PERCENTILE.INC

print("Q1, Q3 (INC):", days.quantile([0.25, 0.75]).tolist())
print("90th percentile (INC):", days.quantile(0.9))

pandas' default quantile uses linear interpolation between the closest ranks, which is exactly the method of QUARTILE.INC, PERCENTILE.INC and the older QUARTILE/PERCENTILE: Q1 = 7, Q3 = 16.5, 90th percentile = 22. "Inclusive" means the 0th and 100th percentiles are the minimum and maximum.

Step 3 — QUARTILE.EXC and PERCENTILE.EXC need another method

The exclusive versions place the quantiles differently — they treat the data as a sample and never return the minimum or maximum for interior percentiles. pandas has no option for this, but NumPy does: method "weibull" is the same formula.

import numpy as np

clean = days.dropna().to_numpy()
print("Q1, Q3 (EXC):", np.quantile(clean, [0.25, 0.75], method="weibull").tolist())
print("90th percentile (EXC):", round(float(np.quantile(clean, 0.9, method="weibull")), 2))
try:
    np.quantile(clean, 0.05, method="weibull")
    print("5th percentile (EXC) computed")
except Exception as exc:
    print(exc)

Q3 moves from 16.5 to 18 and the 90th percentile from 22 to 28.4 — differences large enough to change a service-level report. NumPy clamps very low and very high percentiles to the minimum and maximum, where Excel's .EXC functions return #NUM! because the sample is too small to estimate them; for eleven values that is anything below 1/12 or above 11/12. If a report must reproduce Excel's errors, check 1/(n+1) <= p <= n/(n+1) before computing.

Step 4 — MODE.SNGL, MODE.MULT and ties

The delivery times contain two values that each appear twice, 7 and 15. pandas returns every mode, sorted; Excel's MODE.SNGL returns one value — the mode that appears first in the data:

print("pandas mode():", days.mode().tolist())

def mode_sngl(s):
    s = s.dropna()
    counts = s.value_counts()
    top = counts[counts == counts.max()].index
    if counts.max() == 1:
        return np.nan                      # Excel: #N/A when no value repeats
    return s[s.isin(top)].iloc[0]          # first occurrence in the original order

print("MODE.SNGL:", mode_sngl(days))
print("MODE.SNGL on reversed data:", mode_sngl(days[::-1]))

On this data both give 7, but for different reasons: pandas because 7 sorts before 15, Excel because 7 occurs first. Reverse the rows and mode_sngl returns 15 — as MODE.SNGL would — while days.mode()[0] still says 7. MODE.MULT corresponds to the full mode() list, except that Excel returns the modes in order of appearance. When no value repeats, Excel returns #N/A; pandas returns every value, so check the maximum count.

Step 5 — Per group

Reports usually need the statistics per region, product or month. groupby with named aggregations gives the whole summary at once:

df["Days"] = days
summary = df.groupby("Region")["Days"].agg(
    count="count",
    median="median",
    q1=lambda s: s.quantile(0.25),
    q3=lambda s: s.quantile(0.75),
    p90_exc=lambda s: float(np.quantile(s.dropna(), 0.9, method="weibull")),
    mode=mode_sngl,
)
summary["iqr"] = summary["q3"] - summary["q1"]
print(summary.round(2))

The mode column is empty for both regions: within each region no delivery time occurs twice, which is exactly the case where MODE.SNGL returns #N/A, and the helper returns NaN to match. This replaces a grid of MEDIAN(IF(...)) array formulas or helper columns in the spreadsheet. The interquartile range (q3 − q1) is the usual basis for flagging unusually slow deliveries, as described in Detect Outliers in Excel Data with pandas.

Step 6 — Check against the spreadsheet

import subprocess
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
for v in df["DeliveryDays"]:
    ws.append([v])
formulas = {
    "MEDIAN": "=MEDIAN(A1:A12)",
    "MODE.SNGL": "=_xlfn.MODE.SNGL(A1:A12)",
    "QUARTILE.INC 3": "=_xlfn.QUARTILE.INC(A1:A12,3)",
    "QUARTILE.EXC 3": "=_xlfn.QUARTILE.EXC(A1:A12,3)",
    "PERCENTILE.EXC 0.9": "=_xlfn.PERCENTILE.EXC(A1:A12,0.9)",
}
for i, f in enumerate(formulas.values(), start=1):
    ws[f"C{i}"] = f
wb.save("stats_check.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc", "stats_check.xlsx"],
               check=True, capture_output=True)
sheet = load_workbook("recalc/stats_check.xlsx", data_only=True).active
mine = [days.median(), mode_sngl(days), days.quantile(0.75),
        float(np.quantile(clean, 0.75, method="weibull")), float(np.quantile(clean, 0.9, method="weibull"))]
for (name, _), i, m in zip(formulas.items(), range(1, 6), mine):
    print(f"{name:20} sheet {sheet[f'C{i}'].value!s:>6}  pandas {round(m, 4)!s:>6}")

The .INC, .EXC and MODE.SNGL results agree, with the text cell skipped on both sides. Note the _xlfn. prefix: the dotted function names were added in Excel 2010 and must be written that way from Python, or the spreadsheet shows #NAME? — see Fix #NAME? Errors for New Excel Functions Written by Python.

Step 7 — A five-number summary for the report

Medians and quartiles are most useful together: the minimum, Q1, median, Q3 and maximum — the "five-number summary" — describe a distribution in one row and are exactly what a box-and-whisker chart draws. Build it per region and write it with a label that says which quartile method was used, so nobody has to guess later:

five = df.groupby("Region")["Days"].describe()[["min", "25%", "50%", "75%", "max"]]
five.columns = ["Min", "Q1", "Median", "Q3", "Max"]
five["Method"] = "QUARTILE.INC (linear)"
with pd.ExcelWriter("delivery_summary.xlsx", engine="xlsxwriter") as xw:
    five.to_excel(xw, sheet_name="Summary")
    xw.sheets["Summary"].set_column("A:F", 12)
    xw.sheets["Summary"].set_column("G:G", 24)
print(five)

describe() uses the same linear method as quantile, so its 25%, 50% and 75% columns are the .INC quartiles. Stating the method in the output costs one column and prevents the most common dispute when a Python report replaces a spreadsheet: two correct numbers that differ because they answer slightly different questions. To turn the summary into a chart, see Create a Box and Whisker Chart in Excel with Python.

Performance notes

All of these statistics need sorted data or a partial sort, so they cost more than a mean, but pandas and NumPy implement them in compiled code: a median or set of quantiles over a million values takes a few tens of milliseconds. The slow part in the examples above is the Python-level lambda and mode_sngl called once per group; for thousands of groups, compute quantile([0.25, 0.75]) once on the grouped object (df.groupby("Region")["Days"].quantile([0.25, 0.75]).unstack()) instead of separate lambdas, and keep the custom mode helper for the columns that need Excel's exact tie-breaking.

Common pitfalls

• Text in numeric columns. Excel skips it; pandas refuses to compute. Coerce with pd.to_numeric(errors="coerce") — after deciding what the text means.
• Assuming one quartile definition. .INC and .EXC differ, more so on small samples; confirm which one the original report used.
• mode()[0] as MODE.SNGL. Correct only when the first-occurring tied value also happens to be the smallest.
• Empty groups. median of an empty group is NaN; filter groups by count before reporting.
• Rounding before computing. Round the outputs for display, never the inputs.

Conclusion

MEDIAN is median(), the .INC quartiles and percentiles are pandas' default quantile(), and the .EXC versions are NumPy's quantile with method="weibull". Mode needs care: pandas returns all modes sorted, while MODE.SNGL returns the first tied value in the data and #N/A when nothing repeats. Coerce text to NaN to mirror Excel's skipping, compute per group with named aggregations, and confirm against recalculated spreadsheet formulas — written with the _xlfn. prefix.

Frequently asked questions

Which pandas quantile method matches Excel QUARTILE.INC? The default, linear interpolation: s.quantile(0.25) and s.quantile(0.75) equal QUARTILE.INC(range, 1) and QUARTILE.INC(range, 3), and s.quantile(p) equals PERCENTILE.INC.

How do I calculate QUARTILE.EXC in Python? Use numpy.quantile(values, q, method="weibull") on the non-missing values. pandas' quantile has no equivalent option.

Why does pandas mode() give a different answer from Excel MODE? When several values tie, pandas returns all of them sorted, so mode()0 is the smallest. MODE.SNGL returns the tied value that appears first in the range, which depends on row order.

Does pandas median ignore text like Excel does? Not automatically. Convert the column with pd.to_numeric(column, errors="coerce"), which turns text into NaN; median and quantile then skip it, as Excel skips text cells.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions translated to pandas.
• STDEV and VAR Equivalents in pandas — spread measures and their sample-versus-population defaults.
• Rank and Percentile Formulas in pandas — RANK and PERCENTRANK.
• AVERAGEIFS, MAXIFS and MINIFS in pandas — conditional statistics per group.