Select, filter, and where operations in Apache Spark - Step-by-Step Execution

Concept Flow - Select, filter, and where operations

Start with DataFrame

↓

Select columns

→Filtered DataFrame

↓

Apply filter/where condition

↓

Result DataFrame with rows matching condition

↓

End

Start with a DataFrame, select columns to keep, then filter rows using conditions to get the final result.

Execution Sample

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [(1, 'Alice', 20), (2, 'Bob', 30), (3, 'Cathy', 25)]
df = spark.createDataFrame(data, ['id', 'name', 'age'])
df.select('name', 'age').filter(df.age > 20).show()

Create a DataFrame, select 'name' and 'age' columns, then filter rows where age is greater than 20.

Execution Table

Step	Action	DataFrame State	Output
1	Create DataFrame	[{id:1,name:'Alice',age:20}, {id:2,name:'Bob',age:30}, {id:3,name:'Cathy',age:25}]	Full DataFrame with 3 rows
2	Select columns 'name' and 'age'	[{name:'Alice',age:20}, {name:'Bob',age:30}, {name:'Cathy',age:25}]	DataFrame with 2 columns, 3 rows
3	Filter rows where age > 20	[{name:'Bob',age:30}, {name:'Cathy',age:25}]	Filtered DataFrame with 2 rows
4	Show result	Print rows	name: Bob, age: 30 name: Cathy, age: 25
5	End	No further action	Execution complete

💡 Filtering stops when all rows are checked; only rows with age > 20 remain.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
df	None	[{id:1,name:'Alice',age:20}, {id:2,name:'Bob',age:30}, {id:3,name:'Cathy',age:25}]	[{id:1,name:'Alice',age:20}, {id:2,name:'Bob',age:30}, {id:3,name:'Cathy',age:25}]	[{name:'Bob',age:30}, {name:'Cathy',age:25}]	[{name:'Bob',age:30}, {name:'Cathy',age:25}]

Key Moments - 3 Insights

Why does the filter keep only rows where age is greater than 20?

Because the filter condition df.age > 20 is applied to each row, only rows satisfying this condition remain, as shown in execution_table step 3.

What happens if we select columns after filtering instead of before?

Selecting columns after filtering still works but changes the order of operations; the final output is the same but intermediate DataFrame states differ, as seen in variable_tracker.

Are 'filter' and 'where' different in Spark DataFrame?

No, 'filter' and 'where' are synonyms in Spark and behave the same way, both applying row conditions as shown in the code example.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, how many rows remain after filtering?

A 1 row

B 2 rows

C 3 rows

D 0 rows

💡 Hint

Check the 'DataFrame State' column at step 3 in execution_table.

At which step are columns 'name' and 'age' selected?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Action' column in execution_table for selecting columns.

If the filter condition changed to df.age > 25, how many rows would remain after filtering?

A 1 row

B 2 rows

C 3 rows

D 0 rows

💡 Hint

Refer to variable_tracker and consider which rows have age greater than 25.

Concept Snapshot

Select, filter, and where operations in Spark:
- Use select() to choose columns.
- Use filter() or where() to keep rows matching a condition.
- Both filter and where do the same filtering.
- Operations return new DataFrames, original unchanged.
- Chain select and filter for precise data extraction.

Full Transcript

This visual execution shows how to use select, filter, and where operations in Apache Spark DataFrames. We start by creating a DataFrame with three rows and three columns: id, name, and age. Then we select only the 'name' and 'age' columns, reducing the DataFrame to two columns but keeping all rows. Next, we apply a filter condition to keep only rows where age is greater than 20. This removes the row with age 20, leaving two rows. Finally, we show the result, which prints the filtered rows. The variable tracker shows how the DataFrame changes after each step. Key moments clarify common confusions about filtering and the equivalence of filter and where. The quiz tests understanding of row counts after filtering and the order of operations. The snapshot summarizes the key points for quick reference.

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