Apache Spark is an open-source, distributed computing system that provides a framework for large-scale data processing. It’s widely used in big data analytics and machine learning to process large volumes of data in parallel across multiple nodes in a cluster. One common task in data processing is removing duplicates and null values from a DataFrame. In this blog, we’ll learn how to do this in Apache Spark using PySpark.
Consider the following example of a DataFrame with the ids of clients, which streaming platform they are subscribers to, and how often they pay for their subscription:
Copy code
+------+------------------+---------------------+
| id | subscription_plan| payment_frequency |
+------+------------------+---------------------+
| 3004 | Netflix | Monthly |
| 3004 | Disney + | Monthly |
| 3004 | Netflix | Null |
| 3006 | Star + | Yearly |
| 3006 | Apple TV | Yearly |
| 3006 | Netflix | Monthly |
| 3006 | Star + | Null |
| 3009 | Apple TV | Null |
| 3009 | Star + | Monthly |
+------+------------------+---------------------+
The problem is that there are some duplicate values, and we need to get rid of the ones that are duplicated and where the status on the payment_frequency is null. If payment_frequency is null but the record is not duplicated, this is fine, like for example ID 3009 for Apple TV.
We can achieve this in Apache Spark using PySpark with the following code:
Copy code
from pyspark.sql import functions as F
from pyspark.sql.window import Window
# Define a window function to partition by id and subscription_plan
window = Window.partitionBy("id", "subscription_plan")
# Use the withColumn method to add a new column 'x' that counts the number of rows in each group
df = df.withColumn("x", F.count("payment_frequency").over(window))
# Use the where method to filter the data based on the values in column 'x' and 'payment_frequency'
df = df.where((F.col("x") == 1) | (F.col("payment_frequency").isNotNull()))
# Use the drop method to remove the unwanted column 'x'
df = df.drop("x")
# Show the results
df.show()
In this example, we first define a window function using the Window class from the pyspark.sql.window module. The window function partitions the data by both the id and subscription_plan columns.
Next, we use the withColumn method to add a new column x to the dataframe that counts the number of rows in each group defined by the window function.
Then, we use the where method to filter the data based on the values in columns x and payment_frequency. We keep only the rows where x is equal to 1, or where payment_frequency is not NULL.
Finally, we use the drop method to remove the unwanted column x from the dataframe.
The show method at the end is used to display the results of the dataframe.
Here are two more examples of how to remove duplicates and null values from a DataFrame in Apache Spark using PySpark:
Example 1: Removing Duplicates Based on Multiple Columns
Consider the following example of a DataFrame with the names, ages, and addresses of clients:
Copy code
+-------+------+---------+
| name | age | address |
+-------+------+---------+
| John | 25 | NY |
| Sarah | 30 | LA |
| John | 25 | NY |
| Sarah | 35 | LA |
| Emily | Null | NY |
+-------+------+---------+
We want to remove the duplicates based on the name and age columns. To achieve this, we can use the dropDuplicates method in PySpark:
Copy code
df = df.dropDuplicates(["name", "age"])
df.show()
The dropDuplicates method takes as an argument the list of columns to consider when checking for duplicates. In this case, we’re checking for duplicates based on the name and age columns.
Example 2: Removing Null Values
Consider the following example of a DataFrame with the names and ages of clients:
Copy code
+-------+------+
| name | age |
+-------+------+
| John | 25 |
| Sarah | 30 |
| Emily | Null |
+-------+------+
We want to remove the rows with null values in the age column. To achieve this, we can use the dropna method in PySpark:
Copy code
df = df.dropna(subset=["age"])
df.show()
The dropna method takes as an argument the list of columns to consider when checking for null values. In this case, we’re checking for null values in the age column.
In conclusion, removing duplicates and null values is a common task in data processing, and Apache Spark provides several methods to achieve this. By using the dropDuplicates and dropna methods in PySpark, we can easily remove duplicates and null values from a DataFrame.