Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 48 ยท DataFrames and Spark SQL

Columns and Expressions

Learn how Spark DataFrame columns and expressions work: select, withColumn, col, expr, alias, cast, when/otherwise, drop and rename, with pandas stand-ins.

A DataFrame operation such as df.select(df.price * 2) does not multiply anything at the moment you write it. The expression df.price * 2 is a Column object: a small description of a computation that Spark will carry out later, in parallel, on every row. Once that clicks, most of the DataFrame API becomes predictable. You build expressions from columns, literals and functions, then hand them to select, withColumn, filter or groupBy.

This lesson shows how to refer to columns, build new ones, rename and change types, write conditions with when and otherwise, and remove columns, and it gives you a runnable model of the expression idea. It builds on DataFrames: Creating and Inspecting.

Referring to columns

There are several equivalent ways to name a column. They all produce a Column.

Python
from pyspark.sql.functions import col, lit, expr

df.price             # attribute style (breaks for names with spaces or clashes such as df.count)
df["price"]          # bracket style
col("price")         # works anywhere, without a DataFrame in hand

col("price") is the most flexible because it does not need the DataFrame variable, which is handy inside functions and chained calls. lit(5) turns a plain Python value into a constant column. Arithmetic, comparison and logical operators on columns build bigger expressions: col("price") * col("qty"), col("price") > 10, (col("a") > 1) & (col("b") < 2).

Be careful with the logical operators. In PySpark you must use &, | and ~ with parentheses around each side, never Python's and, or and not, because those cannot be overloaded and will raise an error.

select, withColumn and alias

select returns a new DataFrame with exactly the columns you list, which can be existing columns or new expressions.

Python
df.select("name", "price")                                   # existing columns by name
df.select(col("name"), (col("price") * col("qty")).alias("total"))
df.select("*", (col("price") * 1.2).alias("price_with_tax")) # keep everything and add one

alias gives an expression a readable name. Without it, Spark invents one such as (price * qty).

withColumn(name, expression) adds a column, or replaces it if the name already exists:

Python
df2 = (df
    .withColumn("total", col("price") * col("qty"))
    .withColumn("price", col("price").cast("double")))

Each call returns a new DataFrame, so df is unchanged. Chaining a few withColumn calls is fine, but hundreds of them make the plan huge and slow to analyze; build many columns in one select instead.

expr("...") takes a piece of SQL text and parses it into a column, which is useful for SQL-style logic and for selectExpr:

Python
df.select(expr("price * qty AS total"))
df.selectExpr("name", "price * qty AS total", "upper(name) AS shout")
flowchart LR
    A["col('price')"] --> M["multiply"]
    B["col('qty')"] --> M
    M --> AL["alias('total')"]
    AL --> S["select(...)"]
    S --> N["New DataFrame<br/>(a plan, not data)"]

Columns combine into an expression tree; select or withColumn attaches it to a new DataFrame.

A runnable model of Column expressions

PySpark is not available here, so the code below defines a tiny Col class on top of pandas. A Col holds a function and a text description. Operators return new Col objects without computing anything; the computation happens only when select evaluates the expression against a DataFrame. The text description imitates how Spark names expressions. This is a teaching model of the idea, not Spark's implementation.

Python Columns are expressions, evaluated later

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.

Conditions with when and otherwise

when(condition, value) is Spark's CASE WHEN. Chain more when calls for more branches and finish with otherwise for everything else. If you omit otherwise, unmatched rows get null.

Python
from pyspark.sql.functions import when

df.withColumn("grade",
    when(col("score") >= 90, "A")
    .when(col("score") >= 75, "B")
    .otherwise("C"))

Branches are tried in order and the first match wins, so put the most specific condition first. A condition that evaluates to null (for example when score is null) counts as not true, so such rows fall through to otherwise. Nulls are covered in Filtering, Sorting and Deduplicating.

Changing types with cast

cast converts a column to another type, by name ("int", "double", "string", "date") or with a type object:

Python
df.withColumn("age", col("age").cast("int"))

What happens with a value that cannot be converted depends on the ANSI mode setting. With ANSI mode on, which is the default from Spark 4, an invalid cast such as "abc" to int raises an error. With ANSI mode off (the default in Spark 3), it quietly returns null. The functions try_cast (SQL) and try_* helpers always return null instead of failing, which is useful when cleaning dirty data. Casting a decimal number to an integer truncates toward zero, it does not round.

Python when/otherwise and cast, modeled with pandas

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.

drop, rename and immutability

Python
df.drop("tmp", "debug")                      # drop one or more columns; missing names are ignored
df.withColumnRenamed("price", "unit_price")  # rename one column
df.toDF("a", "b", "c")                       # rename all columns at once, by position
df.select(col("price").alias("unit_price"))  # rename while selecting

None of these change df. They return a new DataFrame, which is why you must assign the result (df = df.drop("tmp")) or keep chaining. Forgetting the assignment is one of the most common beginner mistakes: the line runs, nothing complains, and your column is still there.

Rename tips: column names are case-insensitive by default in Spark SQL, names with spaces or dots need backticks in expr strings (`my col`), and a clean snake_case naming scheme saves trouble later.

Python drop, rename and expr-style strings

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.

