How to Find Duplicates in Spark | Apache Spark Window Function

In this article, we will see a scenario based question in Spark to understand the concept of windowing function in Spark.

Problem Statement:

Consider we have a CSV file with some duplicate records in it as shown in the picture. Our requirement is to find duplicate records or duplicate rows in spark dataframe and report the output Spark Dataframe as shown in diagram (output dataframe).

Solution:

We can solve this problem to find duplicate rows by two Method,

• PySpark GroupBy
• PySpark Window Rank Function

For the Explanation and demo on the above given two methods, please watch the video embedded below

Subscribe to my YouTube channel for more Spark related Question.

Code snippets:

Step 1;

Initialize the SparkSession and read the sample CSV file

import findspark
findspark.init()

# Create SparkSession
from pyspark.sql import SparkSession
spark=SparkSession.builder.appName("Report_Duplicate").getOrCreate()

#Read CSV File
in_df=spark.read.csv("duplicate.csv",header=True)
in_df.show()

Out[]:

Approach 1: GroupBy

in_df.groupby("Name","Age","Education","Year") \
     .count() \
     .where("count > 1") \
     .drop("count").show()

Out[]:

Approach 2: Window Ranking Function

from pyspark.sql.window import Window
from pyspark.sql.functions import col,row_number

#Create window
win=Window.partitionBy("name").orderBy(col("Year").desc())

in_df.withColumn("rank", row_number().over(win)) \
     .filter("rank > 1") \
     .drop("rank").dropDuplicates().show()

out[]:



Happy Learning !!!