The PySpark you write most, and what each line does to a real table on Spark 4.2.
Run on PySpark 4.2.0
Pick a stop, then a task
from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F
spark = SparkSession.builder.appName("orders").getOrCreate()
print(spark.version)On Databricks the session already exists as spark; skip the builder. Spark docs
What it prints
4.2.0
rows = [
(1001, "C01", "placed", 25.5, "2026-10-01 09:15", "2026-10-01 09:15"),
(1002, "C02", "placed", 12.0, "2026-10-01 10:02", "2026-10-01 10:02"),
(1001, "C01", "shipped", 25.5, "2026-10-01 09:15", "2026-10-02 08:00"),
(1003, "C03", "placed", None, "2026-10-02 11:30", "2026-10-02 11:30"),
(1004, "C01", "placed", 40.0, "2026-10-02 12:45", "2026-10-02 12:45"),
(1002, "C02", "cancelled", 12.0, "2026-10-01 10:02", "2026-10-03 09:00"),
(1005, "C09", "placed", 8.75, "2026-10-03 14:10", "2026-10-03 14:10"),
(1004, "C01", "placed", 40.0, "2026-10-02 12:45", "2026-10-02 12:45"),
]
orders = spark.createDataFrame(
rows,
"order_id int, customer_id string, status string, "
"amount double, ordered_at string, updated_at string",
).withColumns(
{
"ordered_at": F.to_timestamp("ordered_at"),
"updated_at": F.to_timestamp("updated_at"),
}
)
customers = spark.createDataFrame(
[
("C01", "Asha", "Mumbai"),
("C02", "Ben", "London"),
("C03", "Chen", "Singapore"),
("C04", "Dana", "Berlin"),
],
"customer_id string, name string, city string",
)Every snippet below uses these 2 tables. Orders 1001 and 1002 appear twice because their status changed, 1004 is an exact duplicate, 1003 has no amount and customer C09 has no customer row. A schema string fixes the types, so Spark does not guess them. Spark docs
orders.printSchema()No shuffleEach machine works on its own rows; nothing moves between them.
orders.show(3, truncate=False)show() prints 20 rows and cuts each value at 20 characters unless you pass truncate=False. Spark docs
print(orders.count())No shuffleEach machine works on its own rows; nothing moves between them.
orders.where(F.col("amount").isNull()).show()orders.where(F.col("amount") == None).show()
Use isNull or isNotNull to test for a missing value. Spark docs
Common mistake: Comparing with None gives null, never true, so the filter keeps no rows and raises no error.
1 shuffleSpark moves rows between machines once so matching rows meet. On big data this is the slow part.
orders.select(
[
F.count_if(F.col(c).isNull()).alias(c)
for c in orders.columns
]
).show()1 shuffleSpark moves rows between machines once so matching rows meet. On big data this is the slow part.
orders.dropDuplicates().show()distinct() does the same. A row goes only when every column matches another row. Spark docs
1 shuffleSpark moves rows between machines once so matching rows meet. On big data this is the slow part.
latest = Window.partitionBy("order_id").orderBy(
F.col("updated_at").desc()
)
current = (
orders.withColumn("n", F.row_number().over(latest))
.where("n = 1")
.drop("n")
)
current.show()orders.groupBy("order_id").agg(
F.max("updated_at").alias("updated_at")
).show()
Number the rows of each key newest first, then keep number 1. Spark docs
Common mistake: A group by keeps only the key and the totals, so every other column of the latest row is lost.
1 shuffleSpark moves rows between machines once so matching rows meet. On big data this is the slow part.
orders.dropDuplicates(["order_id"]).show()orders.dropDuplicates("order_id").show()
Which row survives for each key is not guaranteed. To keep the latest, use the snippet before. Spark docs
Common mistake: The key columns must be a list, even for one column.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.dropna(subset=["amount"]).show()orders.dropna(how="all").show()
Without subset, a missing value in any column drops the row. Spark docs
Common mistake: how="all" drops a row only when every column is missing, so a row with one missing amount stays.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.fillna({"amount": 0.0}).show()orders.fillna("0").show()
fillna only touches columns whose type matches the value, so a number never fills a text column. Spark docs
Common mistake: A text value fills only text columns. amount is a number, so it stays missing and nothing warns you.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.withColumn(
"amount_or_zero", F.coalesce("amount", F.lit(0.0))
).show()orders.withColumn(
"amount_or_zero", F.coalesce("amount", 0.0)
).show()
F.coalesce picks a value; DataFrame.coalesce(n) is unrelated and lowers the number of partitions. Spark docs
Common mistake: coalesce takes columns. A plain number must be wrapped in F.lit.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.select("order_id", "status", "amount").show()orders.select("order_id", "amount").withColumn(
"year", F.year("ordered_at")
).show()
Common mistake: After a select, the columns you left out are gone, so a later step that needs one of them fails.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.withColumn(
"amount_with_tax", F.round(F.col("amount") * 1.2, 2)
).show()orders.withColumn(
"amount_with_tax", F.round(F.col("amount") * 1.2, 2)
)
orders.show()
A missing amount gives a missing result, because any sum with null is null. Spark docs
Common mistake: A DataFrame never changes in place. withColumn returns a new one, and this code throws it away, so orders has no new column.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.withColumns(
{"year": F.year("ordered_at"), "is_big": F.col("amount") > 20}
).show()One withColumns call is cheaper to plan than a long chain of withColumn calls. Spark docs
No shuffleEach machine works on its own rows; nothing moves between them.
orders.withColumnRenamed("amount", "total").show()orders.withColumnRenamed("amont", "total").show()
Common mistake: Renaming a column that does not exist does nothing and raises no error, so a typo goes unnoticed.
orders.withColumn(
"order_id", F.col("order_id").cast("string")
).printSchema()No shuffleEach machine works on its own rows; nothing moves between them.
spark.createDataFrame(
[("12.5",), ("abc",)], "amount string"
).select(
F.col("amount").try_cast("double").alias("amount")
).show()spark.createDataFrame(
[("12.5",), ("abc",)], "amount string"
).select(F.col("amount").cast("double")).show()
try_cast gives null where the text is not a number. Spark docs
Common mistake: Spark 4 turns on ANSI mode by default, so cast stops the whole job on the first value that is not a number.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.drop("ordered_at", "updated_at").show()orders.drop(["ordered_at", "updated_at"]).show()
Dropping a column that does not exist does nothing and raises no error. Spark docs
Common mistake: drop takes each column as its own argument, not a list. Unpack a list with *, as in drop(*names).
No shuffleEach machine works on its own rows; nothing moves between them.
orders.withColumn("currency", F.lit("EUR")).show()orders.withColumn("currency", "EUR").show()
Common mistake: withColumn needs a column. A plain value must be wrapped in F.lit.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.withColumn(
"size",
F.when(F.col("amount") >= 30, "large")
.when(F.col("amount") >= 10, "medium")
.otherwise("small"),
).show()orders.withColumn(
"size", F.when(F.col("amount") >= 30, "large")
).show()
A null amount matches no condition and falls to otherwise; test isNull first if it needs its own label. Spark docs
Common mistake: Without otherwise, every row that matches no condition gets null.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.where(F.col("amount") > 20).show()where and filter are the same method. A row with a missing amount fails every comparison, so it never passes. Spark docs
No shuffleEach machine works on its own rows; nothing moves between them.
orders.where(
(F.col("status") == "placed") & (F.col("amount") < 20)
).show()orders.where(
(F.col("status") == "placed") and (F.col("amount") < 20)
).show()
Use & for and, | for or and ~ for not, with each condition in brackets. Spark docs
Common mistake: Python's and, or and not cannot work on columns, so Spark raises an error.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.where(F.col("customer_id").isin("C01", "C02")).show()orders.where(F.col("amount").isin(None, 8.75)).show()
Common mistake: isin never matches a missing value, even with None in the list. Add | F.col("amount").isNull() to keep those rows.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.where(
F.col("status").startswith("ca")
| F.col("status").contains("hip")
).show()No shuffleEach machine works on its own rows; nothing moves between them.
orders.where(
F.to_date("ordered_at").between("2026-10-02", "2026-10-03")
).show()orders.where(
F.col("ordered_at").between("2026-10-02", "2026-10-03")
).show()
between includes both ends. Turning the timestamp into a date first keeps every order placed on the last day. Spark docs
Common mistake: On a timestamp, "2026-10-03" means midnight at the start of that day, so the orders placed later on 3 October are left out.
1 shuffleSpark moves rows between machines once so matching rows meet. On big data this is the slow part.
orders.orderBy(F.col("amount").desc_nulls_last()).show()Ascending sorts put nulls first and descending sorts put them last unless you say otherwise. Spark docs
No shuffleEach machine works on its own rows; nothing moves between them.
orders.orderBy(F.col("amount").desc_nulls_last()).limit(2).show()orders.limit(2).orderBy(F.col("amount").desc_nulls_last()).show()
Sort first, then limit. Without a sort, which rows limit keeps is not guaranteed. Spark docs
Common mistake: limit first takes any 2 rows and only then sorts them, so these are not the 2 largest orders.
2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
orders.join(customers, "customer_id").show()orders.join(
customers, orders.customer_id == customers.customer_id
).select("customer_id").show()
An inner join keeps only the rows with a match, so customer C09's order goes. Joining on the column name keeps one customer_id column. Spark docs
Common mistake: Joining on an expression keeps both customer_id columns, so naming one afterwards is ambiguous and fails.
2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
orders.join(customers, "customer_id", "left").show()orders.join(customers, "customer_id", "left").where(
F.col("city") != "London"
).show()
Rows with no match keep their place, with null in the columns from the right. Spark docs
Common mistake: A filter on a right-hand column after a left join also drops the rows with no match, because null != "London" is never true. Filter customers before the join instead.
2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
customers.join(orders, "customer_id", "left_anti").show()left_anti returns the left rows that have no match, and only the left columns. Spark docs
2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
people = customers.withColumnRenamed("customer_id", "id")
orders.join(people, orders.customer_id == people.id).show()orders.join(F.broadcast(customers), "customer_id").explain()Spark already broadcasts tables under 10 MB (spark.sql.autoBroadcastJoinThreshold); the hint forces it for larger ones. Spark docs
2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
orders.groupBy("status").count().orderBy("status").show()2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
orders.groupBy("customer_id").agg(
F.count("*").alias("rows"),
F.count("amount").alias("with_amount"),
F.round(F.sum("amount"), 2).alias("total"),
F.max("ordered_at").alias("last_order"),
).orderBy("customer_id").show()count("*") counts every row; count("amount") skips the rows where amount is null. Spark docs
2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
orders.select(
F.count_distinct("order_id").alias("orders"),
F.count_distinct("customer_id").alias("customers"),
).show()1 shuffleSpark moves rows between machines once so matching rows meet. On big data this is the slow part.
orders.groupBy("customer_id").agg(
F.sum("amount").alias("total")
).where(F.col("total") > 30).show()orders.where(F.col("total") > 30).groupBy("customer_id").agg(
F.sum("amount").alias("total")
).show()
A where after agg works like SQL's HAVING. Spark docs
Common mistake: The total only exists after agg, so a filter on it before the group by cannot find the column.
3 shufflesSpark moves rows between machines 3 times so matching rows meet. On big data this is the slow part.
orders.groupBy("customer_id").pivot(
"status", ["placed", "shipped", "cancelled"]
).count().orderBy("customer_id").show()Listing the values saves Spark a pass over the data to find them. Spark docs
2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
by_customer = Window.partitionBy("customer_id").orderBy(
"ordered_at"
)
current.withColumn("n", F.row_number().over(by_customer)).show()current.withColumn(
"n", F.row_number().over(Window.partitionBy("customer_id"))
).show()
current is the orders table after the Clean stop: the latest row of each order. Spark docs
Common mistake: row_number needs an order to count in, so a window without orderBy raises an error.
2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
running = (
Window.partitionBy("customer_id")
.orderBy("ordered_at")
.rowsBetween(Window.unboundedPreceding, Window.currentRow)
)
current.withColumn(
"running_total", F.sum("amount").over(running)
).show()current.withColumn(
"running_total",
F.sum("amount").over(Window.partitionBy("customer_id")),
).show()
Without rowsBetween, rows that tie on the order column all get the same total. Spark docs
Common mistake: Without orderBy the window is the whole group, so every row gets the group's full total instead of a running one.
2 shufflesSpark moves rows between machines 2 times so matching rows meet. On big data this is the slow part.
by_time = Window.partitionBy("customer_id").orderBy("ordered_at")
current.withColumn(
"previous_amount", F.lag("amount").over(by_time)
).show()current.withColumn(
"previous_amount",
F.lag("amount").over(Window.orderBy("ordered_at")),
).show()
The first row of each group has no previous row, so it gets null. Spark docs
Common mistake: Without partitionBy the previous row can belong to another customer, and every row moves to one task.
1 shuffleSpark moves rows between machines once so matching rows meet. On big data this is the slow part.
by_amount = Window.orderBy(F.col("amount").desc())
orders.select(
"order_id",
"amount",
F.rank().over(by_amount).alias("rank"),
F.dense_rank().over(by_amount).alias("dense_rank"),
).show()rank leaves a gap after a tie and dense_rank does not. A window with no partitionBy moves every row to one task; fine here, slow on a big table. Spark docs
No shuffleEach machine works on its own rows; nothing moves between them.
spark.createDataFrame(
[("03/10/2026 14:10",)], "raw string"
).select(
F.to_timestamp("raw", "dd/MM/yyyy HH:mm").alias("ts"),
F.to_date("raw", "dd/MM/yyyy HH:mm").alias("day"),
).show()spark.createDataFrame(
[("03/10/2026 14:10",)], "raw string"
).select(F.to_timestamp("raw", "MM-dd-yyyy")).show()
Use try_to_timestamp to get null instead of an error where some text does not fit the pattern. Spark docs
Common mistake: In Spark 4, text that does not fit the pattern stops the job instead of giving null.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.withColumn(
"day", F.date_format("ordered_at", "EEE d MMM yyyy")
).show()orders.withColumn(
"day", F.date_format("ordered_at", "YYYY-ww")
).show()
Common mistake: Y (week-based year) and w (week of year) are patterns Spark 3 and later refuse. Use yyyy for the year, and weekofyear for the week.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.withColumns(
{
"days_to_update": F.datediff("updated_at", "ordered_at"),
"week_later": F.date_add("ordered_at", 7),
}
).show()orders.withColumn(
"days_to_update", F.datediff("ordered_at", "updated_at")
).show()
datediff takes the later date first. date_add returns a date, without the time. Spark docs
Common mistake: With the earlier date first, every gap comes out negative, and nothing warns you.
No shuffleEach machine works on its own rows; nothing moves between them.
orders.withColumns(
{
"week": F.date_trunc("week", "ordered_at"),
"month": F.trunc("ordered_at", "month"),
}
).show()orders.withColumn(
"month", F.trunc("month", "ordered_at")
).show()
Weeks start on Monday. date_trunc returns a timestamp, trunc returns a date, and they take their arguments in opposite orders. Spark docs
Common mistake: trunc takes the date first and the unit second, the other way round from date_trunc, so Spark looks for a column called month.
No shuffleEach machine works on its own rows; nothing moves between them.
spark.createDataFrame([(" Asha ",)], "name string").select(
F.upper(F.trim("name")).alias("name")
).show()spark.createDataFrame([(" Asha ",)], "name string").select(
F.upper("name").alias("name")
).show()
Common mistake: upper changes only the case. The spaces stay, so " ASHA " still does not match "ASHA".
No shuffleEach machine works on its own rows; nothing moves between them.
customers.select(
F.concat_ws(", ", "name", "city").alias("label")
).show()concat returns null if any part is null; concat_ws skips the null parts. Spark docs
No shuffleEach machine works on its own rows; nothing moves between them.
spark.createDataFrame([("a.b.c",)], "path string").select(
F.split("path", r"\.").alias("parts")
).show()spark.createDataFrame([("a.b.c",)], "path string").select(
F.split("path", ".").alias("parts")
).show()
Common mistake: The separator is a regular expression, and a plain dot matches every character, so nothing but empty strings is left. Escape it as \.
No shuffleEach machine works on its own rows; nothing moves between them.
spark.createDataFrame(
[("order-1001-EU",), ("refund",)], "ref string"
).select(
"ref",
F.regexp_extract("ref", r"order-(\d+)", 1).alias("order_id"),
).show()No match gives an empty string, not null. Spark docs
No shuffleEach machine works on its own rows; nothing moves between them.
spark.createDataFrame(
[("+44 20-7946-0958",)], "phone string"
).select(
F.regexp_replace("phone", r"[^0-9]", "").alias("digits")
).show()orders.write.mode("overwrite").option("header", True).csv(
"out/orders_csv"
)The default mode is errorifexists, so a second run fails until you choose overwrite or append. Spark docs
raw = (
spark.read.option("header", True)
.schema(
"order_id int, customer_id string, status string, "
"amount double, ordered_at timestamp, updated_at timestamp"
)
.csv("out/orders_csv")
)
raw.printSchema()Without a schema every column is a string; inferSchema guesses the types but reads the files an extra time. Spark docs
orders.write.mode("overwrite").partitionBy("status").parquet(
"out/orders_parquet"
)placed = spark.read.parquet("out/orders_parquet").where(
F.col("status") == "placed"
)
print(placed.count())A filter on the partition column reads only that folder, so it costs a fraction of a full read. Spark docs
No shuffleEach machine works on its own rows; nothing moves between them.
orders.limit(2).write.mode("overwrite").json("out/orders_json")
spark.read.json("out/orders_json").select(
"order_id", "amount"
).show()spark.read.json expects one JSON object per line; a file that is one big array needs option("multiLine", True). Spark docs
Every snippet, every common mistake and every pipeline was run on Spark 4.2 against the same 8 orders, so the rows you see are the rows you will get. The cost is read from Spark's own plan: a shuffle is when Spark moves rows between machines, the slow part of a job on big data. Print the page to get every snippet on paper.
Sources