Home / Learn / Data & AI / Data at Scale / Lesson 2.2
All lessons

Module 2: Spark and distributed processing · Lesson 2.2 · about 30 min

PySpark DataFrames, hands on

Run Spark on your laptop, then read, clean, join, group and write data with PySpark and Spark SQL, next to the SQL and pandas you already know.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 2 · about 30 minutes · read first: 2.1. You’ll be able to run PySpark on your own computer, read, filter, join, group and write data, use Spark SQL and window functions, and avoid the habits that crash real jobs.

PySpark DataFrames are SQL tables you build with Python: the same filter, join and group by you already know, run lazily across many cores or many machines.

1. Why this matters

Most Spark work in job postings is PySpark DataFrames, and most of it is moves you already know from SQL or pandas, written slightly differently. Learning the translation takes an afternoon. What takes longer is the habits: never pulling big data to one machine, preferring built-in functions, and writing files in a layout others can query cheaply. This lesson does both.

2. Set up in five minutes

PySpark runs on one laptop in local mode: the driver and executors are threads on your machine. Same code, smaller scale, like rehearsing a play in your living room before the theatre. You need Python and Java (Spark 4 needs Java 17 or 21; check the PySpark install page for the current list).

python -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install pyspark pandas
python -c "import pyspark; print(pyspark.__version__)"

No Java, or a locked-down work laptop? Use a free Google Colab notebook and run !pip install pyspark, or another option from Free sandboxes.

3. Make some data

from pyspark.sql import SparkSession, functions as F, Window
spark = SparkSession.builder.appName("retail").master("local[*]").getOrCreate()

import random, datetime as dt
rows = [(i, random.randint(1, 20), random.randint(1, 500),
         str(dt.date(2026, 10, 1) + dt.timedelta(days=random.randint(0, 30))),
         round(random.uniform(1, 40), 2), random.choice([1, 1, 1, 2, -1]))
        for i in range(200_000)]
sales = spark.createDataFrame(rows, "txn_id long, store_id int, sku int, sale_date string, price double, qty int")
stores = spark.createDataFrame([(s, f"Store {s}", random.choice(["ON", "QC", "BC"])) for s in range(1, 21)],
                               "store_id int, store_name string, province string")
sales.show(5); sales.printSchema()

master("local[*]") means “use every core on this machine”. qty = -1 is a refund: a fault planted on purpose, as in real till data.

4. The core moves, next to what you know

Task PySpark SQL you know
Keep rows sales.filter(F.col("qty") > 0) WHERE qty > 0
Pick columns .select("store_id", "price") SELECT store_id, price
New column .withColumn("revenue", F.col("price") * F.col("qty")) price * qty AS revenue
Fix a type .withColumn("sale_date", F.to_date("sale_date")) CAST(sale_date AS DATE)
Remove duplicates .dropDuplicates(["txn_id"]) QUALIFY ROW_NUMBER() ... = 1
Join .join(stores, "store_id", "left") LEFT JOIN stores USING (store_id)
Group .groupBy("province").agg(F.sum("revenue").alias("rev")) GROUP BY province
Sort and peek .orderBy(F.desc("rev")).show(10) ORDER BY rev DESC LIMIT 10

Watch one till row go through a typical cleaning chain. Nothing runs until the last step, because only that one is an action.

Read. The row arrives as text: txn 1042, store 7, sku 88, "2026-10-14", price 3.50, qty 2. The date is still a string.
withColumn (narrow). to_date turns the string into a real date, and revenue = price × qty adds 7.00. The row never leaves its partition.
filter (narrow). qty > 0 is true, so the row stays. A refund row with qty −1 would be dropped here, or better, flagged and kept in its own table.
join (small table, broadcast). Store 7 is looked up in the 20-row stores table, which Spark copied to every executor, so the row gains province = ON without moving.
groupBy + write (wide, then action). The row is shuffled to the worker that owns the key (ON, 2026-10-14) and added to that day's total. write is the action that makes all of this actually happen.

Put together in code:

clean = (sales
  .withColumn("sale_date", F.to_date("sale_date"))
  .withColumn("is_refund", F.col("qty") < 0)
  .withColumn("revenue", F.col("price") * F.col("qty")))

