Are you looking to find how to order records of PySpark Dataframe into Azure Databricks cloud or maybe you are looking for a solution, to sort records of a Dataframe based on single or multiple columns in PySpark Databricks? If you are looking for any of these problem solutions, then you have landed on the correct page. I will also show you what and how to use PySpark to sort out records of a Dataframe using orderBy() function in Azure Databricks. I will explain it by taking a practical example. So don’t waste time let’s start step by step guide to understanding how to filter record or row using PySpark Dataframe.
In this blog, I will teach you the following with practical examples:
- Syntax of orderBy()
- Order data ascendingly
- Order data descendingly
- Order based on multiple columns
- Order by considering null values
orderBy() method is used to sort records of Dataframe based on column specified as either ascending or descending order in PySpark Azure Databricks.
Syntax: dataframe_name.orderBy(column_name)
Contents
- 1 What is the syntax of the orderBy() function in PySpark Azure Databricks?
- 2 Create a simple DataFrame
- 3 How to order records ascendingly in PySpark Azure Databricks?
- 4 How to order records descendingly in PySpark Azure Databricks?
- 5 How to order records on multiple columns in PySpark Azure Databricks?
- 6 How to order records with null values at the top or at bottom of DataFrame in PySpark Azure Databricks?
- 7 When should you use the PySpark orderBy() function in Azure Databricks?
- 8 Real World Use Case Scenarios for PySpark DataFrame orderBy() function in Azure Databricks?
- 9 What are the alternatives of the orderBy() function in PySpark Azure Databricks?
- 10 Final Thoughts
What is the syntax of the orderBy() function in PySpark Azure Databricks?
The syntax is as follows:
dataframe_name.orderBy(*cols, ascending)
| Parameter Name | Required | Description |
| *cols (str, list or Column) | Optional | It represents the columns to be considered for sorting. |
| ascending (bool or list) | Optional | By default, ascending=True. If a list is specified, length of the list must equal length of the columns. |
Apache Spark Official documentation link: orderBy()
Create a simple DataFrame
Gentle reminder:
In Databricks,
- sparkSession made available as spark
- sparkContext made available as sc
In case, you want to create it manually, use the below code.
from pyspark.sql.session import SparkSession
spark = SparkSession.builder
.master("local[*]")
.appName("azurelib.com")
.getOrCreate()
sc = spark.sparkContext
a) Create manual PySpark DataFrame
data = [
(1, "Ramman", 25, "20000", "Chennai"),
(2, "Suman", 23, "18000", "Coimbatore"),
(3, "Keerthi", 25, "50000", "Bangalore"),
(4, "Victor", 32, None, "Mumbai"),
(5, "Adithya", 40, "50000", "Noida")
]
df = spark.createDataFrame(data = data, schema = ["id","name","age","salary","city"])
df.printSchema()
df.show(truncate=False)
"""
root
|-- id: long (nullable = true)
|-- name: string (nullable = true)
|-- age: long (nullable = true)
|-- salary: string (nullable = true)
|-- city: string (nullable = true)
+---+-------+---+------+----------+
|id |name |age|salary|city |
+---+-------+---+------+----------+
|1 |Ramman |25 |20000 |Chennai |
|2 |Suman |23 |18000 |Coimbatore|
|3 |Keerthi|25 |50000 |Bangalore |
|4 |Victor |32 |null |Mumbai |
|5 |Adithya|40 |50000 |Noida |
+---+-------+---+------+----------+
"""
b) Creating a DataFrame by reading files
Download and use the below source file.
# replace the file_paths with the source file location which you have downloaded.
df_2 = spark.read.format("csv").option("header", True).load(file_path)
df_2.printSchema()
"""
root
|-- id: long (nullable = true)
|-- name: string (nullable = true)
|-- age: long (nullable = true)
|-- salary: string (nullable = true)
|-- city: string (nullable = true)
"""
Note: Here, I will be using the manually created DataFrame.
How to order records ascendingly in PySpark Azure Databricks?
The PySpark function orderBy() is used to sort records from a DataFrame based on specific columns.
Order by Examples in pyspark Azure Databricks:
In the below example, we are trying to sort employee’s records based on salary ascendingly.
from pyspark.sql.functions import col, asc
# Method 1
df.orderBy("salary", ascending = True).show()
# Method 2
df.orderBy(asc("salary")).show()
# Method 3
df.orderBy(col("salary").asc()).show()
# The above code sample gives the same output as mentioned below.
"""
Output:
+---+-------+---+------+----------+
| id| name|age|salary| city|
+---+-------+---+------+----------+
| 4| Victor| 32| null| Mumbai|
| 2| Suman| 23| 18000|Coimbatore|
| 1| Ramman| 25| 20000| Chennai|
| 3|Keerthi| 25| 50000| Bangalore|
| 5|Adithya| 40| 50000| Noida|
+---+-------+---+------+----------+
"""
How to order records descendingly in PySpark Azure Databricks?
Order by descending Example in pyspark Azure Databricks:
In the below example, we are trying to sort employee’s records based on salary in descending order.
from pyspark.sql.functions import col, desc
# Method 1
df.orderBy("salary", ascending = False).show()
# Method 2
df.orderBy( desc("salary") ).show()
# Method 3
df.orderBy( col("salary").desc() ).show()
# The above code sample gives the same output as mentioned below.
"""
Output:
+---+-------+---+------+----------+
| id| name|age|salary| city|
+---+-------+---+------+----------+
| 3|Keerthi| 25| 50000| Bangalore|
| 5|Adithya| 40| 50000| Noida|
| 1| Ramman| 25| 20000| Chennai|
| 2| Suman| 23| 18000|Coimbatore|
| 4| Victor| 32| null| Mumbai|
+---+-------+---+------+----------+
"""
How to order records on multiple columns in PySpark Azure Databricks?
Example:
In the below example, we are trying to sort records on multiple columns.
from pyspark.sql.functions import col
# 1. Order by Ascending based on multiple columns
df.orderBy("salary", "city").show()
"""
Output:
+---+-------+---+------+----------+
| id| name|age|salary| city|
+---+-------+---+------+----------+
| 4| Victor| 32| null| Mumbai|
| 2| Suman| 23| 18000|Coimbatore|
| 1| Ramman| 25| 20000| Chennai|
| 3|Keerthi| 25| 50000| Bangalore|
| 5|Adithya| 40| 50000| Noida|
+---+-------+---+------+----------+
"""
# 2. Order by Ascending or Descending based on multiple columns
df.orderBy(col("salary").asc(), col("city").desc()).show()
"""
Output:
+---+-------+---+------+----------+
| id| name|age|salary| city|
+---+-------+---+------+----------+
| 4| Victor| 32| null| Mumbai|
| 2| Suman| 23| 18000|Coimbatore|
| 1| Ramman| 25| 20000| Chennai|
| 5|Adithya| 40| 50000| Noida|
| 3|Keerthi| 25| 50000| Bangalore|
+---+-------+---+------+----------+
"""
How to order records with null values at the top or at bottom of DataFrame in PySpark Azure Databricks?
Example:
In the below example, sort salary column null records at top and bottom.
from pyspark.sql.functions import col
# 1. Order column by Ascending, but null values at top
df.orderBy(col("salary").asc_nulls_first()).show()
"""
Output:
+---+-------+---+------+----------+
| id| name|age|salary| city|
+---+-------+---+------+----------+
| 4| Victor| 32| null| Mumbai|
| 2| Suman| 23| 18000|Coimbatore|
| 1| Ramman| 25| 20000| Chennai|
| 5|Adithya| 40| 50000| Noida|
| 3|Keerthi| 25| 50000| Bangalore|
+---+-------+---+------+----------+
"""
# 2. Order column by Ascending, but null values at last
df.orderBy(col("salary").asc_nulls_last()).show()
"""
Output:
+---+-------+---+------+----------+
| id| name|age|salary| city|
+---+-------+---+------+----------+
| 2| Suman| 23| 18000|Coimbatore|
| 1| Ramman| 25| 20000| Chennai|
| 3|Keerthi| 25| 50000| Bangalore|
| 5|Adithya| 40| 50000| Noida|
| 4| Victor| 32| null| Mumbai|
+---+-------+---+------+----------+
"""
I have attached the complete code used in this blog in notebook format to this GitHub link. You can download and import this notebook in databricks, jupyter notebook, etc.
When should you use the PySpark orderBy() function in Azure Databricks?
You can use the orderBy() function to sort records based on single or multiple columns.
Real World Use Case Scenarios for PySpark DataFrame orderBy() function in Azure Databricks?
- Assume that we have used a case scenario to write the result in an ordered manner instead of in random order. In this, we can use the orderby() function before calling the DataFrame write function.
What are the alternatives of the orderBy() function in PySpark Azure Databricks?
You can use the sort() function to arrange records from a DataFrame based on columns, both these functions operate exactly the same.
Final Thoughts
In this article, we have learned about the PySpark orderBy() method to sort records or rows of DataFrame based on single or multiple columns in Azure Databricks along with the examples explained clearly. I have also covered different scenarios with practical examples that could be possible. I hope the information that was provided helped in gaining knowledge.
Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.
- For Azure Study material Join Telegram group : Telegram group link:
- Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
- Azure Tutorial Videos: Videos Link
- Azure Databricks Lesson 1
- Azure Databricks Lesson 2
- Azure Databricks Lesson 3
- Azure Databricks Lesson 4
- Azure Databricks Lesson 5
- Azure Databricks Lesson 6
- Azure Databricks Lesson 7
As a big data engineer, I design and build scalable data processing systems and integrate them with various data sources and databases. I have a strong background in Python and am proficient in big data technologies such as Hadoop, Hive, Spark, Databricks, and Azure. My interest lies in working with large datasets and deriving actionable insights to support informed business decisions.
