Null and duplicate detection in Apache Spark - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of detecting null values in a dataset?

Detecting null values helps identify missing or incomplete data, which can affect analysis and model accuracy. Handling nulls properly ensures cleaner, more reliable results.

beginner

How do you check for null values in a Spark DataFrame?

You can use the filter or where method with isNull() on a column, for example: df.filter(df['column'].isNull()).show().

intermediate

What is the difference between dropDuplicates() and distinct() in Spark?

dropDuplicates() removes duplicate rows based on specified columns, while distinct() removes duplicate rows considering all columns.

beginner

Why is it important to detect duplicate rows in data?

Duplicate rows can bias analysis and models by over-representing some data points. Removing duplicates ensures fair and accurate insights.

intermediate

How can you count the number of nulls in each column of a Spark DataFrame?

Use aggregation with sum and when functions: df.select([F.sum(F.when(F.col(c).isNull(),1).otherwise(0)).alias(c) for c in df.columns]).show().

Which Spark function helps to find rows with null values in a column?

A isNull()

B isEmpty()

C isNaN()

D isDuplicate()

The isNull() function checks if a column value is null.

What does dropDuplicates(['col1', 'col2']) do in Spark?

A Removes duplicate rows based on col1 and col2 values

B Removes all duplicates in the DataFrame

C Removes rows with nulls in col1 or col2

D Removes rows where col1 equals col2

It removes rows that have the same values in both col1 and col2.

Which method removes duplicate rows considering all columns in a Spark DataFrame?

A dropDuplicates()

B distinct()

C dropNulls()

D filterDuplicates()

distinct() removes duplicate rows based on all columns.

Why should you handle null values before analysis?

A Nulls are always replaced with zeros automatically

B Nulls improve model accuracy

C Nulls increase dataset size

D Nulls can cause errors or bias in results

Null values can cause errors or bias if not handled properly.

How can you count nulls in each column of a Spark DataFrame?

A Using <code>count()</code>

B Using <code>dropDuplicates()</code>

C Using <code>sum(when(col.isNull(),1).otherwise(0))</code>

D Using <code>filter(col.isNotNull())</code>

This aggregation counts nulls by summing 1 for nulls and 0 otherwise.

Explain how to detect and handle null values in a Spark DataFrame.

Think about checking, counting, and then cleaning nulls.

Describe the difference between removing duplicates with dropDuplicates() and distinct() in Spark.

Focus on which columns each method considers.

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