Introduction

The union() method of a DataFrame is used to combine two DataFrames with the same structure/schema in PySpark. If schemas are not aligned, it returns an error.

Note: The method unionAll() is deprecated as of Spark version 2.0.0 and has been replaced by union().

Note: In traditional SQL, the Union operation eliminates duplicates, while UnionAll combines two datasets including duplicate records. However, in Spark, both union() and the deprecated unionAll() function the same way as unionAll in SQL.

Before using these methods, it's beneficial to understand StructType and StructField, which define the schema of DataFrames in PySpark.

• StructType is a collection of StructFields that define column names, types, nullable properties, and metadata.
• StructField is essentially a field containing metadata such as the column name and datatype.

Let's start by creating two DataFrames with the same schema.

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType, IntegerType

spark = SparkSession.builder.appName("DataFrame Union Example").getOrCreate()

schema = StructType([
    StructField("employee_name", StringType(), True),
    StructField("department", StringType(), True),
    StructField("state", StringType(), True),
    StructField("salary", IntegerType(), False),
    StructField("age", IntegerType(), False),
    StructField("bonus", IntegerType(), False)
])

simpleData = [
    ("James", "Sales", "NY", 90000, 34, 10000),
    ("Michael", "Sales", "NY", 86000, 56, 20000),
    ("Robert", "Sales", "CA", 81000, 30, 23000),
    ("Maria", "Finance", "CA", 90000, 24, 23000)
]

df = spark.createDataFrame(data=simpleData, schema=schema)
df.printSchema()
df.show()

Output:

root
 |-- employee_name: string (nullable = true)
 |-- department: string (nullable = true)
 |-- state: string (nullable = true)
 |-- salary: integer (nullable = false)
 |-- age: integer (nullable = false)
 |-- bonus: integer (nullable = false)

+-------------+----------+-----+------+---+-----+
|employee_name|department|state|salary|age|bonus|
+-------------+----------+-----+------+---+-----+
|        James|     Sales|   NY| 90000| 34|10000|
|      Michael|     Sales|   NY| 86000| 56|20000|
|       Robert|     Sales|   CA| 81000| 30|23000|
|        Maria|   Finance|   CA| 90000| 24|23000|
+-------------+----------+-----+------+---+-----+

Now, let’s create a second DataFrame with new records and some records from the above DataFrame but with the same schema.

simpleData2 = [
    ("James", "Sales", "NY", 90000, 34, 10000),
    ("Maria", "Finance", "CA", 90000, 24, 23000),
    ("Jen", "Finance", "NY", 79000, 53, 15000),
    ("Jeff", "Marketing", "CA", 80000, 25, 18000),
    ("Kumar", "Marketing", "NY", 91000, 50, 21000)
]

df2 = spark.createDataFrame(data=simpleData2, schema=schema)
df2.show()

Output:

+-------------+----------+-----+------+---+-----+
|employee_name|department|state|salary|age|bonus|
+-------------+----------+-----+------+---+-----+
|        James|     Sales|   NY| 90000| 34|10000|
|        Maria|   Finance|   CA| 90000| 24|23000|
|          Jen|   Finance|   NY| 79000| 53|15000|
|         Jeff|Marketing |   CA| 80000| 25|18000|
|        Kumar|Marketing |   NY| 91000| 50|21000|
+-------------+----------+-----+------+---+-----+

1. Combine two or more DataFrames using union

The DataFrame union() method combines two DataFrames and returns a new DataFrame with all rows from the combined DataFrames, regardless of duplicate data.

# Combine two or more DataFrames using union
df3 = df.union(df2)
df3.show(truncate=False)

This outputs all records:

Output:

+-------------+----------+-----+------+---+-----+
|employee_name|department|state|salary|age|bonus|
+-------------+----------+-----+------+---+-----+
|        James|     Sales|   NY| 90000| 34|10000|
|      Michael|     Sales|   NY| 86000| 56|20000|
|       Robert|     Sales|   CA| 81000| 30|23000|
|        Maria|   Finance|   CA| 90000| 24|23000|
|        James|     Sales|   NY| 90000| 34|10000|
|        Maria|   Finance|   CA| 90000| 24|23000|
|          Jen|   Finance|   NY| 79000| 53|15000|
|         Jeff|Marketing |   CA| 80000| 25|18000|
|        Kumar|Marketing |   NY| 91000| 50|21000|
+-------------+----------+-----+------+---+-----+

2. Combine without Duplicates

Since the union() method returns all rows, including duplicates, use the distinct() function to eliminate duplicate records.

# Combine without Duplicates
df5 = df.union(df2).distinct()
df5.show(truncate=False)

Yields:

Output:

+-------------+----------+-----+------+---+-----+
|employee_name|department|state|salary|age|bonus|
+-------------+----------+-----+------+---+-----+
|        James|     Sales|   NY| 90000| 34|10000|
|        Maria|   Finance|   CA| 90000| 24|23000|
|         Jeff|Marketing |   CA| 80000| 25|18000|
|          Jen|   Finance|   NY| 79000| 53|15000|
|        Kumar|Marketing |   NY| 91000| 50|21000|
|      Michael|     Sales|   NY| 86000| 56|20000|
|       Robert|     Sales|   CA| 81000| 30|23000|
+-------------+----------+-----+------+---+-----+