STDEV.S, STDEV.P, VAR.S and VAR.P in pandas and NumPy

Standard deviation is the usual answer to "how consistent is this?" — how much weekly sales vary, how spread out delivery times are, how volatile a price is. Excel has two versions of it, STDEV.S for a sample and STDEV.P for a whole population, and so do Python's libraries. The trap is that they disagree about the default: pandas' .std() is the sample version, NumPy's std() is the population version. A job that switches between the two, or ports a spreadsheet without checking which function it used, reports numbers that differ from the workbook by a few percent — close enough to look plausible and far enough to fail a reconciliation. This page, part of Excel Formula Equivalents in pandas, matches each function exactly.

Prerequisites

pip install pandas numpy openpyxl

deals.xlsx has deals closed per week for three reps: three weeks each for Ada and Ben, one week for Cleo.

Step 1 — See the two defaults disagree

Take all seven weekly figures and compute the standard deviation three ways:

import numpy as np
import pandas as pd

deals = pd.read_excel("deals.xlsx")
x = deals["deals"]
print("pandas std():   ", round(x.std(), 6))
print("numpy std():    ", round(np.std(x.to_numpy()), 6))
print("pandas ddof=0:  ", round(x.std(ddof=0), 6))

pandas gives 3.251373 and NumPy 3.010187 for the same numbers. Entering =STDEV.S(C2:C8) and =STDEV.P(C2:C8) over the same values and recalculating the workbook gives exactly those two figures: pandas' default is STDEV.S, NumPy's is STDEV.P. The parameter that decides it is ddof, "delta degrees of freedom" — the number subtracted from the count before dividing. The sample version divides by n − 1, the population version by n.

Step 2 — Always pass ddof explicitly

Relying on defaults makes code change meaning when it moves between libraries — a calculation refactored from df.std() to np.std(df.values) silently switches from sample to population. State the choice every time, and name it after the Excel function it reproduces:

def stdev_s(values):
    return pd.Series(values, dtype="float64").std(ddof=1)

def stdev_p(values):
    return pd.Series(values, dtype="float64").std(ddof=0)

print(round(stdev_s(x), 6), round(stdev_p(x), 6))
print(round(x.var(ddof=1), 6), round(x.var(ddof=0), 6))      # VAR.S, VAR.P

The variances — 10.571429 and 9.061224 — match VAR.S and VAR.P in the same way; a variance is the square of its standard deviation. Excel's older STDEV and VAR are the sample versions, identical to STDEV.S and VAR.S.

Step 3 — Choose sample or population on purpose

The difference is not a technicality. The sample standard deviation is an estimate of the spread of a larger population, corrected upwards because a small sample tends to understate spread. The population version describes data that is complete. For a report on "variability of weekly deals this quarter" where every week is in the data, the population figure is the description of what happened; for "how variable will a new rep's weekly deals be", based on a few weeks of history, the sample figure is the better estimate. The two converge as the count grows — with seven values they differ by 8%, with seven hundred by less than a tenth of a percent.

In practice the most important rule is consistency: if existing reports use STDEV.S, a Python replacement must use ddof=1, or the new numbers will not reconcile with the old ones.

Step 4 — Per group, and groups with one member

Spread per rep is a groupby away. Groups with a single value need a decision, because the sample standard deviation of one value is undefined:

by_rep = deals.groupby("rep")["deals"].agg(weeks="count", mean="mean",
                                           sd_sample=lambda s: s.std(ddof=1),
                                           sd_population=lambda s: s.std(ddof=0))
print(by_rep.round(3))

Cleo has one week: the sample figure is NaN — Excel shows #DIV/0! for STDEV.S of a single value — and the population figure is 0. Neither says anything useful about Cleo's consistency; with one observation there is nothing to measure. Report the count next to the spread — readers weigh a figure differently when they can see it rests on three weeks — and suppress or flag spreads computed from fewer than a minimum number of values rather than showing a misleading zero.

Step 5 — Relative spread and z-scores

A standard deviation of 3 means something different for a rep averaging 13 deals than for one averaging 130. The coefficient of variation — standard deviation divided by the mean — compares consistency across different scales, and z-scores express each value in standard deviations from its group's mean:

by_rep["cv"] = (by_rep["sd_population"] / by_rep["mean"]).round(3)
print(by_rep[["mean", "sd_population", "cv"]])

