Select, filter, and where operations in Apache Spark

Introduction

We use select, filter, and where to pick specific columns and rows from data. This helps us focus on the important parts.

When you want to see only some columns from a big table.

When you want to find rows that meet certain conditions, like age > 30.

When cleaning data by removing unwanted rows.

When preparing data for analysis by selecting relevant information.

When combining data and you need to filter before joining.

Syntax

Apache Spark

df.select("column1", "column2")
df.filter(df["column1"] > value)
df.where("column1 > value")

select chooses columns.

filter and where do the same thing: pick rows by condition.

Examples

Selects only the 'name' and 'age' columns from the data.

Apache Spark

df.select("name", "age")

Filters rows where the 'age' column is greater than 25.

Apache Spark

df.filter(df["age"] > 25)

Filters rows where the 'salary' column is more than 50000 using a string condition.

Apache Spark

df.where("salary > 50000")

Sample Program

This program creates a small table of people with their age and salary. It then shows how to pick columns and filter rows using select, filter, and where.

Apache Spark

from pyspark.sql import SparkSession

# Start Spark session
spark = SparkSession.builder.appName("SelectFilterWhereExample").getOrCreate()

# Create sample data
data = [
    ("Alice", 30, 60000),
    ("Bob", 22, 40000),
    ("Charlie", 35, 70000),
    ("David", 28, 45000)
]

# Define columns
columns = ["name", "age", "salary"]

# Create DataFrame
df = spark.createDataFrame(data, columns)

# Select name and salary columns
selected_df = df.select("name", "salary")

# Filter rows where age is greater than 25
filtered_df = df.filter(df["age"] > 25)

# Use where to filter rows where salary is greater than 50000
where_df = df.where("salary > 50000")

# Show results
print("Selected columns (name, salary):")
selected_df.show()

print("Filtered rows (age > 25):")
filtered_df.show()

print("Where rows (salary > 50000):")
where_df.show()

# Stop Spark session
spark.stop()

Important Notes

filter and where do the same job; use whichever you find easier.

select only changes columns, it does not filter rows.

Conditions in filter can be written using column expressions or SQL strings in where.

Summary

Select picks columns you want to keep.

Filter and where pick rows that match a condition.

Use these to focus your data for easier analysis.

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