Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 36 ยท Spark and DataFrames

DataFrame Basics: Reading, Selecting and Writing

Learn the core PySpark DataFrame operations in Databricks: reading tables and files, schemas, select, filter, withColumn, renaming, null handling and writing results.

A Spark DataFrame is a distributed table: named columns with types, split into partitions across a cluster. You describe what you want (read, filter, add a column) and Spark runs it in parallel. If you know pandas, the ideas will feel familiar, but there are important differences: Spark DataFrames are immutable (every operation returns a new DataFrame) and lazy (nothing runs until an action; see Transformations, actions and laziness).

Real PySpark code is shown read-only below. The runnable examples use pandas in your browser as a stand-in: the operations have the same shape, but pandas runs on one machine and its method names differ in places. Differences are called out.

Reading data

Python
# from a Unity Catalog table (most common)
orders = spark.read.table("main.sales.orders")

# from files in a volume
csv_df = (spark.read
          .option("header", True)
          .option("inferSchema", True)
          .csv("/Volumes/main/default/landing/orders.csv"))

json_df = spark.read.json("/Volumes/main/default/landing/events/")
parquet_df = spark.read.parquet("/Volumes/main/default/landing/parquet/")

inferSchema makes Spark scan the data to guess types, which is convenient but slow and sometimes wrong. For production files, give an explicit schema:

Python
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, DoubleType

schema = StructType([
    StructField("order_id", IntegerType(), False),
    StructField("city", StringType(), True),
    StructField("amount", DoubleType(), True),
])
orders = spark.read.schema(schema).option("header", True).csv("/Volumes/main/default/landing/orders.csv")
orders.printSchema()

You can also build small DataFrames from Python data, which is handy for tests:

Python
df = spark.createDataFrame(
    [(1, "Oslo", 10.5), (2, "Lima", 7.0)],
    schema="order_id INT, city STRING, amount DOUBLE",
)

Looking at the data

Python
display(orders)              # interactive table (Databricks only)
orders.show(5)               # text table
orders.printSchema()         # column names and types
orders.columns               # list of names
orders.count()               # an action: runs a job
orders.describe("amount").show()   # count, mean, stddev, min, max

Selecting, filtering and adding columns

Python
from pyspark.sql import functions as F

result = (orders
    .select("order_id", "city", "amount")
    .filter(F.col("amount") > 5)
    .withColumn("amount_with_tax", F.round(F.col("amount") * 1.2, 2))
    .withColumnRenamed("city", "customer_city")
    .orderBy(F.col("amount").desc()))

Key points:

  • F.col("name") refers to a column; df["name"] and df.name also work.
  • withColumn adds a column or replaces one with the same name.
  • Several conditions combine with &, | and ~, each wrapped in parentheses: (F.col("a") > 1) & (F.col("b") == "x"). Using Python's and/or is a common error.
  • F.when(cond, value).otherwise(other) is Spark's if/else for columns.

Handling nulls

Missing values are NULL. Comparisons with NULL give NULL (not true), so filters drop those rows silently.

Python
orders.filter(F.col("city").isNull())
orders.na.drop(subset=["amount"])
orders.na.fill({"city": "unknown", "amount": 0.0})
orders.withColumn("city", F.coalesce("city", F.lit("unknown")))

Writing results

Python
(result.write
    .mode("overwrite")                 # append, overwrite, ignore, errorifexists
    .saveAsTable("main.sales.big_orders"))

Tables saved this way default to Delta format in Databricks (see Delta Lake basics). Files can also be written as Parquet, CSV or JSON to a volume path.

Stand-in: the same flow with pandas

The stand-in below mirrors the select, filter, new column, rename and sort steps, then handles nulls. Notice that pandas executes each step immediately, unlike Spark.

Python Stand-in (pandas): select, filter, derive, rename, sort

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Stand-in (pandas): nulls and conditional columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Spark versus pandas: differences to remember

Topic Spark DataFrame pandas DataFrame
Where it runs Cluster, partitioned One machine, in memory
Execution Lazy Eager
Row order Not guaranteed unless sorted Index order
Index None Has an index
Mutation Immutable Mutable
Size Larger than memory is fine Must fit in memory

You can convert between them for small results: df.toPandas() (pulls everything to the driver, so limit first) and spark.createDataFrame(pandas_df). Databricks also offers the pandas API on Spark (pyspark.pandas) for pandas-style code that runs distributed.

Common mistakes

  • Using Python and/or instead of &/| with parentheses.
  • Expecting a DataFrame to change in place: df.withColumn(...) alone does nothing unless you assign the result.
  • Leaving inferSchema on for large files in production.
  • Calling toPandas() on millions of rows.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A Spark DataFrame is...

    Each transformation returns a new DataFrame, and work happens only when an action runs.
  2. 2. How do you combine two column conditions in a filter?

    Column expressions use & and | with parentheses because Python's and/or cannot be overloaded.
  3. 3. What does df.withColumn("x", F.lit(1)) do if you do not assign the result?

    DataFrames are immutable, so you must keep the returned DataFrame.
  4. 4. Why give an explicit schema when reading production CSV files?

    inferSchema scans the data and may guess wrong. An explicit schema is faster and predictable.

Summary

  • DataFrames are immutable, lazy, distributed tables; read from tables, files or Python data.
  • Use select, filter, withColumn, F.when and na helpers to shape data.
  • Combine conditions with & and | in parentheses; assign the results of transformations.
  • Write results with saveAsTable or file writers; pandas stand-ins mirror the shapes but run eagerly.

Next: Transformations, Actions and Lazy Evaluation