g = deals.groupby("rep")["deals"]
deals["z"] = ((deals["deals"] - g.transform("mean")) / g.transform(lambda s: s.std(ddof=0))).round(2)
print(deals.to_string(index=False))

Ada and Ben have similar relative spreads despite different averages. Cleo's z-score is NaN — zero divided by zero — which is correct: a lone value is neither above nor below its own mean. transform keeps the group statistics aligned with each row, the same pattern as Calculate Percent of Total per Group in pandas. For outlier detection, see Detect Outliers in Excel Data with pandas, which explains why z-scores are a weak tool for finding extreme values.

Rolling volatility

Spread over a sliding window — "how volatile were the last three weeks?" — uses the same ddof rule on a rolling window. pandas' rolling standard deviation defaults to the sample version, matching =STDEV.S over a range dragged down the sheet:

ada = deals.loc[deals["rep"] == "Ada", "deals"].reset_index(drop=True)
print(ada.rolling(2).std().round(3).tolist())
print(ada.rolling(2).std(ddof=0).round(3).tolist())

With a window of two weeks, the sample figures are NaN, 2.121 and 2.828; the population figures are NaN, 1.5 and 2.0. The first value is NaN in both because the window is not yet full — Calculate Moving Averages with pandas rolling covers min_periods and time-based windows, which apply unchanged to rolling spreads. Very short windows make the sample correction large: with two values it inflates the spread by 41%, one more reason to state the convention next to the figure.

Step 6 — Missing values and text

pandas skips NaN by default, like Excel's STDEV.S, which ignores empty cells and text in a range. Excel's STDEVA family instead treats text as 0 and TRUE as 1 — rarely what anyone wants, but worth knowing when porting a workbook that uses it:

mixed = pd.Series([12, None, 15, 11])
print(round(mixed.std(), 6), "| count used:", mixed.count())

as_stdeva = pd.to_numeric(pd.Series([12, "n/a", 15, 11]), errors="coerce").fillna(0)
print(round(as_stdeva.std(), 6))

The first figure uses the three real values; the second reproduces STDEVA's habit of counting the text as a zero, which roughly triples the spread. If a workbook's figures look inflated, check whether it uses the A variants.

Common pitfalls

• Trusting defaults across libraries. pandas is ddof=1, NumPy ddof=0; always pass ddof.
• Switching conventions mid-project. Match the function existing reports use, or the numbers will not reconcile.
• Spreads from one or two values. Report the count and flag small groups.
• Zero standard deviation. Dividing by it for z-scores gives NaN or inf; handle constant groups, which are common in small categories where every value is the same.

Performance notes

std and var are vectorised and grouped versions with string arguments — agg("std") — run in optimised code. Lambdas in agg call Python once per group, which is fine for thousands of groups; for more, use agg(["std"]) with the default ddof=1, or compute the population version from grouped sums of values and squares.

Conclusion

pandas' .std() and .var() are Excel's STDEV.S and VAR.S; NumPy's std() and var() are STDEV.P and VAR.P; ddof switches between them. Pass ddof explicitly every time, choose sample or population deliberately — and consistently with existing reports — report group counts beside spreads so single-value groups do not mislead, and use coefficients of variation and z-scores when comparing spread across groups of different size.

Frequently asked questions

Does pandas std() match STDEV.S or STDEV.P? STDEV.S. pandas uses ddof=1 by default, the sample standard deviation, which divides by n - 1. Pass ddof=0 to get the population version, STDEV.P.

Why does numpy.std give a different answer from pandas? NumPy defaults to ddof=0, the population standard deviation, which divides by n. The same list gives STDEV.P with numpy.std and STDEV.S with pandas' .std(). Always pass ddof explicitly when mixing the two.

Which should I use, sample or population? Use the population version when the data is the whole group you care about — every month of this year, every rep in the team. Use the sample version when the data is a sample used to estimate the spread of something larger. For most report figures describing complete data, population is the honest choice; many teams use STDEV.S by habit, so match whatever the existing reports use.

What happens with a single value? The sample standard deviation of one value is undefined: Excel shows #DIV/0! and pandas returns NaN. The population version of one value is 0. Groups with a single member often appear in grouped statistics.

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• RANK and PERCENTILE Formulas in pandas — position-based measures of spread.
• AVERAGEIFS, MAXIFS and MINIFS Equivalents in pandas — conditional statistics.
• Calculate Moving Averages with pandas rolling — rolling standard deviations use the same window options.