Built with Tutify.io

    AIStudyLabs

    Explain row_number() Function in pyspark

    Explain row_number() Function in pyspark

    In PySpark, the row_number() window function in PySpark is used to return a new column containing a unique sequential number to each row based on a specified order. The pyspark.sql.window module provides functions like row_number(), rank(), and dense_rank() to add ranking-based columns to a DataFrame.

    By using Window.orderBy() with or without partitionBy(), you can generate row numbers either within each partition or globally across the entire DataFrame. In this article, I will explain what the row_number() function is and how to use it to add a new column with unique row numbers to a DataFrame based on a specified order.

    Key Points

    <a id="h-pyspark-row-number-window-function"></a>

    PySpark row_number() Window Function

    The row_number() function assigns a unique sequential number to each row within a specified partition based on the defined order. If you don’t apply a partition in the window specification, it uses global ordering and assigns row numbers across the entire DataFrame based on that order.

    <a id="h-syntax-of-the-row-number-function"></a>

    Syntax of the row_number() Function

    The following is the syntax of the row_number() function.

    
# Syntax of the row_number() function
pyspark.sql.functions.row_number()

    <a id="h-parameters"></a>

    Parameters

    <a id="h-return-value"></a>

    Return Value

    It returns a new column with a unique row number for each row based on the specified order.

    <a id="h-pyspark-add-row-number-without-partition"></a>

    PySpark Add Row Number without Partition

    You can use the row_number() function to add a row number as a new column to a DataFrame without applying any partitioning. To do this by using this function along with Window.orderBy()function, specifying only the ordering criteria. In this case, row_number() considers the entire DataFrame as a single group and adds row numbers based on the global order of the specified column.

    
# Add row number using row_number() without partitioning
# Imports 
from pyspark.sql import SparkSession
from pyspark.sql.functions import row_number
from pyspark.sql.window import Window
from pyspark.sql.functions import col

# Start Spark session
spark = SparkSession.builder.appName("RowNumberExample").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)
]
# Create DataFrame
columns = ["employee_name", "department", "salary"]
df = spark.createDataFrame(data, columns)
df.show()

# Define a global window ordered by salary
window_spec = Window.orderBy(col("salary"))

# Add row_number column
result_df = df.withColumn("row_number", row_number().over(window_spec))

result_df.show()

    Yields below the output.

    The above code has returned the final DataFrame with an additional row_number column. Rows are numbered globally from 1, starting with the lowest salary.

    <a id="h-add-row-number-to-dataframe-by-partition"></a>

    Add Row Number to DataFrame by Partition

    Alternatively, you can add a row number as a new column to a DataFrame by applying both partitioning and ordering. To do so, you can use the row_number() function together with a window specification that includes Window.partitionBy() and Window.orderBy().

    In this case, row_number() assigns row numbers within each partition group defined by a specific column. Within each group, rows are ordered based on the specified column, and numbering begins at 1 for each partition.

    
# Add Row Number to DataFrame by Partition
# Imports
from pyspark.sql import Window
from pyspark.sql.functions import row_number

# Applying partitionBy() and orderBy()
window_spec = Window.partitionBy("department").orderBy("salary")

# Add a new column "row_number" using row_number() over the specified window
result_df = df.withColumn("row_number", row_number().over(window_spec))

# Show the result
result_df.show()

# Output:
# +-------------+----------+------+----------+
# |employee_name|department|salary|row_number|
# +-------------+----------+------+----------+
# |        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|         3|
# |      Michael|     Sales|  4600|         4|
# +-------------+----------+------+----------+

    <a id="h-pyspark-add-row-number-over-partition-multiple-columns"></a>

    PySpark Add Row Number over Partition Multiple Columns

    To group data by multiple columns, you can specify them in partitionBy(). This is done using the row_number() function together with a window specification that includes both partitionBy() and orderBy(). In this case, row_number() assigns row numbers within each partition group defined by the combination of columns. The rows in each partition are ordered based on the specified sort column, and the row numbering starts from 1 within each group.

    
# Add Row Number over Partition multiple columns
window_spec = Window.partitionBy("department", "salary").orderBy("employee_name")
df.withColumn("row_number", row_number().over(window_spec)).show()

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

    <a id="h-pyspark-window-partition-and-order-by-multiple-columns-in-descending-order"></a>

    PySpark Window Partition and Order by Multiple Columns in Descending Order

    To sort rows by multiple columns in descending order within each group, you can specify them in orderBy() using the desc() function. This is done using the row_number() function together with a window specification that includes partitionBy() and orderBy().

    In this case, row_number() assigns row numbers within each partition group defined by a specific column. Multiple columns order the rows in each partition in descending order, and the numbering starts from 1 within each group.

    
