Are you looking to find how to combine PySpark Dataframe vertically into Azure Databricks cloud or maybe you are looking for a solution, to join two Dataframes in PySpark Databricks? If you are looking for any of these problem solutions, then you have landed on the correct page. I will also show you what and how to use PySpark to join records of a Dataframe using union() and unionByName() functions in Azure Databricks. I will explain it with a practical example. So don’t waste time let’s start with a step-by-step guide to understanding how to join a record or rows using a PySpark Dataframe.
In this blog, I will teach you the following with practical examples:
- Combine DataFrames
- Combine the distinct value of Two DataFrames
- Combining different DataFrames of unmatched columns
union() function is used to combine two or more data frames having the same structure or schema. This function returns an error if the schema of data frames differs from each other
Syntax: dataframe_name.union()
Contents
- 1 What is the syntax of the union() function in PySpark Azure Databricks?
- 2 Create a simple DataFrame
- 3 How to combine DataFrames in PySpark Azure Databricks using the union() function?
- 4 How to combine DataFrames of unique values in PySpark Azure Databricks using the union() function?
- 5 How to combine different structure DataFrames in PySpark Azure Databricks?
- 6 When should you use the PySpark union() function in Azure Databricks?
- 7 Real World Use Case Scenarios for union() function in PySpark Azure Databricks?
- 8 What are the alternatives of the union() function in PySpark Azure Databricks?
- 9 Final Thoughts
What is the syntax of the union() function in PySpark Azure Databricks?
The syntax is as follows:
dataframe_name.union(other)
| Parameter Name | Required | Description |
| other (pyspark.sql.dataframe.DataFrame) | Yes | It represents the DataFrame to be added. |
Official documentation link: union()
Create a simple DataFrame
Gentle reminder:
In Databricks,
- sparkSession made available as spark
- sparkContext made available as sc
In case, you want to create it manually, use the below code.
from pyspark.sql.session import SparkSession
spark = SparkSession.builder
.master("local[*]")
.appName("azurelib.com")
.getOrCreate()
sc = spark.sparkContext
a) Create manual PySpark DataFrame
# DataFrame 1
df1 = spark.createDataFrame(
data=[("Windstar",4,133,"Europe"),("Yaris",10,122,"Japan")],
schema=["name", "cylinders", "displacement", "origin"])
df1.show()
# DataFrame 2
df2 = spark.createDataFrame(
data=[("Yaris",10,122,"Japan")],
schema=["name", "cylinders", "displacement", "origin"])
df2.show()
# DataFrame 3
df3 = spark.createDataFrame(
data=[("Sonata",6,126,"German",2010)],
schema=["name","origin","displacement","cylinders","year"])
df3.show()
"""
+--------+---------+------------+------+
| name|cylinders|displacement|origin|
+--------+---------+------------+------+
|Windstar| 4| 133|Europe|
| Yaris| 10| 122| Japan|
+--------+---------+------------+------+
+-----+---------+------------+------+
| name|cylinders|displacement|origin|
+-----+---------+------------+------+
|Yaris| 10| 122| Japan|
+-----+---------+------------+------+
+------+------+------------+---------+----+
| name|origin|displacement|cylinders|year|
+------+------+------------+---------+----+
|Sonata| 6| 126| German|2010|
+------+------+------------+---------+----+
"""
b) Creating a DataFrame by reading files
Download and use the below source file.
# replace the file_paths with the source file location which you have downloaded.
df_1 = spark.read.format("csv").option("header", True).load(file_path_1)
df_1.printSchema()
df_2 = spark.read.format("csv").option("header", True).load(file_path_2)
df_2.printSchema()
df_3 = spark.read.format("csv").option("header", True).load(file_path_3)
df_3.printSchema()
"""
root
|-- name: string (nullable = true)
|-- cylinders: long (nullable = true)
|-- displacement: long (nullable = true)
|-- origin: string (nullable = true)
root
|-- name: string (nullable = true)
|-- cylinders: long (nullable = true)
|-- displacement: long (nullable = true)
|-- origin: string (nullable = true)
root
|-- name: string (nullable = true)
|-- origin: long (nullable = true)
|-- displacement: long (nullable = true)
|-- cylinders: string (nullable = true)
|-- year: long (nullable = true)
"""
Note: Here, I will be using the manually created DataFrame.
How to combine DataFrames in PySpark Azure Databricks using the union() function?
The PySpark function union() is used to combine two or more DataFrames of the same schema or structure.
Example:
In the below example, we are trying to combine df1 with df2 of the same structure.
df1.union(df2).show()
"""
Output:
+--------+---------+------------+------+
| name|cylinders|displacement|origin|
+--------+---------+------------+------+
|Windstar| 4| 133|Europe|
| Yaris| 10| 122| Japan|
| Yaris| 10| 122| Japan|
+--------+---------+------------+------+
"""
How to combine DataFrames of unique values in PySpark Azure Databricks using the union() function?
In the below example, we are trying to combine only the unique values of df1 with the unique values of df2. You can see in the below output has only one “Yaris” in the records after using the distinct() function on top of the union() function.
Example:
df1.union(df2).distinct().show()
"""
Output:
+--------+---------+------------+------+
| name|cylinders|displacement|origin|
+--------+---------+------------+------+
|Windstar| 4| 133|Europe|
| Yaris| 10| 122| Japan|
+--------+---------+------------+------+
"""
How to combine different structure DataFrames in PySpark Azure Databricks?
Example:
In the below example, we are trying to combine DataFrames with different structures and also change column position using the unionByName() function. In the below example, you can see the DataFrame df3 has additional columns, and the column position is a bit different compared to the df1.
df1.printSchema()
"""
root
|-- name: string (nullable = true)
|-- cylinders: long (nullable = true)
|-- displacement: long (nullable = true)
|-- origin: string (nullable = true)
"""
df3.printSchema()
"""
root
|-- name: string (nullable = true)
|-- origin: long (nullable = true)
|-- displacement: long (nullable = true)
|-- cylinders: string (nullable = true)
|-- year: long (nullable = true)
"""
df1.unionByName(df3, allowMissingColumns=True).show()
"""
+--------+---------+------------+------+----+
| name|cylinders|displacement|origin|year|
+--------+---------+------------+------+----+
|Windstar| 4| 133|Europe|null|
| Yaris| 10| 122| Japan|null|
| Sonata| German| 126| 6|2010|
+--------+---------+------------+------+----+
"""
I have attached the complete code used in this blog in notebook format to this GitHub link. You can download and import this notebook in databricks, jupyter notebook, etc.
When should you use the PySpark union() function in Azure Databricks?
You can use the PySpark union() function to combine two or more data frames having the same structure or schema. This function returns an error if the schema of data frames differs from each other
Real World Use Case Scenarios for union() function in PySpark Azure Databricks?
For example, you receive similar datasets in different formats for example in CSV and JSON. And you have to perform aggregation by combining both DataFrames to get insights. in this case the PySpark union() and unionByName() functions help in combining DataFrames.
What are the alternatives of the union() function in PySpark Azure Databricks?
You can use the unionAll() function to combine DataFrame of the same structure. But unionAll() is deprecated since the “Spark 2.0.0” version and replaced with the union().
Final Thoughts
In this article, we have learned about the PySpark union() and unionByName() methods to combine DataFrames in Azure Databricks along with the examples explained clearly. I have also covered different scenarios with practical examples that could be possible. I hope the information that was provided helped in gaining knowledge.
Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.
- For Azure Study material Join Telegram group : Telegram group link:
- Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
- Azure Tutorial Videos: Videos Link
- Azure Databricks Lesson 1
- Azure Databricks Lesson 2
- Azure Databricks Lesson 3
- Azure Databricks Lesson 4
- Azure Databricks Lesson 5
- Azure Databricks Lesson 6
- Azure Databricks Lesson 7
As a big data engineer, I design and build scalable data processing systems and integrate them with various data sources and databases. I have a strong background in Python and am proficient in big data technologies such as Hadoop, Hive, Spark, Databricks, and Azure. My interest lies in working with large datasets and deriving actionable insights to support informed business decisions.