Surfalytics
PySpark course overview

DataFrame basics I · Topic 6 Free

NULLs, ranges, and isin()

Same tasks, other language: the SQL version of this topic.

Data model — the tables you will query
N 1 N 1 N 1 N 1 cohorts id PK INT name TEXT start_date DATE format TEXT students id PK INT name TEXT city TEXT country TEXT cohort_id FK INT background TEXT joined_at DATE courses id PK INT title TEXT category TEXT difficulty TEXT hours INT enrollments id PK INT student_id FK INT course_id FK INT enrolled_at DATE completed_at DATE score INT job_offers id PK INT student_id FK INT company TEXT role TEXT city TEXT salary_usd INT remote BOOLEAN offer_date DATE

How the tables join

Relationship Cardinality Join condition
students.cohort_id → cohorts.id many students → one cohort JOIN cohorts ON students.cohort_id = cohorts.id
enrollments.student_id → students.id many enrollments → one student JOIN students ON enrollments.student_id = students.id
enrollments.course_id → courses.id many enrollments → one course JOIN courses ON enrollments.course_id = courses.id
job_offers.student_id → students.id many job offers → one student JOIN students ON job_offers.student_id = students.id

In short: each student belongs to one cohort. Students enroll in courses through enrollments, and their job offers land in job_offers.

The same three tools you learned in SQL — IS NULL, BETWEEN, IN — in DataFrame form.

Missing values

NULL in SQL is None-like in Spark, and the same trap exists: col("city") == None silently matches nothing. The correct tests:

# real PySpark
students.filter(col("city").isNull())
students.filter(col("city").isNotNull())

# the SQL-string form — also real PySpark, and what this playground uses
students.filter("city IS NULL")
students.filter("city IS NOT NULL")

Playground note

This playground’s engine does not support the .isNull() method yet — use the SQL-string form .filter("city IS NULL"). In real Spark both work; knowing both is useful anyway.

Substituting a default for NULL works like SQL’s COALESCE:

students.select(F.coalesce(col("city"), lit("unknown")).alias("city"))

Ranges

Real PySpark has .between(90000, 110000). It is only a shorthand for two comparisons — which is what we use here:

job_offers.filter((col("salary_usd") >= 90000) & (col("salary_usd") <= 110000))

Inclusive on both ends, same as SQL’s BETWEEN.

Membership: isin()

SQL’s IN ('a', 'b', 'c') maps directly:

students.filter(col("city").isin("Vancouver", "Toronto", "Calgary"))

Negate it with ~:

students.filter(~col("city").isin("Vancouver", "Toronto"))

Same warning as SQL: be careful mixing isin negation with NULLs — rows where city is NULL match neither the positive nor the negative test.

Practice

Real Python runs in your browser with the PySpark DataFrame API. Solve the tasks, or just experiment.

Sign in to track progress
Previous Filtering rows: filter() Next Pattern matching: like()