Adding and renaming columns in Apache Spark - Mini Project: Build & Apply

Adding and renaming columns

📖 Scenario: You work in a small bakery that keeps track of daily sales data in a Spark DataFrame. You want to add new information and rename columns to make the data easier to understand.

🎯 Goal: Learn how to add a new column to a Spark DataFrame and rename an existing column.

📋 What You'll Learn

Create a Spark DataFrame with specific sales data

Add a new column with calculated values

Rename an existing column

Display the final DataFrame

💡 Why This Matters

🌍 Real World

Adding and renaming columns is common when cleaning and preparing data for reports or analysis in business settings.

💼 Career

Data analysts and data scientists often need to modify DataFrames to add calculated fields and improve column names for better understanding.

1

Create the initial sales DataFrame

Create a Spark DataFrame called sales_df with these exact columns and data: date as strings and items_sold as integers. The rows are: ('2024-04-01', 30), ('2024-04-02', 45), ('2024-04-03', 50).

Apache Spark

# Your code here to create sales_df with the given data

Hint

Use spark.createDataFrame with a list of tuples and specify the column names.

2

Add a new column for revenue

Create a variable called price_per_item and set it to 2.5. This is the price of one item in dollars.

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('BakerySales').getOrCreate()
data = [('2024-04-01', 30), ('2024-04-02', 45), ('2024-04-03', 50)]
sales_df = spark.createDataFrame(data, ['date', 'items_sold'])

# Create price_per_item variable and set it to 2.5
# Your code here

Hint

Just create a variable named price_per_item and assign it the value 2.5.

3

Add revenue column using price_per_item

Add a new column called revenue to sales_df. Calculate revenue by multiplying items_sold by price_per_item. Use the withColumn method and lit function from pyspark.sql.functions.

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import lit

spark = SparkSession.builder.appName('BakerySales').getOrCreate()
data = [('2024-04-01', 30), ('2024-04-02', 45), ('2024-04-03', 50)]
sales_df = spark.createDataFrame(data, ['date', 'items_sold'])

price_per_item = 2.5

# Add revenue column to sales_df
# Your code here

Hint

Use withColumn('revenue', sales_df['items_sold'] * lit(price_per_item)) to add the new column.

4

Rename the items_sold column and show the DataFrame

Rename the column items_sold to quantity_sold using the withColumnRenamed method on sales_df. Then print the DataFrame using show().

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import lit

spark = SparkSession.builder.appName('BakerySales').getOrCreate()
data = [('2024-04-01', 30), ('2024-04-02', 45), ('2024-04-03', 50)]
sales_df = spark.createDataFrame(data, ['date', 'items_sold'])

price_per_item = 2.5

sales_df = sales_df.withColumn('revenue', sales_df['items_sold'] * lit(price_per_item))

# Rename items_sold to quantity_sold and show the DataFrame
# Your code here

Hint

Use withColumnRenamed('items_sold', 'quantity_sold') and then show() to display the DataFrame.

Practice

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

easy

A. Adds a new column or updates an existing column in the DataFrame

B. Deletes a column from the DataFrame

C. Renames a column in the DataFrame

D. Filters rows based on a condition

Solution

1. Step 1: Understand the purpose of withColumn

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

2. Step 2: Compare with other methods

   Unlike withColumnRenamed which renames columns, withColumn changes or adds columns with new values or expressions.

3. Final Answer:

   Adds a new column or updates an existing column in the DataFrame -> Option A

4. Quick Check:

   withColumn = Add or update column [OK]

Hint: Use withColumn to add or change columns fast [OK]

Common Mistakes:

• Confusing withColumn with withColumnRenamed
• Thinking it deletes columns
• Assuming it filters rows

2. Which of the following is the correct syntax to rename a column named oldName to newName in a Spark DataFrame df?

easy

A. df.rename("oldName", "newName")

B. df.withColumn("oldName", "newName")

C. df.renameColumn("oldName", "newName")

D. df.withColumnRenamed("oldName", "newName")

Solution

1. Step 1: Identify the correct method for renaming

   The method to rename a column in Spark DataFrame is withColumnRenamed.

2. Step 2: Check syntax correctness

   The syntax is df.withColumnRenamed("oldName", "newName"). Other options are invalid methods or misuse of withColumn.

3. Final Answer:

   df.withColumnRenamed("oldName", "newName") -> Option D

4. Quick Check:

   Rename column = withColumnRenamed [OK]

Hint: Rename columns only with withColumnRenamed method [OK]

Common Mistakes:

• Using withColumn instead of withColumnRenamed
• Trying non-existent methods like renameColumn
• Passing arguments in wrong order

3. Given the following Spark code:

from pyspark.sql.functions import lit

df = spark.createDataFrame([(1, "Alice")], ["id", "name"])
df2 = df.withColumn("age", lit(30)).withColumnRenamed("name", "first_name")
df2.show()

