Topic 7 of 28 · Intermediate
Functions Reference: Column & Row Transformations
The built-in column functions every data engineer uses, grouped by purpose.
Concept
Import them once with from pyspark.sql import functions as F. They work on columns, run inside the JVM and are optimised by Spark — always prefer them to Python UDFs.
They cover columns and literals, conditions, strings, numbers, dates, nulls, arrays/maps/JSON, aggregates and windows.
Most functions accept a column name string or a Column object.
Cheat sheet
| Function | What it does | Example |
|---|---|---|
| F.col(name) / F.lit(v) | Refer to a column / a constant | F.col('amount') * F.lit(1.18) |
| F.when(c, v).otherwise(v) | If / else | F.when(F.col('m')>=90,'A').otherwise('B') |
| F.expr(sql) | Write a SQL expression | F.expr('amount * qty') |
| F.concat / F.concat_ws | Join strings | F.concat_ws(' ', 'first', 'last') |
| F.upper / F.lower / F.initcap | Change case | F.initcap('name') |
| F.trim / F.ltrim / F.rtrim | Remove spaces | F.trim('city') |
| F.substring(c, pos, len) | Part of a string (1-based) | F.substring('phone', 1, 3) |
| F.length(c) | String length | F.length('name') |
| F.split(c, pattern) | String → array | F.split('tags', ',') |
| F.regexp_replace / regexp_extract | Regex replace / capture | F.regexp_extract('email', '@(.*)', 1) |
| F.lpad / F.rpad | Pad strings | F.lpad('id', 6, '0') |
| F.round / F.ceil / F.floor | Rounding | F.round('amount', 2) |
| F.abs / F.sqrt / F.pow | Math | F.pow('x', 2) |
| F.rand() / F.monotonically_increasing_id() | Random value / unique id | salting, row ids |
| F.current_date / current_timestamp | Now | F.current_date() |
| F.to_date / F.to_timestamp | Parse text to date/time | F.to_date('d', 'dd-MM-yyyy') |
| F.date_format(c, fmt) | Date → text | F.date_format('d', 'yyyy-MM') |
| F.year / month / dayofmonth / dayofweek | Extract parts | F.year('order_date') |
| F.datediff / F.months_between | Differences | F.datediff(F.current_date(), 'd') |
| F.date_add / date_sub / add_months | Shift dates | F.date_add('d', 7) |
| F.date_trunc(unit, c) | Truncate to month/week... | F.date_trunc('month', 'ts') |
| F.coalesce(a, b) | First non-null | F.coalesce('phone', F.lit('NA')) |
| F.isnull / Column.isNull() | Null test | F.col('email').isNull() |
| F.array / F.array_contains | Build / search arrays | F.array_contains('tags', 'sql') |
| F.explode / explode_outer | One row per array item | F.explode('items') |
| F.size / F.sort_array / array_distinct | Array helpers | F.size('items') |
| F.struct / F.create_map | Build nested columns | F.struct('a', 'b') |
| F.from_json / F.to_json | Parse / write JSON | F.from_json('raw', schema) |
| F.get_json_object(c, path) | Read one JSON field | F.get_json_object('raw', '$.id') |
| F.count / sum / avg / min / max | Aggregates | F.sum('amount').alias('rev') |
| F.countDistinct / approx_count_distinct | Distinct counts | F.approx_count_distinct('user') |
| F.collect_list / collect_set | Gather values into an array | F.collect_set('city') |
| F.first / F.last | First / last value in group | F.first('name', ignorenulls=True) |
| F.row_number / rank / dense_rank | Window ranking | F.row_number().over(w) |
| F.lag / F.lead | Previous / next row | F.lag('amount').over(w) |
| F.broadcast(df) | Hint a broadcast join | big.join(F.broadcast(small), 'id') |
| F.md5 / F.sha2 / F.hash | Hashing | F.sha2(F.concat_ws('|', *cols), 256) |
| F.isnan(c) | True for NaN (not the same as null) | F.isnan('score') |
| F.nanvl(a, b) | b when a is NaN | F.nanvl('score', F.lit(0.0)) |
Code (PySpark 3.5)
from pyspark.sql import functions as F
clean = (raw
.withColumn("email", F.lower(F.trim("email")))
.withColumn("domain", F.regexp_extract("email", "@(.*)$", 1))
.withColumn("order_date", F.to_date("order_date", "dd-MM-yyyy"))
.withColumn("month", F.date_format("order_date", "yyyy-MM"))
.withColumn("days_since", F.datediff(F.current_date(), "order_date"))
.withColumn("phone", F.coalesce("phone", F.lit("NA")))
.withColumn("tag", F.explode_outer(F.split("tags", ",")))
.withColumn("row_hash", F.sha2(F.concat_ws("|", "email", "order_date"), 256)))
summary = clean.groupBy("month").agg(
F.countDistinct("email").alias("customers"),
F.collect_set("domain").alias("domains"),
F.round(F.sum("amount"), 2).alias("revenue"))Remember
- Import as F and never shadow Python's sum/max.
- Built-in functions beat UDFs.
- Spark date formats use Java patterns (yyyy-MM-dd), not Python's %Y.
Interview questions
explode vs explode_outer?
explode drops rows whose array is null or empty; explode_outer keeps them with a null value.
collect_list vs collect_set?
collect_list keeps duplicates in arrival order; collect_set keeps unique values with no guaranteed order.
Lesson test
Take an 8-question test on “Functions Reference: Column & Row Transformations” — concepts, output prediction, bug spotting and interview questions.