Column expressions and functions in Apache Spark - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a column expression in Apache Spark?

A column expression is a way to refer to a column in a DataFrame to perform operations like selecting, filtering, or transforming data.

beginner

How do you add two columns in a Spark DataFrame?

You can add two columns using the '+' operator on column expressions, for example: df.withColumn('sum', df['col1'] + df['col2']).

beginner

What does the function 'lit()' do in Spark column expressions?

The 'lit()' function creates a column of a literal (constant) value, which can be used in expressions with other columns.

intermediate

Explain the use of 'when' and 'otherwise' functions in Spark.

'when' allows conditional expressions on columns, similar to IF statements. 'otherwise' defines the value if the condition is false.

intermediate

How can you chain multiple column functions in Spark?

You can chain functions by applying one after another on column expressions, for example: df.withColumn('new', col('a').cast('int').alias('new')).

Which function creates a constant column in Spark?

A lit()

B col()

C when()

D select()

The lit() function creates a column with a constant value.

How do you refer to a column named 'age' in Spark DataFrame?

A lit('age')

B when('age')

C df['age']

D select('age')

df['age'] or col('age') refers to the column named 'age'.

What does the 'when' function do in Spark?

A Selects columns

B Adds two columns

C Creates a literal value

D Creates a conditional column expression

The when function is used for conditional logic in column expressions.

Which operator is used to add two columns in Spark?

A -

B +

C *

D /

The '+' operator adds two column expressions.

How do you handle the 'else' part of a conditional expression in Spark?

A using otherwise()

B using else()

C using elsewhen()

D using lit()

The otherwise() function defines the value if the when condition is false.

Describe how to create a new column in a Spark DataFrame using column expressions and functions.

Think about how you can combine columns and constants to make a new column.

Explain how conditional logic is implemented in Spark column expressions.

Consider how you choose values based on conditions in a DataFrame.

Practice

1. What does the withColumn function do in Apache Spark DataFrame?

easy

A. It adds a new column or replaces an existing column with a new value.

B. It deletes a column from the DataFrame.

C. It filters rows based on a condition.

D. It groups data by a column.

Solution

1. Step 1: Understand the purpose of withColumn

   The withColumn function is used to add a new column or replace an existing one in a DataFrame.

2. Step 2: Compare with other functions

   Deleting columns is done by drop, filtering by filter, and grouping by groupBy. So these are not correct.

3. Final Answer:

   It adds a new column or replaces an existing column with a new value. -> Option A

4. Quick Check:

   withColumn = add/replace column [OK]

Hint: Remember: withColumn adds or changes columns fast [OK]

Common Mistakes:

• Confusing withColumn with drop or filter
• Thinking it deletes columns
• Mixing it with groupBy

2. Which of the following is the correct syntax to select a column named age from a DataFrame df using Spark's column expression?

easy

A. df.select("age")

B. df.select(age)

C. df.select(df.age())

D. df.select(`age`)

Solution

1. Step 1: Understand select syntax

   The select method expects column names as strings or Column objects. Using double quotes around column names is the standard correct syntax.

2. Step 2: Check each option

   df.select("age") (A) uses a valid string literal.
   df.select(age) (B) references undefined variable age - error.
   df.select(df.age()) (C) tries to call df.age as a function - Column not callable.
   df.select(`age`) (D) uses invalid backtick syntax.

3. Step 3: Identify the correct syntax

   Only df.select("age") executes without error and selects the column correctly.

4. Final Answer:

   df.select("age") -> Option A

5. Quick Check:

   Column names as strings in select = correct [OK]

Hint: Use quotes around column names in select [OK]

Common Mistakes:

• Using variable names without quotes
• Confusing Column objects with strings
• Missing quotes causing errors

3. Given the following Spark code, what will be the output of df.show()?

from pyspark.sql import SparkSession
from pyspark.sql.functions import col