Real versus stand-in. In Spark, col("price") * 2 is a node in a logical plan that the Catalyst optimizer analyzes, checks against the schema and compiles into efficient JVM code. The Col class here is a toy that stores a Python function and a text label; pandas runs everything eagerly on one machine. The names (select, with_column, alias) and the behavior of immutability, null conditions and ANSI-style cast errors follow Spark.

Adding, renaming and dropping: everyday recipes

A few patterns cover most column work. Constants need lit, because a bare Python value is not a column:

Python
from pyspark.sql import functions as F

df = (df
    .withColumn("source", F.lit("web"))                         # a constant column
    .withColumn("size", F.when(F.col("qty") >= 10, "bulk")
                         .when(F.col("qty") >= 3, "small")
                         .otherwise("single")))                 # conditional column

Renaming after an aggregate. Aggregations name their output columns sum(amount) or count(1), which are awkward to reference later. Alias inside agg, or rename afterwards:

Python
totals = df.groupBy("city").agg(F.sum("amount").alias("total_amount"), F.count("*").alias("orders"))

totals2 = df.groupBy("city").sum("amount").withColumnRenamed("sum(amount)", "total_amount")

Renaming many columns at once. Loop over the names, or rebuild the whole list with toDF:

Python
clean = df.toDF(*[c.strip().lower().replace(" ", "_") for c in df.columns])

# only some columns: a mapping and a select
mapping = {"cust nm": "customer_name", "amt": "amount"}
df = df.select([F.col(f"`{c}`").alias(mapping.get(c, c)) for c in df.columns])

Backticks protect column names that contain spaces or dots. selectExpr does the same work with SQL text: df.selectExpr("amt AS amount", "upper(name) AS name_upper", "*"). Order matters when you keep *: columns appear in the order written, so put * first or last on purpose. To keep only a few columns, select them rather than dropping all the others by name.

String, date and regex functions

The pyspark.sql.functions module has hundreds of column functions; these are the ones used most when cleaning text and dates.

Python
df.select(
    F.upper("name"), F.trim("name"), F.length("name"),
    F.concat_ws("-", "country", "city").alias("place"),            # joins with a separator, skips nulls
    F.split("tags", ",").alias("tag_list"),                        # string to array
    F.regexp_extract("url", r"https?://([^/]+)/", 1).alias("host"),  # group 1 of the first match
    F.regexp_replace("phone", r"[^0-9]", "").alias("digits"),      # remove everything but digits
)

regexp_extract returns an empty string (not null) when the pattern does not match. The regex dialect is Java's; in a raw Python string write backslashes once (r"\d+").

Dates arrive as strings and must be parsed before you can compare or subtract them:

Python
df.select(
    F.to_date("order_date", "yyyy-MM-dd").alias("d"),               # Java-style pattern letters
    F.to_timestamp("ts", "yyyy-MM-dd HH:mm:ss").alias("ts"),
    F.date_format("d", "EEEE").alias("weekday"),                   # back to text
    F.datediff("end", "start").alias("days"),                      # end minus start, in days
    F.date_add("d", 30), F.add_months("d", 1), F.year("d"), F.month("d"),
)

Pattern letters differ from Python's strftime: yyyy year, MM month, dd day, HH hour, mm minute, ss second. A string that does not match the pattern becomes null (or an error under ANSI mode), so always check for unexpected nulls after parsing.

Python String, regex and date functions in plain Python

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.

Common mistakes

  • Using and, or or not instead of &, | and ~, or leaving out the parentheses around each condition.
  • Ignoring the return value: df.withColumn(...) and df.drop(...) do not modify df.
  • Calling withColumn in a long loop; use one select with a list of expressions.
  • Casting dirty strings to numbers without deciding how to handle failures (error versus null).
  • Forgetting that a when without otherwise returns null for unmatched rows.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is col('price') * col('qty')?

    Columns are descriptions of computations. Only a select, withColumn, filter and an action make Spark compute them.
  2. 2. You call df.drop('tmp') and then df.show() still shows the tmp column. Why?

    Every DataFrame operation returns a new DataFrame. You must assign it or chain from it, for example df = df.drop('tmp').
  3. 3. A when(...) chain has no otherwise, and a row matches none of the conditions. What does that row get?

    Without otherwise, unmatched rows get null. The row itself stays in the DataFrame.
  4. 4. With ANSI mode on (default in Spark 4), what does col('x').cast('int') do for the string 'abc'?

    ANSI mode makes invalid casts fail loudly. Use try_cast when you want null instead.
  5. 5. Which is the correct PySpark way to combine two conditions?

    Use &, | and ~ with parentheses around each condition. Python's and/or/not cannot be overloaded by Column.

Summary

  • A Column is an expression describing a computation; col("x"), df["x"], lit(v) and operators build bigger ones.
  • select picks or computes columns, withColumn adds or replaces one, alias names an expression, and expr parses SQL text.
  • when(...).otherwise(...) is a CASE WHEN; unmatched rows get null unless otherwise is given, and a null condition is not true.
  • cast converts types; invalid casts raise errors in ANSI mode and give null otherwise, and decimals truncate when cast to integers.
  • drop, withColumnRenamed and toDF change structure, but always return a new DataFrame, so assign the result.
  • Combine conditions with &, |, ~ and parentheses.

Next: keep only the rows you want in Filtering, Sorting and Deduplicating.