GroupBy and aggregations in Apache Spark - Mini Project: Build & Apply

GroupBy and aggregations

📖 Scenario: You work at a small online store. You have a list of sales records with product names and quantities sold. You want to find out how many units of each product were sold in total.

🎯 Goal: Build a Spark DataFrame from sales data, then group the data by product name and calculate the total quantity sold for each product.

📋 What You'll Learn

Create a Spark DataFrame with columns product and quantity using the exact data provided.

Create a variable called grouped_data that groups the DataFrame by product.

Use the agg function with sum aggregation on the quantity column.

Rename the aggregated column to total_quantity.

Print the resulting DataFrame using show().

💡 Why This Matters

🌍 Real World

Grouping and aggregating data is common in sales analysis, inventory management, and reporting to understand totals and summaries.

💼 Career

Data analysts and data scientists use groupBy and aggregation to summarize large datasets and extract meaningful insights for business decisions.

1

Create the sales DataFrame

Create a Spark DataFrame called sales_df with columns product and quantity using this exact data: [('apple', 10), ('banana', 5), ('apple', 7), ('banana', 3), ('orange', 8)].

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('GroupByExample').getOrCreate()
# Create the sales_df DataFrame with the exact data
# Your code here

Hint

Use spark.createDataFrame() with the list of tuples and specify the column names as ['product', 'quantity'].

2

Group the data by product

Create a variable called grouped_data that groups sales_df by the product column using the groupBy method.

Apache Spark

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('GroupByExample').getOrCreate()
data = [('apple', 10), ('banana', 5), ('apple', 7), ('banana', 3), ('orange', 8)]
sales_df = spark.createDataFrame(data, ['product', 'quantity'])
# Group sales_df by product and assign to grouped_data
# Your code here

Hint

Use sales_df.groupBy('product') to group the data by product.

3

Aggregate the total quantity sold

Use the agg function on grouped_data to calculate the sum of the quantity column. Rename the aggregated column to total_quantity and assign the result back to grouped_data.

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import sum

spark = SparkSession.builder.appName('GroupByExample').getOrCreate()
data = [('apple', 10), ('banana', 5), ('apple', 7), ('banana', 3), ('orange', 8)]
sales_df = spark.createDataFrame(data, ['product', 'quantity'])
grouped_data = sales_df.groupBy('product')
# Aggregate sum of quantity and rename to total_quantity
# Your code here

Hint

Use agg(sum('quantity').alias('total_quantity')) on grouped_data.

4

Display the aggregated results

Print the grouped_data DataFrame using the show() method to display the total quantity sold for each product.

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import sum

spark = SparkSession.builder.appName('GroupByExample').getOrCreate()
data = [('apple', 10), ('banana', 5), ('apple', 7), ('banana', 3), ('orange', 8)]
sales_df = spark.createDataFrame(data, ['product', 'quantity'])
grouped_data = sales_df.groupBy('product').agg(sum('quantity').alias('total_quantity'))
# Print grouped_data using show()
# Your code here

Hint

Use grouped_data.show() to display the results.

Practice

1. What does the groupBy function do in Apache Spark?

easy

A. It splits the data into groups based on column values.

B. It sorts the data in ascending order.

C. It filters rows based on a condition.

D. It joins two DataFrames together.

Solution

1. Step 1: Understand the purpose of groupBy

   The groupBy function is used to split data into groups based on one or more column values.

2. Step 2: Compare with other functions

   Sorting, filtering, and joining are different operations and do not split data into groups.

3. Final Answer:

   It splits the data into groups based on column values. -> Option A

4. Quick Check:

   groupBy = splits data into groups [OK]

Hint: Remember: groupBy groups rows by column values [OK]

Common Mistakes:

• Confusing groupBy with filter or sort
• Thinking groupBy changes row order
• Assuming groupBy merges DataFrames

2. Which of the following is the correct syntax to calculate the average of column sales after grouping by region in Spark DataFrame df?

easy

A. df.groupBy("region").sum("sales")

B. df.groupBy("region").agg(avg("sales"))

C. df.groupBy("region").filter(avg("sales"))

D. df.groupBy("region").select(avg("sales"))

Solution

1. Step 1: Identify correct aggregation syntax

   To calculate average after grouping, use groupBy followed by agg(avg(column)).

2. Step 2: Check each option

   df.groupBy("region").agg(avg("sales")) uses agg(avg("sales")) correctly. df.groupBy("region").sum("sales") uses sum, not average. df.groupBy("region").filter(avg("sales")) uses filter incorrectly. df.groupBy("region").select(avg("sales")) uses select which is not valid after groupBy for aggregation.

