Skip to content
Tutorial Reference

Python PySpark: How to Count Distinct Values in a PySpark DataFrame

How to count distinct values in PySpark using distinct().count(), countDistinct(), and SQL queries, with practical examples for DataFrames and individual columns.

Counting distinct (unique) values is a fundamental operation in data analysis - whether you need to find the number of unique customers, deduplicate records, or validate data quality. PySpark provides multiple ways to count distinct values across an entire DataFrame or within specific columns.

In this guide, you will learn three approaches to count distinct values in PySpark: using distinct().count(), the countDistinct() function, and SQL queries. Each method is demonstrated with practical examples and clear outputs.

Method 1: Using distinct().count()

The most straightforward approach chains two DataFrame methods:

  • distinct() removes duplicate rows by comparing all columns.
  • count() returns the total number of remaining rows.

This gives you the count of unique rows across the entire DataFrame.

Example: Count All Distinct Rows

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("distinct_count").getOrCreate()

data = [
    ("Ram", "MCA", 80),
    ("Riya", "MBA", 85),
    ("Jiya", "B.E", 60),
    ("Maria", "B.Tech", 65),
    ("Shreya", "B.Sc", 91),
    ("Ram", "MCA", 80),       # duplicate
    ("John", "M.E", 85),
    ("Shyam", "BA", 70),
    ("Kumar", "B.Sc", 78),
    ("Maria", "B.Tech", 65),  # duplicate
]

columns = ["Name", "Course", "Marks"]
df = spark.createDataFrame(data, columns)

df.show()
print("Total records:", df.count())
print("Distinct records:", df.distinct().count())

Output:

+------+------+-----+
|  Name|Course|Marks|
+------+------+-----+
|   Ram|   MCA|   80|
|  Riya|   MBA|   85|
|  Jiya|   B.E|   60|
| Maria|B.Tech|   65|
|Shreya|  B.Sc|   91|
|   Ram|   MCA|   80|
|  John|   M.E|   85|
| Shyam|    BA|   70|
| Kumar|  B.Sc|   78|
| Maria|B.Tech|   65|
+------+------+-----+

Total records: 10
Distinct records: 8

The DataFrame has 10 total rows but only 8 distinct rows - the two duplicate entries for "Ram" and "Maria" are counted only once.

Method 2: Using countDistinct()

The countDistinct() function from pyspark.sql.functions is an aggregate function that returns the number of distinct values across one or more specified columns. Unlike distinct().count(), it lets you target specific columns without affecting the rest of the DataFrame.

Count Distinct Across All Columns

from pyspark.sql import SparkSession
from pyspark.sql.functions import countDistinct

spark = SparkSession.builder.appName("distinct_count").getOrCreate()

data = [
    ("Ram", "IT", 80000),
    ("Shyam", "Sales", 70000),
    ("Jiya", "Sales", 60000),
    ("Maria", "Accounts", 65000),
    ("Ramesh", "IT", 80000),
    ("John", "Management", 80000),
    ("Shyam", "Sales", 70000),     # duplicate
    ("Kumar", "Sales", 78000),
    ("Maria", "Accounts", 65000),  # duplicate
]

columns = ["Emp_Name", "Department", "Salary"]
df = spark.createDataFrame(data, columns)

df.show()

df.select(countDistinct("Emp_Name", "Department", "Salary").alias("Distinct_Count")).show()

Output:

+--------+----------+------+
|Emp_Name|Department|Salary|
+--------+----------+------+
|     Ram|        IT| 80000|
|   Shyam|     Sales| 70000|
|    Jiya|     Sales| 60000|
|   Maria|  Accounts| 65000|
|  Ramesh|        IT| 80000|
|    John|Management| 80000|
|   Shyam|     Sales| 70000|
|   Kumar|     Sales| 78000|
|   Maria|  Accounts| 65000|
+--------+----------+------+

+--------------+
|Distinct_Count|
+--------------+
|             7|
+--------------+

Count Distinct in a Single Column

You can also count distinct values in just one column:

df.select(countDistinct("Department").alias("Distinct_Departments")).show()

Output:

+--------------------+
|Distinct_Departments|
+--------------------+
|                   4|
+--------------------+

There are 4 distinct departments: IT, Sales, Accounts, and Management.

