# PySpark cheat sheet: examples you can run, common mistakes and what each costs (Spark 4.2)

The PySpark you write most, and what each line does to a real table on Spark 4.2.

Ran on PySpark 4.2.0. Page: https://tecyfy.com/data-engineering/pyspark-cheat-sheet

## Start

### How to start a Spark session in PySpark

```python
from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("orders").getOrCreate()
print(spark.version)
```

Prints:

```text
4.2.0
```

On Databricks the session already exists as spark; skip the builder.

### How to make a DataFrame from rows, with a schema in PySpark

```python
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.

### How to see the columns and their types in PySpark

```python
orders.printSchema()
```

Prints:

```text
root
 |-- order_id: integer (nullable = true)
 |-- customer_id: string (nullable = true)
 |-- status: string (nullable = true)
 |-- amount: double (nullable = true)
 |-- ordered_at: timestamp (nullable = true)
 |-- updated_at: timestamp (nullable = true)
```

### How to show rows without cutting long values in PySpark

```python
orders.show(3, truncate=False)
```

Prints:

```text
+--------+-----------+-------+------+-------------------+-------------------+
|order_id|customer_id|status |amount|ordered_at         |updated_at         |
+--------+-----------+-------+------+-------------------+-------------------+
|1001    |C01        |placed |25.5  |2026-10-01 09:15:00|2026-10-01 09:15:00|
|1002    |C02        |placed |12.0  |2026-10-01 10:02:00|2026-10-01 10:02:00|
|1001    |C01        |shipped|25.5  |2026-10-01 09:15:00|2026-10-02 08:00:00|
+--------+-----------+-------+------+-------------------+-------------------+
only showing top 3 rows
```

show() prints 20 rows and cuts each value at 20 characters unless you pass truncate=False.

Cost: no shuffle.

### How to count the rows in PySpark

```python
print(orders.count())
```

Prints:

```text
8
```

## Clean

### How to find the rows with a missing value in PySpark

```python
orders.where(F.col("amount").isNull()).show()
```

Prints:

```text
+--------+-----------+------+------+-------------------+-------------------+
|order_id|customer_id|status|amount|         ordered_at|         updated_at|
+--------+-----------+------+------+-------------------+-------------------+
|    1003|        C03|placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|
+--------+-----------+------+------+-------------------+-------------------+
```

Use isNull or isNotNull to test for a missing value.

Cost: no shuffle.

Common mistake: Comparing with None gives null, never true, so the filter keeps no rows and raises no error.

```python
orders.where(F.col("amount") == None).show()
```

### How to count the missing values in every column in PySpark

```python
orders.select(
    [
        F.count_if(F.col(c).isNull()).alias(c)
        for c in orders.columns
    ]
).show()
```

Prints:

```text
+--------+-----------+------+------+----------+----------+
|order_id|customer_id|status|amount|ordered_at|updated_at|
+--------+-----------+------+------+----------+----------+
|       0|          0|     0|     1|         0|         0|
+--------+-----------+------+------+----------+----------+
```

Cost: 1 shuffle.

### How to drop rows that are exact duplicates in PySpark

```python
orders.dropDuplicates().show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|
+--------+-----------+---------+------+-------------------+-------------------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|
+--------+-----------+---------+------+-------------------+-------------------+
```

distinct() does the same. A row goes only when every column matches another row.

Cost: 1 shuffle.

### How to keep the latest row per key in PySpark

```python
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()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|
+--------+-----------+---------+------+-------------------+-------------------+
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|
+--------+-----------+---------+------+-------------------+-------------------+
```

Number the rows of each key newest first, then keep number 1.

Cost: 1 shuffle.

Common mistake: A group by keeps only the key and the totals, so every other column of the latest row is lost.

```python
orders.groupBy("order_id").agg(
    F.max("updated_at").alias("updated_at")
).show()
```

### How to keep one row per key in PySpark

```python
orders.dropDuplicates(["order_id"]).show()
```

Prints:

```text
+--------+-----------+------+------+-------------------+-------------------+
|order_id|customer_id|status|amount|         ordered_at|         updated_at|
+--------+-----------+------+------+-------------------+-------------------+
|    1001|        C01|placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|
|    1002|        C02|placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|
|    1003|        C03|placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|
|    1004|        C01|placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1005|        C09|placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|
+--------+-----------+------+------+-------------------+-------------------+
```

Which row survives for each key is not guaranteed. To keep the latest, use the snippet before.

Cost: 1 shuffle.

Common mistake: The key columns must be a list, even for one column.

```python
orders.dropDuplicates("order_id").show()
```

Raises: PySparkTypeError: [NOT_EXPECTED_TYPE] Argument `subset` should be list or tuple, got str.

### How to drop rows with a missing value in PySpark

```python
orders.dropna(subset=["amount"]).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|
+--------+-----------+---------+------+-------------------+-------------------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
+--------+-----------+---------+------+-------------------+-------------------+
```

Without subset, a missing value in any column drops the row.

Cost: no shuffle.

Common mistake: how="all" drops a row only when every column is missing, so a row with one missing amount stays.

```python
orders.dropna(how="all").show()
```

### How to fill missing values in PySpark

```python
orders.fillna({"amount": 0.0}).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|
+--------+-----------+---------+------+-------------------+-------------------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|
|    1003|        C03|   placed|   0.0|2026-10-02 11:30:00|2026-10-02 11:30:00|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
+--------+-----------+---------+------+-------------------+-------------------+
```

fillna only touches columns whose type matches the value, so a number never fills a text column.

Cost: no shuffle.

Common mistake: A text value fills only text columns. amount is a number, so it stays missing and nothing warns you.

```python
orders.fillna("0").show()
```

### How to use a fallback when a value is missing in PySpark

```python
orders.withColumn(
    "amount_or_zero", F.coalesce("amount", F.lit(0.0))
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+--------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|amount_or_zero|
+--------+-----------+---------+------+-------------------+-------------------+--------------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|          25.5|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|          12.0|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|          25.5|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|           0.0|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|          40.0|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|          12.0|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|          8.75|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|          40.0|
+--------+-----------+---------+------+-------------------+-------------------+--------------+
```

F.coalesce picks a value; DataFrame.coalesce(n) is unrelated and lowers the number of partitions.

Cost: no shuffle.

Common mistake: coalesce takes columns. A plain number must be wrapped in F.lit.

```python
orders.withColumn(
    "amount_or_zero", F.coalesce("amount", 0.0)
).show()
```

Raises: PySparkTypeError: [NOT_EXPECTED_TYPE] Argument `col` should be Column or str, got float.

## Columns

### How to pick some columns in PySpark

```python
orders.select("order_id", "status", "amount").show()
```

Prints:

```text
+--------+---------+------+
|order_id|   status|amount|
+--------+---------+------+
|    1001|   placed|  25.5|
|    1002|   placed|  12.0|
|    1001|  shipped|  25.5|
|    1003|   placed|  NULL|
|    1004|   placed|  40.0|
|    1002|cancelled|  12.0|
|    1005|   placed|  8.75|
|    1004|   placed|  40.0|
+--------+---------+------+
```

Cost: no shuffle.

Common mistake: After a select, the columns you left out are gone, so a later step that needs one of them fails.

```python
orders.select("order_id", "amount").withColumn(
    "year", F.year("ordered_at")
).show()
```

Raises: AnalysisException: [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column, variable, or function parameter with name `ordered_at` cannot be resolved. Did you mean one of the following? [`order_id`, `amount`]. SQLSTATE: 42703;

### How to add a column worked out from others in PySpark

```python
orders.withColumn(
    "amount_with_tax", F.round(F.col("amount") * 1.2, 2)
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+---------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|amount_with_tax|
+--------+-----------+---------+------+-------------------+-------------------+---------------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|           30.6|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|           14.4|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|           30.6|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|           NULL|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|           48.0|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|           14.4|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|           10.5|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|           48.0|
+--------+-----------+---------+------+-------------------+-------------------+---------------+
```

A missing amount gives a missing result, because any sum with null is null.

Cost: no shuffle.

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.

```python
orders.withColumn(
    "amount_with_tax", F.round(F.col("amount") * 1.2, 2)
)
orders.show()
```

### How to add several columns at once in PySpark

```python
orders.withColumns(
    {"year": F.year("ordered_at"), "is_big": F.col("amount") > 20}
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+----+------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|year|is_big|
+--------+-----------+---------+------+-------------------+-------------------+----+------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|2026|  true|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|2026| false|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|2026|  true|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|2026|  NULL|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|2026|  true|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|2026| false|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|2026| false|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|2026|  true|
+--------+-----------+---------+------+-------------------+-------------------+----+------+
```

One withColumns call is cheaper to plan than a long chain of withColumn calls.

Cost: no shuffle.

### How to rename a column in PySpark

```python
orders.withColumnRenamed("amount", "total").show()
```

Prints:

```text
+--------+-----------+---------+-----+-------------------+-------------------+
|order_id|customer_id|   status|total|         ordered_at|         updated_at|
+--------+-----------+---------+-----+-------------------+-------------------+
|    1001|        C01|   placed| 25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|
|    1002|        C02|   placed| 12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|
|    1001|        C01|  shipped| 25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|
|    1003|        C03|   placed| NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|
|    1004|        C01|   placed| 40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1002|        C02|cancelled| 12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|
|    1005|        C09|   placed| 8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|
|    1004|        C01|   placed| 40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
+--------+-----------+---------+-----+-------------------+-------------------+
```

Cost: no shuffle.

Common mistake: Renaming a column that does not exist does nothing and raises no error, so a typo goes unnoticed.

```python
orders.withColumnRenamed("amont", "total").show()
```

### How to change a column's type in PySpark

```python
orders.withColumn(
    "order_id", F.col("order_id").cast("string")
).printSchema()
```

Prints:

```text
root
 |-- order_id: string (nullable = true)
 |-- customer_id: string (nullable = true)
 |-- status: string (nullable = true)
 |-- amount: double (nullable = true)
 |-- ordered_at: timestamp (nullable = true)
 |-- updated_at: timestamp (nullable = true)
```

### How to cast text that may not be a number in PySpark

```python
spark.createDataFrame(
    [("12.5",), ("abc",)], "amount string"
).select(
    F.col("amount").try_cast("double").alias("amount")
).show()
```

Prints:

```text
+------+
|amount|
+------+
|  12.5|
|  NULL|
+------+
```

try_cast gives null where the text is not a number.

Cost: no shuffle.

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.

```python
spark.createDataFrame(
    [("12.5",), ("abc",)], "amount string"
).select(F.col("amount").cast("double")).show()
```

Raises: NumberFormatException: [CAST_INVALID_INPUT] The value 'abc' of the type "STRING" cannot be cast to "DOUBLE" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. SQLSTATE: 22018

### How to drop columns in PySpark

```python
orders.drop("ordered_at", "updated_at").show()
```

Prints:

```text
+--------+-----------+---------+------+
|order_id|customer_id|   status|amount|
+--------+-----------+---------+------+
|    1001|        C01|   placed|  25.5|
|    1002|        C02|   placed|  12.0|
|    1001|        C01|  shipped|  25.5|
|    1003|        C03|   placed|  NULL|
|    1004|        C01|   placed|  40.0|
|    1002|        C02|cancelled|  12.0|
|    1005|        C09|   placed|  8.75|
|    1004|        C01|   placed|  40.0|
+--------+-----------+---------+------+
```

Dropping a column that does not exist does nothing and raises no error.

Cost: no shuffle.

Common mistake: drop takes each column as its own argument, not a list. Unpack a list with *, as in drop(*names).

```python
orders.drop(["ordered_at", "updated_at"]).show()
```

Raises: PySparkTypeError: [NOT_EXPECTED_TYPE] Argument `col` should be Column or str, got list.

### How to add a column with the same value on every row in PySpark

```python
orders.withColumn("currency", F.lit("EUR")).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+--------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|currency|
+--------+-----------+---------+------+-------------------+-------------------+--------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|     EUR|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|     EUR|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|     EUR|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|     EUR|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|     EUR|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|     EUR|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|     EUR|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|     EUR|
+--------+-----------+---------+------+-------------------+-------------------+--------+
```

Cost: no shuffle.

Common mistake: withColumn needs a column. A plain value must be wrapped in F.lit.

```python
orders.withColumn("currency", "EUR").show()
```

Raises: PySparkTypeError: [NOT_EXPECTED_TYPE] Argument `col` should be Column, got str.

### How to set a value by condition (if, else) in PySpark

```python
orders.withColumn(
    "size",
    F.when(F.col("amount") >= 30, "large")
    .when(F.col("amount") >= 10, "medium")
    .otherwise("small"),
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|  size|
+--------+-----------+---------+------+-------------------+-------------------+------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|medium|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|medium|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|medium|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00| small|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00| large|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|medium|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00| small|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00| large|
+--------+-----------+---------+------+-------------------+-------------------+------+
```

A null amount matches no condition and falls to otherwise; test isNull first if it needs its own label.

Cost: no shuffle.

Common mistake: Without otherwise, every row that matches no condition gets null.

```python
orders.withColumn(
    "size", F.when(F.col("amount") >= 30, "large")
).show()
```

## Filter and sort

### How to keep rows that match a condition in PySpark

```python
orders.where(F.col("amount") > 20).show()
```

Prints:

```text
+--------+-----------+-------+------+-------------------+-------------------+
|order_id|customer_id| status|amount|         ordered_at|         updated_at|
+--------+-----------+-------+------+-------------------+-------------------+
|    1001|        C01| placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|
|    1001|        C01|shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|
|    1004|        C01| placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1004|        C01| placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
+--------+-----------+-------+------+-------------------+-------------------+
```

where and filter are the same method. A row with a missing amount fails every comparison, so it never passes.

Cost: no shuffle.

### How to combine conditions with and, or in PySpark

```python
orders.where(
    (F.col("status") == "placed") & (F.col("amount") < 20)
).show()
```

Prints:

```text
+--------+-----------+------+------+-------------------+-------------------+
|order_id|customer_id|status|amount|         ordered_at|         updated_at|
+--------+-----------+------+------+-------------------+-------------------+
|    1002|        C02|placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|
|    1005|        C09|placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|
+--------+-----------+------+------+-------------------+-------------------+
```

Use & for and, | for or and ~ for not, with each condition in brackets.

Cost: no shuffle.

Common mistake: Python's and, or and not cannot work on columns, so Spark raises an error.

```python
orders.where(
    (F.col("status") == "placed") and (F.col("amount") < 20)
).show()
```

Raises: PySparkValueError: [CANNOT_CONVERT_COLUMN_INTO_BOOL] Cannot convert column into bool: please use '&' for 'and', '|' for 'or', '~' for 'not' when building DataFrame boolean expressions.

### How to keep rows whose value is in a list in PySpark

```python
orders.where(F.col("customer_id").isin("C01", "C02")).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|
+--------+-----------+---------+------+-------------------+-------------------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
+--------+-----------+---------+------+-------------------+-------------------+
```

Cost: no shuffle.

Common mistake: isin never matches a missing value, even with None in the list. Add | F.col("amount").isNull() to keep those rows.

```python
orders.where(F.col("amount").isin(None, 8.75)).show()
```

### How to keep rows where text starts with or contains a value in PySpark

```python
orders.where(
    F.col("status").startswith("ca")
    | F.col("status").contains("hip")
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|
+--------+-----------+---------+------+-------------------+-------------------+
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|
+--------+-----------+---------+------+-------------------+-------------------+
```

Cost: no shuffle.

### How to keep rows between 2 dates in PySpark

```python
orders.where(
    F.to_date("ordered_at").between("2026-10-02", "2026-10-03")
).show()
```

Prints:

```text
+--------+-----------+------+------+-------------------+-------------------+
|order_id|customer_id|status|amount|         ordered_at|         updated_at|
+--------+-----------+------+------+-------------------+-------------------+
|    1003|        C03|placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|
|    1004|        C01|placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1005|        C09|placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|
|    1004|        C01|placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
+--------+-----------+------+------+-------------------+-------------------+
```

between includes both ends. Turning the timestamp into a date first keeps every order placed on the last day.

Cost: no shuffle.

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.

```python
orders.where(
    F.col("ordered_at").between("2026-10-02", "2026-10-03")
).show()
```

### How to sort largest first, with nulls last in PySpark

```python
orders.orderBy(F.col("amount").desc_nulls_last()).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|
+--------+-----------+---------+------+-------------------+-------------------+
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|
+--------+-----------+---------+------+-------------------+-------------------+
```

Ascending sorts put nulls first and descending sorts put them last unless you say otherwise.

Cost: 1 shuffle.

### How to take the top rows in PySpark

```python
orders.orderBy(F.col("amount").desc_nulls_last()).limit(2).show()
```

Prints:

```text
+--------+-----------+------+------+-------------------+-------------------+
|order_id|customer_id|status|amount|         ordered_at|         updated_at|
+--------+-----------+------+------+-------------------+-------------------+
|    1004|        C01|placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
|    1004|        C01|placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|
+--------+-----------+------+------+-------------------+-------------------+
```

Sort first, then limit. Without a sort, which rows limit keeps is not guaranteed.

Cost: no shuffle.

Common mistake: limit first takes any 2 rows and only then sorts them, so these are not the 2 largest orders.

```python
orders.limit(2).orderBy(F.col("amount").desc_nulls_last()).show()
```

## Combine

### How to join 2 DataFrames on a column in PySpark

```python
orders.join(customers, "customer_id").show()
```

Prints:

```text
+-----------+--------+---------+------+-------------------+-------------------+----+---------+
|customer_id|order_id|   status|amount|         ordered_at|         updated_at|name|     city|
+-----------+--------+---------+------+-------------------+-------------------+----+---------+
|        C01|    1001|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|Asha|   Mumbai|
|        C01|    1001|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|Asha|   Mumbai|
|        C01|    1004|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|Asha|   Mumbai|
|        C01|    1004|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|Asha|   Mumbai|
|        C02|    1002|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00| Ben|   London|
|        C02|    1002|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00| Ben|   London|
|        C03|    1003|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|Chen|Singapore|
+-----------+--------+---------+------+-------------------+-------------------+----+---------+
```

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.

Cost: 2 shuffles.

Common mistake: Joining on an expression keeps both customer_id columns, so naming one afterwards is ambiguous and fails.

```python
orders.join(
    customers, orders.customer_id == customers.customer_id
).select("customer_id").show()
```

Raises: AnalysisException: [AMBIGUOUS_REFERENCE] Reference `customer_id` is ambiguous, could be: [`customer_id`, `customer_id`]. SQLSTATE: 42704

### How to keep every row on the left (left join) in PySpark

```python
orders.join(customers, "customer_id", "left").show()
```

Prints:

```text
+-----------+--------+---------+------+-------------------+-------------------+----+---------+
|customer_id|order_id|   status|amount|         ordered_at|         updated_at|name|     city|
+-----------+--------+---------+------+-------------------+-------------------+----+---------+
|        C01|    1001|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|Asha|   Mumbai|
|        C02|    1002|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00| Ben|   London|
|        C01|    1001|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|Asha|   Mumbai|
|        C03|    1003|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|Chen|Singapore|
|        C01|    1004|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|Asha|   Mumbai|
|        C02|    1002|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00| Ben|   London|
|        C09|    1005|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|NULL|     NULL|
|        C01|    1004|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|Asha|   Mumbai|
+-----------+--------+---------+------+-------------------+-------------------+----+---------+
```

Rows with no match keep their place, with null in the columns from the right.

Cost: 2 shuffles.

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.

```python
orders.join(customers, "customer_id", "left").where(
    F.col("city") != "London"
).show()
```

### How to find rows with no match (anti join) in PySpark

```python
customers.join(orders, "customer_id", "left_anti").show()
```

Prints:

```text
+-----------+----+------+
|customer_id|name|  city|
+-----------+----+------+
|        C04|Dana|Berlin|
+-----------+----+------+
```

left_anti returns the left rows that have no match, and only the left columns.

Cost: 2 shuffles.

### How to join on columns with different names in PySpark

```python
people = customers.withColumnRenamed("customer_id", "id")
orders.join(people, orders.customer_id == people.id).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+---+----+---------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at| id|name|     city|
+--------+-----------+---------+------+-------------------+-------------------+---+----+---------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|C01|Asha|   Mumbai|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|C01|Asha|   Mumbai|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|C01|Asha|   Mumbai|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|C01|Asha|   Mumbai|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|C02| Ben|   London|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|C02| Ben|   London|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|C03|Chen|Singapore|
+--------+-----------+---------+------+-------------------+-------------------+---+----+---------+
```

Cost: 2 shuffles.

### How to send a small table to every worker (broadcast join) in PySpark

```python
orders.join(F.broadcast(customers), "customer_id").explain()
```

Prints:

```text
== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=false
+- Project [customer_id#1, order_id#0, status#2, amount#3, ordered_at#6, updated_at#7, name#9, city#10]
   +- BroadcastHashJoin [customer_id#1], [customer_id#8], Inner, BuildRight, false, false
      :- Project [order_id#0, customer_id#1, status#2, amount#3, cast(ordered_at#4 as timestamp) AS ordered_at#6, cast(updated_at#5 as timestamp) AS updated_at#7]
      :  +- Filter isnotnull(customer_id#1)
      :     +- Scan ExistingRDD[order_id#0,customer_id#1,status#2,amount#3,ordered_at#4,updated_at#5]
      +- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, false]),false), [plan_id=329758]
         +- Filter isnotnull(customer_id#8)
            +- Scan ExistingRDD[customer_id#8,name#9,city#10]
```

Spark already broadcasts tables under 10 MB (spark.sql.autoBroadcastJoinThreshold); the hint forces it for larger ones.

## Summarise

### How to count rows per group in PySpark

```python
orders.groupBy("status").count().orderBy("status").show()
```

Prints:

```text
+---------+-----+
|   status|count|
+---------+-----+
|cancelled|    1|
|   placed|    6|
|  shipped|    1|
+---------+-----+
```

Cost: 2 shuffles.

### How to work out several totals per group in PySpark

```python
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()
```

Prints:

```text
+-----------+----+-----------+-----+-------------------+
|customer_id|rows|with_amount|total|         last_order|
+-----------+----+-----------+-----+-------------------+
|        C01|   4|          4|131.0|2026-10-02 12:45:00|
|        C02|   2|          2| 24.0|2026-10-01 10:02:00|
|        C03|   1|          0| NULL|2026-10-02 11:30:00|
|        C09|   1|          1| 8.75|2026-10-03 14:10:00|
+-----------+----+-----------+-----+-------------------+
```

count("*") counts every row; count("amount") skips the rows where amount is null.

Cost: 2 shuffles.

### How to count distinct values in PySpark

```python
orders.select(
    F.count_distinct("order_id").alias("orders"),
    F.count_distinct("customer_id").alias("customers"),
).show()
```

Prints:

```text
+------+---------+
|orders|customers|
+------+---------+
|     5|        4|
+------+---------+
```

Cost: 2 shuffles.

### How to keep groups whose total passes a test in PySpark

```python
orders.groupBy("customer_id").agg(
    F.sum("amount").alias("total")
).where(F.col("total") > 30).show()
```

Prints:

```text
+-----------+-----+
|customer_id|total|
+-----------+-----+
|        C01|131.0|
+-----------+-----+
```

A where after agg works like SQL's HAVING.

Cost: 1 shuffle.

Common mistake: The total only exists after agg, so a filter on it before the group by cannot find the column.

```python
orders.where(F.col("total") > 30).groupBy("customer_id").agg(
    F.sum("amount").alias("total")
).show()
```

Raises: AnalysisException: [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column, variable, or function parameter with name `total` cannot be resolved. Did you mean one of the following? [`status`, `amount`, `order_id`, `ordered_at`, `updated_at`]. SQLSTATE: 42703;

### How to turn values into columns (pivot) in PySpark

```python
orders.groupBy("customer_id").pivot(
    "status", ["placed", "shipped", "cancelled"]
).count().orderBy("customer_id").show()
```

Prints:

```text
+-----------+------+-------+---------+
|customer_id|placed|shipped|cancelled|
+-----------+------+-------+---------+
|        C01|     3|      1|     NULL|
|        C02|     1|   NULL|        1|
|        C03|     1|   NULL|     NULL|
|        C09|     1|   NULL|     NULL|
+-----------+------+-------+---------+
```

Listing the values saves Spark a pass over the data to find them.

Cost: 3 shuffles.

### How to number the rows in each group in PySpark

```python
by_customer = Window.partitionBy("customer_id").orderBy(
    "ordered_at"
)
current.withColumn("n", F.row_number().over(by_customer)).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+---+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|  n|
+--------+-----------+---------+------+-------------------+-------------------+---+
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|  1|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|  2|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|  1|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|  1|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|  1|
+--------+-----------+---------+------+-------------------+-------------------+---+
```

current is the orders table after the Clean stop: the latest row of each order.

Cost: 2 shuffles.

Common mistake: row_number needs an order to count in, so a window without orderBy raises an error.

```python
current.withColumn(
    "n", F.row_number().over(Window.partitionBy("customer_id"))
).show()
```

Raises: AnalysisException: [WINDOW_FUNCTION_FRAME_NOT_ORDERED] Window function row_number requires the window to be ordered. You need to add an ORDER BY clause. For example: SELECT row_number() OVER (PARTITION BY window_partition ORDER BY window_ordering) FROM table. SQLSTATE: 42601

### How to add a running total in PySpark

```python
running = (
    Window.partitionBy("customer_id")
    .orderBy("ordered_at")
    .rowsBetween(Window.unboundedPreceding, Window.currentRow)
)
current.withColumn(
    "running_total", F.sum("amount").over(running)
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+-------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|running_total|
+--------+-----------+---------+------+-------------------+-------------------+-------------+
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|         25.5|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|         65.5|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|         12.0|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|         NULL|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|         8.75|
+--------+-----------+---------+------+-------------------+-------------------+-------------+
```

Without rowsBetween, rows that tie on the order column all get the same total.

Cost: 2 shuffles.

Common mistake: Without orderBy the window is the whole group, so every row gets the group's full total instead of a running one.

```python
current.withColumn(
    "running_total",
    F.sum("amount").over(Window.partitionBy("customer_id")),
).show()
```

### How to get the value from the previous row (lag) in PySpark

```python
by_time = Window.partitionBy("customer_id").orderBy("ordered_at")
current.withColumn(
    "previous_amount", F.lag("amount").over(by_time)
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+---------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|previous_amount|
+--------+-----------+---------+------+-------------------+-------------------+---------------+
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|           NULL|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|           25.5|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|           NULL|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|           NULL|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|           NULL|
+--------+-----------+---------+------+-------------------+-------------------+---------------+
```

The first row of each group has no previous row, so it gets null.

Cost: 2 shuffles.

Common mistake: Without partitionBy the previous row can belong to another customer, and every row moves to one task.

```python
current.withColumn(
    "previous_amount",
    F.lag("amount").over(Window.orderBy("ordered_at")),
).show()
```

### How to rank rows, with ties in PySpark

```python
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()
```

Prints:

```text
+--------+------+----+----------+
|order_id|amount|rank|dense_rank|
+--------+------+----+----------+
|    1004|  40.0|   1|         1|
|    1004|  40.0|   1|         1|
|    1001|  25.5|   3|         2|
|    1001|  25.5|   3|         2|
|    1002|  12.0|   5|         3|
|    1002|  12.0|   5|         3|
|    1005|  8.75|   7|         4|
|    1003|  NULL|   8|         5|
+--------+------+----+----------+
```

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.

Cost: 1 shuffle.

## Dates and text

### How to turn text into a date or a timestamp in PySpark

```python
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()
```

Prints:

```text
+-------------------+----------+
|                 ts|       day|
+-------------------+----------+
|2026-10-03 14:10:00|2026-10-03|
+-------------------+----------+
```

Use try_to_timestamp to get null instead of an error where some text does not fit the pattern.

Cost: no shuffle.

Common mistake: In Spark 4, text that does not fit the pattern stops the job instead of giving null.

```python
spark.createDataFrame(
    [("03/10/2026 14:10",)], "raw string"
).select(F.to_timestamp("raw", "MM-dd-yyyy")).show()
```

Raises: DateTimeException: [CANNOT_PARSE_TIMESTAMP] Text '03/10/2026 14:10' could not be parsed at index 2. Use `try_to_timestamp` to tolerate invalid input string and return NULL instead. SQLSTATE: 22007

### How to format a date as text in PySpark

```python
orders.withColumn(
    "day", F.date_format("ordered_at", "EEE d MMM yyyy")
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+--------------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|           day|
+--------+-----------+---------+------+-------------------+-------------------+--------------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|Thu 1 Oct 2026|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|Thu 1 Oct 2026|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|Thu 1 Oct 2026|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|Fri 2 Oct 2026|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|Fri 2 Oct 2026|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|Thu 1 Oct 2026|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|Sat 3 Oct 2026|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|Fri 2 Oct 2026|
+--------+-----------+---------+------+-------------------+-------------------+--------------+
```

Cost: no shuffle.

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.

```python
orders.withColumn(
    "day", F.date_format("ordered_at", "YYYY-ww")
).show()
```

Raises: SparkUpgradeException: [INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_PATTERN_RECOGNITION] You may get a different result due to the upgrading to Spark >= 3.0:

### How to count the days between 2 dates, and add days in PySpark

```python
orders.withColumns(
    {
        "days_to_update": F.datediff("updated_at", "ordered_at"),
        "week_later": F.date_add("ordered_at", 7),
    }
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+--------------+----------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|days_to_update|week_later|
+--------+-----------+---------+------+-------------------+-------------------+--------------+----------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|             0|2026-10-08|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|             0|2026-10-08|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|             1|2026-10-08|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|             0|2026-10-09|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|             0|2026-10-09|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|             2|2026-10-08|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|             0|2026-10-10|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|             0|2026-10-09|
+--------+-----------+---------+------+-------------------+-------------------+--------------+----------+
```

datediff takes the later date first. date_add returns a date, without the time.

Cost: no shuffle.

Common mistake: With the earlier date first, every gap comes out negative, and nothing warns you.

```python
orders.withColumn(
    "days_to_update", F.datediff("ordered_at", "updated_at")
).show()
```

### How to round down to the start of a week or month in PySpark

```python
orders.withColumns(
    {
        "week": F.date_trunc("week", "ordered_at"),
        "month": F.trunc("ordered_at", "month"),
    }
).show()
```

Prints:

```text
+--------+-----------+---------+------+-------------------+-------------------+-------------------+----------+
|order_id|customer_id|   status|amount|         ordered_at|         updated_at|               week|     month|
+--------+-----------+---------+------+-------------------+-------------------+-------------------+----------+
|    1001|        C01|   placed|  25.5|2026-10-01 09:15:00|2026-10-01 09:15:00|2026-09-28 00:00:00|2026-10-01|
|    1002|        C02|   placed|  12.0|2026-10-01 10:02:00|2026-10-01 10:02:00|2026-09-28 00:00:00|2026-10-01|
|    1001|        C01|  shipped|  25.5|2026-10-01 09:15:00|2026-10-02 08:00:00|2026-09-28 00:00:00|2026-10-01|
|    1003|        C03|   placed|  NULL|2026-10-02 11:30:00|2026-10-02 11:30:00|2026-09-28 00:00:00|2026-10-01|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|2026-09-28 00:00:00|2026-10-01|
|    1002|        C02|cancelled|  12.0|2026-10-01 10:02:00|2026-10-03 09:00:00|2026-09-28 00:00:00|2026-10-01|
|    1005|        C09|   placed|  8.75|2026-10-03 14:10:00|2026-10-03 14:10:00|2026-09-28 00:00:00|2026-10-01|
|    1004|        C01|   placed|  40.0|2026-10-02 12:45:00|2026-10-02 12:45:00|2026-09-28 00:00:00|2026-10-01|
+--------+-----------+---------+------+-------------------+-------------------+-------------------+----------+
```

Weeks start on Monday. date_trunc returns a timestamp, trunc returns a date, and they take their arguments in opposite orders.

Cost: no shuffle.

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.

```python
orders.withColumn(
    "month", F.trunc("month", "ordered_at")
).show()
```

Raises: AnalysisException: [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column, variable, or function parameter with name `month` cannot be resolved. Did you mean one of the following? [`amount`, `status`, `order_id`, `ordered_at`, `updated_at`]. SQLSTATE: 42703;

### How to change case and trim spaces in PySpark

```python
spark.createDataFrame([("  Asha ",)], "name string").select(
    F.upper(F.trim("name")).alias("name")
).show()
```

Prints:

```text
+----+
|name|
+----+
|ASHA|
+----+
```

Cost: no shuffle.

Common mistake: upper changes only the case. The spaces stay, so "  ASHA " still does not match "ASHA".

```python
spark.createDataFrame([("  Asha ",)], "name string").select(
    F.upper("name").alias("name")
).show()
```

### How to join text columns with a separator in PySpark

```python
customers.select(
    F.concat_ws(", ", "name", "city").alias("label")
).show()
```

Prints:

```text
+---------------+
|          label|
+---------------+
|   Asha, Mumbai|
|    Ben, London|
|Chen, Singapore|
|   Dana, Berlin|
+---------------+
```

concat returns null if any part is null; concat_ws skips the null parts.

Cost: no shuffle.

### How to split text into a list in PySpark

```python
spark.createDataFrame([("a.b.c",)], "path string").select(
    F.split("path", r"\.").alias("parts")
).show()
```

Prints:

```text
+---------+
|    parts|
+---------+
|[a, b, c]|
+---------+
```

Cost: no shuffle.

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 \.

```python
spark.createDataFrame([("a.b.c",)], "path string").select(
    F.split("path", ".").alias("parts")
).show()
```

### How to pull out part of the text with a pattern in PySpark

```python
spark.createDataFrame(
    [("order-1001-EU",), ("refund",)], "ref string"
).select(
    "ref",
    F.regexp_extract("ref", r"order-(\d+)", 1).alias("order_id"),
).show()
```

Prints:

```text
+-------------+--------+
|          ref|order_id|
+-------------+--------+
|order-1001-EU|    1001|
|       refund|        |
+-------------+--------+
```

No match gives an empty string, not null.

Cost: no shuffle.

### How to replace text that matches a pattern in PySpark

```python
spark.createDataFrame(
    [("+44 20-7946-0958",)], "phone string"
).select(
    F.regexp_replace("phone", r"[^0-9]", "").alias("digits")
).show()
```

Prints:

```text
+------------+
|      digits|
+------------+
|442079460958|
+------------+
```

Cost: no shuffle.

## Files

### How to write a CSV file with a header in PySpark

```python
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.

### How to read a CSV with a header and a schema in PySpark

```python
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()
```

Prints:

```text
root
 |-- order_id: integer (nullable = true)
 |-- customer_id: string (nullable = true)
 |-- status: string (nullable = true)
 |-- amount: double (nullable = true)
 |-- ordered_at: timestamp (nullable = true)
 |-- updated_at: timestamp (nullable = true)
```

Without a schema every column is a string; inferSchema guesses the types but reads the files an extra time.

### How to write Parquet, one folder per value of a column in PySpark

```python
orders.write.mode("overwrite").partitionBy("status").parquet(
    "out/orders_parquet"
)
```

### How to read Parquet and keep one partition in PySpark

```python
placed = spark.read.parquet("out/orders_parquet").where(
    F.col("status") == "placed"
)
print(placed.count())
```

Prints:

```text
6
```

A filter on the partition column reads only that folder, so it costs a fraction of a full read.

### How to read JSON lines in PySpark

```python
orders.limit(2).write.mode("overwrite").json("out/orders_json")
spark.read.json("out/orders_json").select(
    "order_id", "amount"
).show()
```

Prints:

```text
+--------+------+
|order_id|amount|
+--------+------+
|    1001|  25.5|
|    1002|  12.0|
+--------+------+
```

spark.read.json expects one JSON object per line; a file that is one big array needs option("multiLine", True).

Cost: no shuffle.

## Questions

### Is this PySpark cheat sheet up to date for Spark 4?

Yes. Every snippet was run on PySpark 4.2.0 and the output shown is what it printed. It uses Spark 4 behaviour, such as ANSI mode being on by default, which changes what a bad cast does.

### Does this PySpark code work on Databricks?

Yes. Databricks runs Apache Spark, so the DataFrame code is the same. Skip the session builder, because a Databricks notebook already has spark, and read from tables or volumes instead of local folders.

### What is the difference between PySpark and pandas?

pandas works on data in one machine's memory. PySpark splits the data across many machines and runs the work lazily, only when you ask for a result such as show, count or write, so it handles data far larger than one machine's memory.

### What is a shuffle in PySpark?

A shuffle is when Spark moves rows between machines so that rows with the same key end up together. dropDuplicates, groupBy, window functions, joins and orderBy cause one; filter, withColumn, fillna and dropna do not. On big data the shuffle is usually the slowest and most costly part of a job, which is why each snippet here shows how many it needs.

### How do I clean data in PySpark?

Drop exact duplicates with dropDuplicates(), keep the latest row per key with row_number over a window ordered newest first, then handle missing values with dropna(subset=[...]) or fillna({...}). In the Clean stop of this page you can add those steps to one pipeline and copy it as a script.

### Can I print this PySpark cheat sheet as a PDF?

Yes. Print the page from your browser and choose Save as PDF. The search, the menus and the outputs' frames are left out of the print, so the snippets fit on fewer pages.

## Sources

- [PySpark API reference (Spark 4.2.0)](https://spark.apache.org/docs/4.2.0/api/python/reference/index.html), Apache Spark
- [ANSI compliance in Spark SQL](https://spark.apache.org/docs/4.2.0/sql-ref-ansi-compliance.html), Apache Spark
- [Performance tuning (join strategy hints, autoBroadcastJoinThreshold)](https://spark.apache.org/docs/4.2.0/sql-performance-tuning.html), Apache Spark
- [RDD programming guide, shuffle operations](https://spark.apache.org/docs/4.2.0/rdd-programming-guide.html#shuffle-operations), Apache Spark