What you will be able to do
- Define the interquartile range and the 1.5 × IQR fences around the quartiles
- Compute Q1 and Q3 with DataFrame.approxQuantile and choose a relativeError
- Compute quartiles as Column expressions with percentile_approx, per group if needed, and filter on the fences
1.The interquartile range and the 1.5 × IQR fences
The IQR method builds the bounds from quartiles instead of the average and standard deviation. Spark's box-plot reference gives the definitions. The box extends from the Q1 to the Q3 quartile, with a line at the median (Q2), and the interquartile range is IQR = Q3 − Q1. The whiskers reach at most 1.5 × IQR beyond the edges of the box. Points outside that range count as outliers and are plotted as separate dots.
As a filter, this rule gives two fences:
- lower fence = Q1 − 1.5 × IQR - upper fence = Q3 + 1.5 × IQR
Rows whose value lies between the fences are kept, and the rest are the outliers. The fences are offset from Q1 and Q3, not from the median, so a column whose middle half is widely spread gets wide fences. The only inputs to the rule are two quantiles, at probabilities 0.25 and 0.75. Computing those two numbers is the only Spark-specific work.
Checkpoint 1 of 6· Put it in order
Put the steps of IQR outlier removal in order
- 1.Filter the DataFrame to keep rows whose value lies between the fences
- 2.Compute IQR = Q3 − Q1
- 3.Compute Q1 and Q3, the quantiles at 0.25 and 0.75
- 4.Set the fences at Q1 − 1.5 × IQR and Q3 + 1.5 × IQR
Each step needs the result of the one before it. The IQR comes from the quartiles, the fences come from the IQR, and the filter needs the fences.
“By default, they extend no more than 1.5 × IQR (IQR = Q3 - Q1) from the edges of the box”Source: docs.databricks.com
Sources1
2.Computing quartiles with approxQuantile
DataFrame.approxQuantile calculates approximate quantiles of numerical columns. You can also reach it as df.stat.approxQuantile. It takes three arguments:
| Parameter | Type | Meaning |
|---|---|---|
| col | str, tuple or list | One column name, or a list of names to compute several columns in one call |
| probabilities | list or tuple of floats in [0, 1] | 0.0 is the minimum, 0.5 the median, 1.0 the maximum; use 0.25 and 0.75 for Q1 and Q3 |
| relativeError | float (>= 0) | Target precision; 0 computes exact quantiles, which could be very expensive; values above 1 behave like 1 |
Checkpoint 2 of 6· Fill the gap
Which DataFrame method completes this sample?
data = [(1,), (2,), (3,), (4,), (5,)]
df = spark.createDataFrame(data, ["values"])
quantiles = df. ? ("values", [0.0, 0.5, 1.0], 0.05)
quantiles
# [1.0, 3.0, 5.0]approxQuantile is the DataFrame method that takes a column, a list of probabilities and a relativeError, and returns a list of floats. percentile_approx is a function that returns a Column, and approx_percentile is the SQL function.
Source: docs.databricks.comThe method uses a variation of the Greenwald-Khanna algorithm, and relativeError controls its precision. A value of zero computes exact quantiles, which could be very expensive. The documentation examples use 0.05.
The return type is what makes approxQuantile easy to use for fences. It returns plain Python floats, not a Column. For one column name you get a list of floats. For a list of names you get a list of lists, one per column, in the order you named them:
data = [(1, 10), (2, 20), (3, 30), (4, 40), (5, 50)]
df = spark.createDataFrame(data, ["col1", "col2"])
quantiles = df.approxQuantile(["col1", "col2"], [0.0, 0.5, 1.0], 0.05)
quantiles
# [[1.0, 3.0, 5.0], [10.0, 30.0, 50.0]]Since you get plain floats back, the IQR and the fences are ordinary Python arithmetic, and the results can go straight into between as literal bounds. Nulls need no special handling, because null values are ignored before the calculation. A column that contains only nulls returns an empty list, so code that indexes into the result should allow for that case.
data = [(1,), (None,), (3,), (4,), (None,)]
df = spark.createDataFrame(data, ["values"])
df.stat.approxQuantile("values", [0.0, 0.5, 1.0], 0.05)
# [1.0, 3.0, 4.0]Checkpoint 3 of 6· Match them up
Match each approxQuantile input to its result
Tap a term, then the definition that fits it.
Each pairing comes from the approxQuantile reference: zero error means exact computation, all-null columns give an empty list, multiple columns give a list of lists, and 0.5 is the median.
“For columns only containing null values, an empty list is returned.”Source: docs.databricks.com
Checkpoint 4 of 6· Exam question
A data engineer needs to remove outliers from the `amount` column of a Spark DataFrame using the IQR method, using a relative error tolerance of `0.05` when computing quartiles to keep the job fast on a large dataset. Which code correctly implements this?
Correct answer: A — ``` q1, q3 = df.approxQuantile("amount", [0.25, 0.75], 0.05) iqr = q3 - q1 df.filter((col("amount") >= q1 - 1.5*iqr) & (col("amount") <= q3 + 1.5*iqr)) ```
- A. Passing `0.05` as the relative error gives Spark the requested approximation tolerance, and the IQR is correctly computed as `q3 - q1` before building the standard lower and upper fences from it.
- B. Passing `0.0` forces Spark to compute the exact quantiles instead of the requested `0.05` tolerance, which requires a far more expensive full pass over the data and defeats the goal of keeping the job fast.
- C. This computes the mean and standard deviation instead of quartiles, which is the standard-deviation outlier method rather than the IQR method the task specifically asked for, and it ignores the relative error tolerance entirely.
- D. The IQR is computed as `q1 - q3` instead of `q3 - q1`, producing a negative value that inverts the fence calculation and leaves the filter with nonsensical, backwards bounds.
3.Quartiles as column expressions, and applying the fences
approxQuantile returns Python lists. Sometimes you want the quartiles inside a query instead, for example one pair of fences for each category. sf.percentile_approx(col, percentage, accuracy) returns a Column, and percentage can be a list such as [0.25, 0.5, 0.75]. Because it is an aggregate, it also works inside groupBy().agg() and computes a separate percentile for each group:
from pyspark.sql import functions as sf
key = (sf.col("id") % 3).alias("key")
value = (sf.randn(42) + key * 10).alias("value")
df = spark.range(0, 1000, 1, 1).select(key, value)
df.groupBy("key").agg(
sf.percentile_approx("value", sf.lit(0.5), sf.lit(1000000))
).sort("key").show()Its precision setting works the opposite way to relativeError. accuracy is a positive integer: a higher value yields better accuracy at the cost of memory, and the relative error is 1.0/accuracy. The default is 10000. In SQL, approx_percentile is a synonym for percentile_approx and has the same accuracy default.
| API | Where you call it | Precision setting | Result |
|---|---|---|---|
| approxQuantile | DataFrame method (or df.stat) | relativeError: lower is more precise; 0 is exact | Python list of floats |
| percentile_approx | pyspark.sql.functions, inside select or agg | accuracy: higher is more precise; default 10000 | Column |
| approx_percentile | SQL aggregate (synonym for percentile_approx) | accuracy: omitted means 10000 | Value or array per group |
Checkpoint 5 of 6· Check yourself
You want percentile_approx to compute Q1 and Q3 with a relative error of 1%. Which accuracy value gives that?
The relative error is 1.0/accuracy, so 1% needs accuracy = 100. A value of 0.01 is how you would express it with approxQuantile's relativeError, and accuracy must be a positive integer.
“1.0/accuracy is the relative error (default: 10000).”Source: docs.databricks.com
However you get the quartiles, the removal step is a filter. df.filter returns a new DataFrame with the rows that satisfy a Column of BooleanType or a SQL expression string. You write the condition for the rows you keep. Something like df.value.between(lower_fence, upper_fence) is the natural way to say it, since between includes both bounds.
Checkpoint 6 of 6· Exam question
A sensor readings DataFrame is approximately normally distributed, and the team wants to flag values more than three standard deviations from the mean as outliers before removing them from the `reading` column. Which Spark code correctly computes this filter?
Correct answer: A — ``` stats = df.select(mean("reading").alias("m"), stddev("reading").alias("s")).first() df.filter((col("reading") >= stats["m"] - 3*stats["s"]) & (col("reading") <= stats["m"] + 3*stats["s"])) ```
- A. The mean and standard deviation are computed once, and the filter keeps rows within three standard deviations on either side, matching the requested three-sigma threshold exactly.
- B. This filters to within one standard deviation rather than three, which is a far tighter band that flags a large share of ordinary sensor readings as outliers instead of only the extreme ones.
- C. This mixes a quartile-based IQR calculation with the requested standard-deviation threshold, which is a different outlier method entirely and does not measure distance from the mean in standard deviations.
- D. The comparison operators and the `|` combine to keep only rows outside the three-standard-deviation band, which retains the outliers themselves rather than removing them from the DataFrame.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.The 1.5 × IQR fences are measured from the median.Why is that wrong?
They are measured from the edges of the box, Q1 for the lower fence and Q3 for the upper fence.
2.Setting relativeError to 0 in approxQuantile is a free way to get precise quartiles.Why is that wrong?
Zero does compute exact quantiles, but the reference warns that this could be very expensive. Non-zero values return approximate quantiles.
Covered in Computing quartiles with approxQuantile
3.percentile_approx's accuracy works like relativeError, so a smaller value is more precise.Why is that wrong?
It is the inverse. A higher accuracy is more precise, and the relative error is 1.0/accuracy.
Covered in Quartiles as column expressions, and applying the fences
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The box extends from the Q1 to Q3 quartile values of the data, with a line at the median (Q2).”
↩︎ The interquartile range and the 1.5 × IQR fences“Outliers are plotted as separate dots.”
↩︎ The interquartile range and the 1.5 × IQR fences“By default, they extend no more than 1.5 × IQR (IQR = Q3 - Q1) from the edges of the box”
↩︎ Exam trap 1“By default, they extend no more than 1.5 × IQR (IQR = Q3 - Q1) from the edges of the box”
↩︎ Prediction - 2.https://docs.databricks.com/aws/en/pyspark/reference/classes/dataframestatfunctions/approxQuantileOfficial docs
“This method implements a variation of the Greenwald-Khanna algorithm with some speed optimizations.”
↩︎ Computing quartiles with approxQuantile“If col is a list or tuple of strings, returns a list of lists of floats.”
↩︎ Computing quartiles with approxQuantile - 3.
“Null values will be ignored in numerical columns before calculation.”
↩︎ Computing quartiles with approxQuantile“For example 0.0 is the minimum, 0.5 is the median, 1.0 is the maximum.”
↩︎ Computing quartiles with approxQuantile“If set to zero, the exact quantiles are computed, which could be very expensive.”
↩︎ Exam trap 2“For columns only containing null values, an empty list is returned.”
↩︎ Checkpoint - 4.
“A positive numeric literal which controls approximation accuracy at the cost of memory.”
↩︎ Quartiles as column expressions, and applying the fences“Higher value yields better accuracy.”
↩︎ Exam trap 3“1.0/accuracy is the relative error (default: 10000).”
↩︎ Checkpoint - 5.
“This function is a synonym for percentile_approx aggregate function.”
↩︎ Quartiles as column expressions, and applying the fences - 6.
“A Column of BooleanType or a string of SQL expressions.”
↩︎ Quartiles as column expressions, and applying the fences - 7.
“Check if the current column's values are between the specified lower and upper bounds, inclusive.”
↩︎ Quartiles as column expressions, and applying the fences