Null and duplicate detection in Apache Spark - Step-by-Step Execution

Concept Flow - Null and duplicate detection

Start with DataFrame

↓

Check for Nulls

↓

Count Nulls per Column

↓

Check for Duplicates

↓

Count Duplicate Rows

↓

Output Null and Duplicate Summary

↓

End

We start with a DataFrame, check each column for null values, count them, then check for duplicate rows and count those, finally outputting a summary.

Execution Sample

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, when, count, lit
spark = SparkSession.builder.getOrCreate()
df = spark.createDataFrame(
  [(1, 'A'), (2, None), (1, 'A')], ['id', 'value'])

null_counts = df.select([count(when(col(c).isNull(), lit(1))).alias(c) for c in df.columns]).collect()[0].asDict()
dup_count = df.count() - df.dropDuplicates().count()

This code creates a DataFrame, counts nulls per column, and counts duplicate rows.

Execution Table

Step	Action	DataFrame State	Null Counts	Duplicate Count
1	Create DataFrame with 3 rows	[ (1, 'A'), (2, None), (1, 'A') ]	N/A	N/A
2	Count nulls in 'id'	Same	id: 0	N/A
3	Count nulls in 'value'	Same	value: 1	N/A
4	Count total nulls per column	Same	{'id': 0, 'value': 1}	N/A
5	Count total rows	3 rows	Same	N/A
6	Drop duplicates	[ (1, 'A'), (2, None) ]	Same	N/A
7	Count rows after dropDuplicates	2 rows	Same	N/A
8	Calculate duplicates: 3 - 2	Same	Same	1
9	Output null counts and duplicate count	Same	{'id': 0, 'value': 1}	1
10	End of detection	Same	Same	Same

💡 All rows processed; nulls and duplicates counted.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 7	After Step 8	Final
df	Empty	3 rows with data	Same	2 rows after dropDuplicates	Same	Same
null_counts	N/A	Partial counts	{'id': 0, 'value': 1}	Same	Same	{'id': 0, 'value': 1}
dup_count	N/A	N/A	N/A	N/A	1	1

Key Moments - 3 Insights

Why does the null count for 'value' show 1 but 'id' shows 0?

Because in the DataFrame, only the 'value' column has a None (null) value at step 3, while 'id' has no nulls, as shown in execution_table rows 2-4.

How is the duplicate count calculated?

Duplicate count is total rows minus rows after dropping duplicates, as shown in execution_table rows 5-8.

Does dropDuplicates remove all duplicates or just some?

dropDuplicates removes all exact duplicate rows, leaving only unique rows, as seen in execution_table row 6.

Visual Quiz - 3 Questions

Look at the execution table at step 4, what is the null count for the 'value' column?

A 0

B 2

C 1

D 3

💡 Hint

Check the 'Null Counts' column at step 4 in the execution_table.

At which step does the DataFrame reduce from 3 rows to 2 rows?

A Step 2

B Step 6

C Step 5

D Step 8

💡 Hint

Look at the 'DataFrame State' column showing row counts in execution_table.

If the DataFrame had no duplicate rows, what would the duplicate count be at step 8?

A 0

B 1

C 2

D 3

💡 Hint

Duplicate count is total rows minus unique rows after dropDuplicates, see execution_table step 8.

Concept Snapshot

Null and duplicate detection in Spark:
- Use isNull() with when() and count() to find nulls per column.
- Use dropDuplicates() to remove duplicate rows.
- Duplicate count = total rows - unique rows.
- Helps clean data before analysis.

Full Transcript

This visual execution shows how to detect null and duplicate values in a Spark DataFrame. We start by creating a DataFrame with some null and duplicate values. Then, we count nulls in each column using isNull and count functions. Next, we find duplicates by comparing the total row count with the count after removing duplicates using dropDuplicates. The difference gives the number of duplicate rows. This process helps identify data quality issues before analysis.

Practice

1. What does null detection in Apache Spark help you find in a dataset?

easy

A. Columns with only numeric data

B. Rows with duplicate values

C. Missing or empty values in columns

D. The total number of rows

Solution

1. Step 1: Understand null detection purpose

   Null detection identifies missing or empty values in dataset columns.

2. Step 2: Differentiate from duplicates

   Duplicate detection finds repeated rows, not missing values.

3. Final Answer:

   Missing or empty values in columns -> Option C

