Surfalytics
PySpark course overview

DataFrame basics I · Topic 1 Free

DataFrames and select()

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.

In SQL you query tables. In PySpark you work with DataFrames — the same tables, wrapped in a Python object with methods. Every operation you learned in SQL has a DataFrame twin.

In this playground the five tables are already loaded as DataFrames: students, cohorts, courses, enrollments, job_offers. It is the exact same dataset as the SQL course.

The same query, two languages

SQL:

SELECT name, country
FROM students;

PySpark:

students.select("name", "country")

.select() picks columns, just like the SELECT list. The DataFrame itself plays the role of FROM.

All columns

In SQL you write SELECT *. In PySpark, the DataFrame already is all columns:

courses          # the whole table
courses.columns  # just the column names, as a Python list

Renaming: alias()

SQL’s hours AS duration_hours becomes:

from pyspark.sql.functions import col

courses.select("title", col("hours").alias("duration_hours"))

Two ways to refer to a column:

  • "hours" — a plain string. Fine when you just pick the column.
  • col("hours") — a Column object. Needed when you transform it: alias, math, comparisons.

In this playground col and F (the functions module) are already imported for you.

Why learn this API?

This one DataFrame API is what you use as Spark in Databricks. The same style powers Snowpark in Snowflake, AWS Glue, and Microsoft Fabric. Learn it once, use it everywhere.

Try it below. End your code with a DataFrame expression — the playground shows it as a table.

Practice

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

Sign in to track progress
Next Expressions and lit()