LEFT JOIN PySpark

In this PySpark tutorial, we will see how to perform left join on two PySpark dataframes.

Let's create two pyspark dataframes and before that we have to install pyspark module.

pip install pyspark

First PySpark DataFrame:

It has three columns with 5 rows.

# import the below modules
import pyspark
from pyspark.sql import SparkSession

# create an app
spark = SparkSession.builder.appName('tutorialsinhand').getOrCreate()

#create a  list of data
values = [{'rollno': 1, 'student name': 'Gottumukkala Sravan','marks': 98},

        {'rollno': 2, 'student name': 'Gottumukkala Bobby','marks': 89},

        {'rollno': 3, 'student name': 'Lavu Ojaswi','marks': 90},

        {'rollno': 4, 'student name': 'Lavu Gnanesh','marks': 78},

        {'rollno': 5, 'student name': 'Chennupati Rohith','marks': 100}]


# create the dataframe from the values
data1 = spark.createDataFrame(values)

#pdisplay first dataframe
data1.show()

Output:

+-----+------+-------------------+
|marks|rollno|       student name|
+-----+------+-------------------+
|   98|     1|Gottumukkala Sravan|
|   89|     2| Gottumukkala Bobby|
|   90|     3|        Lavu Ojaswi|
|   78|     4|       Lavu Gnanesh|
|  100|     5|  Chennupati Rohith|
+-----+------+-------------------+

First PySpark DataFrame:

It has two columns with 3 rows.

# import the below modules
import pyspark
from pyspark.sql import SparkSession

# create an app
spark = SparkSession.builder.appName('tutorialsinhand').getOrCreate()

#create a  list of data
values = [{'rollno': 1, 'gender': 'Male'},

        {'rollno': 2, 'gender': 'Female'},

        {'rollno': 6, 'gender': 'Male'},

        ]


# create the dataframe from the values
data2 = spark.createDataFrame(values)

#pdisplay second dataframe
data2.show()

Output:

+------+------+
|gender|rollno|
+------+------+
|  Male|     1|
|Female|     2|
|  Male|     6|
+------+------+

From the above dataframes, we can observe that rollno column is common in both the dataframes. so we are joining the two dataframes using LEFT JOIN/LEFT OUTER JOIN.

LEFT JOIN will join all the rows from both the dataframes by returning all the rows from 1st dataframe and only matched rows from 2nd dataframe. If the values in 2nd dataframe are not matched, then it will keep null whereever the rows are unmatched in the 2nd dataframe. 

There are two ways to perform LEFT JOIN -  leftouter and left

Syntax:

data1.join(data2,data1.column==  data2.column,"leftouter")
data1.join(data2,data1.column==  data2.column,"left")

where,

data1 is the 1st pyspark dataframe and data2 is the 2nd pyspark dataframe. column is the column name in which the dataframes are joined based on this column.

Example 1:

Perform left join on the above two dataframes with left keyword.

# import the below modules
import pyspark
from pyspark.sql import SparkSession

# create an app
spark = SparkSession.builder.appName('tutorialsinhand').getOrCreate()

#create a  list of data
values = [{'rollno': 1, 'student name': 'Gottumukkala Sravan','marks': 98},

        {'rollno': 2, 'student name': 'Gottumukkala Bobby','marks': 89},

        {'rollno': 3, 'student name': 'Lavu Ojaswi','marks': 90},

        {'rollno': 4, 'student name': 'Lavu Gnanesh','marks': 78},

        {'rollno': 5, 'student name': 'Chennupati Rohith','marks': 100}]


# create the dataframe from the values
data1 = spark.createDataFrame(values)

#create a  list of data
values = [{'rollno': 1, 'gender': 'Male'},

        {'rollno': 2, 'gender': 'Female'},

        {'rollno': 6, 'gender': 'Male'},

        ]


# create the dataframe from the values
data2 = spark.createDataFrame(values)

#perform left join
data1.join(data2,data1.rollno ==  data2.rollno,"left").show()

Output:

+-----+------+-------------------+------+------+
|marks|rollno|       student name|gender|rollno|
+-----+------+-------------------+------+------+
|   98|     1|Gottumukkala Sravan|  Male|     1|
|   89|     2| Gottumukkala Bobby|Female|     2|
|   90|     3|        Lavu Ojaswi|  null|  null|
|   78|     4|       Lavu Gnanesh|  null|  null|
|  100|     5|  Chennupati Rohith|  null|  null|
+-----+------+-------------------+------+------+

We are performing left join using "left" keyword. we can see that null is placed in the 2nd dataframe (data2) for rows-3,4,5 unmatched with respect to 1st dataframe(data1) . These rows are not there in 2nd dataframe.

 

Example 2:

Perform left join on the above two dataframes with leftouter  keyword.

# import the below modules
import pyspark
from pyspark.sql import SparkSession

# create an app
spark = SparkSession.builder.appName('tutorialsinhand').getOrCreate()

#create a  list of data
values = [{'rollno': 1, 'student name': 'Gottumukkala Sravan','marks': 98},

        {'rollno': 2, 'student name': 'Gottumukkala Bobby','marks': 89},

        {'rollno': 3, 'student name': 'Lavu Ojaswi','marks': 90},

        {'rollno': 4, 'student name': 'Lavu Gnanesh','marks': 78},

        {'rollno': 5, 'student name': 'Chennupati Rohith','marks': 100}]


# create the dataframe from the values
data1 = spark.createDataFrame(values)

#create a  list of data
values = [{'rollno': 1, 'gender': 'Male'},

        {'rollno': 2, 'gender': 'Female'},

        {'rollno': 6, 'gender': 'Male'},

        ]


# create the dataframe from the values
data2 = spark.createDataFrame(values)

#perform left join
data1.join(data2,data1.rollno ==  data2.rollno,"leftouter").show()

Output:

+-----+------+-------------------+------+------+
|marks|rollno|       student name|gender|rollno|
+-----+------+-------------------+------+------+
|   98|     1|Gottumukkala Sravan|  Male|     1|
|   89|     2| Gottumukkala Bobby|Female|     2|
|   90|     3|        Lavu Ojaswi|  null|  null|
|   78|     4|       Lavu Gnanesh|  null|  null|
|  100|     5|  Chennupati Rohith|  null|  null|
+-----+------+-------------------+------+------+

We are performing left join using "leftouter" keyword. we can see that null is placed in the 2nd dataframe (data2) for rows-3,4,5 unmatched with respect to 1st dataframe(data1) . These rows are not there in 2nd dataframe.