4. Quick Check:

   Null detection = Missing values [OK]

Hint: Null detection finds missing data, not duplicates [OK]

Common Mistakes:

• Confusing null detection with duplicate detection
• Thinking null means zero or empty string
• Assuming null detection counts rows

2. Which Apache Spark DataFrame method is used to find duplicate rows?

easy

A. count()

B. isNull()

C. fillna()

D. dropDuplicates()

Solution

1. Step 1: Identify method for duplicates

   The method dropDuplicates() removes duplicate rows from a DataFrame.

2. Step 2: Confirm other methods' purposes

   isNull() checks for nulls, fillna() fills nulls, count() counts rows.

3. Final Answer:

   dropDuplicates() -> Option D

4. Quick Check:

   Duplicates removed by dropDuplicates() [OK]

Hint: Use dropDuplicates() to find/remove duplicates [OK]

Common Mistakes:

• Using isNull() to find duplicates
• Confusing fillna() with duplicate removal
• Trying count() to detect duplicates

3. Given this Spark code snippet, what is the output?

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [(1, 'A'), (2, None), (1, 'A'), (3, 'C'), (None, 'D')]
df = spark.createDataFrame(data, ['id', 'value'])
null_count = df.filter(df['id'].isNull()).count()
duplicate_count = df.count() - df.dropDuplicates().count()
print(null_count, duplicate_count)

medium

A. 1 2

B. 1 1

C. 0 2

D. 2 1

Solution

1. Step 1: Count nulls in 'id'

   There is one row with id = None, so null_count = 1.

2. Step 2: Calculate duplicates

   Total rows = 5, dropDuplicates() removes one duplicate row (1, 'A'), so duplicate_count = 5 - 4 = 1.

3. Final Answer:

   1 1 -> Option B

4. Quick Check:

   Nulls = 1, Duplicates = 1 [OK]

Hint: Count nulls with filter, duplicates by count difference [OK]

Common Mistakes:

• Counting duplicates before removing nulls
• Miscounting duplicates as 2
• Ignoring None as null

4. What is wrong with this Spark code to detect duplicates?

df = spark.createDataFrame([(1, 'x'), (1, 'x'), (2, 'y')], ['id', 'val'])
duplicates = df.dropDuplicates().count()
print(duplicates)

medium

A. dropDuplicates() removes duplicates, so count shows unique rows, not duplicates

B. The code will raise a syntax error

C. dropDuplicates() counts duplicates instead of removing them

D. The DataFrame is empty, so count is zero

Solution

1. Step 1: Understand dropDuplicates() behavior

   dropDuplicates() removes duplicate rows, so count() returns unique row count.

2. Step 2: Identify mistake in duplicate detection

   Counting duplicates requires comparing total count with unique count, not just unique count alone.

3. Final Answer:

   dropDuplicates() removes duplicates, so count shows unique rows, not duplicates -> Option A

4. Quick Check:

   dropDuplicates() = unique rows, not duplicates count [OK]

Hint: Compare total count and dropDuplicates count to find duplicates [OK]

Common Mistakes:

• Assuming dropDuplicates() counts duplicates
• Expecting syntax error from correct code
• Ignoring difference between total and unique counts

5. You have a Spark DataFrame with columns 'user_id' and 'email'. You want to remove rows where 'email' is null and also remove duplicate rows based on 'user_id'. Which code snippet correctly does this?

hard

A. df.filter(df['email'].isNotNull()).dropDuplicates(['user_id'])

B. df.dropDuplicates(['user_id']).filter(df['email'].isNull())

C. df.filter(df['email'].isNull()).dropDuplicates(['email'])

D. df.dropDuplicates().filter(df['email'].isNotNull())

Solution

1. Step 1: Filter out rows with null emails

   Use filter(df['email'].isNotNull()) to keep only rows with email values.

2. Step 2: Remove duplicates by 'user_id'

   Use dropDuplicates(['user_id']) to remove rows with repeated user_id values.

3. Final Answer:

   df.filter(df['email'].isNotNull()).dropDuplicates(['user_id']) -> Option A

4. Quick Check:

   Filter null emails then drop duplicates by user_id [OK]

Hint: Filter nulls first, then drop duplicates by key column [OK]

Common Mistakes:

• Filtering null emails after dropping duplicates
• Dropping duplicates on wrong column
• Filtering for null emails instead of non-null