Skip to content

Machine Learning Associate · Domain 2: Data Processing · 19% of the exam

Task 2.1: Compute summary statistics on a Spark DataFrame using .summary() or dbutils data summaries; Remove outliers from a Spark DataFrame based on standard deviation or IQR; Create visualizations for categorical or continuous features; Compare two categorical or two continuous features using the appropriate method

summary and describe, data profiles, filtering outliers with standard deviations or quartiles, notebook charts for categorical and numeric columns, and correlation or contingency tables to compare two features.

Study it

  • Exploring data: summary statistics, data profiles and outliers

    Partly covered by: Data Readiness for AI

  • Visualizing and comparing features in notebooks

    Lesson coming

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

Before choosing clipping thresholds for the numeric columns amount and duration of a Spark DataFrame df, a data scientist needs, for each column, the count, the minimum, the 1st and 99th percentiles and the maximum, returned as a DataFrame. Which call returns exactly these statistics?

  1. Adf.select("amount", "duration").summary("count", "min", "1%", "99%", "max")
  2. Bdf.select("amount", "duration").describe("count", "min", "1%", "99%", "max")
  3. Cdf.select("amount", "duration").summary()
  4. Ddbutils.data.summarize(df.select("amount", "duration"), precise=True)
Show the answer and why
  • Adf.select("amount", "duration").summary("count", "min", "1%", "99%", "max")

    Correct

    summary accepts the statistics to compute, including arbitrary approximate percentiles written as percentages.

  • Bdf.select("amount", "duration").describe("count", "min", "1%", "99%", "max")

    Incorrect

    describe takes column names, not statistics, and computes count, mean, stddev, min and max only.

  • Cdf.select("amount", "duration").summary()

    Incorrect

    Without arguments summary returns count, mean, stddev, min, the 25%, 50% and 75% quartiles and max, not the 1st and 99th percentiles.

  • Ddbutils.data.summarize(df.select("amount", "duration"), precise=True)

    Incorrect

    summarize displays summary statistics and returns nothing; it takes no list of percentiles, and it is in Public Preview.

DataFrame.summary computes the statistics you name: count, mean, stddev, min, max and arbitrary approximate percentiles such as "75%". With no arguments it computes count, mean, stddev, min, the approximate quartiles and max. DataFrame.describe computes count, mean, stddev, min and max for the columns you name. dbutils.data.summarize displays a profile instead of returning a DataFrame.

Question 2 · choose 1

The amount column of a Spark DataFrame df is strongly right-skewed and holds many extreme values. Before training a linear model, a data scientist wants to drop outlier rows with a rule that stays reliable on non-normal data with many extreme values. Which code does that? (F is pyspark.sql.functions.)

  1. Am, s = df.select(F.mean("amount"), F.stddev("amount")).first(); df.filter(F.abs((F.col("amount") - m) / s) < 3)
  2. Bq1, q3 = df.approxQuantile("amount", [0.25, 0.75], 0.01); iqr = q3 - q1; df.filter(F.col("amount").between(q1 - 1.5 * iqr, q3 + 1.5 * iqr))
  3. Cq1, q3 = df.approxQuantile("amount", [0.25, 0.75], 0.01); df.filter(F.col("amount").between(q1, q3))
  4. Dq1, q3 = df.approxQuantile("amount", [0.25, 0.75], 0.01); df.filter(F.col("amount").between(q1 - 1.5 * q1, q3 + 1.5 * q3))
Show the answer and why
  • Am, s = df.select(F.mean("amount"), F.stddev("amount")).first(); df.filter(F.abs((F.col("amount") - m) / s) < 3)

    Incorrect

    A z-score rule is best used when the data is normally distributed and when not too many or too extreme outliers are expected.

  • Bq1, q3 = df.approxQuantile("amount", [0.25, 0.75], 0.01); iqr = q3 - q1; df.filter(F.col("amount").between(q1 - 1.5 * iqr, q3 + 1.5 * iqr))

    Correct

    This is the IQR rule with fences at 1.5 times the interquartile range beyond Q1 and Q3, which is robust for non-normal data with many extreme outliers.

  • Cq1, q3 = df.approxQuantile("amount", [0.25, 0.75], 0.01); df.filter(F.col("amount").between(q1, q3))

    Incorrect

    Keeping only values between Q1 and Q3 drops half of the rows, not just the values beyond the IQR fences.

  • Dq1, q3 = df.approxQuantile("amount", [0.25, 0.75], 0.01); df.filter(F.col("amount").between(q1 - 1.5 * q1, q3 + 1.5 * q3))

    Incorrect

    The fences must be 1.5 times the interquartile range (Q3 - Q1) beyond the quartiles, not 1.5 times each quartile.

