Skip to content
Tutorial Reference

Python PySpark: How to Select a Range of Rows from a DataFrame in PySpark

Learn how to filter PySpark DataFrames by numeric, date, or string ranges using filter(), where(), SQL BETWEEN, and window functions for row position selection.

When working with large datasets in PySpark, you'll often need to filter rows based on specific ranges - for example, selecting records where a score falls between two values, dates within a time period, or IDs within a certain range. PySpark provides several methods to accomplish this, including filter(), where(), and SQL expressions.

In this guide, you'll learn three approaches to select a range of rows from a PySpark DataFrame, understand the differences between them, and see practical examples for different data types.

Creating a Sample DataFrame

Let's start by creating a PySpark DataFrame to use throughout all examples:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('row_selection').getOrCreate()

data = [
    ["1", "Sravan", "Vignan", 67, 89],
    ["2", "Ojaswi", "VVIT", 78, 89],
    ["3", "Rohith", "VVIT", 100, 80],
    ["4", "Sridevi", "Vignan", 78, 80],
    ["5", "Sravan", "Vignan", 89, 98],
    ["6", "Gnanesh", "IIT", 94, 98]
]

columns = ['ID', 'Name', 'College', 'Subject1', 'Subject2']

df = spark.createDataFrame(data, columns)
df.show()

Output:

+---+-------+-------+--------+--------+
| ID|   Name|College|Subject1|Subject2|
+---+-------+-------+--------+--------+
|  1| Sravan| Vignan|      67|      89|
|  2| Ojaswi|   VVIT|      78|      89|
|  3| Rohith|   VVIT|     100|      80|
|  4|Sridevi| Vignan|      78|      80|
|  5| Sravan| Vignan|      89|      98|
|  6|Gnanesh|    IIT|      94|      98|
+---+-------+-------+--------+--------+

Method 1: Using filter()

The filter() method selects rows based on a given condition. Combined with the between() function, it provides a clean way to select rows within a range.

Selecting a Numeric Range

result = df.filter(df.Subject1.between(70, 90))
result.show()

Output:

+---+-------+-------+--------+--------+
| ID|   Name|College|Subject1|Subject2|
+---+-------+-------+--------+--------+
|  2| Ojaswi|   VVIT|      78|      89|
|  4|Sridevi| Vignan|      78|      80|
|  5| Sravan| Vignan|      89|      98|
+---+-------+-------+--------+--------+

Using Comparison Operators

Instead of between(), you can use explicit comparison operators for more control:

result = df.filter((df.Subject1 >= 70) & (df.Subject1 <= 90))
result.show()

Output:

+---+-------+-------+--------+--------+
| ID|   Name|College|Subject1|Subject2|
+---+-------+-------+--------+--------+
|  2| Ojaswi|   VVIT|      78|      89|
|  4|Sridevi| Vignan|      78|      80|
|  5| Sravan| Vignan|      89|      98|
+---+-------+-------+--------+--------+

Combining Multiple Conditions

result = df.filter(
    (df.Subject1.between(70, 90)) & (df.Subject2 > 85)
)
result.show()

Output:

+---+------+-------+--------+--------+
| ID|  Name|College|Subject1|Subject2|
+---+------+-------+--------+--------+
|  2|Ojaswi|   VVIT|      78|      89|
|  5|Sravan| Vignan|      89|      98|
+---+------+-------+--------+--------+

Method 2: Using where()

The where() method is functionally identical to filter() - they are aliases for the same operation. Use whichever reads better in your context:

result = df.where(df.Subject1.between(85, 100))
result.show()

Output:

+---+-------+-------+--------+--------+
| ID|   Name|College|Subject1|Subject2|
+---+-------+-------+--------+--------+
|  3| Rohith|   VVIT|     100|      80|
|  5| Sravan| Vignan|      89|      98|
|  6|Gnanesh|    IIT|      94|      98|
+---+-------+-------+--------+--------+

Filtering on String Columns

You can also use between() on string columns. It performs lexicographic (alphabetical) comparison:

result = df.where(df.College.between("IIT", "VVIT"))
result.show()

Output:

