PySpark Cheat Sheet

The DataFrame operations you reach for every day, each with a short example that runs as written on PySpark 3.5 and a note on the mistake people make with it. Paste the first block into the PySpark.in compiler, then any example after it.

Every example on this page is run automatically on PySpark 3.5.1 and checked against the output shown in it.

Open the free PySpark compilerPractise with graded problems

1. Start a session and create sample data

Every example on this page runs on the two small DataFrames created here, so you can paste them one after another into a notebook. The PySpark.in compiler, Databricks and the pyspark shell already give you a session called spark, so the block only builds one where it is missing; replacing the provided session would hide your temp views from later cells.

from pyspark.sql import SparkSession, functions as F
from pyspark.sql.window import Window

if "spark" not in globals():   # already defined in notebooks and the PySpark.in compiler
    spark = SparkSession.builder.appName("cheat-sheet").getOrCreate()

emp = spark.createDataFrame([
    (1, "Asha",  "Engineering", 92000, "2021-03-15"),
    (2, "Ravi",  "Engineering", 78000, "2022-07-01"),
    (3, "Meera", "Sales",       61000, "2020-11-20"),
    (4, "Kabir", "Sales",       None,  "2023-01-09"),
    (5, "Zoya",  "HR",          55000, "2019-05-30"),
    (6, "Ravi",  "Engineering", 78000, "2022-07-01"),
], "id INT, name STRING, dept STRING, salary INT, joined STRING")

depts = spark.createDataFrame(
    [("Engineering", "Bengaluru"), ("Sales", "Mumbai"), ("Finance", "Pune")],
    "dept STRING, city STRING")

emp.show()

Watch out: Pass the schema as a DDL string ("id INT, name STRING") instead of letting Spark infer it: the types are then exactly what you wrote, and a None in the first row cannot turn a column into the wrong type.

2. Inspect a DataFrame

Look before you transform. These calls show the schema, the size and a statistical summary.

emp.printSchema()
print(emp.columns)
print(emp.count())
emp.describe("salary").show()

Watch out: count() and show() are actions: each one runs a Spark job. printSchema() and columns only read metadata, so they are free.

3. Select, add, rename and cast columns

select() picks or computes columns; withColumn() adds or replaces one; withColumnRenamed() renames. All of them return a new DataFrame: the original never changes. Round computed money values: 92000 * 1.10 is 101200.00000000001 in floating point.

emp.select("name", F.round(F.col("salary") * 1.10, 2).alias("salary_after_raise")).show(3)
emp2 = (emp
    .withColumn("joined", F.to_date("joined"))
    .withColumn("band", F.when(F.col("salary") >= 75000, "senior").otherwise("junior"))
    .withColumnRenamed("dept", "department")
    .drop("id"))
emp2.printSchema()
emp2.show(3)

Watch out: A row with a NULL salary gets "junior" here, because NULL >= 75000 is not true. Test for nulls explicitly (F.col("salary").isNull()) when that distinction matters.

4. Filter rows

filter() and where() are the same method. Combine conditions with & (and), | (or) and ~ (not), and wrap each condition in parentheses.

emp.filter((F.col("dept") == "Engineering") & (F.col("salary") > 80000)).show()
emp.where(F.col("dept").isin("Sales", "HR")).show()
emp.filter(F.col("salary").isNull()).select("name").show()
emp.filter(F.col("name").startswith("R")).select("id", "name").show()

Watch out: Python's and / or do not work on columns and raise an error. Without the parentheses, & binds tighter than ==, so the condition is parsed wrongly.

5. Handle missing values

Decide per column whether a missing value should be replaced, dropped or carried through.

emp.fillna({"salary": 0}).filter(F.col("name") == "Kabir").show()
print(emp.dropna(subset=["salary"]).count())
emp.select("name", F.coalesce("salary", F.lit(-1)).alias("salary_or_flag")).show(4)

Watch out: fillna() only fills columns whose type matches the value: fillna(0) leaves string columns alone, and fillna("unknown") leaves numeric columns alone.

6. Group and aggregate

groupBy() followed by agg() computes several aggregates in one pass. Name every output with alias(), or you get column names like avg(salary).

(emp.groupBy("dept")
    .agg(F.count("*").alias("employees"),
         F.count("salary").alias("with_salary"),
         F.round(F.avg("salary"), 0).alias("avg_salary"),
         F.countDistinct("name").alias("distinct_names"))
    .orderBy("dept")
    .show())

Watch out: count("*") counts rows; count("salary") skips NULLs, which is why Sales shows 2 employees but 1 salary. avg() also ignores NULLs rather than treating them as zero.

7. Join DataFrames

Join on a column name (a string or a list) to keep one copy of the key. The how argument picks the join type.

emp.join(depts, on="dept", how="left").select("name", "dept", "city").orderBy("id").show()
# departments with no employees, and employees whose department is not listed
depts.join(emp, "dept", "left_anti").show()
emp.join(depts, "dept", "left_anti").select("name", "dept").show()
# hint Spark to copy the small table to every executor instead of shuffling both sides
emp.join(F.broadcast(depts), "dept").explain()

Watch out: left_anti answers "rows with no match" without a NULL check after a left join, and it never multiplies rows when the right side has duplicate keys.