Databricks' data cleaning guide describes two rules: z-scores, best for normally distributed data without many extreme outliers, and the IQR rule (IQR = Q3 - Q1, keep values between Q1 - 1.5 * IQR and Q3 + 1.5 * IQR), which is robust to non-normal data and many extreme outliers. On a Spark DataFrame, approxQuantile returns the quartiles with a chosen relative error.

Question 3 · choose 1

A notebook query returns 40,000 deliveries with a region column (six values) and a numeric delivery_minutes column. An analyst wants one chart that compares the median, the spread and the skew of delivery_minutes across the six regions. Which notebook visualization type fits?

  1. AA pie chart of delivery_minutes by region
  2. BA heatmap of region against delivery_minutes
  3. CA bar chart of the average delivery_minutes per region
  4. DA box chart of delivery_minutes grouped by region
Show the answer and why
  • AA pie chart of delivery_minutes by region

    Incorrect

    A pie chart shows proportionality between values, not the spread of a distribution.

  • BA heatmap of region against delivery_minutes

    Incorrect

    A heatmap encodes numeric values as colors; it does not show quartiles for each region.

  • CA bar chart of the average delivery_minutes per region

    Incorrect

    One average per region hides the median, the spread and the skew of the values.

  • DA box chart of delivery_minutes grouped by region

    Correct

    A box chart summarizes a numeric distribution through its quartiles, optionally grouped by category, and 40,000 rows is within its aggregation limit.

The box chart visualization shows the distribution summary of numerical data, optionally grouped by category, so you can compare locality, spread and skewness across categories through the quartiles. Box charts aggregate up to 64,000 rows. Pie charts show proportionality, and heatmaps encode values as colors.

Question 4 · choose 2

A churn table of 50 million rows has the string columns plan_type (four values) and cancel_reason (seven values). A data scientist wants, computed in Spark, a count for every plan and reason pair, including pairs that never occur, and a test of whether the two columns are independent. Which two fit? (Choose TWO.)

  1. Adf.crosstab("plan_type", "cancel_reason")
  2. Bdf.freqItems(["plan_type", "cancel_reason"])
  3. CChiSquareTest.test on a DataFrame with cancel_reason indexed as the label and plan_type indexed and assembled into a vector features column
  4. DChiSquareTest.test(df, "plan_type", "cancel_reason") on the string columns as they are
  5. Edf.cov("plan_idx", "reason_idx") after indexing both columns with StringIndexer
Show the answer and why
  • Adf.crosstab("plan_type", "cancel_reason")

    Correct

    crosstab computes a pair-wise frequency (contingency) table of two columns, and pairs with no occurrences get a count of zero.

  • Bdf.freqItems(["plan_type", "cancel_reason"])

    Incorrect

    freqItems lists the frequent items of each column, possibly with false positives; it gives no count for each pair.

  • CChiSquareTest.test on a DataFrame with cancel_reason indexed as the label and plan_type indexed and assembled into a vector features column

    Correct

    ChiSquareTest runs Pearson's independence test of each feature against the label, with categorical values.

  • DChiSquareTest.test(df, "plan_type", "cancel_reason") on the string columns as they are

    Incorrect

    The features column must be a Vector column and the label numeric, so the strings have to be indexed and the feature assembled first.

  • Edf.cov("plan_idx", "reason_idx") after indexing both columns with StringIndexer

    Incorrect

    cov returns a single sample covariance value, which gives neither the pair counts nor an independence test.

For two categorical features, DataFrame.crosstab returns the contingency table of their combinations, with zero for pairs that never occur. ChiSquareTest computes the Chi-squared statistic from such a contingency matrix for each feature against the label; all label and feature values must be categorical, the features come as a Vector column and the label as a numeric column. freqItems finds frequent items column by column, and cov returns a single number for a pair of columns.

Question 5 · choose 1

