Null and duplicate detection in Apache Spark - Mini Project: Build & Apply

Null and Duplicate Detection

📖 Scenario: You work as a data analyst for an online store. You receive a dataset of customer orders. Some orders have missing information or are repeated by mistake. You want to find these problems before analyzing the data.

🎯 Goal: You will create a Spark DataFrame with order data, set a threshold for missing values, find rows with nulls, detect duplicate rows, and print the results.

📋 What You'll Learn

Create a Spark DataFrame with given order data

Create a variable for the threshold of allowed null values

Use Spark functions to find rows with null values

Use Spark functions to find duplicate rows

Print the rows with nulls and duplicates

💡 Why This Matters

🌍 Real World

Detecting missing and repeated data is important before analyzing or modeling data. It helps keep data clean and reliable.

💼 Career

Data scientists and analysts often clean data by finding and handling nulls and duplicates to improve data quality.

1

Create the Spark DataFrame

Create a Spark DataFrame called orders with these exact rows and columns: order_id, customer_id, product, quantity. Use these rows: (1, 101, 'Book', 2), (2, 102, 'Pen', null), (3, 103, 'Notebook', 1), (4, 101, 'Book', 2), (5, null, 'Pencil', 3).

Apache Spark

# Create the Spark DataFrame called orders with the given data
# Your code here

Hint

Use spark.createDataFrame with a schema to create the DataFrame.

2

Set the null threshold

Create an integer variable called null_threshold and set it to 1. This will be the maximum allowed number of null values per row.

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, IntegerType, StringType

spark = SparkSession.builder.appName('NullDuplicateDetection').getOrCreate()

schema = StructType([
    StructField('order_id', IntegerType(), True),
    StructField('customer_id', IntegerType(), True),
    StructField('product', StringType(), True),
    StructField('quantity', IntegerType(), True)
])

data = [
    (1, 101, 'Book', 2),
    (2, 102, 'Pen', None),
    (3, 103, 'Notebook', 1),
    (4, 101, 'Book', 2),
    (5, None, 'Pencil', 3)
]

orders = spark.createDataFrame(data, schema)

# Create null_threshold variable and set it to 1
# Your code here

Hint

Just create a variable named null_threshold and assign it the value 1.

3

Find rows with null values and duplicates

Create a DataFrame called null_rows that contains rows from orders where the number of null values is greater than null_threshold. Then create a DataFrame called duplicate_rows that contains rows from orders that are duplicates (appear more than once). Use Spark functions to do this.

Apache Spark

from pyspark.sql import functions as F

# Create null_rows DataFrame with rows having more than null_threshold nulls
# Create duplicate_rows DataFrame with duplicate rows
# Your code here

Hint

Use isNull() and cast('int') to count nulls per row. Use groupBy and count() to find duplicates.

4

Print the rows with nulls and duplicates

Print the rows in null_rows and duplicate_rows DataFrames using show().

Apache Spark

# Print the rows with null values
# Print the duplicate rows
# Your code here

Hint

Use print() and show() to display the DataFrames.

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