Count Distinct for Multiple Columns Individually

To get distinct counts for each column separately, apply countDistinct() to each column within a single select():

from pyspark.sql.functions import countDistinct

df.select(
    countDistinct("Emp_Name").alias("Distinct_Names"),
    countDistinct("Department").alias("Distinct_Depts"),
    countDistinct("Salary").alias("Distinct_Salaries"),
).show()

Output:

+--------------+--------------+-----------------+
|Distinct_Names|Distinct_Depts|Distinct_Salaries|
+--------------+--------------+-----------------+
|             7|             4|                5|
+--------------+--------------+-----------------+

Method 3: Using SQL Queries

If you prefer SQL syntax, you can register your DataFrame as a temporary view and run standard SQL COUNT(DISTINCT ...) queries.

Setup

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("distinct_count").getOrCreate()

data = [
    ("Ram", "IT", 44, 80000),
    ("Shyam", "Sales", 45, 70000),
    ("Jiya", "Sales", 30, 60000),
    ("Maria", "Accounts", 29, 65000),
    ("Ram", "IT", 38, 80000),
    ("John", "Management", 35, 80000),
    ("Shyam", "Sales", 45, 70000),    # duplicate
    ("Kumar", "Sales", 27, 70000),
    ("Maria", "Accounts", 32, 65000),
    ("Ria", "Management", 32, 65000),
]

columns = ["Emp_Name", "Department", "Age", "Salary"]
df = spark.createDataFrame(data, columns)

df.createOrReplaceTempView("employees")

Count All Distinct Rows

spark.sql("""
    SELECT COUNT(DISTINCT Emp_Name, Department, Age, Salary) AS Distinct_Count
    FROM employees
""").show()

Output:

+--------------+
|Distinct_Count|
+--------------+
|             9|
+--------------+

Count Distinct in Specific Columns

spark.sql("""
    SELECT COUNT(DISTINCT Emp_Name, Salary) AS Distinct_Name_Salary
    FROM employees
""").show()

Output:

+--------------------+
|Distinct_Name_Salary|
+--------------------+
|                   7|
+--------------------+

Count Distinct per Group

SQL makes it easy to count distinct values within groups:

spark.sql("""
    SELECT Department,
           COUNT(DISTINCT Emp_Name) AS Unique_Employees
    FROM employees
    GROUP BY Department
    ORDER BY Unique_Employees DESC
""").show()

Output:

+----------+----------------+
|Department|Unique_Employees|
+----------+----------------+
|     Sales|               3|
|  Accounts|               1|
|        IT|               1|
|Management|               2|
+----------+----------------+

Common Mistake: Confusing distinct() with dropDuplicates()

Both distinct() and dropDuplicates() remove duplicate rows, but dropDuplicates() allows you to specify which columns to consider for deduplication - which distinct() does not.

Using distinct() - considers ALL columns:

data = [("Ram", "IT", 44, 80000), ("Ram", "IT", 38, 80000)]
df = spark.createDataFrame(data, ["Name", "Dept", "Age", "Salary"])

print("distinct() count:", df.distinct().count())  # 2

Using dropDuplicates() - considers only specified columns:

print("dropDuplicates count:", df.dropDuplicates(["Name", "Dept"]).count())  # 1

Comparison of Methods

Method Scope Returns Best For
distinct().count() All columns Single integer Quick total distinct row count
countDistinct(cols) Specified columns DataFrame with one row Distinct count on specific columns
SQL COUNT(DISTINCT) Specified columns DataFrame with one row SQL-familiar workflows, grouped counts
dropDuplicates(cols).count() Specified columns Single integer Subset-based distinct count

Summary

PySpark offers flexible ways to count distinct values depending on your needs:

  • Use distinct().count() for a quick count of unique rows across all columns.
  • Use countDistinct() when you need distinct counts for specific columns or want the result as a DataFrame column.
  • Use SQL queries with COUNT(DISTINCT ...) when working with temporary views or when you need GROUP BY functionality.
  • Use dropDuplicates(subset).count() when you need to deduplicate based on a subset of columns before counting.

Remember to use .alias() to give readable names to aggregated columns, and be mindful of the difference between distinct() (all columns) and dropDuplicates() (configurable columns) to avoid unexpected results.

Navigation