Developing Apache Spark DataFrame/DataSet API Applications · Topic 2 of 6

Deduplication, Validation and Aggregation

Remove duplicates with distinct and dropDuplicates, validate data for nulls, and aggregate with count, approx_count_distinct, mean, describe and summary.

4 min

In a nutshell

distinct() compares all columns, and dropDuplicates(subset) compares only the subset.
With a subset, which duplicate survives is not guaranteed.
count("*") counts rows, and count("col") ignores nulls.

What it is

Deduplication removes repeated rows. Validation checks that the data is fit to use, for example that required columns have no nulls. Aggregation reduces many rows to summary values such as a count or a mean.

The exam asks which call fits, what a short snippet returns, and where a result is exact or only approximate.

How it works

Removing duplicates

Call	Compares	Result
`df.distinct()`	All columns	Unique full rows
`df.dropDuplicates()`	All columns	Same as distinct
`df.dropDuplicates(["id"])`	Only the listed columns	One row per id, all columns kept

drop_duplicates is an alias of dropDuplicates. When you pass a subset, Spark keeps one row per key but does not guarantee which one. To keep the latest row per key, rank the rows with a window and keep rank 1.

from pyspark.sql import Window, functions as F
w = Window.partitionBy("id").orderBy(F.col("updated").desc())
latest = df.withColumn("r", F.row_number().over(w)).filter("r = 1").drop("r")

Deduplication is a wide transformation, so it shuffles data across the cluster.

Validating data

Common checks use plain DataFrame calls.

Need	Call
Remove rows with any null	`df.dropna()`
Remove rows only if specific columns are null	`df.dropna(subset=["id", "email"])`
Require at least N non null values	`df.dropna(thresh=2)`
Replace nulls	`df.fillna({"qty": 0, "name": "unknown"})`
Count nulls in a column	`df.filter(F.col("email").isNull()).count()`
Test a rule	`df.filter(F.col("age") < 0)` returns the bad rows

dropna with the default how="any" drops a row if any checked column is null. With how="all" it drops the row only when every checked column is null.

Basic aggregation

groupBy(...).agg(...) computes values per group. Calling agg directly on the DataFrame aggregates the whole table into one row.

df.groupBy("dept").agg(
    F.count("*").alias("rows"),
    F.countDistinct("emp").alias("people"),
    F.mean("salary").alias("avg_salary"))

F.mean and F.avg are the same function. F.count("*") counts all rows, while F.count("col") skips nulls in that column. df.count() is an action that returns a Python integer, but groupBy().count() returns a DataFrame.

Approximate count distinct

F.countDistinct is exact but must track every distinct value, which is expensive on large data. F.approx_count_distinct(col, rsd=0.05) uses a HyperLogLog sketch. It is much cheaper and uses less memory, with a maximum relative standard deviation that defaults to 0.05 (5 percent).

Use the approximate version when a close answer is enough, such as unique visitors on a huge table. A smaller rsd is more accurate but costs more.

describe and summary

Method	Statistics returned
`df.describe()`	count, mean, stddev, min, max
`df.summary()`	count, mean, stddev, min, 25%, 50%, 75%, max

Both return a DataFrame, so call .show() to see them. They work on numeric and string columns, and you can pass column names to describe. summary also accepts the statistic names you want, such as df.summary("count", "min", "75%"). Its percentiles are approximate.

How the exam asks about this

"Keep one row per customer id and ignore other columns when comparing." Use dropDuplicates(["customer_id"]).
"Unique users on a table with billions of rows; a small error is acceptable." Use approx_count_distinct.
"Get quartiles for a numeric column with one call." Use summary(), because describe() has no quartiles.

What to remember for the exam

distinct() compares all columns, and dropDuplicates(subset) compares only the subset.
With a subset, which duplicate survives is not guaranteed.
count("*") counts rows, and count("col") ignores nulls.
approx_count_distinct trades a small error (default rsd 0.05) for speed and memory.
describe gives five statistics, and summary adds the 25%, 50% and 75% percentiles.
dropna takes how, thresh and subset.

Watch out

describe() and summary() return DataFrames, not printed text. Without .show() nothing is displayed.

Common mistakes

Expecting the first or latest row to survive. dropDuplicates(["id"]) keeps an arbitrary row per id. If order matters, use a window with row_number.

Counting nulls as values. F.count("email") skips null emails, so it can be lower than F.count("*"). Use that gap to measure missing data, not as a sign of a bug.

Using an exact distinct count where an estimate is enough. countDistinct on a very large column is slow and memory heavy. Choose approx_count_distinct when the requirement allows a small error.

Check yourself

What is the difference between df.distinct() and df.dropDuplicates(["id"])?
distinct() compares every column, while the subset version compares only id and keeps one arbitrary row per id with all its columns.
Which function gives a fast estimate of distinct values, and what controls its accuracy?
F.approx_count_distinct, whose rsd argument sets the maximum relative standard deviation and defaults to 0.05.
Which method returns quartiles, describe or summary?
summary, which returns count, mean, stddev, min, 25%, 50%, 75% and max, while describe omits the percentiles.

Topic 2 of 6 in Developing Apache Spark DataFrame/DataSet API Applications

Test yourself on Developing Apache Spark DataFrame/DataSet API Applications

The full Databricks Associate Developer for Apache Spark question bank has 600 questions, with timed practice exams and an explanation for every answer.

Get Full Access Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Databricks. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.