# window partition by order by multiple columns in descending
from pyspark.sql.functions import desc

window_spec = Window.partitionBy("department").orderBy(desc("salary"), desc("employee_name"))
df.withColumn("row_number", row_number().over(window_spec)).show()

# OUtput: 
# +-------------+----------+------+----------+
# |employee_name|department|salary|row_number|
# +-------------+----------+------+----------+
# |          Jen|   Finance|  3900|         1|
# |        Scott|   Finance|  3300|         2|
# |        Maria|   Finance|  3000|         3|
# |         Jeff| Marketing|  3000|         1|
# |        Kumar| Marketing|  2000|         2|
# |      Michael|     Sales|  4600|         1|
# |         Saif|     Sales|  4100|         2|
# |       Robert|     Sales|  4100|         3|
# |        James|     Sales|  3000|         4|
# +-------------+----------+------+----------+

    <a id="h-pyspark-row-number-without-window-function"></a>

    PySpark Row Number without Window Function

    You can add a row number as a new column to a DataFrame without using the row_number() or window functions by using the RDD API. For that, you can convert the DataFrame to an RDD and using the zipWithIndex() method, that assigns a unique, sequential index to each row. In this case, the DataFrame is treated as a whole, and row numbers are assigned globally based on the original order of rows in the RDD.

    
# Add row number without Window Function
indexed_df = df.rdd.zipWithIndex().toDF()
indexed_df.show()

    <a id="h-pyspark-rank-vs-dense-rank-vs-row-number"></a>

    PySpark rank() vs dense_rank() vs row_number()

    This example shows the differences between the rank(), dense_rank(), and row_number() functionsin 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()

# Output:
# +-------------+----------+------+----------+----+----------+
# |employee_name|department|salary|row_number|rank|dense_rank|
# +-------------+----------+------+----------+----+----------+
# |        Maria|   Finance|  3000|         1|   1|         1|
# |        Scott|   Finance|  3300|         2|   2|         2|
# |          Jen|   Finance|  3900|         3|   3|         3|
# |        Kumar| Marketing|  2000|         1|   1|         1|
# |         Jeff| Marketing|  3000|         2|   2|         2|
# |        James|     Sales|  3000|         1|   1|         1|
# |       Robert|     Sales|  4100|         2|   2|         2|
# |         Saif|     Sales|  4100|         3|   2|         2|
# |      Michael|     Sales|  4600|         4|   4|         3|
# +-------------+----------+------+----------+----+----------+

    <a id="h-frequently-asked-questions-of-pyspark-row-number"></a>

    Frequently Asked Questions of PySpark row_number

    The row_number() function assigns a unique sequential number to each row based on a specified sort order.

    You can use row_number() without partitionBy() to generate a global row number across the entire DataFrame.

    When you omit partitionBy(), the function treats the entire DataFrame as a single partition, and numbering is applied globally based on the orderBy() condition.

    Yes, orderBy() is mandatory with row_number() to determine the sequence in which row numbers are assigned.

    You wrap the row_number().over(Window.orderBy(...)) inside a withColumn() method to add the row number column.

    row_number() assigns sequential numbers based on a specified order, whereas monotonically_increasing_id() generates unique but non-sequential IDs.

    You can't use row_number() without a window specification. It always requires a window defined using the Window class, at minimum with an orderBy() clause to determine the order in which row numbers are assigned—even if you don’t partition the data.

    <a id="h-conclusion"></a>

    Conclusion

    In this article, you have learned how to use the PySpark row_number() function to assign unique sequential numbers to rows based on a specified order. We explored how to apply row_number() without partitions to generate global row numbers across the entire DataFrame, and how to use it with partitions to number rows within specific groups. You also saw examples of using multiple columns in partitionBy() and orderBy(), as well as sorting in descending order. Additionally, we compared row_number() with rank() and dense_rank(), and covered alternative approaches like zipWithIndex() for adding row numbers without window functions. By understanding these techniques, you can effectively add row numbers for various analytical and ranking tasks in PySpark.

    Happy Learning!!

    <a id="h-references"></a>

    References

    <a id="h-related-articles"></a>

    Related Articles