Introduction

Let's explore different ways to get distinct values in every column or select multiple columns in a DataFrame using PySpark.

First, let's create a DataFrame with some duplicate rows and duplicate values in a few columns. We'll use this DataFrame to demonstrate how to get distinct values across multiple columns.

from pyspark.sql import SparkSession

# Create a Spark session
spark = SparkSession.builder.appName("example").getOrCreate()

# Sample data
simpleData = [
    ("James", "Sales", 3000),
    ("Michael", "Sales", 4600),
    ("Robert", "Sales", 4100),
    ("Maria", "Finance", 3000),
    ("James", "Sales", 3000),
    ("Scott", "Finance", 3300),
    ("Jen", "Finance", 3900),
    ("Jeff", "Marketing", 3000),
    ("Kumar", "Marketing", 2000),
    ("Saif", "Sales", 4100)
]

# Create DataFrame
df = spark.createDataFrame(simpleData, ["employee_name", "department", "salary"])
df.show()

Output:

+-------------+----------+------+
|employee_name|department|salary|
+-------------+----------+------+
|        James|     Sales|  3000|
|      Michael|     Sales|  4600|
|       Robert|     Sales|  4100|
|        Maria|   Finance|  3000|
|        James|     Sales|  3000|
|        Scott|   Finance|  3300|
|          Jen|   Finance|  3900|
|         Jeff| Marketing|  3000|
|        Kumar| Marketing|  2000|
|         Saif|     Sales|  4100|
+-------------+----------+------+

As you notice, we have 2 rows that have duplicate values on all columns, and we have 4 rows that have duplicate values on the "department" and "salary" columns.

1. Get Distinct All Columns

In the above DataFrame, we have a total of 10 rows and one row with all values duplicated. Performing distinct on this DataFrame should get us 9 unique rows, as we have one duplicate.

# Distinct all columns
distinctDF = df.distinct()
print("Distinct count:", distinctDF.count())
distinctDF.show(truncate=False)

The distinct() method on a DataFrame returns a new DataFrame with duplicate records removed. This example yields the output below.

Output:

Distinct count: 9
+-------------+----------+------+
|employee_name|department|salary|
+-------------+----------+------+
|James        |Sales     |3000  |
|Michael      |Sales     |4600  |
|Maria        |Finance   |3000  |
|Robert       |Sales     |4100  |
|Saif         |Sales     |4100  |
|Scott        |Finance   |3300  |
|Jeff         |Marketing |3000  |
|Jen          |Finance   |3900  |
|Kumar        |Marketing |2000  |
+-------------+----------+------+

Alternatively, you can also use the dropDuplicates() method which returns a new DataFrame with duplicate rows removed.

df2 = df.dropDuplicates()
print("Distinct count:", df2.count())
df2.show(truncate=False)

2. Distinct of Multiple Columns

Spark doesn't have a distinct method that takes columns to run distinct on. However, Spark provides another signature of the dropDuplicates() method, which takes multiple columns to eliminate duplicates.

Calling dropDuplicates() on a DataFrame returns a new DataFrame with duplicate rows removed based on specified columns.

# Distinct using dropDuplicates
dropDisDF = df.dropDuplicates(["department", "salary"])
print("Distinct count of department & salary:", dropDisDF.count())
dropDisDF.show(truncate=False)

This yields the output below. Notice how it dropped 2 records that were duplicates based on the specified columns.

Output:

Distinct count of department & salary: 8
+-------------+----------+------+
|employee_name|department|salary|
+-------------+----------+------+
|Jen          |Finance   |3900  |
|Maria        |Finance   |3000  |
|Scott        |Finance   |3300  |
|Michael      |Sales     |4600  |
|Kumar        |Marketing |2000  |
|Robert       |Sales     |4100  |
|James        |Sales     |3000  |
|Jeff         |Marketing |3000  |
+-------------+----------+------+