How to get the index of value of a column in another column of ArrayType() in pyspark?

Question

I'm using spark 2.4.
I have an ArrayType(StringType()) column and a StringType() column in a spark dataframe. I need to find the position of the StringType() column in the ArrayType(StringType()) column.

Sample Input:

+---------------+---------+
|arrayCol       |stringCol|
+---------------+---------+
|['a', 'b', 'c']|'b'      |
+---------------+---------+
|['a', 'b', 'c']|'d'      |
+---------------+---------+

Sample Output:

+---------------+---------+-----+
|arrayCol       |stringCol|Index|
+---------------+---------+-----+
|['a', 'b', 'c']|'b'      |2    |
+---------------+---------+-----+
|['a', 'b', 'c']|'d'      |null |
+---------------+---------+-----+

I have tried array_position but it's not working and I'm getting "Column is not iterable" error.
I have also tried combining expr, transform, and array_position, but I'm wondering if there's a solution that doesn't need using expr.
Thanks :)

notNull · Accepted Answer · 2020-09-04 19:52:42Z

8

Try with expr with array_position function.

Example:

df.show()
#+---------+---------+
#| arrayCol|stringCol|
#+---------+---------+
#|[a, b, c]|        b|
#|[a, b, c]|        d|
#+---------+---------+

from pyspark.sql.functions import *
df.withColumn("Index",expr('if(array_position(arrayCol,stringCol)=0,null,array_position(arrayCol,stringCol))')).\
show()
#+---------+---------+-----+
#| arrayCol|stringCol|Index|
#+---------+---------+-----+
#|[a, b, c]|        b|    2|
#|[a, b, c]|        d| null|
#+---------+---------+-----+

answered Sep 4, 2020 at 19:52

notNull

31.8k4 gold badges41 silver badges58 bronze badges

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

1 Comment

s510 Over a year ago

any reason why expr works and not the function call directly?

Collectives™ on Stack Overflow

How to get the index of value of a column in another column of ArrayType() in pyspark?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related