+---+-------+-------+--------+--------+
| ID|   Name|College|Subject1|Subject2|
+---+-------+-------+--------+--------+
|  2| Ojaswi|   VVIT|      78|      89|
|  3| Rohith|   VVIT|     100|      80|
|  6|Gnanesh|    IIT|      94|      98|
+---+-------+-------+--------+--------+

Method 3: Using SQL Expressions

PySpark allows you to write SQL queries directly against DataFrames by registering them as temporary views. This is especially useful for teams familiar with SQL:

Setting Up a Temporary View

df.createOrReplaceTempView("students")

Selecting a Range with SQL

result = spark.sql("""
    SELECT * FROM students 
    WHERE Subject1 BETWEEN 70 AND 90
""")
result.show()

Output:

+---+-------+-------+--------+--------+
| ID|   Name|College|Subject1|Subject2|
+---+-------+-------+--------+--------+
|  2| Ojaswi|   VVIT|      78|      89|
|  4|Sridevi| Vignan|      78|      80|
|  5| Sravan| Vignan|      89|      98|
+---+-------+-------+--------+--------+

Complex SQL Queries

result = spark.sql("""
    SELECT * FROM students 
    WHERE Subject1 BETWEEN 70 AND 95
    AND College = 'Vignan'
    ORDER BY Subject1 DESC
""")
result.show()

Output:

+---+-------+-------+--------+--------+
| ID|   Name|College|Subject1|Subject2|
+---+-------+-------+--------+--------+
|  5| Sravan| Vignan|      89|      98|
|  4|Sridevi| Vignan|      78|      80|
+---+-------+-------+--------+--------+

Selecting by ID Range

result = spark.sql("""
    SELECT * FROM students 
    WHERE ID BETWEEN 2 AND 4
""")
result.show()

Output:

+---+-------+-------+--------+--------+
| ID|   Name|College|Subject1|Subject2|
+---+-------+-------+--------+--------+
|  2| Ojaswi|   VVIT|      78|      89|
|  3| Rohith|   VVIT|     100|      80|
|  4|Sridevi| Vignan|      78|      80|
+---+-------+-------+--------+--------+

Selecting Rows by Position (Row Number)

Unlike Pandas, PySpark DataFrames don't have a built-in positional index. To select rows by position (e.g., rows 2 through 4), you need to add a row number column:

from pyspark.sql.window import Window
from pyspark.sql import functions as F

window = Window.orderBy("ID")
df_with_row_num = df.withColumn("row_num", F.row_number().over(window))

result = df_with_row_num.filter(
    F.col("row_num").between(2, 4)
).drop("row_num")

result.show()

Output:

+---+-------+-------+--------+--------+
| ID|   Name|College|Subject1|Subject2|
+---+-------+-------+--------+--------+
|  2| Ojaswi|   VVIT|      78|      89|
|  3| Rohith|   VVIT|     100|      80|
|  4|Sridevi| Vignan|      78|      80|
+---+-------+-------+--------+--------+

Using limit() and head() for Top N Rows

If you simply need the first N rows without a specific condition:

top_3 = df.limit(3)
top_3.show()

Output:

+---+------+-------+--------+--------+
| ID|  Name|College|Subject1|Subject2|
+---+------+-------+--------+--------+
|  1|Sravan| Vignan|      67|      89|
|  2|Ojaswi|   VVIT|      78|      89|
|  3|Rohith|   VVIT|     100|      80|
+---+------+-------+--------+--------+

Comparison of Methods

Method Syntax Best For
filter() df.filter(condition) Pythonic PySpark code
where() df.where(condition) SQL-familiar users
SQL expression spark.sql("SELECT ... WHERE ... BETWEEN") Complex queries, team collaboration
row_number() Window function + filter Positional row selection
limit() df.limit(n) Selecting the first N rows

Summary

To select a range of rows from a PySpark DataFrame:

  • Use filter() or where() with the between() function for range-based filtering - they're functionally identical aliases.
  • Use SQL expressions with BETWEEN when writing complex queries or when your team prefers SQL syntax.
  • Use row_number() with a Window function when you need to select rows by their position rather than by column values.
  • Always use &, |, ~ operators (not and, or, not) when combining multiple conditions, and wrap each condition in parentheses.
Navigation