spark = SparkSession.builder.getOrCreate()
data = [(1, 10), (2, 20), (3, 30)]
df = spark.createDataFrame(data, ['id', 'value'])
df = df.withColumn('double_value', col('value') * 2)
df.show()

medium

A. +---+-----+------------+
|id |value|double_value|
+---+-----+------------+
|1 |10 |10 |
|2 |20 |20 |
|3 |30 |30 |
+---+-----+------------+

B. Error: col function not found

C. +---+-----+------------+
|id |value|double_value|
+---+-----+------------+
|1 |10 |20 |
|2 |20 |40 |
|3 |30 |60 |
+---+-----+------------+

D. +---+-----+
|id |value|
+---+-----+
|1 |10 |
|2 |20 |
|3 |30 |
+---+-----+

Solution

1. Step 1: Understand the data and transformation

   The DataFrame starts with columns 'id' and 'value' with values (1,10), (2,20), (3,30). The withColumn adds 'double_value' by multiplying 'value' by 2.

2. Step 2: Calculate the new column values

   For each row, double_value = value * 2, so 10*2=20, 20*2=40, 30*2=60.

3. Final Answer:

   The DataFrame will show three columns with double_value as twice value: -> Option C

4. Quick Check:

   double_value = value * 2 [OK]

Hint: Multiply column with col('name') * number [OK]

Common Mistakes:

• Forgetting to import col function
• Using wrong column name
• Expecting original DataFrame without new column

4. Identify the error in this Spark code snippet:

from pyspark.sql.functions import col

df = spark.createDataFrame([(1, 'Alice'), (2, 'Bob')], ['id', 'name'])
df = df.withColumn('name_length', length(col('name')))
df.show()

medium

A. Syntax error in withColumn usage

B. Incorrect column name 'name'

C. DataFrame creation syntax is wrong

D. Missing import for length function

Solution

1. Step 1: Check imports for used functions

   The code uses length function but only imports col. The length function must be imported from pyspark.sql.functions.

2. Step 2: Verify other parts

   Column name 'name' exists, syntax for withColumn is correct, and DataFrame creation is valid.

3. Final Answer:

   Missing import for length function -> Option D

4. Quick Check:

   Import all functions you use [OK]

Hint: Always import functions like length explicitly [OK]

Common Mistakes:

• Forgetting to import needed functions
• Assuming all functions are auto-imported
• Typos in column names

5. You have a DataFrame df with columns id, score1, and score2. You want to create a new column max_score that contains the maximum value between score1 and score2 for each row. Which code snippet correctly achieves this?

hard

A. df = df.withColumn('max_score', max(col('score1'), col('score2')))

B. from pyspark.sql.functions import greatest

df = df.withColumn('max_score', greatest(col('score1'), col('score2')))

C. df = df.withColumn('max_score', col('score1') if col('score1') > col('score2') else col('score2'))

D. df = df.withColumn('max_score', col('score1').max(col('score2')))

Solution

1. Step 1: Identify the correct function for max between columns

   Spark provides the greatest function to get the maximum value among columns for each row.

2. Step 2: Check each option

   from pyspark.sql.functions import greatest df = df.withColumn('max_score', greatest(col('score1'), col('score2'))) uses greatest correctly with imported function. df = df.withColumn('max_score', max(col('score1'), col('score2'))) uses max which is an aggregate function, not for row-wise max. df = df.withColumn('max_score', col('score1') if col('score1') > col('score2') else col('score2')) tries Python if-else which does not work on columns. df = df.withColumn('max_score', col('score1').max(col('score2'))) uses invalid syntax.

3. Final Answer:

   Use greatest function with columns -> Option B

4. Quick Check:

   greatest(col1, col2) = max per row [OK]

Hint: Use greatest() for max of columns per row [OK]

Common Mistakes:

• Using max() instead of greatest() for columns
• Trying Python if-else on columns
• Wrong method chaining syntax