← PySpark roadmap

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

FunctionWhat it doesExample
F.col(name) / F.lit(v)Refer to a column / a constantF.col('amount') * F.lit(1.18)
F.when(c, v).otherwise(v)If / elseF.when(F.col('m')>=90,'A').otherwise('B')
F.expr(sql)Write a SQL expressionF.expr('amount * qty')
F.concat / F.concat_wsJoin stringsF.concat_ws(' ', 'first', 'last')
F.upper / F.lower / F.initcapChange caseF.initcap('name')
F.trim / F.ltrim / F.rtrimRemove spacesF.trim('city')
F.substring(c, pos, len)Part of a string (1-based)F.substring('phone', 1, 3)
F.length(c)String lengthF.length('name')
F.split(c, pattern)String → arrayF.split('tags', ',')
F.regexp_replace / regexp_extractRegex replace / captureF.regexp_extract('email', '@(.*)', 1)
F.lpad / F.rpadPad stringsF.lpad('id', 6, '0')
F.round / F.ceil / F.floorRoundingF.round('amount', 2)
F.abs / F.sqrt / F.powMathF.pow('x', 2)
F.rand() / F.monotonically_increasing_id()Random value / unique idsalting, row ids
F.current_date / current_timestampNowF.current_date()
F.to_date / F.to_timestampParse text to date/timeF.to_date('d', 'dd-MM-yyyy')
F.date_format(c, fmt)Date → textF.date_format('d', 'yyyy-MM')
F.year / month / dayofmonth / dayofweekExtract partsF.year('order_date')
F.datediff / F.months_betweenDifferencesF.datediff(F.current_date(), 'd')
F.date_add / date_sub / add_monthsShift datesF.date_add('d', 7)
F.date_trunc(unit, c)Truncate to month/week...F.date_trunc('month', 'ts')
F.coalesce(a, b)First non-nullF.coalesce('phone', F.lit('NA'))
F.isnull / Column.isNull()Null testF.col('email').isNull()
F.array / F.array_containsBuild / search arraysF.array_contains('tags', 'sql')
F.explode / explode_outerOne row per array itemF.explode('items')
F.size / F.sort_array / array_distinctArray helpersF.size('items')
F.struct / F.create_mapBuild nested columnsF.struct('a', 'b')
F.from_json / F.to_jsonParse / write JSONF.from_json('raw', schema)
F.get_json_object(c, path)Read one JSON fieldF.get_json_object('raw', '$.id')
F.count / sum / avg / min / maxAggregatesF.sum('amount').alias('rev')
F.countDistinct / approx_count_distinctDistinct countsF.approx_count_distinct('user')
F.collect_list / collect_setGather values into an arrayF.collect_set('city')
F.first / F.lastFirst / last value in groupF.first('name', ignorenulls=True)
F.row_number / rank / dense_rankWindow rankingF.row_number().over(w)
F.lag / F.leadPrevious / next rowF.lag('amount').over(w)
F.broadcast(df)Hint a broadcast joinbig.join(F.broadcast(small), 'id')
F.md5 / F.sha2 / F.hashHashingF.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 NaNF.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.

Practise PySpark with the AI mentor

Lesson test

Take an 8-question test on “Functions Reference: Column & Row Transformations” — concepts, output prediction, bug spotting and interview questions.

Can't find a PySpark topic you need for exams or placements? Request it and we'll add it.