by_prov = (clean.join(stores, "store_id", "left")
  .groupBy("province", "sale_date")
  .agg(F.sum("revenue").alias("net_revenue"),
       F.countDistinct("txn_id").alias("txns"),
       F.sum(F.when(F.col("is_refund"), 1).otherwise(0)).alias("refunds")))

by_prov.orderBy("province", "sale_date").show(10)
by_prov.explain()   # read the plan: 'Exchange' means a shuffle; look for 'BroadcastHashJoin'

Row counts are the first thing to check after each step. A join type you did not think about can silently drop or multiply rows.

5. Spark SQL: same engine, SQL words

Many teams write mostly SQL. Register a view and query it:

clean.createOrReplaceTempView("sales")
spark.sql("""
  SELECT store_id, sale_date, SUM(revenue) AS rev,
         SUM(SUM(revenue)) OVER (PARTITION BY store_id ORDER BY sale_date) AS running_rev
  FROM sales GROUP BY store_id, sale_date
""").show(5)

The DataFrame version of the same window:

w = Window.partitionBy("store_id").orderBy("sale_date")
daily = clean.groupBy("store_id", "sale_date").agg(F.sum("revenue").alias("rev"))
daily.withColumn("running_rev", F.sum("rev").over(w)).show(5)

Both become the same plan. Use whichever your team reads more easily.

6. Write it like a real pipeline

(clean.write.mode("overwrite")
   .partitionBy("sale_date")
   .parquet("out/sales_clean"))

spark.read.parquet("out/sales_clean").filter("sale_date = '2026-10-15'").count()

Open the out/sales_clean folder: one sub-folder per date, named like sale_date=2026-10-15. A query for one date reads one folder, like going straight to one drawer of a filing cabinet. In the cloud the path becomes gs://my-bucket/sales_clean/ and nothing else changes.

Parquet stores each column together, compressed, with its type written in the file. A query that needs two of twenty columns reads about a tenth of the data. CSV makes the engine read every character of every row and guess the types.

SparkSession
Your connection to Spark; every DataFrame starts from it.
DataFrame
A table with named, typed columns, spread over partitions.
F (functions)
Spark's built-in column functions: sum, when, to_date and hundreds more. They run inside the engine, fast.
partitionBy
Write one folder per value of a column, so later readers can skip folders.
Physical meaning
A grocery chain's "net sales by province and day" report is exactly the chain above: read the till files, fix types, flag refunds, look up the store, group, write. On a laptop it handles 200,000 rows; on a cluster the same code handles 2 billion.

“I’ll just call toPandas() and finish the analysis in pandas.” On a sample, fine. On real data, toPandas() and collect() pull every row to the driver, one machine, and crash it or run for hours. Use show(), limit() or aggregate first and then convert the small result. And prefer built-in F. functions to Python UDFs (@udf): built-ins run inside the engine and are many times faster. If you truly need Python, use a pandas UDF (@pandas_udf), which works on whole batches.

7. Practice

Using the data above:

  1. Count refunds per store and find the store with the highest refund rate (refunds ÷ transactions).
  2. Find each store’s best day by net revenue using a window and F.row_number().
  3. Write the result as Parquet partitioned by province, read it back and confirm the row count matches.
One solution for step 2, and what to check
w = Window.partitionBy("store_id").orderBy(F.desc("rev"))
(daily.withColumn("rn", F.row_number().over(w))
      .filter("rn = 1").orderBy("store_id").show())

What to check: step 2 returns exactly 20 rows (one per store), and the count you read back in step 3 equals the count you wrote.

New words: local mode, SparkSession, DataFrame, temp view, window function, partitionBy, partition pruning, Parquet, UDF.

  • PySpark DataFrame moves map one-to-one onto SQL: filter, select, withColumn, join, groupBy, orderBy.
  • Transformations are lazy; show, count and write are actions that run the plan. Use explain() to see shuffles and join types.
  • Check row counts after every step: an inner join can silently drop unmatched rows.
  • Write Parquet with partitionBy on the column people filter by, so readers skip folders.
  • Never collect() or toPandas() big data, and prefer built-in functions to Python UDFs.

Check yourself

1Why is df.collect() on a 500 GB DataFrame dangerous?
2What does partitionBy("sale_date") do when writing Parquet?
3Why prefer Parquet over CSV for big data?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.