Select, filter, and where operations in Apache Spark - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the select() operation do in Apache Spark DataFrame?

The select() operation chooses specific columns from a DataFrame, like picking certain ingredients from a recipe.

beginner

How do filter() and where() operations differ in Apache Spark?

They do the same thing: keep only rows that meet a condition. filter() and where() are just two names for the same operation.

beginner

Why use filter() or where() in data analysis?

To focus on rows that matter, like finding all customers from a certain city or sales above a number.

intermediate

Show a simple example of using select() and filter() together.

Example: df.select('name', 'age').filter(df.age > 30) picks only the 'name' and 'age' columns and keeps rows where age is over 30.

intermediate

Can you chain multiple filter() or where() conditions?

Yes! You can chain them or combine conditions with & (and), | (or) to filter rows with multiple rules.

What does df.select('col1', 'col2') do?

A Selects only columns 'col1' and 'col2' from the DataFrame

B Filters rows where 'col1' and 'col2' are true

C Deletes columns 'col1' and 'col2'

D Sorts the DataFrame by 'col1' and 'col2'

select() picks columns, not rows or sorting.

Which operation keeps rows where a condition is true?

A groupBy()

B select()

C filter()

D join()

filter() keeps rows matching a condition.

Are filter() and where() different in Spark?

A No, they are the same

B Yes, they do different things

C Only <code>filter()</code> works on DataFrames

D Only <code>where()</code> works on DataFrames

Both do the same filtering job.

How to filter rows where age is greater than 25?

A df.filter(df.age &lt; 25)

B df.filter(df.age &gt; 25)

C df.select(df.age &gt; 25)

D df.where(df.age == 25)

Use filter() or where() with the condition.

What happens if you chain select() and filter()?

A It sorts the DataFrame

B You filter rows first, then select columns

C It causes an error

D You select columns first, then keep rows matching filter

Operations run in order: select columns, then filter rows.

Explain how to use select(), filter(), and where() in Apache Spark DataFrames with simple examples.

Think about picking columns like choosing ingredients and filtering rows like picking fruits that are ripe.

Describe the difference and similarity between filter() and where() in Spark.

They are like two words for the same action.

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