In PySpark, row_number, rank, and dense_rank window functions are used for ranking and ordering data within a window partition. Data analysts commonly use these functions to assign ranks or row numbers to rows within a group.
In this article, you’ll learn what these functions are, how they work, how they differ, use cases, and practical examples.
Key Points-
row_number()adds a unique sequential number to each row within a partition, even if multiple rows have the same values.rank()assigns the same rank to tied rows but skips the subsequent ranks, creating gaps in the ranking.dense_rank()assigns the same rank to ties but does not skip any subsequent ranks—ranking remains continuous.- You can use these functions with a Window specification, which defines how to partition and order the data.
- You must use
Window.partitionBy()andWindow.orderBy()to define the logic for each partition and row sorting. row_number()is best when you need unique numbering, regardless of duplicate values in the ordering column.rank()is helpful when you want to assign equal rank to duplicates but need to respect the gap for fairness.dense_rank()is ideal for continuous rankings where you don't want gaps in case of duplicates.- If you omit
partitionBy(), the functions operate on the entire DataFrame as a single group. - The order defined in
orderBy()plays a crucial role; it determines how ranks or row numbers are assigned.
<a id="h-row-number-in-pyspark"></a>
row_number() in PySpark
The row_number() function returns a unique numerical rank to each row within a specified window or partition of a DataFrame. If two rows have the same values, they will get different row numbers(ranks).
<a id="h-syntax"></a>
Syntax
The following is the syntax of the row_number() function.
# Syntax of the row_number()
from pyspark.sql.functions import row_number
pyspark.sql.functions.row_number()
<a id="h-parameters"></a>
Parameters
- It does not take any parameters directly.
- Requires a Window specification defined using
Window.partitionBy()andWindow.orderBy().
<a id="h-usage"></a>
Usage
Let's create a sample DataFrame containing employee data, which we'll use throughout this article to demonstrate the examples of PySpark rank() vs dense_rank() vs row_number().
# Craete DataFrame
# Imports
from pyspark.sql import SparkSession
from pyspark.sql.functions import row_number, rank, dense_rank, col
from pyspark.sql.window import Window
# Create SparkSession
spark = SparkSession.builder.appName("aistudylabs").getOrCreate()
# Sample data
data = [
("James", "Sales", 3000),
("Michael", "Sales", 4600),
("Robert", "Sales", 4100),
("Maria", "Finance", 3000),
("Scott", "Finance", 3300),
("Jen", "Finance", 3900),
("Jeff", "Marketing", 3000),
("Kumar", "Marketing", 2000),
("Saif", "Sales", 4100)
]
columns = ["employee_name", "department", "salary"]
df = spark.createDataFrame(data, columns)
df.show()
Yields below the output.
You can use the row_number() function over a window specification to generate a column of unique row numbers, based on partitioning and ordering. Even if multiple rows have the same values, it still generates distinct row numbers based on the specified order.
# Add a new column using row_number() over the specified window
# Applying partitionBy() and orderBy()
window_spec = Window.partitionBy("department").orderBy(col("salary"))
result_df = df.withColumn("row_number", row_number().over(window_spec))
# Show the result
result_df.show()
Yields below the output.
rank() in PySpark
The rank() function assigns a rank to each row within a certain partition based on the specified order.
If multiple rows have the same value (tie), they receive the same rank, and the next rank(s) are skipped.
<a id="h-syntax-0"></a>
Syntax
The following is the syntax of the rank() function.
# Syntax of the rank()
from pyspark.sql.functions import rank
pyspark.sql.functions.rank()
<a id="h-parameters-0"></a>
Parameters
- Like
row_number(), it doesn’t take parameters. - It depends on a window specification for partitioning and ordering.
<a id="h-usage-0"></a>
Usage
You can use the rank() function to add a new column based on a specified window. For that, you can apply the rank() function to a certain partition based on the specified order. It assigns a unique rank to each row, gives the same rank to duplicate values, and skips subsequent ranks accordingly.
# Add a new column using rank() over the specified window
# Applying partitionBy() and orderBy()
window_spec = Window.partitionBy("department").orderBy(col("salary"))
result_df = df.withColumn("rank", rank().over(window_spec))
# Show the result
result_df.show()
# Output:
# +-------------+----------+------+----+
# |employee_name|department|salary|rank|
# +-------------+----------+------+----+
# | Maria| Finance| 3000| 1|
# | Scott| Finance| 3300| 2|
# | Jen| Finance| 3900| 3|
# | Kumar| Marketing| 2000| 1|
# | Jeff| Marketing| 3000| 2|
# | James| Sales| 3000| 1|
# | Robert| Sales| 4100| 2|
# | Saif| Sales| 4100| 2|
# | Michael| Sales| 4600| 4|
# +-------------+----------+------+----+
<a id="h-dense-rank-in-pyspark"></a>
dense_rank() in PySpark
The dense_rank() function also assigns ranks, but unlike rank(), it does not skip any ranks when there are duplicates. It provides continuous ranking.
<a id="h-syntax-1"></a>
Syntax
The following is the syntax of the dense_rank() function.
# Syntax of the dense_rank()
from pyspark.sql.functions import dense_rank
pyspark.sql.functions.dense_rank()
<a id="h-parameters-1"></a>
Parameters
- It has no direct parameters.
- Needs a Window specification to work.
<a id="h-usage-1"></a>
Usage
You can use the dense_rank() function to add a new column based on a specified window. For that, you can apply the dense_rank() function to a specific partition using a defined ordering. This function assigns a rank to each row based on the order. If two or more rows have the same value, they get the same rank, but unlike rank(), dense_rank() does not skip the next numbers in the ranking.
# Add a new column using dense_rank() over the specified window
# Applying partitionBy() and orderBy()
window_spec = Window.partitionBy("department").orderBy(col("salary"))
result_df = df.withColumn("dense_rank", dense_rank().over(window_spec))
# Show the result
result_df.show()
# Output:
# +-------------+----------+------+----------+
# |employee_name|department|salary|dense_rank|
# +-------------+----------+------+----------+
# | Maria| Finance| 3000| 1|
# | Scott| Finance| 3300| 2|
# | Jen| Finance| 3900| 3|
# | Kumar| Marketing| 2000| 1|
# | Jeff| Marketing| 3000| 2|
# | James| Sales| 3000| 1|
# | Robert| Sales| 4100| 2|
# | Saif| Sales| 4100| 2|
# | Michael| Sales| 4600| 3|
# +-------------+----------+------+----------+
<a id="h-pyspark-rank-vs-dense-rank-vs-row-number-by-partition"></a>
PySpark rank() vs dense_rank() vs row_number() by Partition
This example highlights the differences between the rank(), dense_rank(), and row_number() functions in PySpark with a window partition. We’ll apply these functions to a DataFrame to add columns that represent row rankings based on the specified partition.
# Complete example of Difference between PySpark rank(), dense_rank(),and row_number()
# Applying partitionBy() and orderBy()
window_spec = Window.partitionBy("department").orderBy(col("salary"))
result_df = df.withColumn("row_number", row_number().over(window_spec))\
.withColumn("rank", rank().over(window_spec)) \
.withColumn("dense_rank", dense_rank().over(window_spec))
# Show the result
result_df.show()
Yields below the output.
<a id="h-pyspark-rank-vs-dense-rank-vs-row-number-without-partition"></a>
PySpark rank() vs dense_rank() vs row_number() without Partition
This example shows the differences between the rank(), dense_rank(), and row_number() functions in PySpark without using a partition. We define a window specification based on only the ordering criteria, so all three functions treat the entire DataFrame as a single group and add row numbers based on the global order of the specified column.
# Difference between PySpark rank(), dense_rank(),and row_number() without partition
# Define a global window ordered by salary
window_spec = Window.orderBy(col("salary"))
result_df = df.withColumn("row_number", row_number().over(window_spec))\
.withColumn("rank", rank().over(window_spec)) \
.withColumn("dense_rank", dense_rank().over(window_spec))
result_df.show()
# Output:
# +-------------+----------+------+----------+----+----------+
# |employee_name|department|salary|row_number|rank|dense_rank|
# +-------------+----------+------+----------+----+----------+
# | Kumar| Marketing| 2000| 1| 1| 1|
# | James| Sales| 3000| 2| 2| 2|
# | Maria| Finance| 3000| 3| 2| 2|
# | Jeff| Marketing| 3000| 4| 2| 2|
# | Scott| Finance| 3300| 5| 5| 3|
# | Jen| Finance| 3900| 6| 6| 4|
# | Robert| Sales| 4100| 7| 7| 5|
# | Saif| Sales| 4100| 8| 7| 5|
# | Michael| Sales| 4600| 9| 9| 6|
# +-------------+----------+------+----------+----+----------+
<a id="h-frequently-asked-questions-of-pyspark-rank-vs-dense-rank-vs-row-number"></a>
Frequently Asked Questions of PySpark rank() vs dense_rank() vs row_number()
row_number(): Assigns a unique number to each row, even if there are ties.
Use dense_rank() if you want continuous rankings without any gaps when values are tied.
All tied rows get the same rank, and the next rank(s) are skipped.
Example: Ranks may go like 1, 2, 2, 4 (rank 3 is skipped).
Yes, it is very important.
The ranking functions (row_number(), rank(), dense_rank()) use the order you set in the Window.orderBy() to decide how rows are ranked.
If you change the sort order or column, the ranking will change too.
You can simply skip the partitionBy() part in the window specification.
When you do that, the whole DataFrame is treated as one group (one big partition), and the functions rank all rows based on the orderBy() column.
Rows with NULL values in the ordering column are treated as lowest by default and will appear first in ascending order.
<a id="h-conclusion"></a>
Conclusion
In this article, we explored the differences between three essential PySpark window functions: row_number(), rank(), and dense_rank(). These functions are extremely useful in data analysis for assigning ranks or unique row numbers within grouped data (partitions). We covered their syntax, use cases, and behavior when dealing with ties, as well as practical examples with sample employee data. Understanding how each of these functions behaves helps you choose the right one depending on your ranking needs—whether you want strict uniqueness, allow ties, or prefer continuous ranking without gaps.
Happy Learning!!
<a id="h-related-articles"></a>
Related Articles
- PySpark Select Top N Rows From Each Group
- PySpark Find Maximum Row per Group in DataFrame
- PySpark Select First Row of Each Group?
- PySpark Window Functions
- Pyspark Select Distinct Rows
- PySpark Distinct to Drop Duplicate Rows
- Explain PySpark repartition() vs partitionBy() functions
- How to calculate the cumulative sum in PySpark?