Surfalytics
PySpark course overview

DataFrame basics I · Topic 4 Free

Removing duplicates: distinct()

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.

SQL’s SELECT DISTINCT country becomes a two-step chain in PySpark:

students.select("country").distinct()

First pick the columns, then deduplicate what is left. This order matters — .distinct() looks at whole rows of the current DataFrame, exactly like SQL’s DISTINCT looks at the whole select list.

Several columns

courses.select("category", "difficulty").distinct()

Each unique pair survives — same behavior as SQL.

dropDuplicates()

PySpark has a second method that does the same thing:

students.select("country").dropDuplicates()

dropDuplicates() is more powerful in real Spark — it can take a subset of columns (df.dropDuplicates(["country"])) to deduplicate by some columns while keeping the rest. Remember it exists; you will meet it in data-cleaning work constantly.

Chaining is the PySpark way

Notice the style forming: each method returns a new DataFrame, so you chain steps left to right:

students.select("country").distinct()

This reads as a pipeline: take students → keep one column → deduplicate. SQL says the same thing in a different order. Data engineers usually format long chains one method per line:

(students
    .select("country")
    .distinct())

Practice

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

Sign in to track progress
Previous Built-in functions (F.*) Next Filtering rows: filter()