7.1 Row Operations: Filtering, Sorting, and Deduplication

While column operations govern vertical projections and schema adjustments, row operations dictate horizontal data manipulation: filtering subsets of records, ordering partitions, truncating results, and eliminating duplicate entries. Because Apache Spark executes across distributed partitions, understanding the mechanical difference between local partition operations and cluster-wide shuffle transformations is paramount for performance and accuracy.

---

Filtering Rows: filter() and where()

In PySpark, filter() and where() are exact semantic synonyms. The where() method is simply an alias provided for SQL parity, while filter() reflects functional programming paradigms. Both invoke the exact same underlying Catalyst Filter logical operator.

# Both expressions produce identical Catalyst execution plans
df_filtered1 = df.filter(col("age") >= 21)
df_filtered2 = df.where(col("age") >= 21)

Specifying Filter Conditions

Filter predicates can be defined using either SQL expression strings or PySpark Column expressions:

# 1. SQL string syntax
df_active = df.filter("age >= 21 AND department = 'Sales'")

# 2. Programmatic Column syntax with bitwise operators
df_active = df.filter((col("age") >= 21) & (col("department") == "Sales"))

The Mandatory Bitwise Operator and Precedence Trap

One of the most frequently tested topics on developer exams is the correct usage of boolean operators in PySpark column expressions:

# INCORRECT: Raises ValueError
# df.filter(df.age > 21 and df.department == "Sales")

# INCORRECT: Precedence bug
# df.filter(df.age > 21 & df.department == "Sales")

# CORRECT: Bitwise & with explicit parentheses
df_correct = df.filter((df.age > 21) & (df.department == "Sales"))

Why Does and Fail?

In Python, logical keywords (and, or, not) cannot be overloaded by classes. Python attempts to coerce the operands into primitive boolean values by calling bool(). In PySpark, calling bool() on an unresolved distributed Column object raises an explicit error: ValueError: Cannot convert column into bool: please use '&' for 'and', '|' for 'or', '~' for 'not'.

Why Are Parentheses Mandatory?

In Python's operator precedence table, bitwise operators (&, |, ~) have higher precedence than comparison operators (>, <, ==, !=). If you omit parentheses: df.age > 21 & df.department == "Sales" is parsed by the Python interpreter as: df.age > (21 & df.department) == "Sales" This causes unexpected type errors and fails query planning. Always wrap every individual condition in parentheses: (condition_a) & (condition_b).

---

Sorting Rows: sort() and orderBy()

Just like filter() and where(), sort() and orderBy() are exact synonyms in PySpark. Both methods perform a total global ordering of the distributed dataset.

from pyspark.sql.functions import col

# Basic ascending sort (default)
df_sorted = df.orderBy("department", "salary")

# Mixed ascending and descending sort
df_sorted = df.sort(
    col("department").asc(),
    col("salary").desc()
)

Default Null Ordering and Explicit Null Positioning

When sorting columns that contain null values, Spark follows strict SQL ordering conventions:

• Ascending (asc): null values are positioned FIRST by default (NULLS FIRST).
• Descending (desc): null values are positioned LAST by default (NULLS LAST).

To explicitly override this default behavior, PySpark provides dedicated null ordering functions:

# Force nulls to the end in an ascending sort
df_nulls_last = df.orderBy(col("salary").asc_nulls_last())

# Force nulls to the top in a descending sort
df_nulls_first = df.orderBy(col("salary").desc_nulls_first())

Global Sort vs. sortWithinPartitions()

A critical architectural distinction exists between global sorting and partition-level sorting:

GLOBAL SORT (df.sort() / df.orderBy())
[Partition 1] --\
[Partition 2] ---> [SHUFFLE (RangePartitioner)] ---> [Partition A: 0-99]
[Partition 3] --/                                    [Partition B: 100-199]
                                                     [Partition C: 200+]
(Total global ordering across all partitions; requires expensive network shuffle)

LOCAL SORT (df.sortWithinPartitions())
[Partition 1] ---> [Sort Local Data Only] ---> [Partition 1 (Sorted)]
[Partition 2] ---> [Sort Local Data Only] ---> [Partition 2 (Sorted)]
(Narrow transformation; zero network shuffle; no global ordering across partitions)

• sort() / orderBy() (Wide Transformation): Samples data to construct a RangePartitioner, shuffling data across the entire cluster so that the ordered partition ranges follow the requested ordering; tied values are not strictly smaller. This is computationally expensive.
• sortWithinPartitions() (Narrow Transformation): Orders rows strictly within their existing local partitions without transferring bytes across the network. If downstream operations (such as file writing or local windowing) only require partition-level sorted order, sortWithinPartitions() provides dramatic performance gains.

---

Row Slicing and Limiting: df.limit(n)

The df.limit(n) transformation restricts the resulting DataFrame to at most n records:

df_top10 = df.orderBy(col("salary").desc()).limit(10)

Internal Execution Mechanics of limit()

To execute limit(n) efficiently without collecting all cluster data to the driver, Spark may use local/global limit operators, a collect-limit path, or an optimized top-N operator, depending on the consumer. A common logical pattern is:

1. LocalLimit(n) (Narrow): Each executor runs a local limit, selecting at most n records from its local partition.
2. GlobalLimit(n) (Single Partition): The pre-filtered candidate rows from all executors are shuffled to a single partition on one executor, which merges them and extracts the final n records.

---

Deduplicating Data: distinct() vs. dropDuplicates()

Eliminating duplicate records is a common requirement in data processing pipelines. PySpark provides two methods with distinct scopes:

1. Whole-Row Deduplication: df.distinct()

distinct() returns a new DataFrame containing only unique rows across all columns in the schema:

# Evaluates uniqueness across all columns
df_unique_rows = df.distinct()

Internally, distinct() is a wide transformation that shuffles all rows using a hash of all column values to group identical records together on the same executor.

2. Targeted Deduplication: df.dropDuplicates()

dropDuplicates() provides fine-grained control by accepting an optional subset parameter specifying a list of column names:

# If no subset is supplied, dropDuplicates() is identical to distinct()
df_unique_all = df.dropDuplicates()

# Deduplicate based on specific key columns only
df_unique_users = df.dropDuplicates(subset=["user_id"])

The Non-Deterministic Row Selection Gotcha

When using dropDuplicates(subset=["user_id"]) on a DataFrame with additional columns (such as timestamp or transaction_id), multiple records may match the subset key. Spark does not guarantee which matching row will be preserved.

To deterministically preserve a specific record (e.g., the latest transaction per user), use a Window specification with row_number():

from pyspark.sql.window import Window
from pyspark.sql.functions import row_number

window_spec = Window.partitionBy("user_id").orderBy(
    col("transaction_time").desc(), col("transaction_id").desc()
)

df_latest_per_user = df.withColumn("row_num", row_number().over(window_spec)) \
                       .filter(col("row_num") == 1) \
                       .drop("row_num")

Deduplication and Sorting Performance Matrix

Mastering these row operations allows developers to craft precise, performant ETL transformations while avoiding unexpected shuffle bottlenecks.