PySpark select and filter: Columns, Rows and Null Values (Part 1 of 3)

Before you aggregate or join, shape the rows you care about and find out what is missing. This post follows Part 1 of the video series on querying data with PySpark in Databricks Free Edition: select, filter, withColumn, sorting, and counting nulls, all on eight fictional orders. This part is read-only. It creates no schemas and writes no tables.

Watch on YouTube

The notebook, source file and cheat sheet are in the Part 1 folder on GitHub. Use a Python notebook on Serverless compute and run the four code cells in order.

Why this step comes first

select chooses columns. filter (or its alias where) keeps rows that match a condition. withColumn returns a new DataFrame with a derived field and leaves the previous one unchanged, so you can keep a raw view and a cleaned view side by side.

Nulls are not a crash. They signal that something upstream was incomplete. If you skip this step, groupBy and SQL will still run and will happily summarize bad inputs.

The sample data

Eight fictional orders with an explicit schema: order ID, customer, region, category, quantity, unit price and order date. Customer, quantity and unit price are nullable on purpose.

orders = spark.createDataFrame([...], schema).withColumn(
    "order_total",
    (F.col("quantity") * F.col("unit_price")).cast("decimal(12,2)")
)

When either input is missing, order_total is null. Prices use decimal types so currency math stays exact. For IDs 101 through 108, the totals are 20, 25, 25.50, 30, 12, null, null and 30.

Keep valid rows, then filter by business rule

Valid revenue rows need both quantity and unit price. Build that filter first, then add a high-value flag:

valid = orders.filter(
    F.col("quantity").isNotNull() & F.col("unit_price").isNotNull()
).withColumn(
    "is_high_value", F.col("order_total") >= F.lit(Decimal("25.00"))
)

booksnorthwest = (valid .select("orderid", "customer", "region", "category", "ordertotal", "ishighvalue") .where((F.col("category") == "Books") & (F.col("region").isin("NORTH", "WEST"))) .orderBy("order_id")) ```

Six rows are valid. Books in NORTH or WEST returns orders 101 and 103, totaling 45.50.

Keep the validity rule separate from the business rule. When someone asks why a row disappeared, you can point at one clear condition. Notice that order 105 stays valid even though its customer is null. A missing customer is a different quality problem from a missing price.

Sort with a tiebreaker

high_value = (valid
    .filter(F.col("is_high_value"))
    .select("order_id", "customer", "region", "category", "order_total")
    .orderBy(F.col("order_total").desc(), F.col("order_id").desc()))

The result is 108, 104, 103 and 102, totaling 110.50. Orders 108 and 104 both total 30, and the secondary sort on order_id descending breaks the tie. Sorting controls how results are displayed. It does not change the data.

Make nulls visible before cleaning

Print the schema, summarize the numeric columns, and count nulls explicitly:

orders.printSchema()
orders.select("quantity", "unit_price", "order_total").summary(
    "count", "min", "max", "mean"
).show()

nullcounts = orders.select( F.count(F.when(F.col("customer").isNull(), 1)).alias("nullcustomer"), F.count(F.when(F.col("quantity").isNull(), 1)).alias("nullquantity"), F.count(F.when(F.col("unitprice").isNull(), 1)).alias("nullunitprice"), ) ```

The actual run found one missing customer, one missing quantity and one missing unit price. The summary showed seven non-null quantities, seven non-null unit prices and six non-null order totals.

Together, the schema, the summary and the null counts make a lightweight data quality checklist. Cleaning is a separate business decision: fill, drop, quarantine, or reject with a reason. This part only makes the gaps visible.

Practice challenge

From the valid rows, select Toys in SOUTH or NORTH, keep orderid, customer and ordertotal, and order by total descending. Predict first: 108 and 104 at 30, then 102 at 25. If you get a different answer, check whether you included rows with a missing quantity or price.

Key takeaways

• select, filter and withColumn each return a new DataFrame.
• Write the validity rule separately from the business filter.
• Add a secondary sort so ties display predictably.
• Count nulls with explicit when expressions before you decide how to clean.

Next, Part 2 aggregates revenue with groupBy and ranks rows with window functions.

Check your understanding

1. What does withColumn return?

• A) The same DataFrame, modified in place
• B) A new DataFrame with the added or replaced column
• C) A Python list
• D) A saved table

2. Why is order 105 still counted as a valid revenue row?

• A) Its customer is null, but quantity and price are present
• B) Its total is above 25
• C) It is in the NORTH region
• D) Nulls are ignored by filters

3. What is the purpose of the secondary sort on order_id descending?

• A) It filters duplicates
• B) It breaks ties so rows with the same total display predictably
• C) It speeds up the query
• D) It changes the stored data

4. How does the lesson count nulls in a column?

• A) F.count(F.when(F.col("x").isNull(), 1))
• B) F.sum("x")
• C) orders.count()
• D) F.max("x")

5. If either quantity or unit price is null, what is order_total?

• A) 0
• B) The non-null value
• C) Null
• D) An error

Answer key

1. B. DataFrames are immutable, so the original is unchanged.
2. A. The validity rule only requires quantity and unit price.
3. B. Orders 108 and 104 both total 30, and the tiebreaker orders them.
4. A. count of a when expression counts only the matching rows.
5. C. Arithmetic with a null returns null.