41. Capstone 1 — clean a messy orders feed

Time to put it together. Raw orders has every problem real data throws at you: a duplicated row, missing values, and columns that need derived fields. Your job: turn it into a clean, analysis-ready table.

The brief

Produce a cleaned orders DataFrame that:

1. Removes the duplicate row (order 1021 appears twice).
2. Fills missing country with the string "unknown".
3. Drops rows with a missing quantity (we won't guess a quantity).
4. Adds a line_total = quantity * unit_price, rounded to 2.
5. Keeps only: order_id, product, category, country, quantity, line_total.

Every step is something you've done in Modules 1–3 — the skill here is sequencing them into one clean pipeline.

Working it out

Sketch the chain before writing it:

(orders
 .dropDuplicates()                    # 1. de-dupe
 .na.fill({"country": "unknown"})     # 2. fill nulls
 .filter(F.col("quantity").isNotNull())  # 3. drop missing quantity
 # 4. add line_total
 # 5. select the final columns
).printSchema()

Order matters a little: de-dupe first (so nothing downstream double- counts), then fill/drop, then derive. Watch the row count fall from 41 → 40 (dedupe) → 38 (two null quantities dropped).

Your turn

Build the full cleaned DataFrame described above. Assign to result.

result = orders  # <- dedupe, fill country, drop null quantity, add line_total, select 6 cols
result.show()

result = (
    orders.dropDuplicates()
    .na.fill({"country": "unknown"})
    .filter(F.col("quantity").isNotNull())
    .withColumn("line_total", F.round(F.col("quantity") * F.col("unit_price"), 2))
    .select("order_id", "product", "category", "country", "quantity", "line_total")
)