3. Final Answer:

   df.groupBy("region").agg(avg("sales")) -> Option B

4. Quick Check:

   groupBy + agg(avg) = df.groupBy("region").agg(avg("sales")) [OK]

Hint: Use agg() with avg() inside groupBy for averages [OK]

Common Mistakes:

• Using sum instead of avg for average
• Using filter instead of agg
• Using select after groupBy without agg

3. Given the following Spark code, what will be the output schema and result?

from pyspark.sql import SparkSession
from pyspark.sql.functions import sum

spark = SparkSession.builder.getOrCreate()
data = [("East", 100), ("West", 200), ("East", 150)]
df = spark.createDataFrame(data, ["region", "sales"])
result = df.groupBy("region").agg(sum("sales").alias("total_sales"))
result.show()

medium

A. region | total_sales
East | 250
West | 200

B. region | sales
East | 100
West | 200
East | 150

C. region | total_sales
East | 150
West | 200

D. region | total_sales
East | 100
West | 200

Solution

1. Step 1: Understand groupBy and sum aggregation

   The code groups rows by "region" and sums the "sales" values for each group.

2. Step 2: Calculate sums for each region

   For "East": 100 + 150 = 250; For "West": 200.

3. Final Answer:

   region | total_sales East | 250 West | 200 -> Option A

4. Quick Check:

   Sum sales by region = 250 (East), 200 (West) [OK]

Hint: Sum sales per group to get total_sales [OK]

Common Mistakes:

• Not summing all values per group
• Confusing alias name with original column
• Expecting original rows without aggregation

4. Identify the error in the following Spark code snippet:

df.groupBy("category").agg(sum("amount"), avg("amount"))

medium

A. Missing alias for aggregated columns causes error.

B. agg() requires a dictionary, not multiple arguments.

C. groupBy cannot be used with agg.

D. sum and avg functions are not imported.

Solution

1. Step 1: Check function imports

   The code uses sum and avg functions but does not show imports from pyspark.sql.functions.

2. Step 2: Validate other options

   agg() accepts multiple arguments; aliasing is optional; groupBy works with agg.

3. Final Answer:

   sum and avg functions are not imported. -> Option D

4. Quick Check:

   Missing imports cause NameError [OK]

Hint: Always import aggregation functions before use [OK]

Common Mistakes:

• Assuming agg needs dict, but it accepts multiple args
• Forgetting to import sum, avg from pyspark.sql.functions
• Thinking groupBy can't be chained with agg

5. You have a Spark DataFrame sales_df with columns store, product, and revenue. You want to find the maximum revenue per store and the average revenue per product across all stores in a single query. Which code snippet correctly achieves this?

hard

A. sales_df.groupBy("store").agg(max("revenue").alias("max_revenue"), avg("revenue").alias("avg_revenue"))

B. sales_df.agg(max("revenue").alias("max_revenue"), avg("revenue").alias("avg_revenue"))

C. sales_df.groupBy("store").agg(max("revenue").alias("max_revenue")).crossJoin(sales_df.groupBy("product").agg(avg("revenue").alias("avg_revenue")))

D. sales_df.groupBy("store").agg(max("revenue").alias("max_revenue")).groupBy("product").agg(avg("revenue").alias("avg_revenue"))

Solution

1. Step 1: Understand the requirements

   We need max revenue per store and average revenue per product in one result.

2. Step 2: Analyze options

   sales_df.groupBy("store").agg(max("revenue").alias("max_revenue"), avg("revenue").alias("avg_revenue")) aggregates both by store only, so avg revenue per product is incorrect. sales_df.agg(max("revenue").alias("max_revenue"), avg("revenue").alias("avg_revenue")) aggregates globally, losing grouping. sales_df.groupBy("store").agg(max("revenue").alias("max_revenue")).groupBy("product").agg(avg("revenue").alias("avg_revenue")) tries chaining groupBy but overwrites previous aggregation. sales_df.groupBy("store").agg(max("revenue").alias("max_revenue")).crossJoin(sales_df.groupBy("product").agg(avg("revenue").alias("avg_revenue"))) computes both groupings separately and joins results, correctly combining both summaries.

3. Final Answer:

   sales_df.groupBy("store").agg(max("revenue").alias("max_revenue")).crossJoin(sales_df.groupBy("product").agg(avg("revenue").alias("avg_revenue"))) -> Option C

4. Quick Check:

   Separate groupBy + crossJoin combines both aggregations [OK]

Hint: Use separate groupBy then join to combine different aggregations [OK]

Common Mistakes:

• Trying to aggregate different groupings in one groupBy
• Overwriting aggregation by chaining groupBy
• Forgetting to join separate aggregation results