DataFrame basics I · Topic 7 Free
Pattern matching: like()
Same tasks, other language: the SQL version of this topic.
Data model — the tables you will query
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 LIKE patterns you learned in SQL work unchanged — as Column methods.
SELECT title FROM courses WHERE title LIKE '%SQL%';courses.filter(col("title").like("%SQL%")).select("title")Same wildcards: % for any sequence, _ for one character.
courses.filter(col("title").like("Data%")) # starts with
students.filter(col("name").like("%ova")) # ends with
courses.filter(col("title").like("%SQL%")) # containsCase-insensitive: ilike()
job_offers.filter(col("role").ilike("%engineer%"))The friendlier helpers
PySpark also gives you readable shortcuts that need no wildcards:
students.filter(col("name").startswith("Maria"))
students.filter(col("name").contains("Khan"))These map to the same idea as anchored LIKE patterns; many teams prefer them because the intent is obvious.
Negation
Wrap the condition with ~:
courses.filter(~col("title").like("%SQL%"))Same performance note as SQL applies at scale: a pattern that starts with % forces a full scan. On big Spark clusters this shows up as a slow job, so anchored patterns and startswith are your friends.
Practice
Real Python runs in your browser with the PySpark DataFrame API. Solve the tasks, or just experiment.