A data scientist displays a Spark DataFrame with display(df) in a Databricks notebook and wants summary statistics and value histograms for every column without writing more code. What should she use?

  1. AThe Spark UI's SQL tab for the display query
  2. BThe + > Data Profile option next to the table output
  3. CThe notebook's revision history
  4. DThe Catalog Explorer permissions tab of the source table
Show the answer and why
  • AThe Spark UI's SQL tab for the display query

    Incorrect

    The Spark UI shows query execution details, not column statistics.

  • BThe + > Data Profile option next to the table output

    Correct

    Data Profile generates summary statistics and histograms of the values for each column.

  • CThe notebook's revision history

    Incorrect

    Revision history shows earlier versions of the notebook, not data statistics.

  • DThe Catalog Explorer permissions tab of the source table

    Incorrect

    The permissions tab shows grants, not column statistics.

Databricks notebooks include built-in data profiling. After display(df), click + > Data Profile next to the table output to run a command that profiles the DataFrame: summary statistics for numeric, string and date columns and histograms of each column's values.

Question 6 · choose 1

A data scientist has assembled several numeric features into a vector column features and wants their Spearman correlation matrix in Spark. Which call returns it?

  1. ACorrelation.corr(df, "features", "spearman")
  2. Bdf.corr("features", "features", method="spearman")
  3. Cdf.summary("spearman")
  4. Ddf.crosstab("features", "features")
Show the answer and why
  • ACorrelation.corr(df, "features", "spearman")

    Correct

    Correlation.corr computes the correlation matrix of a vector column with the Pearson or Spearman method.

  • Bdf.corr("features", "features", method="spearman")

    Incorrect

    DataFrame.corr compares two columns and currently supports only the Pearson method.

  • Cdf.summary("spearman")

    Incorrect

    summary computes per-column statistics, not correlations.

  • Ddf.crosstab("features", "features")

    Incorrect

    crosstab builds a frequency table of two columns' values, not a correlation matrix.

spark.ml's Correlation computes the correlation matrix for a Dataset of Vectors with Pearson's (the default) or Spearman's method. DataFrame.corr returns a single coefficient for two columns and supports only Pearson.

Question 7 · choose 1

To get the median latency, a data scientist calls df.approxQuantile("latency_ms", [0.5], 0.0) on a table with billions of rows. What does the relativeError of 0.0 do?

  1. AIt makes Spark ignore null values
  2. BIt returns the median of a random sample only
  3. CIt computes exact quantiles, which could be very expensive on this table
  4. DIt raises an error, because relativeError must be above zero
Show the answer and why
  • AIt makes Spark ignore null values

    Incorrect

    Nulls are ignored in numerical columns whatever relativeError is set to.

  • BIt returns the median of a random sample only

    Incorrect

    relativeError sets precision; 0 asks for exact quantiles, not a sample.

  • CIt computes exact quantiles, which could be very expensive on this table

    Correct

    With relativeError set to zero, the exact quantiles are computed, which the API reference warns could be very expensive.

  • DIt raises an error, because relativeError must be above zero

    Incorrect

    Any value of 0 or more is accepted; zero means exact quantiles.

approxQuantile returns approximate quantiles within a relative target precision (relativeError, 0 or more). Zero computes the exact quantiles, which could be very expensive; values above 1 give the same result as 1. Null values are ignored before calculation.

Question 8 · choose 1

A notebook query returns 2 million rows, and an analyst wants to chart the number of rows in each category of a categorical feature without the result being truncated. Which visualization type supports backend aggregation for results of more than 64K rows?

  1. ABar chart
  2. BBox chart
  3. CWord cloud of the category values
  4. DCounter of the rows per category
Show the answer and why
  • ABar chart

    Correct

    Bar charts support backend aggregations, so queries returning more than 64K rows are not truncated.

  • BBox chart

    Incorrect

    Box charts aggregate only up to 64,000 rows; larger datasets are truncated.

  • CWord cloud of the category values

    Incorrect

    Word clouds aggregate only up to 64,000 rows; larger datasets are truncated.

  • DCounter of the rows per category

    Incorrect

    Counters aggregate only up to 64,000 rows; larger datasets are truncated.

Several notebook visualization types, including bar, line, pie, heatmap, histogram and scatter charts, support backend aggregations for queries that return more than 64K rows. Box charts, counters, funnels and word clouds aggregate only up to 64,000 rows and truncate larger datasets.

Practice domain 2 →Practice all domains →