Null and duplicate detection in Apache Spark - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Detecting Null Values Count in Spark DataFrame

What is the output of this Spark code snippet that counts null values in each column?

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum

spark = SparkSession.builder.getOrCreate()
data = [(1, None), (2, 'a'), (None, 'b'), (4, None)]
df = spark.createDataFrame(data, ['id', 'value'])

null_counts = df.select([sum(col(c).isNull().cast('int')).alias(c) for c in df.columns])
null_counts.show()

A

+---+-----+
| id|value|
+---+-----+
|  1|    2|
+---+-----+

B

+---+-----+
| id|value|
+---+-----+
|  2|    1|
+---+-----+

C

+---+-----+
| id|value|
+---+-----+
|  1|    1|
+---+-----+

D

+---+-----+
| id|value|
+---+-----+
|  0|    2|
+---+-----+

💡 Hint

Remember that isNull() returns a boolean, which we cast to int to sum nulls.

The code counts nulls in each column by checking isNull(), casting True to 1 and False to 0, then summing. The 'id' column has one null, 'value' has two nulls.

❓ data_output

intermediate

Number of Duplicate Rows in Spark DataFrame

Given this Spark DataFrame, how many duplicate rows does it contain?

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()
data = [(1, 'a'), (2, 'b'), (1, 'a'), (3, 'c'), (2, 'b'), (4, 'd')]
df = spark.createDataFrame(data, ['id', 'value'])
duplicates_count = df.count() - df.dropDuplicates().count()
print(duplicates_count)

A 2

B 3

C 1

D 4

💡 Hint

Duplicates are rows that appear more than once.

The DataFrame has 6 rows. After dropping duplicates, 4 unique rows remain. So duplicates count is 6 - 4 = 2.

🔧 Debug

advanced

Identify the Error in Null Filtering Code

What error does this Spark code raise when trying to filter rows with nulls in 'age' column?

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()
data = [(1, 25), (2, None), (3, 30)]
df = spark.createDataFrame(data, ['id', 'age'])

filtered = df.filter(df['age'] == None)
filtered.show()

A No error, but filtered DataFrame is empty

B SyntaxError

C TypeError

D The filter returns no rows because '==' does not detect nulls

💡 Hint

In Spark, '==' does not work to check null values.

Using '==' to compare with None returns no rows because null comparisons require 'isNull()' method in Spark.

🚀 Application

advanced

Remove Duplicate Rows Based on Subset of Columns

Which code snippet correctly removes duplicates based only on the 'email' column in a Spark DataFrame?

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()
data = [(1, '[e-mail]'), (2, '[e-mail]'), (3, '[e-mail]'), (4, '[e-mail]')]
df = spark.createDataFrame(data, ['id', 'email'])

A df.dropDuplicates(['id']).show()

B df.dropDuplicates(['email']).show()

C df.dropDuplicates().show()

D df.dropDuplicates('email').show()

💡 Hint

dropDuplicates expects a list of column names to consider for duplicates.

Option B correctly passes a list with 'email' to dropDuplicates, removing rows with duplicate emails.

🧠 Conceptual

expert

Understanding Null Handling in Spark Aggregations

When using Spark's aggregation functions like sum() on a column with null values, what is the behavior?

A Null values are treated as zero in the aggregation.

B Null values cause the aggregation to return null.

C Null values are ignored in the aggregation, sum returns the total of non-null values.

D Aggregation raises an error if null values exist.

💡 Hint

Think about how SQL aggregates handle nulls.

Spark aggregation functions ignore nulls by default, so sum() adds only non-null values.

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