Select, filter, and where operations in Apache Spark - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of filter with multiple conditions

What is the output of this Apache Spark code snippet filtering a DataFrame?

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [(1, "apple", 10), (2, "banana", 5), (3, "carrot", 7), (4, "date", 10)]
df = spark.createDataFrame(data, ["id", "fruit", "quantity"])
filtered_df = df.filter((df.quantity == 10) & (df.fruit != "date"))
result = filtered_df.select("id", "fruit").collect()
print(result)

A []

B [Row(id=1, fruit='apple')]

C [Row(id=1, fruit='apple'), Row(id=4, fruit='date')]

D [Row(id=4, fruit='date')]

💡 Hint

Remember that filter uses AND logic with & and excludes rows where fruit is 'date'.

The filter keeps rows where quantity is 10 and fruit is not 'date'. Only the row with id=1 and fruit='apple' meets both conditions.

❓ data_output

intermediate

Number of rows after where condition

How many rows remain after applying this where condition on the DataFrame?

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [(10, "red"), (15, "blue"), (20, "red"), (25, "green"), (30, "blue")]
df = spark.createDataFrame(data, ["value", "color"])
filtered_df = df.where("value > 15 AND color = 'blue'")
count = filtered_df.count()
print(count)

A 3

B 2

C 1

D 0

💡 Hint

Check which rows have value greater than 15 and color exactly 'blue'.

Only the row with value 30 and color 'blue' meets both conditions, so count is 1.

🔧 Debug

advanced

Identify the error in this filter expression

What error does this Apache Spark code raise when run?

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [(1, 100), (2, 200)]
df = spark.createDataFrame(data, ["id", "score"])
filtered_df = df.filter(df.score >= 150)
filtered_df.show()

A SyntaxError: invalid syntax

B TypeError: '>' not supported between instances

C AnalysisException: cannot resolve 'score => 150' given input columns

D No error, outputs filtered rows

💡 Hint

Check the operator used for comparison in the filter condition.

The operator '=>' is invalid syntax in Python; the correct operator is '>='.

🚀 Application

advanced

Select and filter to find fruits with quantity less than average

Given a DataFrame of fruits and quantities, which code snippet correctly selects fruit names with quantity less than the average quantity?

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import avg
spark = SparkSession.builder.getOrCreate()
data = [("apple", 10), ("banana", 5), ("carrot", 7), ("date", 10)]
df = spark.createDataFrame(data, ["fruit", "quantity"])
avg_qty = df.select(avg("quantity")).collect()[0][0]

A df.filter(df.quantity < avg_qty).select("fruit").show()

B df.where("quantity < avg_qty").select("fruit").show()

C df.filter(df.quantity < df.avg_qty).select("fruit").show()

D df.filter(df["quantity"] < avg_qty).select("fruit").show()

💡 Hint

Remember avg_qty is a Python variable, not a DataFrame column.

Option A correctly uses the Python variable avg_qty in the filter condition. Option A uses a string expression which does not recognize avg_qty. Option A is valid syntax but less common; however, it works the same as C. Option A incorrectly tries to access avg_qty as a DataFrame column.

🧠 Conceptual

expert

Understanding lazy evaluation in filter and select

Which statement best describes how Apache Spark handles filter and select operations on a DataFrame?

A Spark executes select operations immediately but delays filter operations.

B Spark immediately executes filter and select operations and returns results.

C Spark executes filter operations immediately but delays select operations.

D Spark builds a query plan and delays execution until an action is called.

💡 Hint

Think about when Spark actually runs computations on data.

Spark uses lazy evaluation, meaning it builds a plan for filter and select but only runs when an action like show() or collect() is called.

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