What you will be able to do
- Pick a comparison method from the types of the two features: Pearson correlation for two continuous features, a contingency table for two categorical features
- Compute Pearson correlation with df.corr, df.stat.corr, sf.corr or the SQL corr aggregate, and say what the method, DISTINCT and FILTER options do
- Explain why covariance (cov) and correlation (corr) return different numbers for the same pair of columns
- Build a contingency table with crosstab and read its $col1_$col2 header and its zero counts
Key concept
Match the method to the feature type — The comparison method depends on what the two features are. Two continuous (numeric) features are summarised by one Pearson correlation coefficient. Two categorical features are compared by counting how often each pair of values occurs together, which gives a contingency table.
1.Start with the feature types
Before you compute anything, look at the types of the two columns. If both are continuous, such as price and square footage, you can reduce their relationship to one number: a correlation coefficient. If both are categorical, such as payment method and region, you can't do arithmetic on the labels. Instead you count how often each combination of values appears, and that grid of counts is called a contingency table.
In PySpark, the tools for both cases are on one object, DataFrameStatFunctions, which the docs describe as "Functionality for statistical functions with a DataFrame." You reach it through df.stat. The table below lists the three methods that matter for comparing two features. The same class has other methods, such as quantiles, frequent items and stratified sampling, but they belong to other tasks.
| Method | Feature pair it suits | What it returns |
|---|---|---|
| corr(col1, col2, method) | Two continuous features | Pearson correlation coefficient as a double value |
| cov(col1, col2) | Two continuous features | Sample covariance as a double value |
| crosstab(col1, col2) | Two categorical features | Pair-wise frequency table (a DataFrame) |
Checkpoint 1 of 8· Check yourself
Which pairing of feature types and Spark method is correct?
corr returns a Pearson coefficient for numeric columns. crosstab builds the frequency table you use to compare categories.
“Returns Pearson coefficient of correlation between a group of number pairs.”Source: docs.databricks.com
2.Pearson correlation from a DataFrame
For two continuous columns, call corr with the two column names and you get back one float. DataFrame.corr and df.stat.corr are not two implementations that might disagree. The docs say plainly that "DataFrame.corr and DataFrameStatFunctions.corr are aliases of each other." Use whichever form reads better.
df = spark.createDataFrame([(1, 12), (10, 1), (19, 8)], ["c1", "c2"])
df.corr("c1", "c2")
# -0.3592106040535498
df = spark.createDataFrame([(11, 12), (10, 11), (9, 10)], ["small", "bigger"])
df.corr("small", "bigger")
# 1.0Look at the second example. In every row, bigger is exactly one more than small, so the two columns move in perfect step and the result is 1.0. The first example mixes directions and gives a negative value.
The signature has a third argument, method, which looks like it might let you choose another kind of correlation. It doesn't. The parameter table says: "The correlation method. Currently only supports \"pearson\"." If an exam option passes a different method name to this API, that option is wrong.
If you want the correlation as a column expression instead of a value returned to the driver, use the corr function in pyspark.sql.functions. It "Returns a new Column for the Pearson Correlation Coefficient for col1 and col2", so you use it inside agg:
from pyspark.sql import functions as sf
a = range(20)
b = [2 * x for x in range(20)]
df = spark.createDataFrame(zip(a, b), ["a", "b"])
df.agg(sf.corr("a", df.b)).show()
+----------+
|corr(a, b)|
+----------+
| 1.0|
+----------+Checkpoint 2 of 8· Fill the gap
This call returned -0.3592106040535498 for two numeric columns. Which method fills the blank?
df = spark.createDataFrame([(1, 12), (10, 1), (19, 8)], ["c1", "c2"])
df. ? ("c1", "c2")
# -0.3592106040535498corr returns the Pearson coefficient as a float. cov on the same data returns -18.0, and crosstab returns a DataFrame, not a single number.
Source: docs.databricks.comCheckpoint 3 of 8· Exam question
A data science team at a retail company has a Spark DataFrame with two categorical columns, `customer_segment` (Budget, Value, Premium, Luxury) and `preferred_channel` (Online, In-Store, Mobile App). They want to determine whether a statistically significant association exists between which segment a customer belongs to and which channel they prefer. Which approach should they use?
Correct answer: A — Build a contingency table cross-tabulating `customer_segment` against `preferred_channel`, then run a chi-square test of independence on the counts.
- A. Cross-tabulating two categorical columns produces the observed frequency counts that a chi-square test of independence compares against the counts expected under no association. This is the standard method for testing whether two categorical features are related.
- B. Pearson correlation requires numeric, ordered values and quantifies a linear relationship; string category labels like segment names have no inherent numeric order, so computing a correlation on them directly is not meaningful.
- C. ANOVA compares the mean of a continuous variable across groups defined by a categorical variable. Neither `customer_segment` nor `preferred_channel` is continuous, so there is no numeric outcome for ANOVA to compare means of.
- D. Linear regression models a continuous response as a function of predictors. `customer_segment` is a categorical label, not a continuous quantity, so it cannot serve as the response variable in this setup.
3.The SQL corr aggregate: DISTINCT and FILTER
In Databricks SQL and Databricks Runtime, corr is an aggregate function. Both arguments must be numeric: "expr1: An expression that evaluates to a numeric." The same applies to expr2. The result is a DOUBLE. It accepts two optional modifiers, and both change which rows go into the calculation.
> SELECT corr(c1, c2) FROM VALUES (3, 2), (3, 3), (3, 3), (6, 4) as tab(c1, c2);
0.816496580927726
> SELECT corr(DISTINCT c1, c2) FROM VALUES (3, 2), (3, 3), (3, 3), (6, 4) as tab(c1, c2);
0.8660254037844387
> SELECT corr(DISTINCT c1, c2) FILTER(WHERE c1 != c2)
FROM VALUES (3, 2), (3, 3), (3, 3), (6, 4) as tab(c1, c2);
1.0The third query adds FILTER (WHERE c1 != c2). That drops the (3, 3) pair completely and leaves (3, 2) and (6, 4). Those two surviving points slope upward, and the documented result is 1.0. A perfect coefficient from a heavily filtered query tells you about the rows that survived the filter, not about the features as a whole. The docs also note the function "can also be invoked as a window function using the OVER clause", which lets you compute a correlation per partition instead of over the whole table.
Checkpoint 4 of 8· Check yourself
You want the SQL corr aggregate to ignore rows where a sensor reported a known-bad status, without writing a subquery. Which part of the syntax does this?
The FILTER clause takes a boolean condition that decides which rows the aggregation uses. DISTINCT only removes duplicate pairs, and ALL is the default behaviour.
“cond: An optional boolean expression filtering the rows used for aggregation.”Source: docs.databricks.com
Sources5
4.Covariance is not the same number
The stat functions include a second two-column numeric method, cov. It is easy to confuse with corr because the signature is the same and so is the return type, a double. What it computes is different: it will "Calculate the sample covariance for the given columns, specified by their names, as a double value." Run it on the same data as the first corr example and compare.
df = spark.createDataFrame([(1, 12), (10, 1), (19, 8)], ["c1", "c2"])
df.cov("c1", "c2")
# -18.0
df = spark.createDataFrame([(11, 12), (10, 11), (9, 10)], ["small", "bigger"])
df.cov("small", "bigger")
# 1.0No. On the c1/c2 data, corr gives -0.3592106040535498 and cov gives -18.0. They agree on the small/bigger data only because of those particular values. When a question asks for the Pearson correlation between two continuous features, the method is corr, not cov.
Checkpoint 5 of 8· Match them up
Match each call to what it gives you
Tap a term, then the definition that fits it.
corr and cov both return a double but compute different statistics. crosstab returns a frequency DataFrame, and sf.corr is the column-function form of Pearson correlation.
“Calculates the sample covariance for the given columns as a double value.”Source: docs.databricks.com
Checkpoint 6 of 8· Exam question
A real estate analytics team has a Spark DataFrame containing two continuous numeric columns, `square_footage` and `sale_price`, for thousands of home sales. They want to quantify the strength and direction of the linear relationship between these two features. Which method is appropriate?
Correct answer: A — Compute the Pearson correlation coefficient between `square_footage` and `sale_price`, yielding a value between -1 and 1 summarizing the linear relationship.
- A. The Pearson correlation coefficient is the standard measure for the strength and direction of a linear relationship between two continuous features, expressed as a single value from -1 to 1. It directly answers what this team is asking about `square_footage` and `sale_price`.
- B. A contingency table and chi-square test are built for two categorical features with a finite set of category values. Applying them to continuous, near-unique numeric columns like square footage and price does not produce a meaningful frequency table.
- C. Binning both continuous columns and comparing means throws away the granularity of both features and answers a different question than the strength of their linear relationship; a t-test also assumes only two groups, not a full continuous relationship.
- D. One-hot encoding is meant for categorical features with a small number of distinct labels, not continuous measurements like square footage and price. Encoding these columns this way would create an unmanageable number of indicator columns and would not measure a linear relationship.
Sources6
5.Two categorical features: the contingency table
When both features are categorical, you count co-occurrences instead of computing a coefficient. crosstab does this. The distinct values of the first column become the rows, the distinct values of the second column become the column headers, and each cell holds how many rows had that pair of values. As with corr, the DataFrame and stat forms are aliases: df.crosstab and df.stat.crosstab give the same result.
df = spark.createDataFrame([(1, 11), (1, 11), (3, 10), (4, 8), (4, 8)], ["c1", "c2"])
df.crosstab("c1", "c2").sort("c1_c2").show()
# +-----+---+---+---+
# |c1_c2| 10| 11| 8|
# +-----+---+---+---+
# | 1| 0| 2| 0|
# | 3| 1| 0| 0|
# | 4| 0| 0| 2|
# +-----+---+---+---+Three details matter when you read this output. First, the leading column is not called c1. The docs say "The name of the first column will be $col1_$col2", which is why the example sorts on "c1_c2". Second, combinations that never occur still get a cell: "Pairs that have no occurrences will have zero as their counts", so you see 0, not null. Third, the result is a DataFrame ("Frequency matrix of two columns"), not a single statistic, so you can sort it, filter it or display it like any other DataFrame.
In this example, each value of c1 appears with only one value of c2, so every row of the table has a single non-zero cell.
Checkpoint 7 of 8· Check yourself
In the crosstab output above, what is in the cell for c1_c2 = 3 under column 11?
crosstab fills every combination of distinct values. A pair that never appears gets a count of 0, not a null and not a missing row.
“Pairs that have no occurrences will have zero as their counts.”Source: docs.databricks.com
Checkpoint 8 of 8· Exam question
A machine learning engineer is working with a native PySpark DataFrame `df` that has two numeric columns, `engine_size` and `fuel_consumption`, and wants to compute the Pearson correlation coefficient between them without converting to pandas. Which method call accomplishes this?
Correct answer: A — `df.stat.corr("engine_size", "fuel_consumption")` returns the Pearson correlation coefficient computed directly on the two numeric columns.
- A. The `stat.corr` method on a PySpark DataFrame computes the Pearson correlation coefficient between two named numeric columns without requiring a conversion to pandas. This is exactly the built-in method for this task.
- B. `stat.crosstab` builds a contingency table of co-occurrence counts, which is the tool for comparing two categorical columns, not for computing a correlation coefficient between two continuous columns.
- C. `describe` reports per-column summary statistics like mean and standard deviation independently for each column, but it does not compute any measure of the relationship between two columns.
- D. Grouping by one column and aggregating the mean of another produces conditional averages, not a correlation coefficient, and it treats the grouping column as if it were categorical rather than measuring a linear relationship between two continuous features.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.The method argument of DataFrame.corr lets you switch to another correlation type when Pearson doesn't fit.Why is that wrong?
The method parameter currently accepts only "pearson". This API computes Pearson correlation and nothing else.
Covered in Pearson correlation from a DataFrame
2.Adding DISTINCT to the SQL corr aggregate is harmless and can't change the coefficient.Why is that wrong?
DISTINCT removes duplicate (expr1, expr2) pairs before the calculation. On the documented data it changes the result from 0.816496580927726 to 0.8660254037844387.
Covered in The SQL corr aggregate: DISTINCT and FILTER
3.The first column of a crosstab result keeps the name of col1, and value pairs that never occur come back as nulls.Why is that wrong?
The first column is named $col1_$col2 (for example c1_c2), and pairs with no occurrences are filled with zero counts.
4.cov and corr are two names for the same Pearson statistic, since both take two columns and return a double.Why is that wrong?
cov returns the sample covariance, which is a different number. On the same c1/c2 data, cov gives -18.0 and corr gives -0.3592106040535498.
Covered in Covariance is not the same number
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Functionality for statistical functions with a DataFrame.”
↩︎ Start with the feature types“Calculates the sample covariance for the given columns as a double value.”
↩︎ Checkpoint - 2.
“Computes a pair-wise frequency table of the given columns. Also known as a contingency table.”
↩︎ Start with the feature types“The name of the first column will be $col1_$col2.”
↩︎ Two categorical features: the contingency table“DataFrame: Frequency matrix of two columns.”
↩︎ Two categorical features: the contingency table“The name of the first column will be $col1_$col2. Pairs that have no occurrences will have zero as their counts.”
↩︎ Exam trap 3“Pairs that have no occurrences will have zero as their counts.”
↩︎ Checkpoint - 3.
“DataFrame.corr and DataFrameStatFunctions.corr are aliases of each other.”
↩︎ Pearson correlation from a DataFrame“Calculates the correlation of two columns of a DataFrame as a double value.”
↩︎ Key concept“The correlation method. Currently only supports "pearson".”
↩︎ Exam trap 1 - 4.
“Returns a new Column for the Pearson Correlation Coefficient for col1 and col2.”
↩︎ Pearson correlation from a DataFrame - 5.
“expr1: An expression that evaluates to a numeric.”
↩︎ The SQL corr aggregate: DISTINCT and FILTER“This function can also be invoked as a window function using the OVER clause.”
↩︎ The SQL corr aggregate: DISTINCT and FILTER“If DISTINCT is specified the function operates only on a unique set of expr1, expr2 pairs.”
↩︎ Exam trap 2“Returns Pearson coefficient of correlation between a group of number pairs.”
↩︎ Checkpoint“If DISTINCT is specified the function operates only on a unique set of expr1, expr2 pairs.”
↩︎ Prediction“cond: An optional boolean expression filtering the rows used for aggregation.”
↩︎ Checkpoint - 6.
“Calculate the sample covariance for the given columns, specified by their names, as a double value.”
↩︎ Covariance is not the same number“Calculate the sample covariance for the given columns, specified by their names, as a double value.”
↩︎ Exam trap 4 - 7.https://docs.databricks.com/aws/en/pyspark/reference/classes/dataframestatfunctions/crosstabOfficial docs
“DataFrame.crosstab and DataFrameStatFunctions.crosstab are aliases of each other.”
↩︎ Two categorical features: the contingency table