Pyspark agg max function showing different result

user_14582309 0
Viewed 37

I was just studying some pyspark code and didnt understand these particular lines. I have a python code such as below:

emp = [("James", "Java", "4"),
("James", "R", "4"),
("James", "Python", "1"),
("Michael", "Java", "2"),
("Michael", "PHP", "4"),
("Michael", "PHP", "2"),
("Robert", "C#", "3"),
("Robert", "Java", "4"),
("Robert", "R", "1"),
("Washington", "Java", "2")
       ]
empColumns = ["name", "booksInterested", "id"]
empDF = spark.createDataFrame(data=emp, schema=empColumns)
empDF.show()
w = Window.partitionBy('id')
empDF = empDF.withColumn('e_array', F.collect_list('booksInterested').over(w))
empDF.show(truncate=False)
empDF = empDF.agg(F.max('e_array').alias('new_array'))
empDF.show(truncate=False)

When showing empDF after

empDF = empDF.agg(F.max('e_array').alias('new_array'))

Isn't it supposed to show the longest list? It is showing [Python , R] as the output ? I dont understand how is this output coming?

pyspark python-3.x python
1 Answers

Pyspark's max function returns

the maximum value of the expression in a group

When used on string columns (array columns are internally converted to strings), it returns the maximum value according to alphabetical order: therefore, since all rows in e_array start with a square bracket [, the maximum value for that column will be the one that starts with the last letter in alphabetical order, i.e. the P of Python in this case.

In order to retrieve the longest array in your column, you need to use Pyspark's size function and then filter based on its values:

# calculate size column
empDF = empDF.withColumn('size', F.size('e_array'))
# compute maximum size
max_size = empDF.agg(F.max('size')).collect()[0][0]
print(max_size)
# 4

# show rows with longest arrays
empDF.filter(F.col('size') == max_size).show(truncate=False)

+-------+---------------+---+--------------------+----+
|name   |booksInterested|id |e_array             |size|
+-------+---------------+---+--------------------+----+
|James  |Java           |4  |[Java, R, PHP, Java]|4   |
|James  |R              |4  |[Java, R, PHP, Java]|4   |
|Michael|PHP            |4  |[Java, R, PHP, Java]|4   |
|Robert |Java           |4  |[Java, R, PHP, Java]|4   |
+-------+---------------+---+--------------------+----+
user_74654620
Related