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

  1. Two monthly DataFrames to combine
  2. Same columns in the same order? Yes: union() is safe. No: unionByName(allowMissingColumns=True).
  3. Should duplicate rows be removed? Yes: distinct() or dropDuplicates(keys). No: Keep all rows.
  4. Sort with orderBy and explicit NULL placement
  5. 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

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic