Select, filter, and where operations in Apache Spark - Mini Project: Build & Apply

Select, filter, and where operations

📖 Scenario: You work as a data analyst for a small online bookstore. You have a dataset of books with details like title, author, genre, and price. Your manager wants you to find books that are affordable and belong to a specific genre.

🎯 Goal: Build a Spark DataFrame with book data, set a price limit, filter books by genre and price using select, filter, and where operations, and display the filtered results.

📋 What You'll Learn

Create a Spark DataFrame with exact book data

Create a variable for the maximum price allowed

Use select to choose specific columns

Use filter or where to select books by genre and price

Print the final filtered DataFrame

💡 Why This Matters

🌍 Real World

Filtering and selecting data is a common task in data analysis to focus on relevant information, such as affordable books in a specific genre.

💼 Career

Data analysts and data scientists often use select, filter, and where operations in Spark to prepare and explore large datasets efficiently.

1

Create the books DataFrame

Create a Spark DataFrame called books_df with these exact entries: {'title': 'The Alchemist', 'author': 'Paulo Coelho', 'genre': 'Fiction', 'price': 10}, {'title': 'Deep Learning', 'author': 'Ian Goodfellow', 'genre': 'Education', 'price': 50}, {'title': 'Clean Code', 'author': 'Robert C. Martin', 'genre': 'Education', 'price': 40}, {'title': 'The Hobbit', 'author': 'J.R.R. Tolkien', 'genre': 'Fiction', 'price': 15}, {'title': 'Cooking 101', 'author': 'Jamie Oliver', 'genre': 'Cooking', 'price': 20}. Use Spark's createDataFrame method with a list of dictionaries.

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('BookStore').getOrCreate()
# Create the books_df DataFrame with the exact book entries
# Your code here

Hint

Use spark.createDataFrame with a list of dictionaries containing the exact book details.

2

Set the maximum price limit

Create a variable called max_price and set it to 20 to represent the maximum price allowed for books.

Apache Spark

from pyspark.sql import SparkSession

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

books_data = [
    {'title': 'The Alchemist', 'author': 'Paulo Coelho', 'genre': 'Fiction', 'price': 10},
    {'title': 'Deep Learning', 'author': 'Ian Goodfellow', 'genre': 'Education', 'price': 50},
    {'title': 'Clean Code', 'author': 'Robert C. Martin', 'genre': 'Education', 'price': 40},
    {'title': 'The Hobbit', 'author': 'J.R.R. Tolkien', 'genre': 'Fiction', 'price': 15},
    {'title': 'Cooking 101', 'author': 'Jamie Oliver', 'genre': 'Cooking', 'price': 20}
]

books_df = spark.createDataFrame(books_data)
# Create a variable max_price and set it to 20
# Your code here

Hint

Just create a variable named max_price and assign it the value 20.

3

Filter books by genre and price

Use select to choose the columns 'title', 'author', and 'price' from books_df. Then use filter or where to keep only books where genre is 'Fiction' and price is less than or equal to max_price. Save the result in a new DataFrame called filtered_books.

Apache Spark

from pyspark.sql import SparkSession

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

books_data = [
    {'title': 'The Alchemist', 'author': 'Paulo Coelho', 'genre': 'Fiction', 'price': 10},
    {'title': 'Deep Learning', 'author': 'Ian Goodfellow', 'genre': 'Education', 'price': 50},
    {'title': 'Clean Code', 'author': 'Robert C. Martin', 'genre': 'Education', 'price': 40},
    {'title': 'The Hobbit', 'author': 'J.R.R. Tolkien', 'genre': 'Fiction', 'price': 15},
    {'title': 'Cooking 101', 'author': 'Jamie Oliver', 'genre': 'Cooking', 'price': 20}
]

books_df = spark.createDataFrame(books_data)

max_price = 20
# Use select and filter or where to create filtered_books with only 'title', 'author', 'price' columns
# and where genre is 'Fiction' and price <= max_price
# Your code here

Hint

Use select('title', 'author', 'price') and then where with conditions joined by &.

4

Display the filtered books

Use print and filtered_books.show() to display the filtered books DataFrame.

Apache Spark

from pyspark.sql import SparkSession

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

books_data = [
    {'title': 'The Alchemist', 'author': 'Paulo Coelho', 'genre': 'Fiction', 'price': 10},
    {'title': 'Deep Learning', 'author': 'Ian Goodfellow', 'genre': 'Education', 'price': 50},
    {'title': 'Clean Code', 'author': 'Robert C. Martin', 'genre': 'Education', 'price': 40},
    {'title': 'The Hobbit', 'author': 'J.R.R. Tolkien', 'genre': 'Fiction', 'price': 15},
    {'title': 'Cooking 101', 'author': 'Jamie Oliver', 'genre': 'Cooking', 'price': 20}
]

books_df = spark.createDataFrame(books_data)

max_price = 20

filtered_books = books_df.select('title', 'author', 'price').where((books_df.genre == 'Fiction') & (books_df.price <= max_price))
# Print the filtered_books DataFrame
# Your code here