What will be the output?

medium

A. +---+----------+---+
| id|first_name|age|
+---+----------+---+
| 1| Alice| 30|
+---+----------+---+

B. +---+-----+---+
| id| name|age|
+---+-----+---+
| 1|Alice| 30|
+---+-----+---+

C. +---+----------+
| id|first_name|
+---+----------+
| 1| Alice|
+---+----------+

D. Error: withColumnRenamed cannot be chained after withColumn

Solution

1. Step 1: Add new column 'age' with value 30

   The withColumn("age", lit(30)) adds a new column 'age' with constant value 30.

2. Step 2: Rename column 'name' to 'first_name'

   The withColumnRenamed("name", "first_name") renames the 'name' column to 'first_name'.

3. Step 3: Show the resulting DataFrame

   The DataFrame now has columns: 'id', 'first_name', and 'age' with one row (1, Alice, 30).

4. Final Answer:

   +---+----------+---+ | id|first_name|age| +---+----------+---+ | 1| Alice| 30| +---+----------+---+ -> Option A

5. Quick Check:

   withColumn + withColumnRenamed = added column + renamed column [OK]

Hint: Chain withColumn then withColumnRenamed to add and rename [OK]

Common Mistakes:

• Assuming withColumnRenamed cannot be chained
• Forgetting to import lit for constant values
• Expecting original column names unchanged

4. Consider this code snippet:

df = spark.createDataFrame([(1, "John")], ["id", "name"])
df2 = df.withColumnRenamed("Name", "first_name")
df2.show()

What is the issue and how to fix it?

medium

A. withColumnRenamed cannot rename columns; use withColumn instead

B. Column 'Name' does not exist; use correct case 'name' in withColumnRenamed

C. Syntax error: missing parentheses in withColumnRenamed

D. No issue; code runs correctly and renames column

Solution

1. Step 1: Check column names in DataFrame

   The DataFrame has columns 'id' and 'name' (lowercase). The code tries to rename 'Name' (uppercase N) which does not exist.

2. Step 2: Fix the column name case

   Change withColumnRenamed("Name", "first_name") to withColumnRenamed("name", "first_name") to match exact column name.

3. Final Answer:

   Column 'Name' does not exist; use correct case 'name' in withColumnRenamed -> Option B

4. Quick Check:

   Column names are case-sensitive in Spark [OK]

Hint: Match exact column name case when renaming [OK]

Common Mistakes:

• Ignoring case sensitivity of column names
• Using withColumn instead of withColumnRenamed to rename
• Assuming code runs without error despite wrong column name

5. You have a DataFrame df with columns ['id', 'score']. You want to add a new column grade based on score: if score ≥ 90 then 'A', else 'B'. Then rename score to exam_score. Which code correctly does this?

hard

A. df2 = df.withColumn('grade', when(df.score >= 90, 'A').otherwise('B')).renameColumn('score', 'exam_score')

B. df2 = df.withColumnRenamed('score', 'exam_score').withColumn('grade', when(df.score >= 90, 'A').otherwise('B'))

C. from pyspark.sql.functions import when

df2 = df.withColumn('grade', when(df.score >= 90, 'A').otherwise('B')).withColumnRenamed('score', 'exam_score')

D. df2 = df.withColumn('grade', when(df.score > 90, 'A').otherwise('B')).withColumnRenamed('score', 'exam_score')

Solution

1. Step 1: Add 'grade' column with condition on 'score'

   Use when to assign 'A' if score ≥ 90, else 'B'. This is done with withColumn('grade', when(df.score >= 90, 'A').otherwise('B')).

2. Step 2: Rename 'score' column to 'exam_score'

   Use withColumnRenamed('score', 'exam_score') to rename the column after adding 'grade'. The order matters to keep references correct.

3. Step 3: Check other options

   df2 = df.withColumnRenamed('score', 'exam_score').withColumn('grade', when(df.score >= 90, 'A').otherwise('B')) uses old column name after renaming, causing error. df2 = df.withColumn('grade', when(df.score >= 90, 'A').otherwise('B')).renameColumn('score', 'exam_score') uses non-existent renameColumn. df2 = df.withColumn('grade', when(df.score > 90, 'A').otherwise('B')).withColumnRenamed('score', 'exam_score') uses > 90 instead of ≥ 90, which changes logic.

4. Final Answer:

   from pyspark.sql.functions import when df2 = df.withColumn('grade', when(df.score >= 90, 'A').otherwise('B')).withColumnRenamed('score', 'exam_score') -> Option C

5. Quick Check:

   Add column with when, then rename column [OK]

Hint: Add conditional column first, then rename original column [OK]

Common Mistakes:

• Renaming column before adding new column using old name
• Using wrong comparison operator (> instead of >=)
• Using non-existent renameColumn method