How to extract an element from an array in PySpark

Question

I have a data frame with following type:

col1|col2|col3|col4
xxxx|yyyy|zzzz|[1111],[2222]

I want my output to be of the following type:

col1|col2|col3|col4|col5
xxxx|yyyy|zzzz|1111|2222

My col4 is an array, and I want to convert it into a separate column. What needs to be done?

I saw many answers with flatMap, but they are increasing a row. I want the tuple to be put in another column but in the same row.

The following is my current schema:

root
 |-- PRIVATE_IP: string (nullable = true)
 |-- PRIVATE_PORT: integer (nullable = true)
 |-- DESTINATION_IP: string (nullable = true)
 |-- DESTINATION_PORT: integer (nullable = true)
 |-- collect_set(TIMESTAMP): array (nullable = true)
 |    |-- element: string (containsNull = true)

Also, can please someone help me with explanation on both dataframes and RDD's.

What's the schema of your data frame? Can you show df.printSchema()? — akuiper
– akuiper, Commented Jul 22, 2017 at 13:26
Does all cells in the array column have the same number of elements? Always 2? What if another row have three elements in the array? — akuiper
– akuiper, Commented Jul 22, 2017 at 13:47
No all the elements have exactly 2 elements. Because the element in the array are a start date and end date. — AnmolDave
– AnmolDave, Commented Jul 22, 2017 at 13:56
Also this is my actual requirment if you can help me with it. stackoverflow.com/questions/45252906/… — AnmolDave
– AnmolDave, Commented Jul 22, 2017 at 14:00

pault · Accepted Answer · 2018-10-25 13:58:57Z

67

Create sample data:

from pyspark.sql import Row
x = [Row(col1="xx", col2="yy", col3="zz", col4=[123,234])]
rdd = sc.parallelize([Row(col1="xx", col2="yy", col3="zz", col4=[123,234])])
df = spark.createDataFrame(rdd)
df.show()
#+----+----+----+----------+
#|col1|col2|col3|      col4|
#+----+----+----+----------+
#|  xx|  yy|  zz|[123, 234]|
#+----+----+----+----------+

Use getItem to extract element from the array column as this, in your actual case replace col4 with collect_set(TIMESTAMP):

df = df.withColumn("col5", df["col4"].getItem(1)).withColumn("col4", df["col4"].getItem(0))
df.show()
#+----+----+----+----+----+
#|col1|col2|col3|col4|col5|
#+----+----+----+----+----+
#|  xx|  yy|  zz| 123| 234|
#+----+----+----+----+----+

edited Oct 25, 2018 at 13:58

pault

43.7k17 gold badges121 silver badges161 bronze badges

answered Jul 22, 2017 at 14:00

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

desertnaut Over a year ago

@Lydia please be extremely careful and sure you know what you are doing when altering code: your edit had ruined a perfectly good answer, leading it to throw an exception (restored it to OP's original)...

Amesys Over a year ago

Do you have a way to generalize the iteration over the original col4's array ?

Union find Over a year ago

@Amesys Did you try destructuring a list comprehension ?

jgtrz Over a year ago

I have a follow-up question, dropping the link, thanks in advance! stackoverflow.com/questions/61823544/… @Psidom

haneulkim Over a year ago

What is efficient way to apply this to more than 10 columns but each column has only one item in list

Mykola Zotko · Accepted Answer · 2022-05-26 20:48:26Z

You have 4 options to extract the value inside the array:

df = spark.createDataFrame([[1, [10, 20, 30, 40]]], ['A', 'B'])
df.show()

+---+----------------+
|  A|               B|
+---+----------------+
|  1|[10, 20, 30, 40]|
+---+----------------+

from pyspark.sql import functions as F

df.select(
    "A",
    df.B[0].alias("B0"), # dot notation and index        
    F.col("B")[1].alias("B1"), # function col and index
    df.B.getItem(2).alias("B2"), # dot notation and method getItem
    F.col("B").getItem(3).alias("B3"), # function col and method getItem
).show()

+---+---+---+---+---+
|  A| B0| B1| B2| B3|
+---+---+---+---+---+
|  1| 10| 20| 30| 40|
+---+---+---+---+---+

In case you have many columns, use a list comprehension:

df.select(
    'A', *[F.col('B')[i].alias(f'B{i}') for i in range(4)]
).show()

+---+---+---+---+---+
|  A| B0| B1| B2| B3|
+---+---+---+---+---+
|  1| 10| 20| 30| 40|
+---+---+---+---+---+

Collectives™ on Stack Overflow

How to extract an element from an array in PySpark

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related