Hint

Use print('Filtered books:') and then filtered_books.show() to display the data.

Practice

1. What does the select operation do in Apache Spark DataFrame?

easy

A. It chooses specific columns from the DataFrame.

B. It filters rows based on a condition.

C. It sorts the DataFrame by a column.

D. It adds new columns to the DataFrame.

Solution

1. Step 1: Understand the purpose of select

   The select operation is used to pick specific columns from a DataFrame, reducing the data to only those columns.

2. Step 2: Differentiate from filtering rows

   Filtering rows is done by filter or where, not by select.

3. Final Answer:

   It chooses specific columns from the DataFrame. -> Option A

4. Quick Check:

   Select = Choose columns [OK]

Hint: Select picks columns, filter/where pick rows [OK]

Common Mistakes:

• Confusing select with filter or where
• Thinking select filters rows
• Assuming select adds columns

2. Which of the following is the correct syntax to filter rows where column age is greater than 30 in a Spark DataFrame df?

easy

A. df.where(df.age > 30)

B. df.filter('age > 30')

C. All of the above

D. df.filter(df.age > 30)

Solution

1. Step 1: Understand filter syntax options

   In Spark, filter can accept either a column expression like df.age > 30 or a string condition like 'age > 30'.

2. Step 2: Recognize where is similar to filter

   where is an alias for filter and accepts the same arguments.

3. Final Answer:

   All of the above -> Option C

4. Quick Check:

   filter and where accept column or string conditions [OK]

Hint: filter and where accept column or string conditions [OK]

Common Mistakes:

• Using wrong syntax like df.filter(age > 30) without df.
• Confusing filter with select syntax
• Thinking where is different from filter

3. Given the Spark DataFrame df with columns name and age, what will be the output of this code?

df.select('name').filter(df.age >= 25).show()

medium

A. Shows names of all rows where age is 25 or more.

B. Shows all columns for rows where age is 25 or more.

C. Shows names of all rows regardless of age.

D. Throws an error because of wrong syntax.

Solution

1. Step 1: Analyze select operation

   select('name') projects only the name column, resulting in a DataFrame schema of ['name'].

2. Step 2: Analyze filter condition

   filter(df.age >= 25) tries to use the age column, but age is unavailable after select, causing a column resolution error.

3. Final Answer:

   Throws an error because of wrong syntax. -> Option D

4. Quick Check:

   Filter on unselected column after select = error [OK]

Hint: Filter rows before selecting columns [OK]

Common Mistakes:

• Thinking filter applies before select due to lazy evaluation
• Expecting all columns to show
• Confusing syntax causing errors

4. Identify the error in this Spark code snippet:

df.filter(df['age'] > 20).select('name', 'age').where('age < 18').show()

medium

A. Using both filter and where is not allowed.

B. The filter and where conditions contradict each other.

C. select must come before filter or where.

D. The syntax for where is incorrect.

Solution

1. Step 1: Analyze filter condition

   filter(df['age'] > 20) keeps rows where age is greater than 20.

2. Step 2: Analyze where condition

   where('age < 18') keeps rows where age is less than 18.

3. Step 3: Identify contradiction

   Rows cannot satisfy both conditions simultaneously, so the result will be empty or logically inconsistent.

4. Final Answer:

   The filter and where conditions contradict each other. -> Option B

5. Quick Check:

   Contradictory filters = no matching rows [OK]

Hint: Check if filter and where conditions conflict [OK]

Common Mistakes:

• Thinking filter and where cannot be chained
• Assuming select must come first
• Ignoring logical contradictions in conditions

5. You have a Spark DataFrame df with columns city, temperature, and humidity. You want to select only city and temperature columns, but only for rows where temperature is above 30 and humidity is below 50. Which code snippet correctly does this?

hard

A. df.filter((df.temperature > 30) & (df.humidity < 50)).select('city', 'temperature')

B. df.select('city', 'temperature').where('temperature > 30 AND humidity < 50')

C. df.select('city', 'temperature').filter((df.temperature > 30) & (df.humidity < 50))

D. df.select('city', 'temperature').where('temperature > 30').filter('humidity < 50')

Solution

1. Step 1: Filter rows first based on both conditions

   We must first keep rows where temperature > 30 and humidity < 50. This is done by filter or where with both conditions combined.

2. Step 2: Select only the desired columns after filtering

   After filtering, select only city and temperature columns to reduce data.

3. Step 3: Check each option

   A: selects first then filters on temperature and humidity -> error (humidity unavailable).
   B: selects first then where string with both -> error (humidity unavailable).
   C: filters first then selects -> correct.
   D: selects first, where on temperature then filter on humidity -> error.

4. Final Answer:

   df.filter((df.temperature > 30) & (df.humidity < 50)).select('city', 'temperature') -> Option A

5. Quick Check:

   Filter rows first, then select columns [OK]

Hint: Filter rows before selecting columns with needed data [OK]

Common Mistakes:

• Selecting columns before filtering on columns not selected
• Splitting filter conditions incorrectly
• Using string conditions inconsistently