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:

Python
Copy to clipboard
1from pyspark.sql import SparkSession
2
3# Initialize a SparkSession
4spark = SparkSession.builder.master("local").appName("JoiningAndSavingDataFrames").getOrCreate()
5
6# Read the CSV files into DataFrames
7dept_df = spark.read.csv("departments.csv", header=True, inferSchema=True)
8emp_df = spark.read.csv("employees.csv", header=True, inferSchema=True)
9
10# Display the original departments DataFrame
11dept_df.show()
12
13# Display the original employees DataFrame
14emp_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:

Plain text
Copy to clipboard
1+-----------+------+
2| Department|DeptID|
3+-----------+------+
4|         IT|     1|
5|         HR|     2|
6|    Finance|     3|
7+-----------+------+

Employees DataFrame:

Plain text
Copy to clipboard
1+-------+------+
2|   Name|DeptID|
3+-------+------+
4|  Alice|     1|
5|    Bob|     2|
6|Charlie|     4|
7+-------+------+

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 with matching values in both DataFrames, making it useful when you want only the records present in both datasets.

• 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.

In PySpark, the join() method allows us to combine DataFrames based on shared columns. Here's the generic format:

Python
Copy to clipboard
1result_df = left_df.join(right_df, "common_column", "join_type")

To set up the join() method:

• left_df and right_df are the DataFrames you want to join.
• "common_column" is the column used for matching rows.
• "join_type" specifies the join type, such as "inner", "left", or "right". An unspecified type defaults to an Inner Join.

With the technical setup in place, let's look at examples illustrating each join type.

Let's begin with an Inner Join, which returns only the rows that have matching values in both DataFrames.

Here's how you can perform it:

Python
Copy to clipboard
1# Perform an inner join on the DeptID column
2inner_join_df = dept_df.join(emp_df, "DeptID")
3
4# Display the DataFrame resulting from the inner join
5inner_join_df.show()

Upon executing the above code, you'll observe that the result includes only those departments which have employees listed in the emp_df DataFrame.

Plain text
Copy to clipboard
1+------+-----------+-----+
2|DeptID| Department| Name|
3+------+-----------+-----+
4|     1|         IT|Alice|
5|     2|         HR|  Bob|
6+------+-----------+-----+

Next, let's explore a Left Join, which retains all rows from the left DataFrame (departments), while including matches from the right DataFrame where available.

Here's how to apply a left join:

Python
Copy to clipboard
1# Perform a left join on the DeptID column
2left_join_df = dept_df.join(emp_df, "DeptID", "left")
3
4# Display the DataFrame resulting from the left join
5left_join_df.show()

In the result of a left join, you will find all departments listed, with NULL in the Name column where no corresponding employee exists in the employees DataFrame:

Plain text
Copy to clipboard
1+------+-----------+-----+
2|DeptID| Department| Name|
3+------+-----------+-----+
4|     1|         IT|Alice|
5|     2|         HR|  Bob|
6|     3|    Finance| NULL|
7+------+-----------+-----+

Lastly, we discuss the Right Join, which retains all rows from the right DataFrame (employees), while including matches from the left DataFrame where available.

Here's how to apply a right join:

Python
Copy to clipboard
1# Perform a right join on the DeptID column
2right_join_df = dept_df.join(emp_df, "DeptID", "right")
3
4# Display the DataFrame resulting from the right join
5right_join_df.show()

The output from the right join will include all employees, with NULL in the Department column where the employee's department does not exist in the departments DataFrame:

Plain text
Copy to clipboard
1+------+-----------+-------+
2|DeptID| Department|   Name|
3+------+-----------+-------+
4|     1|         IT|  Alice|
5|     2|         HR|    Bob|
6|     4|       NULL|Charlie|
7+------+-----------+-------+

These join operations are essential tools in PySpark, allowing you to effectively manage and analyze relational data within large datasets.

With our new DataFrames, we can now export them into various file formats such as CSV, JSON, and Parquet for sharing and further processing. PySpark DataFrames are distributed, similar to RDDs, and they are partitioned across the nodes of the cluster. When you save a DataFrame, it is also written out in a partitioned manner, creating multiple output files — each corresponding to a partition. This is beneficial for parallel processing but might result in more files than expected if not consolidated properly.

To save DataFrames, you can use the following write methods for each specific format, specifying a path where the data will be saved:

Python
Copy to clipboard
1# Save the inner joined DataFrame as a CSV file
2inner_join_df.write.csv("output/inner_joined_data", header=True)
3
4# Save the left joined DataFrame as a JSON file
5left_join_df.write.json("output/left_joined_data")
6
7# Save the right joined DataFrame as a Parquet file
8right_join_df.write.parquet("output/right_joined_data")

By default, the DataFrame is saved with one file for each partition. If you wish to consolidate the output into a single file, adjust the coalesce or repartition method before writing:

Python
Copy to clipboard
1# Reduce the number of partitions to 1 for a single output file
2inner_join_df.coalesce(1).write.csv("output/inner_joined_data_single_file", header=True)

These commands not only show the process of exporting DataFrames but also highlight how partitioning affects file output, similar to how you might remember handling RDDs in terms of saving partitions.

Congratulations on reaching the end of this PySpark course! You have gained a wealth of knowledge on working with DataFrames — from creating them, performing basic and complex operations, handling missing values, to our current task of joining and exporting data.

You've mastered essential PySpark skills needed for real-world data engineering tasks, and you're now equipped to take on the practice exercises that will solidify your understanding and encourage exploration of PySpark's capabilities. As you continue your data journey, remember to build upon these foundations and keep experimenting with different datasets. Well done on your achievement, and best wishes for your data adventures ahead!