Filter Rows, Split Strings and Explode Arrays in PySpark

1.Keeping only the rows you want: filter and where

Column methods change a DataFrame's width. Row operations change its length, and the simplest one keeps only the rows that satisfy a condition. filter(condition) and where(condition) are interchangeable, and both return a new DataFrame. The condition can take two forms: a Column of BooleanType, such as df.age > 3 or col("c_custkey") == 412449, or a string of SQL, such as "age > 3". Both forms give the same rows.

To combine conditions on Columns, use & for AND and | for OR, and put each comparison in its own parentheses, as the reference does. The Databricks guide shows both, for example (col("c_nationkey") == 20) & (col("c_acctbal") > 1000).

df.filter((df.age > 3) & (df.subject == "Physics")).show()
# +---+----+-------+
# |age|name|subject|
# +---+----+-------+
# |  5| Bob|Physics|
# +---+----+-------+

Sources12

2.Splitting a string column into an array with split

Splitting changes a column's shape rather than the number of rows. split(str, pattern, limit) lives in pyspark.sql.functions rather than on the DataFrame, and it returns a Column whose value is an array of the separated strings. You usually wrap it in select or withColumn. The pattern is a Java regular expression, not a literal delimiter. In the example below, '[ABC]' splits on any of the three capital letters. This matters for characters such as . or |, which have special meanings in a regex.

from pyspark.sql import functions as dbf
df = spark.createDataFrame([('oneAtwoBthreeC',)], ['s',])
df.select('*', dbf.split(df.s, '[ABC]')).show()
df.select('*', dbf.split(df.s, '[ABC]', 2)).show()
df.select('*', dbf.split('s', '[ABC]', -2)).show()

limit sets how many times the pattern is applied. If limit > 0, the array has at most limit entries, and the last entry holds everything after the last match. If limit <= 0, the pattern is applied as many times as possible and the array can be any size. Recent versions also accept a column or column name for pattern and limit, so each row can use its own values.

Sources3

3.Turning array elements into rows: explode and explode_outer

An array column, whether split produced it or it came from the source data, can be turned into rows. explode(col) returns one new row for each element of an array, or for each entry of a map. The new column is called col for arrays, or key and value for maps, unless you set your own names with alias. The input in the reference example has three rows: i=1 with [1, 2, 3, NULL], i=2 with an empty array, and i=3 with a NULL array.

df.select('*', sf.explode('a')).show()

+---+---------------+----+
|  i|              a| col|
+---+---------------+----+
|  1|[1, 2, 3, NULL]|   1|
|  1|[1, 2, 3, NULL]|   2|
|  1|[1, 2, 3, NULL]|   3|
|  1|[1, 2, 3, NULL]|NULL|
+---+---------------+----+

Losing those rows can be a quiet bug. Customers with no orders, for example, disappear from the result. explode_outer behaves the same way, except that an empty or NULL array produces a single row with NULL in the element column, so rows 2 and 3 survive. posexplode_outer does the same and adds a pos column with each element's position.

df.select('*', sf. ? ('a')).show()

One more rule matters: you can use only one explode per SELECT clause. To explode two arrays, chain two select calls and alias each result.

import pyspark.sql.functions as sf
df = spark.sql('SELECT ARRAY(1,2) AS a1, ARRAY(3,4,5) AS a2')
df.select(
    '*', sf.explode('a1').alias('v1')
).select('*', sf.explode('a2').alias('v2')).show()

If the array holds structs, explode it, alias the result, and then use select("s.*") to spread the struct's fields into ordinary columns. The reference example df.select(sf.explode('a').alias("s")).select("s.*") turns an array of {a, b} structs into a two-column table with one row per struct.

Sources45

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.explode keeps every input row, filling in NULL when the array is empty or NULL.Why is that wrong?

   explode produces no row for an empty or NULL array, so those input rows disappear. explode_outer is the function that keeps them with a NULL element.

   Covered in Turning array elements into rows: explode and explode_outer

2. 2.Two array columns can be exploded side by side in the same select.Why is that wrong?

   Only one explode is allowed per SELECT clause. Chain a second select to explode the other array.

   Covered in Turning array elements into rows: explode and explode_outer

3. 3.split's pattern is a literal delimiter string.Why is that wrong?

   The pattern is a Java regular expression, so characters with a regex meaning are interpreted as regex, not as literal text.

   Covered in Splitting a string column into an array with split