How to query when list of None is value in column in pyspark

user_13846348 0
Viewed 341

I'm new to pyspark and I want to query dataframe when SYMBOL == [] and when SYMBOL == [,]. This is SYMBOL structure.

+------+
|SYMBOL|
+------+
|    []|
|    []|
|    []|
|   [,]|
|    []|
|   [,]|

I try to use something like df.filter(array_contains(df.INFO_CSQ.SYMBOL, None)).show() it can't query and there is warning.

> cannot resolve 'array_contains(`INFO_CSQ`.`SYMBOL`, NULL)' due to data type mismatch: Null typed values cannot be used as arguments;;

I think maybe this problem come from I use None as argument, but I'm no idea to query this one.

However, this is working when there is value as argument for example df.filter(array_contains(df.INFO_CSQ.Allele, "A")).select("INFO_CSQ.Allele").show()

I fill "A" as argument as result would be like this

+------+
|Allele|
+------+
|   [A]|
|   [A]|
|   [A]|
|   [A]|
|   [A]|
pyspark apache-spark-sql apache-spark sql python
1 Answers

If you're on Spark >= 2.4, you can use transform to check whether there are null elements in the array, and filter on the array_max of the transformed result.

If there is at least one null element, the transformed result will have at least one True, and the result of array_max will be True. Otherwise, all elements will return False because all of them are not null, and array_max will return False.

df = spark.createDataFrame([[[None]], [[None, None]], [[None, 1]], [[1,1]]], 'symbol array<int>')

df.show()
+------+
|symbol|
+------+
|    []|
|   [,]|
| [, 1]|
|[1, 1]|
+------+

df.filter('array_max(transform(symbol, x -> x is null))').show()
+------+
|symbol|
+------+
|    []|
|   [,]|
| [, 1]|
+------+

For older Spark versions, UDF can be used:

import pyspark.sql.functions as F

has_null = F.udf(lambda arr: None in arr, 'boolean')

df.filter(has_null('symbol')).show()
+------+
|symbol|
+------+
|    []|
|   [,]|
| [, 1]|
+------+

If you want to specifically check for [] and [,], you can use:

df.filter('symbol in (array(null), array(null, null))').show()
+------+
|symbol|
+------+
|    []|
|   [,]|
+------+
user_141657300
Related