8. Window functions

A window computes a value per row from a group of related rows, without collapsing them the way groupBy() does: rankings, running totals, the previous row.

w = Window.partitionBy("dept").orderBy(F.desc_nulls_last("salary"), "id")
ranked = (emp
    .withColumn("rank_in_dept", F.row_number().over(w))
    .withColumn("prev_salary", F.lag("salary").over(w))
    .withColumn("running_total", F.sum("salary").over(
        w.rowsBetween(Window.unboundedPreceding, Window.currentRow))))
ranked.select("dept", "name", "salary", "rank_in_dept", "prev_salary", "running_total").show()

# top earner per department
ranked.filter(F.col("rank_in_dept") == 1).select("dept", "name").show()

Watch out: Add a tie-breaker ("id" above) to the ordering. Without one, row_number() may number tied rows differently from one run to the next.

9. Sort and remove duplicates

orderBy() sorts the whole DataFrame; dropDuplicates() keeps one row per combination of the columns you list.

emp.orderBy(F.desc_nulls_last("salary"), "name").select("name", "salary").show()
print(emp.count(), emp.dropDuplicates(["name", "dept", "salary"]).count())
emp.select("dept").distinct().orderBy("dept").show()

Watch out: dropDuplicates() keeps an arbitrary row of each group. To keep a specific one (the latest, say), rank with row_number() over a window and filter on rank 1.

10. Strings and dates

Use the built-in functions in pyspark.sql.functions: they run inside the JVM and are much faster than Python code.

(emp.select(
    F.upper("name").alias("upper"),
    F.concat_ws(" - ", "name", "dept").alias("label"),
    F.year(F.to_date("joined")).alias("join_year"),
    F.date_format(F.to_date("joined"), "MMM yyyy").alias("joined_month"),
    F.datediff(F.lit("2026-01-01"), F.to_date("joined")).alias("days_to_2026"))
 .show(2, truncate=False))

Watch out: to_date() without a format expects yyyy-MM-dd. For other layouts pass the pattern, for example F.to_date("d", "dd/MM/yyyy"). With Spark 3's default settings an unparseable value becomes NULL; with ANSI mode on (the default from Spark 4) it raises an error instead.

11. Spark SQL on a DataFrame

Register a DataFrame as a temporary view and query it with SQL. Both APIs compile to the same plan, so choose whichever reads better.

emp.createOrReplaceTempView("emp")
spark.sql("""
    SELECT dept, COUNT(*) AS employees, MAX(salary) AS top_salary
    FROM emp
    GROUP BY dept
    HAVING COUNT(*) > 1
    ORDER BY dept
""").show()

Watch out: A temp view lives only as long as the SparkSession and is visible only to it.

12. User-defined functions, and when not to write one

A Python UDF lets you run any Python on each value, but every row is shipped from the JVM to a Python worker and back. Reach for one only when no built-in function does the job.

from pyspark.sql.types import StringType

initials = F.udf(lambda s: s[0] + "." if s else None, StringType())
emp.select("name", initials("name").alias("udf"),
           F.concat(F.substring("name", 1, 1), F.lit(".")).alias("built_in")).show(2)

Watch out: The built-in version gives the same answer, needs no Python worker and is visible to the optimizer. A UDF is a black box Spark cannot push filters through.

13. Read and write files

Readers and writers share one pattern: format, options, path. In the PySpark.in compiler, /tmp is a private scratch folder for your session.

path = "/tmp/pyspark-cheat-sheet/emp_parquet"
emp.write.mode("overwrite").partitionBy("dept").parquet(path)
back = spark.read.parquet(path)
print(back.count(), back.columns)

emp.write.mode("overwrite").option("header", True).csv("/tmp/pyspark-cheat-sheet/emp_csv")
csv = (spark.read
    .option("header", True)
    .schema("id INT, name STRING, dept STRING, salary INT, joined DATE")
    .csv("/tmp/pyspark-cheat-sheet/emp_csv"))
csv.printSchema()

Watch out: Give the reader an explicit schema for anything you run more than once: inferSchema reads the data an extra time and can guess differently when the file changes. partitionBy() moves the column into the folder names, which is why dept comes back last.

14. Partitions, caching and the query plan

Transformations are lazy: Spark only builds a plan until an action runs it. These tools show and shape what that plan does.

print(emp.rdd.getNumPartitions())
print(emp.repartition(4, "dept").rdd.getNumPartitions())
print(emp.repartition(4).coalesce(2).rdd.getNumPartitions())

busy = emp.filter(F.col("salary") > 60000).cache()
print(busy.count())   # first action fills the cache
busy.groupBy("dept").count().explain()

Watch out: repartition() always shuffles and can raise or lower the count; coalesce() only merges partitions, so it is cheaper but can only lower it. Call unpersist() when a cached DataFrame is no longer needed.

15. Bring results back to Python

collect(), first() and take() return Row objects to the driver. Use them on small results only.

top = emp.orderBy(F.desc_nulls_last("salary")).first()
print(top["name"], top.salary)
names = [r.name for r in emp.select("name").distinct().collect()]
print(sorted(names))

Watch out: collect() on a large DataFrame copies all of it into the driver's memory and is a common cause of out-of-memory failures. Aggregate or limit() first.