Spark Scala Standard Deviation and Variance

Standard deviation and variance measure how spread out the values in a numeric column are. Spark provides a full family of aggregate functions for both — stddev, stddev_samp, and stddev_pop for standard deviation, and variance, var_samp, and var_pop for variance. The only real decision you have to make is whether you want the sample or population form.

Sample standard deviation with stddev

stddev is the function you'll reach for most often. It returns the sample standard deviation of the values in a group — it's a direct alias for stddev_samp. Both have two overloads, one taking a column name as a string and one taking a Column:

def stddev(columnName: String): Column

def stddev(e: Column): Column

def stddev_samp(e: Column): Column

Because stddev and stddev_samp are the same function, they always produce identical results:

val df = Seq(
  ("Alice",  "Section A", 85.0),
  ("Bob",    "Section A", 90.0),
  ("Carol",  "Section A", 78.0),
  ("Dave",   "Section B", 92.0),
  ("Eve",    "Section B", 88.0),
  ("Frank",  "Section B", 95.0),
).toDF("student", "section", "score")

val df2 = df
  .groupBy("section")
  .agg(
    stddev(col("score")).as("stddev"),
    stddev_samp(col("score")).as("stddev_samp"),
  )
  .orderBy("section")

df2.show(false)
// +---------+-----------------+-----------------+
// |section  |stddev           |stddev_samp      |
// +---------+-----------------+-----------------+
// |Section A|6.027713773341708|6.027713773341708|
// |Section B|3.511884584284246|3.511884584284246|
// +---------+-----------------+-----------------+

The result is always a Double. A larger value means the scores in that section are more spread out; Section A's scores (78–90) are more scattered than Section B's (88–95), so it has the larger standard deviation.

Sample vs population: stddev_samp vs stddev_pop

This is the distinction that trips people up. Sample and population standard deviation differ only in their denominator:

stddev_samp (the sample form, and what stddev aliases) divides by n - 1. Use it when your data is a sample drawn from a larger population and you want to estimate the spread of that whole population.
stddev_pop divides by n. Use it when your data is the entire population you care about.

def stddev_pop(e: Column): Column

Because the sample form divides by a smaller number, it always produces a slightly larger value than the population form for the same data:

val df = Seq(
  ("Alice",  "Section A", 85.0),
  ("Bob",    "Section A", 90.0),
  ("Carol",  "Section A", 78.0),
  ("Dave",   "Section B", 92.0),
  ("Eve",    "Section B", 88.0),
  ("Frank",  "Section B", 95.0),
).toDF("student", "section", "score")

val df2 = df
  .groupBy("section")
  .agg(
    stddev_samp(col("score")).as("stddev_samp"),
    stddev_pop(col("score")).as("stddev_pop"),
  )
  .orderBy("section")

df2.show(false)
// +---------+-----------------+------------------+
// |section  |stddev_samp      |stddev_pop        |
// +---------+-----------------+------------------+
// |Section A|6.027713773341708|4.921607686744467 |
// |Section B|3.511884584284246|2.8674417556808756|
// +---------+-----------------+------------------+

If you're not sure which one you want, stddev_samp (i.e. plain stddev) is the conventional default — it matches what most spreadsheet and statistics tools call "standard deviation."

Variance: variance, var_samp, and var_pop

Variance is simply the square of the standard deviation, and it comes in the same three flavors. variance is an alias for var_samp (the sample form), and var_pop is the population form:

def variance(e: Column): Column

def var_samp(e: Column): Column

def var_pop(e: Column): Column

val df = Seq(
  ("Alice",  "Section A", 85.0),
  ("Bob",    "Section A", 90.0),
  ("Carol",  "Section A", 78.0),
  ("Dave",   "Section B", 92.0),
  ("Eve",    "Section B", 88.0),
  ("Frank",  "Section B", 95.0),
).toDF("student", "section", "score")

val df2 = df
  .groupBy("section")
  .agg(
    variance(col("score")).as("variance"),
    var_samp(col("score")).as("var_samp"),
    var_pop(col("score")).as("var_pop"),
  )
  .orderBy("section")

df2.show(false)
// +---------+------------------+------------------+------------------+
// |section  |variance          |var_samp          |var_pop           |
// +---------+------------------+------------------+------------------+
// |Section A|36.333333333333336|36.333333333333336|24.222222222222225|
// |Section B|12.333333333333332|12.333333333333332|8.222222222222221 |
// +---------+------------------+------------------+------------------+

Notice that variance and var_samp match exactly, just as stddev and stddev_samp did. And each variance is the square of the corresponding standard deviation from the previous examples — Section A's sample standard deviation of 6.0277... squared gives its sample variance of 36.333....

The std SQL alias

Spark SQL exposes one more name for the sample standard deviation: std. It isn't available directly in the org.apache.spark.sql.functions object, so you call it through expr():

def std(expr): Column — via expr()

It's just another alias for stddev_samp, so it produces the same result — handy if you're porting SQL that already uses std:

val df = Seq(
  ("Alice",  "Section A", 85.0),
  ("Bob",    "Section A", 90.0),
  ("Carol",  "Section A", 78.0),
  ("Dave",   "Section B", 92.0),
  ("Eve",    "Section B", 88.0),
  ("Frank",  "Section B", 95.0),
).toDF("student", "section", "score")

val df2 = df
  .groupBy("section")
  .agg(
    expr("std(score)").as("std"),
    stddev_samp(col("score")).as("stddev_samp"),
  )
  .orderBy("section")

df2.show(false)
// +---------+-----------------+-----------------+
// |section  |std              |stddev_samp      |
// +---------+-----------------+-----------------+
// |Section A|6.027713773341708|6.027713773341708|
// |Section B|3.511884584284246|3.511884584284246|
// +---------+-----------------+-----------------+

Nulls and single-value groups

Two edge cases are worth knowing before you rely on these functions in production.

First, nulls are skipped, exactly like the other aggregates — they don't count toward n and don't affect the result.

Second, and more surprising: the sample functions need at least two non-null values. Sample standard deviation and variance divide by n - 1, so a group with a single value would divide by zero — Spark returns null rather than an error. The population functions divide by n, so a single-value group returns 0.0 (a group of one has no spread from its own mean).

val df = Seq(
  ("Alice",  "Section A", Some(85.0)),
  ("Bob",    "Section A", Some(90.0)),
  ("Carol",  "Section A", None),
  ("Dave",   "Section B", Some(92.0)),
  ("Solo",   "Section C", Some(70.0)),
).toDF("student", "section", "score")

val df2 = df
  .groupBy("section")
  .agg(
    count("score").as("non_null_scores"),
    stddev_samp(col("score")).as("stddev_samp"),
    stddev_pop(col("score")).as("stddev_pop"),
  )
  .orderBy("section")

df2.show(false)
// +---------+---------------+------------------+----------+
// |section  |non_null_scores|stddev_samp       |stddev_pop|
// +---------+---------------+------------------+----------+
// |Section A|2              |3.5355339059327378|2.5       |
// |Section B|1              |null              |0.0       |
// |Section C|1              |null              |0.0       |
// +---------+---------------+------------------+----------+

In Section A, Carol's null score is ignored, leaving two values (85 and 90) — enough for the sample standard deviation. Sections B and C each have a single non-null score, so stddev_samp comes back as null while stddev_pop is 0.0. If you'd rather see 0 than null for single-value groups, wrap the result with coalesce: coalesce(stddev_samp(col("score")), lit(0.0)).