How to Split Multiple Array Columns into Rows in PySpark?

To split multiple array columns into rows in PySpark, you can make use of the `explode` function. This function generates a new row for each element in the specified array or map column, effectively “flattening” the structure. Let’s go through a detailed explanation and example code to help you understand this better.

Contents hide

1 Example: Splitting Multiple Array Columns into Rows

1.1 Output

2 Additional Considerations

2.1 Handling Unequal Arrays

2.2 Using `posexplode`

3 About Editorial Team

4 You Might Also Like:

Example: Splitting Multiple Array Columns into Rows

Suppose you have a PySpark DataFrame with multiple array columns, and you want to split each element of these arrays into separate rows.


from pyspark.sql import SparkSession
from pyspark.sql.functions import explode

# Initialize SparkSession
spark = SparkSession.builder.appName("example").getOrCreate()

# Sample DataFrame
data = [
    (1, ['a', 'b', 'c'], [10, 20]),
    (2, ['d', 'e'], [30]),
    (3, [], [40, 50, 60])
]
df = spark.createDataFrame(data, ["id", "array_col1", "array_col2"])

# Show initial DataFrame
df.show()

# Explode array columns
df_exploded = df.select(
    df.id,
    explode(df.array_col1).alias('exploded_col1'),
    explode(df.array_col2).alias('exploded_col2')
)

df_exploded.show()

This code initializes a sample DataFrame and then uses the `explode` function to transform the array columns into rows.

Output

Initial DataFrame:


+---+----------------+-----------+
| id|       array_col1| array_col2|
+---+----------------+-----------+
|  1|    [a, b, c]|    [10, 20]|
|  2|          [d, e]|       [30]|
|  3|                []|[40, 50, 60]|
+---+----------------+-----------+

DataFrame after exploding array columns:


+---+------------+------------+
| id|exploded_col1|exploded_col2|
+---+------------+------------+
|  1|           a|           10|
|  1|           b|           20|
|  2|           d|           30|

You might notice that rows with unequal or empty arrays cause mismatches. Therefore, additional logic or pre-processing may be necessary depending on the exact nature of your data and requirements.

Additional Considerations

Handling Unequal Arrays

If your array columns have unequal lengths, you might encounter issues when exploding them simultaneously. You may need to adjust your logic to ensure that the array lengths are equal, or handle the resulting `null` values appropriately.

Using `posexplode`

You can also use the `posexplode` function, which provides you with the positional index of each element. This can be particularly useful for more complex transformations.


from pyspark.sql.functions import posexplode

df_pos_exploded = df.select(
    df.id,
    posexplode(df.array_col1).alias('pos1', 'exploded_col1'),
    posexplode(df.array_col2).alias('pos2', 'exploded_col2')
)

df_pos_exploded.show()

This code snippet shows how to use `posexplode` to include positional indices for each element in the exploded arrays.

About Editorial Team

Our Editorial Team is made up of tech enthusiasts who are highly skilled in Apache Spark, PySpark, and Machine Learning. They are also proficient in Python, Pandas, R, Hive, PostgreSQL, Snowflake, and Databricks. They aren't just experts; they are passionate teachers. They are dedicated to making complex data concepts easy to understand through engaging and simple tutorials with examples.