Joining DataFrames and Exporting to Multiple Formats

Welcome to this lesson on joining and exporting PySpark DataFrames in various formats. Building on the skills you've gained in processing and manipulating DataFrames, we will explore how to combine data from multiple DataFrames using join operations — a fundamental step in data analysis for creating meaningful insights. We will also discuss how to export these data results into different file formats, a key process for sharing data and collaborating across platforms. Let's dive into the task at hand and understand how these operations enhance our data processing capabilities.

Before we perform joins, we need to load and understand our datasets. Let's briefly review data loading — as you might recall from our previous lessons, loading data involves initializing our SparkSession and using the read.csv() function to bring CSV files into our DataFrames.

Here are the datasets we'll be working with: departments.csv and employees.csv. As a reminder, here is how we read these datasets:

from pyspark.sql import SparkSession

# Initialize a SparkSession
spark = SparkSession.builder.master("local").appName("JoiningAndSavingDataFrames").getOrCreate()

# Read the CSV files into DataFrames
dept_df = spark.read.csv("departments.csv", header=True, inferSchema=True)
emp_df = spark.read.csv("employees.csv", header=True, inferSchema=True)

# Display the original departments DataFrame
dept_df.show()

# Display the original employees DataFrame
emp_df.show()

The departments.csv file provides us with department names and IDs, while employees.csv lists employee names along with their department IDs. Upon running the above code, you will see:

Departments DataFrame:

+-----------+------+
| Department|DeptID|
+-----------+------+
|         IT|     1|
|         HR|     2|
|    Finance|     3|
+-----------+------+

Employees DataFrame:

+-------+------+
|   Name|DeptID|
+-------+------+
|  Alice|     1|
|    Bob|     2|
|Charlie|     4|
+-------+------+

These outputs will serve as our starting point for performing DataFrame joins.

In this lesson, we'll explore three types of joins in PySpark, which are essential for merging data from multiple DataFrames:

• Inner Join: This join returns rows where the join key has matching values in both DataFrames. When duplicate keys exist in either DataFrame, the join produces all possible combinations of the matching rows—this means that if a key appears twice in each DataFrame, the result will contain four rows for that key. Inner joins are useful when you want to include only records that appear in both datasets, but it’s important to remember that duplicates can increase the number of resulting rows.

• Left Join: This join includes all rows from the left DataFrame and matches them with rows from the right DataFrame. If there are no matches, the entries from the right DataFrame are filled with nulls.

• Right Join: This join includes all rows from the right DataFrame and matches them with rows from the left DataFrame. If no matches exist, the entries from the left DataFrame are filled with nulls.

Now that we've defined the types of joins, let's see how we perform these operations using PySpark.