Sorting, distinct, union & sample
Apache Spark · DataFrames
Theory
orderBy (alias sort) sorts a DataFrame by one or more columns; use F.desc('amount') or col.desc_nulls_last() to control direction and where NULLs go. A global sort needs a shuffle with range partitioning, so sort once at the end, or use sortWithinPartitions when only per-partition order matters. distinct() removes rows that are identical in every column, while dropDuplicates(['city']) keeps one row per value of the chosen columns.
union stacks two DataFrames by column position, so mismatched column orders silently put cities under customer names; unionByName matches columns by name and, with allowMissingColumns=True, fills columns missing on one side with NULL. Both keep duplicates, like SQL UNION ALL; add distinct() for SQL UNION behaviour. sample(fraction=0.1, seed=42) returns roughly 10% of rows, not exactly 10%, and the seed makes it repeatable. For example, jan.unionByName(feb) of 4 and 2 orders gives 6 rows, 5 after distinct().
Why we use it
These are everyday DataFrame operations and a common source of quiet bugs: union by position mixing up columns, expecting union to remove duplicates, or expecting sample to return an exact count. Interviewers often ask union vs unionByName.
When to use it
Use unionByName for combining monthly or source files whose column order may differ. Use dropDuplicates with key columns for dedup, distinct for whole-row duplicates. Sort only for final output or top-N (orderBy + limit is optimised). Use sample for quick exploration and limit for a fixed number of rows.
How it works
- Two monthly DataFrames to combine
- Same columns in the same order? Yes: union() is safe. No: unionByName(allowMissingColumns=True).
- Should duplicate rows be removed? Yes: distinct() or dropDuplicates(keys). No: Keep all rows.
- Sort with orderBy and explicit NULL placement
- Write or show the final result
Syntax
df.orderBy(F.col("amount").desc_nulls_last(), "city")
df.sort("city", F.desc("amount")); df.sortWithinPartitions("city")
df.distinct(); df.dropDuplicates(["city"])
a.union(b) # by position, keeps duplicates
a.unionByName(b, allowMissingColumns=True)
df.sample(fraction=0.1, seed=42); df.limit(10)Example
from pyspark.sql import SparkSession, functions as F
spark = (SparkSession.builder.master("local[2]")
.config("spark.ui.enabled", "false")
.getOrCreate())
jan = spark.createDataFrame([
(101, "Asha", "Jaipur", 55000.0), (102, "Ravi", "Pune", 30000.0),
(103, "Meera", "Jaipur", None), (104, "Karan", "Delhi", 52000.0),
], "order_id INT, customer STRING, city STRING, amount DOUBLE")
feb = spark.createDataFrame([("Delhi", 104, 52000.0, "Karan"), ("Pune", 105, 18000.0, "Priya")],
"city STRING, order_id INT, amount DOUBLE, customer STRING")
# 1) Sorting: desc + where NULLs go
jan.orderBy(F.col("amount").desc_nulls_last()).show()
print(jan.sort("city", F.desc("amount")).select("order_id").collect())
# 2) distinct (whole row) vs dropDuplicates (chosen columns)
print("distinct cities:", jan.select("city").distinct().count())
print("first row per city:", jan.dropDuplicates(["city"]).count())
# 3) union matches columns BY POSITION; unionByName matches BY NAME
both = jan.unionByName(feb)
both.orderBy("order_id").show()
print("union by position, first city value:",
jan.select("city").union(feb.select("customer")).orderBy("city").first()[0])
print("union keeps duplicates:", both.count(), "| after distinct():", both.distinct().count())
# 4) unionByName with missing columns fills NULL
extra = spark.createDataFrame([(106, "Jaipur")], "order_id INT, city STRING")
print(jan.unionByName(extra, allowMissingColumns=True).filter("order_id = 106").collect())
# 5) sample: a random fraction (approximate, repeatable with a seed)
big = spark.range(1000)
print("sample ~10%:", big.sample(fraction=0.1, seed=42).count())
print("same seed, same rows:",
big.sample(fraction=0.1, seed=42).collect() == big.sample(fraction=0.1, seed=42).collect())
# Output:
# +--------+--------+------+-------+
# |order_id|customer| city| amount|
# +--------+--------+------+-------+
# | 101| Asha|Jaipur|55000.0|
# | 104| Karan| Delhi|52000.0|
# | 102| Ravi| Pune|30000.0|
# | 103| Meera|Jaipur| NULL|
# +--------+--------+------+-------+
#
# [Row(order_id=104), Row(order_id=101), Row(order_id=103), Row(order_id=102)]
# distinct cities: 3
# first row per city: 3
# +--------+--------+------+-------+
# |order_id|customer| city| amount|
# +--------+--------+------+-------+
# | 101| Asha|Jaipur|55000.0|
# | 102| Ravi| Pune|30000.0|
# | 103| Meera|Jaipur| NULL|
# | 104| Karan| Delhi|52000.0|
# | 104| Karan| Delhi|52000.0|
# | 105| Priya| Pune|18000.0|
# +--------+--------+------+-------+
#
# union by position, first city value: Delhi
# union keeps duplicates: 6 | after distinct(): 5
# [Row(order_id=106, customer=None, city='Jaipur', amount=None)]
# sample ~10%: 95
# same seed, same rows: True
Key points
- orderBy/sort is a wide transformation: range-partitioned shuffle
- desc_nulls_last / asc_nulls_first control where NULLs go
- union matches by POSITION; unionByName matches by NAME
- Both unions keep duplicates (UNION ALL); add distinct() if needed
- sample(fraction) is approximate; pass fraction= and seed= by keyword
- distinct = all columns; dropDuplicates(cols) = chosen columns